Page Setup Excel

Silahkan klik menu File à Page Setup. Terdapat 4 tab utama pada Page Setup ini.

Tab Page

Pada tab Page ini, terdapat 5 kotak utama sebagai berikut:

1. Orientation, yang digunakan untuk memilih jenis/format halaman yang akan digunakan, yakni landscape (horizontal) and portrait (vertikal).

2. Scaling, digunakan untuk memperbesar atau memperkecil tampilan worksheet atau range yang kita pilih saat hendak di print atau print preview. Pilihlah adjust to 100% jika hendak melihat tampilan aslinya. Atau kita bisa juga memilih check box “Fit to” untuk membuat beberapa halaman menjadi satu halaman atau lebih.

3. Paper size, digunakan untuk memilih jenis atau ukuran kertas, diantaranya Letter, Legal, A4 atau size lainnya yang terdapat pada print yang diinstall.

4. Print quality, digunakan untuk memilih resolusi kualitas print pada worksheet yang sedang aktif dibuka. Resolusi adalah ukuran untuk menentukan berapa banyak titik (dot) per 1 inch2, atau disingkat juga DPI (dots per linear inch). Resolusi ini akan disesuaikan dengan printer yang diinstall pada PC kita. Semakin besar resolusi yang digunakan, maka akan semakin bagus kualitas cetakan yang dihasilkan, dan hal ini akan lebih terasa saat mencetak gambar atau grafik (chart).

5. First page number, digunakan untuk menetapkan nomor halaman pertama. Jika kita isi dengan nilai 1, maka halaman akan dimulai dari halaman 1.

Tab Margins

Tab margins digunakan untuk menset beberapa pilihan margin, diantaranya adalah:

1. Top, Bottom, Left, dan Right. Top digunakan untuk menset seberapa jauh jarak antara sisi paling atas kertas dengan isi utama pada worksheet kita. Bottom digunakan untuk menset seberapa jauh jarak antara sisi paling bawah kertas dengan isi utama pada worksheet kita. Left digunakan untuk menset seberapa jauh jarak antara sisi paling kiri kertas dengan isi utama pada worksheet kita. Right digunakan untuk menset seberapa jauh jarak antara sisi paling kanan kertas dengan isi utama pada worksheet kita. Isilah margin yang kita inginkan, kemudian tekan tombol Preview untuk melihat hasilnya.

2. Header dan Footer. Header digunakan untuk mengisi nilai seberapa jauh jarak dari sisi paling atas kertas. Nilai header harus lebih kecil dari Top. Sedangkan Footer digunakan untuk mengisi nilai seberapa jauh jarak dari sisi paling bawah kertas. Nilai Footer harus lebih kecil dari Bottom.

3. Center on page, digunakan untuk menset letak/posisi pertengahan data pada worksheet kita saat di print atau print preview. Pilihlah check box Vertically, jika kita ingin data kita diletakkan pada posisi paling tengah dalam mode vertikal. Pilihlah check box Horizontally, jika kita ingin data kita diletakkan pada posisi paling tengah dalam mode horizontal. Atau pilihlah keduanya (Vertically dan Horizontally) jika kita ingin data kita diletakkan pada posisi paling tengah dalam mode vertikal dan horizontal secara sekaligus.

Tab Header/Footer

Header digunakan untuk menampilkan data yang selalu terlihat pada sisi atas setiap halaman worksheet kita. Sedangkan Footer digunakan untuk menampilkan data yang selalu terlihat pada sisi bawah setiap halaman worksheet kita. Kita dapat memilih jenis atau isi header dan footer yang kita inginkan pada combo box header dan footer yang tersedia secara built-in. Jika kita menginginkan format yang lain, bisa dengan cara klik tombol “Custom Header” atau “Custom Footer”.

Tab Sheet

Terdapat 4 kotak utama pada tab Sheet ini, diantaranya adalah:

1. Print area, digunakan untuk memilih area yang akan kita print. Sebagai contoh, dalam beberapa halaman yang terdapat pada worksheet kita, inginnya hanya data dari sell A1 sampai dengan sell C10 saja yang di print. Maka kita dapat memilih area A1:C10 tersebut pada print area ini.

2. Print titles, digunakan untuk membuat judul worksheet sehingga ia akan akan ditampilkan pada setiap halaman saat berada pada mode print preview atau print. Pilihlah “Rows to repeat” jika kita ingin baris tertentu ditampilkan pada setiap halaman. Dan pilihlah “Columns to repeat” jika kita ingin baris tertentu ditampilkan pada setiap halaman. Fasilitas ini sangat berguna manakala dalam table yang kita buat, judul kolom inginnya selalu ditampilkan pada setiap halaman, sehingga kita tidak perlu membuat judul kolom pada setiap halaman.

3. Print, digunakan untuk menentukan format atau tampilan worksheet saat di print preview atau di print. Terdapat beberapa pilihan, diantaranya mode gridlines, yakni setiap grid akan ikut ditampilkan. Mode berwarna (color) atau hitam putih (black and white), dan lain-lain. Kita juga dapat menghilangkan tampilan setiap error dengan cara mengisi nilai combo box Cell error as: .

4. Page order, digunakan untuk mengurutkan setiap halaman yang akan di print atau print preview, apakah dalam mode “Down, then over” atau dalam mode “Over, then down”. Ini dapat disesuaikan dengan format penulisan setiap halaman yang sudah kita lakukan, namun umumnya orang lebih banyak menulis menggunakan mode “Down, then over”.

More about → Page Setup Excel

Security System Excel

Di dalam satu file Excel, terdapat 6 object yang dapat kita proteksi, yakni:

1. File Excel (xls)

2. Workbook

3. Sheet/Worksheet

4. Cell

5. Range

6. VBA Project

7. Procedure/Macro di dalam VBA Project

Di dalam satu file Excel terdapat sebuah workbook dan sebuah VBA project. Workbook adalah object yang di dalamnya terdiri dari beberapa sheet, dan di dalam sheet terdapat banyak cell, sedangkan kumpulan dari beberapa cell disebut juga range.

VBA project adalah kumpulan dari beberapa module dan class module, sedangkan di dalam module dan class module terdapat beberapa procedure. VBA project dapat diakses dengan menekan tombol ALT+F11 secara bersamaan. Umumnya, orang sering menyebut VBA project di dalam Excel ini dengan sebutan Macro.

System Security untuk File Excel (xls)

Penggunaan system security di dalam file Microsoft Excel (xls) mempunyai banyak kegunaan. Penerapannya adalah dengan menggunakan password, yakni kumpulan karakter tertentu yang berupa gabungan antara huruf, angka, dan simbol yang harus dimasukkan saat membuka file Excel tersebut. Umumnya password file Excel digunakan untuk mengamankan data yang disimpan di dalamnya agar tidak dapat dibuka/diubah oleh pihak yang tidak berkepentingan. Pengamanan ini juga diperlukan pada saat pengiriman file melalui elektronik mail (e-mail), serta mengurangi resiko file dibuka/diubah oleh yang tidak berhak sebelum sampai ke tujuan.

Kepada pihak yang di perbolehkan membuka/mengubah data tersebut harap memberitahukan password yang telah di buat melalui lisan ataupun media lainnya (tidak bersamaan dengan pengiriman file). Atau bahkan untuk yang sifatnya rutin dapat dibuat password permanen yang sudah disepakati bersama.

Ketika kita membuat password, hafalkanlah password tersebut dan kalau perlu simpan data password nya ditempat yang aman. Sebab lupa akan password membuat file Excel menjadi tidak dapat dibuka lagi.

Password dapat terdiri dari berbagai kombinasi antara huruf, angka, simbol dan spasi. Semua kombinasi password ini maksimal sebanyak 15 karakter. Password dalam Microsoft Excel mempunyai sensitivitas yang baik, sehingga ketika kita membuat password ini menggunakan huruf kecil, maka kitapun harus membuka file Excel dengan menggunakan password yang menggunakan huruf kecil pula. Jadi besar kecilnya masing-masing karakter pada password harap dihafal pula.

Beberapa metode untuk memproteksi file Excel dari orang yang tidak berhak (unauthorized person)

Beberapa metode dalam memproteksi file Excel dari orang yang tidak berhak diantaranya adalah:

1. Password untuk membuka file Excel. Dalam kondisi ini, user hanya dapat membuka file Excel setelah ia memasukkan password. Pada metode ini, user masih dapat mengcopy ataupun mengedit isi didalam file Excel, hanya saja file Excelnya tidak dapat di-Save (Read-Only). Seandainya ia ingin men-Save file Excel, maka nama file Excel harus berbeda dengan nama aslinya, dengan kata lain, ia haru melakukan proses Save-As. Cara menggunakan metode ini:

* Buka file Excel yang hendak di-protect.

* Pada menu File, klik Save As.

* Pada menu Tools dalam kotak dialog Save As, klik General Options.

* Pada field Password to open, ketikkan password yang Anda inginkan, kemudian klik tombol OK (lihat gambar di bawah).

* Maka akan muncul input box “Confirm password”, lalu ketik ulang password yang telah diisi pada field Password to open, kemudian klik OK.

* Klik Save setelah selesai.

2. Password untuk memodifikasi file Excel. Dalam kondisi ini, user dapat membuka dan memodifikasi file Excel setelah ia memasukkan password. Jika ia melakukan beberapa perubahan didalamnya, ia dapat langsung men-Save file Excel. Cara menggunakan metode ini:

* Buka file Excel yang hendak di-protect.

* Pada menu File, klik Save As.

* Pada menu Tools dalam kotak dialog Save As, klik General Options.

* Pada field Password to modify, ketikkan password yang Anda inginkan, kemudian klik tombol OK.

* Maka akan muncul input box “Confirm password”, lalu ketik ulang password yang telah diisi pada field Password to modify, kemudian klik OK.

* Klik Save setelah selesai.

3. Membuka file Excel hanya secara Read-Only dan tanpa password. Dalam kondisi ini, user dapat membuka file Excel tanpa memasukkan password. Pada metode ini, user masih dapat mengcopy ataupun mengedit isi didalam file Excel, hanya saja file Excel yang dibuka tidak dapat di-Save (Read-Only). Seandainya ia ingin men-Save file Excel, maka nama file Excel harus berbeda dengan nama aslinya. Cara menggunakan metode ini :

* Buka file Excel yang hendak di-protect.

* Pada menu File, klik Save As.

* Pada menu Tools dalam kotak dialog Save As, klik General Options.

* Beri tanda checklist pada check box “Read-only recommended”, kemudian klik tombol OK.

* Klik Save setelah selesai.

4. Password untuk membuka dan memodifikasi file Excel. Dalam kondisi ini, user dapat membuka file Excel setelah memasukkan password dan sekaligus dapat memodifikasi file Excel setelah memasukkan password, dimana password untuk membuka dan password untuk memodifikasi bisa berbeda. Jadi user yang membuka file ini akan menemukan 2 kotak dialog Password, dimana password yang pertama adalah untuk membuka file Excel (secara Read-Only) dan password yang kedua adalah untuk memodifikasi file Excel, sehingga jika ada perubahan pada file Excel, file Excel dapat langsung di Save tanpa harus berbeda nama filenya. Cara menggunakan metode ini:

* Buka file Excel yang hendak di-protect.

* Pada menu File, klik Save As.

* Pada menu Tools dalam kotak dialog Save As, klik General Options.

* Pada field Password to open, ketikkan password yang Anda inginkan, kemudiaN klik tombol OK.

* Pada field Password to modify, ketikkan password yang Anda inginkan, kemudian klik tombol OK.

* Maka akan muncul input box “Confirm Password”, lalu ketik ulang password yang telah diisi pada field Password to open, kemudian klik OK.

* Maka akan muncul input box “Confirm Password”, lalu ketik ulang password yang telah diisi pada field Password to modify, kemudian klik OK.

* Klik Save setelah selesai.

System Security untuk Workbook

Dengan menerapkan system security pada workbook, maka user tidak dapat melakukan beberapa hal, diantaranya adalah menambah dan menghapus sheet, menampilkan sheet yang di-hidden (disembunyikan), mengubah ukuran/size window (meminimize, restore, dan maximize Excel window), bahkan meng-unhide (menampilkan) kembali workbook yang telah di-hidden. Untuk meng-hidden workbook, terdapat pada menu Window à Hide.

Untuk menerapkan system security pada workbook, silahkan ikuti beberapa langkah sebagai berikut:

1. Klik menu Tools à Protection à Protect Workbook.

2. Beri tanda centang pada check box Structure dan Window. Dengan memberi tanda centang pada check box Structure, maka user tidak dapat menambah dan menghapus sheet, dan menampilkan sheet yang di-hidden (disembunyikan). Dengan memberi tanda centang pada check box Window, maka user tidak dapat mengubah ukuran/size window (meminimize, restore, dan maximize Excel window), bahkan meng-unhide (menampilkan) kembali workbook yang telah di-hidden.

3. Masukan passwordnya, lalu klik OK.

4. Masukan kembali password pada langkah sebelumnya, lalu klik OK.

System Security untuk Sheet/Worksheet

Cara menggunakan metode ini:

1. Buka workbook yang hendak di-protect.

2. Pada menu Tools, klik Protection à Protect Sheet.

3. Beri tanda centang pada check box yang Anda inginkan.

5. Masukan passwordnya, lalu klik OK.

4. Masukan kembali password pada langkah sebelumnya, lalu klik OK.

System Security untuk Cell

Dengan menerapkan system security untuk cell, maka cell tersebut menjadi bisa ataupun tidak bisa diedit datanya. Secara default, setiap cell senantiasa di lock, yakni tidak dapat di edit isi/data di dalam cell nya. Namun, kita bisa mensetting sendiri, mana cell yang perlu kita lock, dan mana yang boleh diedit. Caranya, klik dahulu cell yang Anda inginkan, lalu klik menu Format à Cells. Masuk ke tab Protection, lalu beri tanda centang pada check box Locked jika Anda ingin data pada cell tersebut tidak dapat diedit oleh user, atau hilangkan tanda centang pada check box Locked jika Anda ingin data pada cell tersebut dapat diedit oleh user. Anda juga dapat menghide formula, yakni dengan cara memberi tanda centang pada check box Hidden. Penting! System security untuk cell ini hanya dapat aktif jika worksheet tersebut sudah diset system securitynya. Silahkan lihat kembali sub bab System Security untuk Sheet/Worksheet.

System Security untuk Range

Dengan menerapkan system security untuk range, maka range tersebut menjadi bisa ataupun tidak bisa diedit datanya. Secara default, setiap range senantiasa di lock, yakni tidak dapat di edit isi/data di dalam cell nya. Namun, kita bisa mensetting sendiri, mana range yang perlu kita lock, dan mana yang boleh diedit. Caranya, pilih dahulu range yang Anda inginkan, lalu klik menu Format à Cells. Masuk ke tab Protection, lalu beri tanda centang pada check box Locked jika Anda ingin data pada range tersebut tidak dapat diedit oleh user, atau hilangkan tanda centang pada check box Locked jika Anda ingin data pada range tersebut dapat diedit oleh user. Anda juga dapat menghide formula, yakni dengan cara memberi tanda centang pada check box Hidden.

Namun, kita juga bisa mensetting lebih spesifik lagi, yakni user mana saja yang boleh mengedit range tersebut, dan yang hanya boleh melihat saja. Caranya adalah:

1. Klik menu Tools à Protection à Allow Users to Edit Ranges.

2. Klik New.

3. Masukan title range dan range yang ingin Anda set permissionnya.

4. Ketikkan password range-nya.

5. Klik tombol Permission, lalu pilih user mana yang boleh mengedit, dan user mana yang hanya boleh membaca saja.

6. Setelah selesai, klik tombol OK.

Penting! System security untuk range ini hanya dapat aktif jika worksheet tersebut sudah diset system securitynya. Silahkan lihat kembali sub bab System Security untuk Sheet/Worksheet.

System Security untuk VBA Project

Dengan menerapkan system security untuk VBA project, maka VBA project tersebut menjadi tidak bisa diedit seluruh object module dan class module nya. Caranya adalah:

1. Tekan ALT+F11 secara bersamaan atau bisa juga dengan cara klik Menu Tools à Macro à Visual Basic Editor

2. Klik kanan VBA project yang ingin Anda amankan, lalu pilih menu VBAProject Properties.

3. Masuk ke tab Protection, lalu beri tanda centang pada check box lock project for viewing.

4. Masukan passwordnya di kedua field yang tersedia.

5. Klik OK.

System Security untuk Procedure/Macro di Dalam VBA Project

Dengan menerapkan system security untuk procedure di dalam VBA project, maka procedure tersebut hanya dapat dijalankan oleh orang yang mengetahui passwordnya. Caranya adalah:

1. Tekan ALT + F11 bersamaan atau bisa juga dengan cara klik Menu Tools è Macro è Visual Basic Editor.

2. Tambahkan kode yang berwarna biru sbb (boleh di Copy – Paste) pada procedure/macro yang Anda inginkan:

Sub Macro1()

'

' Macro1 Macro

' Macro recorded 31/07/2007 by OPI

' Masukkan system proteksi-nya disini:

Dim strPwd As String

strPwd = InputBox("Silahkan input Code Password-nya:", "Password")

If strPwd = "MyPassword" Then ' "MyPassword" adalah kode passwordnya

GoTo MyProgram

Else

MsgBox "Anda tidak berhak menjalankan macro ini!", vbInformation, "No Permission"

Exit Sub

End If

' Kode kita berada dibawah ini:

MyProgram:

Range("B1:C3").Select

' dan seterusnya ...

End Sub

3. Klik menu File à Save.

4. Silahkan jalankan macro tersebut, maka akan muncul input box.

5. Jika password nya benar maka macro akan di jalankan, jika salah maka macro tidak akan dijalankan.

Apakah Ada Cara Lain Untuk Menjebol System Security Excel?

Saat ini, sudah ada beberapa vendor yang telah mengeluarkan software untuk mengetahui password di dalam Excel. Salah satu yang cukup terkenal adalah software Passware, yang bisa diakses pada alamat http://www.lostpassword.com. Bukan hanya Excel yang dapat ia jebol, hampir semua software Office dan lainnya dapat ia jebol passwordnya.

Sebenarnya software ini sangat berguna untuk setiap orang yang lupa akan passwordnya. Namun pada kenyataannya, banyak juga yang menggunakannya untuk mengetahui password system security pada file Excel milik orang lain.

Saya menyarankan agar dalam menginput password agar sepanjang mungkin, kalau bisa hingga 15 karakter, dan kombinasikan antara huruf, angka, simbol dan spasi. Kemudian simpanlah password ini pada file yang Anda inginkan, yang tentunya harus di rahasiakan. Karena jika pasword yang kita input semakin panjang, maka proses pencarian password akan memakan waktu semakin lama.

Selain itu, hadir pula Open Office Calc, yang setahu saya dapat pula membuka file Excel. Namun saya sendiri belum mencoba terlalu jauh sampai sejauh mana ia dapat masuk ke system security Excel.

Dalam hal ini, saya menyarankan agar jangan sampai file Excel kita yang berisi data-data rahasia jatuh ke tangan yang tidak berhak, walaupun sebenarnya file tersebut sudah kita set secara full system securitynya.

Namun, ada satu metode yang tidak bisa dijebol system proteksinya, yakni jika kita telah set file Excel tersebut menggunakan fasilitas Do Not Distribute. Silahkan lihat kembali caranya pada tips yang saya kirimkan pertama kali (Menjaga Agar File Excel Tidak Dapat di Distribusikan).

More about → Security System Excel

Sharing Workbook di Microsoft Excel

Sharing workbook adalah fasilitas penggunaan file Excel secara multiuser, yakni penggunaan satu file Excel secara bersama-sama. Setelah sebuah file Excel di share, beberapa orang menjadi dapat mengedit data secara simultan, baik nilainya, formatnya, maupun data-data lainnya yang terdapat pada workbook.

Hal yang perlu diperhatikan sebelum melakukan Sharing Workbook

Harap diperhatikan, dengan melakukan sharing workbook, maka beberapa fasilitas didalam Excel menjadi tidak dapat lagi kita lakukan, yaitu: merge cell, delete atau insert row-column, delete worksheet, melakukan perubahan pada dialog box dan menu, conditional format dan data validation, insert charts, pictures, objects, atau hyperlinks, menggunakan drawing tools, membuat password, melihat atau merubah atau menyimpan scenarios, group atau outline data, insert subtotals, membuat data tables, membuat pivottable atau pivotchart, serta membuat macro.

Jumlah user maksimum yang dapat menggunakan file Excel yang telah di sharing adalah 256 orang. User yang dapat menggunakan file ini dapat dibatasi hak penggunaannya sesuai dengan setting permission yang diset di dalam suatu folder sharing network. Dengan kata lain, semua user yang mempunyai akses/permission pada suatu folder sharing secara full permission, maka ia pun mempunyai akses yang sama ke dalam file Excel tersebut.

Membuat Sharing Workbook

Untuk membuat suatu file Excel agar dapat digunakan secara multi user, silahkan ikuti beberapa langkah sebagai berikut:

1. Pada menu Tools, klik Share Workbook, kemudian klik tab Editing.

2. Beri tanda check list pada check box Allow changes by more than one user at the same time, kemudian klik OK.

3. Maka akan muncul kotak dialog Save Workbook. Silahkan save / simpan file yang telah di share ini di lokasi network (folder sharing) yang mana user-user yang Anda inginkan dapat mengakses folder ini.

Fasilitas Advanced pada Share Workbook

Jika kita lihat gambar di atas, terdapat tab Advanced di sebelah tab Editing. Saya coba terangkan beberapa bagiannya sebagai berikut:

1. Track changes :

- Keep change history for 30 days. Dengan memilih pilihan ini, maka segala perubahan yang terjadi selama 30 hari akan disimpan. Jadi jika ada beberapa data yang sebenarnya tidak ingin berubah, namun ternyata ada seorang user yang telah merubahnya, maka Anda dapat menghubungi user tersebut, kemudian data original-nya kita restore/copy kembali.

- Don’t keep change history. Pilihan ini membuat Excel tidak mencatat history setiap perubahan yang terjadi.

2. Update change :

- When file is saved. Pilihlah ini jika Anda ingin setiap perubahan yang dilakukan oleh masing-masing user dapat diupdate ke dalam file Excel dengan cara men-save file Excel ini.

- Automatically every 15 minutes. Pada pilihan ini masih terdapat dua pilihan, yaitu :

· Save my changes and see others’ changes. Dengan memilih pilihan ini, maka secara otomatis, setiap 15 menit file Excel Anda akan selalu di Save, kemudian perbahan-perubahan yang dilakukan oleh user-user lainnya akan diperlihatkan.

· Just see other users’ changes. File Excel Anda tidak akan di Save, tapi perubahan-perubahan yang dilakukan oleh user-user selain Anda akan diperlihatkan setiap 15 menit sekali.

3. Conflicting changes between users :

- Ask me which changes win. Dengan memilih pilihan ini, setiap perubahan yang terjadi pada sell/lokasi yang sama akan langsung muncul peringatan, kemudian kita diminta memilih, perubahan oleh user yang mana yang akan disimpan oleh Excel.

- The changes being saved win. Maka siapapun yang telah men-save paling terakhir, data dialah yang akan disimpan oleh Excel.

4. Include in personal View:

- Print settings. Maka settingan print Anda akan disimpan selalu walaupun user-user lainnya berbeda settingan print-nya. Misalnya user A format kertasnya = Landscape dan user B menggunakan format kertas Potrait. Maka pilihan user A dan user B akan selalu tersimpan untuk masing-masing user tersebut, walaupun user-user lainnya merubah settingan print-nya. Print setting ini akan berubah jika kita sendiri yang merubahnya. Dengan kata lain, masing-masing user mempunyai setting print yang berbeda sesuai yang diinginkan.

- Filter settings. Filter-filter yang telah kita buat tidak akan berubah walaupun user-user lainnya merubahnya. Filter setting ini akan berubah jika kita sendiri yang merubahnya.

Membuat Sharing Workbook dengan Menjaga History Agar Tidak Terhapus Oleh Yang Tidak Berhak

Pada metode sebelumnya, user yang pertama kali membuka workbook menjadi user yang exclusive, artinya ia dapat saja mengubah settingan workbook, atau bahkan meng-unshare workbook. Supaya sharing workbook tetap terjaga, dan juga history setiap perubahan tidak terhapus oleh yang tidak berhak, silahkan ikuti langkah-langkah sebagai berikut:

1. Klik menu Tools à Shaer Workbook.

2. Hilangkan tanda centang pada check box Allow changes by more than one user at the same time, kemudian klik OK.

3. Klik menu Tools à Protection à Protect Shared Workbook.

4. Beri tanda centang pada check box Sharing with track changes.

5. Isilah passwordnya, lalu klik OK.

6. Isi kembali passwordnya, lalu klik OK.

Saya menyarankan agar kita memilih metode ini daripada metode sebelumnya.

Mengijinkan dan Meng-Cancel Perubahan Yang Terjadi

Untuk mengetahui apa saja yang berubah, klik menu Tools à Track Changes à Highlight Changes. Untuk mengijinkan dan Meng-cancel perubahan yang terjadi, Anda dapat menggunakan menu Tools à Track Changes à Accept or Reject Changes.

More about → Sharing Workbook di Microsoft Excel

PivotTable Dan PivotChart

I. Membuat PivotTable

PivotTable adalah salah satu fasilitas didalam Excel, dimana ia berupa table interaktif yang sangat cepat dalam menganalisa, mengkombinasikan dan membandingkan sejumlah data baik sedikit maupun banyak. Dalam PivotTable, kita dapat memilih suatu baris atau kolom yang hendak dijadikan referensi dalam melihat jumlah total dari suatu data. PivotTable hanya bersifat report / laporan, artinya Anda tidak dapat mengedit data pada PivotTable. Jika ingin mengedit data, maka Anda harus melakukannya pada table aslinya (sumber data). Adapun PivotChart adalah sama saja dengan PivotTable, hanya saja ia ditampilkan berupa Grafik. Untuk lebih jelasnya, dapat melihat contoh berikut ini:

Gambar 1. Contoh penggunaan PivotTable

Penjelasan dari nomor-nomor yang berwarna biru pada gambar 1 diatas adalah sebagai berikut:

1) Sumber data (Source data / table aslinya)

2) Sumber nilai sales bulan Jan untuk pensil 2B

3) Laporan PivotTable

4) Jumlah sales bulan Jan untuk pensil 2B

5) PivotChart yang menyatakan grafik dari laporan PivotTable (no.3)

Terlihat gambar 1 diatas, untuk menampilkan total sales per-bulan untuk masing-masing pensil, kita dapat menggunakan PivotTable. Baiklah, kita mulai saja latihan dalam membuat PivotTable ini. Silahkan ikuti langkah-langkah dibawah ini:

1. Buatlah table “Daftar Penjualan Barang” seperti dibawah ini :

Gambar 2. Daftar penjualan barang 

2. Sorotlah range B4:E18.

Catatan: dalam menyorot range, harap mencangkup seluruh nama field (judul kolom) berikut dengan seluruh data yang ada dibawahnya.

3. Klik menu Data è PivotTabel and PivotChart Report. Maka kotak dialog PivotTabel and PivotChart Wizard – Step 1 of 3 akan ditampilkan.

Gambar 3. Kotak dialog PivotTabel and PivotChart Wizard – Step 1 of 3

4. Pada kotak dialog PivotTabel and PivotChart Wizard – Step 1 of 3 ini terdapat 4 pilihan pada “Where is the data that you want to analyze ?”, yaitu:

· Microsoft Excel lists or databases

Pilihan ini jika sumber data yang hendak dibuat PivotTablenya berasal dari Excel worksheet.

· External data sources

Pilihan ini jika sumber data yang hendak dibuat PivotTablenya berasal dari database external, seperti Microsoft SQL Server, Microsoft Access database, text file database (misalnya dBase), dll.

· Multiple consolidation ranges

Pilihan ini jika sumber data yang hendak dibuat PivotTablenya berasal dari beberapa (lebih dari satu) Excel worksheet.

· Another PivotTable report

Pilihan ini jika sumber data yang hendak dibuat PivotTablenya berasal dari PivotTable lainnya (bukan dari PivotChart).

Pada latihan ini, pilihlah : Microsoft Excel lists or databases

5. Juga pada kotak dialog PivotTabel and PivotChart Wizard – Step 1 of 3 ini terdapat 2 pilihan pada “What kind of report do you want to create ?”, yaitu:

· PivotTable

Pilihan ini jika kita hendak membuat suatu PivotTable

· PivotChart report

Pilihan ini jika kita hendak membuat PivotChart berikut dengan PivotTable-nya.

Pada latihan ini, pilihlah : PivotTable

6. Klik tombol Next. Maka akan muncul kotak dialog PivotTabel and PivotChart Wizard – Step 2 of 3 sbb:

Gambar 4. Kotak dialog PivotTabel and PivotChart Wizard – Step 2 of 3

Karena pada awalnya kita sudah menyorot Range B4:E18, maka secara otomatis pada field Range sudah terisi range yang kita sorot tersebut.

7. Klik tombol Next. Maka akan muncul kotak dialog PivotTabel and PivotChart Wizard – Step 3 of 3 sbb:

Gambar 5. Kotak dialog PivotTabel and PivotChart Wizard – Step 3 of 3

8. Klik tombol Layout. Maka akan muncul kotak dialog sbb:

Gambar 6. Kotak dialog Layout pada PivotTabel and PivotChart Wizard – Step 3 of 3

Pada kotak dialog ini, klik & drag (digeser dengan mouse kiri tidak dilepas sampai objek telah sampai ke tempat tujuan) tombol-tombol nama field yang ada disebelah kanan ke diagram PivotTable yang disediakan. Silahkan ikuti langkah-langkah ini:

· Tombol “Barang” ditempatkan (klik & drag) ke ROW

· Tombol “Kota” ditempatkan (klik & drag) ke COLUMN

· Tombol “Unit” ditempatkan (klik & drag) ke DATA

· Tombol “Bulan” ditempatkan (klik & drag) ke PAGE

Sehingga akan didapatkan hasil sbb :

Gambar 7. Hasil yang diperoleh setelah tombol-tombolnya di geser ke diagram PivotTable

Kemudian klik tombol OK. Kemudian kotak dialog PivotTabel and PivotChart Wizard – Step 3 of 3 akan aktif kembali. Pada kotak dialog PivotTabel and PivotChart Wizard – Step 3 of 3, pilihlah New Worksheet.

9. Klik tombol Finish. Dengan langkah ini, maka akan muncul sheet baru lengkap dengan PivotTable-nya. Lihat gambar dibawah ini:

Gambar 8. PivotTable untuk Daftar Penjualan Barang PT.Mastermedia

Terlihat pada gambar 8 diatas, pada field Bulan, Barang, dan Kota berupa ComboBox yaitu suatu field berjenis pilihan, dimana kita bisa memilih checklist ketika kita klik field tersebut. Silahkan dicoba untuk klik field Barang (Sell A4). Akan ditampilkan gambar sbb :

Gambar 9. Pilihan yang tersedia dalam menampilkan Barang apa saja yang hendak diperlihatkan di PivotTable

II. Mengedit / Mengubah Layout (tampilan) PivotTable

Apabila diperlukan, kita dapat mengedit tampilan PivotTable ini, seperti mengubah posisi tombol-tombol nama field yang ada pada layout PivotTable ke posisi yang lain sesuai yang kita inginkan. Sebagai contoh, silahkan Anda coba menempatkan field “Bulan” pada posisi Row berdampingan dengan field “Barang”. Caranya adalah, field “Bulan” tersebut di klik & drag ke posisi ROW. Hasilnya dapat dilihat pada gambar 10 dibawah ini.

Gambar 10. Hasil yang diperoleh setelah field “Bulan” di klik & drag ke posisi ROW

III. Mengedit Data Sumber PivotTable

Sebagaimana sudah saya jelaskan sebelumnya, kita tidak dapat mengedit data di dalam PivotTable. Kita hanya dapat mengedit pada sumber data. Setelah mengedit data, jangan lupa PivotTable tersebut harus di refresh. Caranya, klik kanan PivotTable tersebut, lalu pilih menu Refresh Data. Atau bisa juga dengan mengklik icon dengan gambar tanda seru () yang terdapat pada toolbar PivotTable.

IV. Membuat PivotChart

Untuk membuat PivotChart, pastikan toolbar PivotTable belum Anda close. Gambar toolbar-nya adalah sbb :

Gambar 11. Toolbar PivotTable.

Klik tombol pada toolbar PivotTable. Maka akan muncul sheet baru yang menampilkan grafik dari PivotTable yang sedang Anda buka ini.

Gambar 12. Tampilan Grafik (PivotChart)

Apabila dibutuhkan, Anda dapat mengedit tampilan grafik ini sesuai yang Anda inginkan. Untuk mengeditnya, lakukan langkah-langkah berikut ini :

· Klik gambar grafik tersebut.

· Klik kanan mouse Anda, pilih : Chart Type. Maka akan muncul kotak dialog sbb :

Gambar 13. Kotak dialog Chart Type

· Pilihlah Chart Type : Column dan Chart sub-type seperti yang terdapat pada gambar 13 diatas (background warna hitam).

· Klik tombol OK.

· Maka akan ditampilkan grafik baru sbb :

Gambar 14. Hasil yang diperoleh setelah mengedit Chart Type.

More about → PivotTable Dan PivotChart

Data Validation di Excel

Kesempatan ini, kita akan mencoba belajar membuat validasi data menggunakan combo box (list), yaitu suatu kumpulan data yang dapat kita pilih. Fungsi combo box ini adalah agar data yang kita input memiliki nilai kepresisian yang tinggi, yakni tidak mungkin berbeda dengan isi pada combo box tersebut. Jadi dengan membuat combo box pada suatu sell, maka kita tidak dapat menginput data selain yang terdapat pada daftar di combo box tersebut.

Misalnya, suatu sell seharusnya berisi data = “Pensil 2B”. Maka jika ia diinput dengan nama yang berbeda, misalnya saja = “Pensil2B”, maka ia akan memberikan suatu peringatan bahwa data yang kita input ini salah, dan proses tidak dapat dilanjutkan sampai kita membetulkan penulisannya. Demikianlah sekilas fungsi penggunaan combo box ini.

Baiklah, kita mulai saja pembuatan combo box ini pada Excel. Silahkan rekan-rekan ikuti langkah-langkah sbb:

1. Buatlah suatu sample database seperti gambar 1 dibawah ini.

Gambar 1. Sample database

2. Silahkan ke sell IV1, lalu ketikkan data seperti gambar 2 dibawah ini.

Gambar 2. Range sumber data yang digunakan untuk combo box

Ket : Anda dapat menyimpan data combo box di range yang lain selain yang diatas, namun karena ini latihan, dianjurkan untuk mengikuti sebagaimana pada langkah-langkah yang dijelaskan diatas. Range yang digunakan untuk combo box hanya dapat disimpan pada sheet yang sama.

3. Silahkan ke sell C2, lalu klik menu Data è Validation. Set nilai properties pada tab “Settings” dan tab “Error Alert” seperti gambar 3 dan gambar 4 dibawah ini (jangan dulu klik tombol OK).

Gambar 3. Settingan untuk combo box pada tab “Settings”

Gambar 4. Settingan untuk combo box pada tab “Error Alert”

4. Silahkan klik tombol OK. Maka kini sel C2 menjadi berjenis combo box.

Gambar 5. Combo box pada sell C2

5. Silahkan copy sell C2 ini, lalu sorot / block Sell C3:C15. Klik menu Edit è Paste Special. Silahkan pilih : Validation, lalu klik OK.

Gambar 6. Mempaste sell dibawahnya dengan paste validation nya saja.

6. Silahkan klik tombol OK. Maka kini seluruh sell C2:C15 menjadi berjenis combo box.

Sebenarnya cukup banyak jenis validasi yang dimiliki Excel, silahkan dicoba sendiri untuk menggunakan jenis validasi yang lain.

More about → Data Validation di Excel

Fungsi VLOOKUP Excel

Fungsi VLOOKUP digunakan untuk membaca suatu data secara vertikal, lalu mengambil nilai yang diinginkan pada table tersebut berdasarkan kunci tertentu. Fungsi ini adalah yang paling sering digunakan daripada fungsi HLOOKUP. Misalnya kita mempunyai data Customer pada Sheet1 dimana pada tabel tersebut tersimpan informasi sbb:

- ID_Customer

- Nama_Customer

- Alamat

Lalu pada Sheet2, kita ingin jika kita telah menginput ID_Customer pada suatu sell di kolom A, secara otomatis pada sell sebelahnya di kolom B muncul Nama_Customer nya. Maka untuk mewujudkan hal ini, diperlukan rumus VLOOKUP.

Bentuk umum penulisan fungsi ini adalah sbb:

=VLOOKUP(Lookup_value, Table_array, Col_index_num, range_lookup)

Keterangan :

§ Lookup_value adalah sel referensi/nilai yang hendak dijadikan kunci dalam pencarian data.

§ Table_array adalah tabel/range yang merupakan table yang menyimpan data yang hendak kita cari.

Catatan Penting !

· Usahakan Table_array sudah disort pada field kunci nya (baik secara ascending ataupun descending)

· Huruf besar ataupun huruf kecil dianggap sama saja atau diabaikan pada field kunci di Table_array.

§ Col_index_num adalah nomor kolom yang hendak kita ambil nilainya. Col_index_num harus berisi nilai sama dengan atau lebih besar dari 1. Jika kurang dari 1, VLOOKUP akan mengembalikan nilai error = #VALUE!. Jika col_index_num lebih besar dari jumlah kolom pada table_array nya, VLOOKUP akan mengembalikan nilai error = #REF!.

§ Range_lookup adalah nilai logika TRUE / FALSE yang mana VLOOKUP akan mencari data secara tepat atau secara kira-kira atau pendekatan.

· Jika range_lookup kita isi TRUE, maka yang akan digunakan adalah metode pendekatan atau kira-kira. Seandainya nilai yang kita cari tidak ada, maka yang akan diambil adalah nilai yang lebih besar pada sell berikutnya dimana nilainya lebih kecil atau sama dengan nilai lookup_value (field kunci). Seandainya tidak ada nilai yang lebih kecil atau sama dengan nilai lookup_value (field kunci), maka VLOOKUP akan mengembalikan nilai error = #N/A.

· Adapun jika range_lookup kita isi FALSE, maka yang akan digunakan adalah metode tepat. Pada metode tepat ini, jika VLOOKUP tidak menemukan data yang kita cari, maka ia akan mengembalikan nilai error = #N/A, padahal kita ingin jika ia tidak menemukan data yang kita cari, inginnya sell ini diisi dengan nilai 0 atau nilai lainnya yang kita inginkan. Maka untuk mengatasi hal ini, gunakan rumus IF(logical_test,value_if_true,value_if_false) dan ISERROR(Value).

CONTOH :

Dengan mengacu pada gambar diatas, yaitu range A4:C12 kita akan mencoba contoh-contoh penggunaan rumus VLOOKUP sbb:

1. VLOOKUP(1,A4:C12,1,TRUE) = 0.946

2. VLOOKUP(1,A4:C12,2) = 2.17

3. VLOOKUP(1,A4:C12,3,TRUE) = 100

4. VLOOKUP(.746,A4:C12,3,FALSE) = 200

5. VLOOKUP(0.1,A4:C12,2,TRUE) = #N/A, karena 0.1 kurang dari nilai terkecil pada kolom A.

6. VLOOKUP(2,A4:C12,2,TRUE) = 1.71

7. VLOOKUP(2,A4:C12,2,FALSE) = #N/A

8. IF(ISERROR(VLOOKUP(2,A4:C12,2,FALSE)),0,(VLOOKUP(2,A4:C12,2,FALSE)) = 0

More about → Fungsi VLOOKUP Excel

Macro di Excel

Macro di dalam Excel adalah procedure di dalam VBA project (Microsoft Visual Basic) yang dapat dibuat oleh kita, baik dengan cara merekam dari berbagai aktifitas yang sudah kita lakukan di dalam Excel, maupun dibuat dengan cara mengetik sendiri secara langsung. Macro berguna untuk menjalankan berbagai aktifitas yang sering kita lakukan sehari-hari di dalam Excel, sehingga kita tidak perlu lagi mengulangnya secara manual.

Tempat penyimpanan macro dibagi menjadi dua, yakni di dalam workbook itu sendiri (This Workbook dan New Workbook) dan di dalam personal workbook (Personal Macro Workbook). Jika kita menyimpan macro di dalam workbook itu sendiri, maka macro dapat dijalankan hanya oleh workbook tersebut. Namun jika kita menyimpan macro di dalam personal workbook, maka macro dapat dijalankan oleh seluruh workbook yang aktif dibuka pada aplikasi Microsoft Excel.

Merekam Macro

Untuk merekam macro, silahkan ikuti langkah-langkah sebagai berikut:

1. Klik menu Tools à Macro à Record New Macro.

2. Ketikan nama macronya, misalnya “TestMacro”.

3. Shortcut key adalah kombinasi tombol pada keyboard yang dapat kita tekan untuk menjalankan macro ini. Jadi kita tidak perlu membuat ataupun membuka menu untuk menjalankan macro ini. Kotak ini tidak perlu diisi jika macro kita jarang digunakan.

4. Pada kotak Store macro in, terdapat tiga pilihan untuk menyimpan macro, yakni This Workbook, New Workbook dan Personal Macro Workbook. Pilihlah This Workbook jika kita ingin macro disimpan di dalam workbook yang sedang aktif. Pilihlah New Workbook jika kita ingin macro disimpan di dalam workbook baru. Pilihlah Personal Macro Workbook jika kita ingin macro disimpan di dalam workbook Personal.xls. Jika kita simpan macro di dalam Personal Macro Workbook, maka macro yang kita rekam akan dapat dijalankan oleh seluruh workbook yang aktif dibuka pada aplikasi Microsoft Excel.

5. Klik OK, maka segala aktifitas yang kita kerjakan di dalam Excel akan langsung direkam, kemudian dikonversi kedalam procedure di dalam VBA project.

6. Silahkan pilih/blok range A1:B3.

7. Hentikan proses perekaman, yakni dengan cara klik tombol stop.

Melihat/Mengedit Macro

1. Klik menu Tools à Macro à Visual Basic Editor.

2. Pilihlah workbook tempat penyimpanan macro yang sudah kita buat ini, lalu pilih Modules, kemudian klik dua kali nama module yang terakhir (pada contoh ini adalah Module1).

3. Maka terlihat disana procedure VBA yang sudah kita lakukan di dalam Excel, yakni memilih range A1:B3.

4. Tambahkan pada baris sebelum End Sub kode sebagai berikut:

MsgBox "Macro Selesai di Run."

5. Close VBA ini, lalu kembali ke workbook kita.

Persiapan Sebelum Menggunakan/Menjalankan Macro

Macro hanya dapat dijalankan jika macro yang kita buat tersebut di aktifkan. Untuk itu, kita harus memberi izin (enable) macro tersebut agar dapat dijalankan di dalam Excel. Silahkan klik menu Tools à Macro à Securty. Pilihlah level “Medium”.

Jika kita nanti membuka file Excel yang di dalamnya terdapat macro, maka Excel akan menanyakan apakah macro tersebut akan di disable atau di enable. Jika kita yakin macro tersebut memang kita yang membuatnya, atau mungkin juga orang lain yang membuatnya namun macro tersebut aman, maka pilihlah Enable Macros. Namun jika kita ragu-ragu, yakni macro tersebut mungkin berbahaya, pilih Disable Macros.

Menjalankan/Run Macro

1. Pilih cell A1. Untuk mencoba menjalankan macro yang sudah kita rekam tadi, klik menu Tools à Macro à Macros.

2. Pilih macro TestMacro, lalu klik Run.

3. Maka terlihat range A1:B3 diblok secara otomatis. Lalu muncul pesan sebagai berikut:

Untuk tahap selanjutnya, kita dapat membuat macro yang komplek, yang berisi berbagai aktifitas di dalam Excel.

More about → Macro di Excel