Excel Shortcut Key

Berikut ini adalah daftar shortcut key di excel yang akan berguna untuk mempercepat proses pekerjaan

1. format selected object, press ctrl+1
2. insert current date, press ctrl+;
3. insert current time, press ctrl+shift+;
4. repeat last action, press F4
5. edit a cell comment, press shift + F2
6. autosum selected cells, press alt + =
7. see the suggest drop-down in a cell, press alt + down arrow
8. enter multiple lines in a cell, press alt+enter
9. insert a new sheet, press shift + F11
10. edit active cell, press F2 (places cursor in the end)
11. hide current row, press ctrl+9
12. hide current column, press ctrl+0
13. unhide rows in selected range, press ctrl+shift+9
14. unhide columns in selected range, press ctrl+shift+0
15. recalculate formulas, press F9
16. select data in current region, press ctrl+shift+8
17. see formulas in the worksheet, press ctrl+shift+` (ctrl+~)
18. editing formulas to change the reference type from absolute to relative vice versa, press F4
19. format a number as currency, press ctrl+shift+4 (ctrl+$)
20. apply outline border around selected cells, press ctrl+shift+7
21. open the macros dialog box, press alt+F8
22. copy value from above cell, press ctrl+’
23. format current cell with comma formats, press ctrl+shift+1
24. go to the next worksheet, press ctrl+shift+pg down
25. go to the previous worksheet, press ctrl+shift+pg up

Semoga membantu

Short Cut : Menjumlahkan semua baris dan kolom bersamaan

Biasanya dalam menjumlahkan data kita secara manual mengetikkan formula =SUM(B2:B6), kemudian kita copy/drag formula tersebut ke kolom lain untuk menjumlahkan data di kolom lainnya.
Sebenarnya ada cara cepat untuk melakukan hal tersebut, antara lain adalah sebagai berikut :
Asumsi datanya adalah

 Lakukan block data dengan menambahkan satu baris dan satu kolom setelah data terakhir

 Kemudian tekan tombol AutoSum

Cara kedua adalah dengan cara menekan tombol Alt + =

Maka hasilnya akan seperti tampak dibawah ini

Note : Data dalam gambar berubah ubah karena penggunaan fungsi RAND()

Excel Lambat ??? (Series 2)

Melanjutkan dari artikel sebelumnya Excel Lambat ??? (Series 2), akan saya tambahkan mengapa kelemotan excel bisa terjadi.

Selanjutnya adalah Kesalahan Design, design excel yang baik adalah berurutan penempatan data dan perhitungan, urutan yang baik adalah dari Kiri Atas ke Kanan Bawah.
Mengapa demikian ? karena excel menggunakan metode forward reference yang cara perhitungannya adalah dari kiri atas ke kanan bawah, jika excel anda kecil, hal ini tidak akan terlalu terlihat, jika excel anda besar, maka hal sepele ini akan menyumbangkan kelemotan.

Next , Hapus baris dan kolom yang tidak digunakan di posisi baris paling akhir dari data anda kebawah, atau kolom paling akhir dari data anda ke kanan.
Karena terkadang setelah kita menggunakan baris atau kolom yang kita lakukan adalah delete datanya saja, sehingga excel masih akan mencatat baris dan kolom tersebut digunakan.

Berikutnya adalah formula IF, terkadang kita tidak ingin menampilkan hasil dari formula kita dalam bentuk #N/A, #VALUE, dll, sehingga kita menggunakan fungsi IF untuk menghindari hal itu.
Nah formula yang biasa dituliskan adalah sbb :
=IF(iserror(expression), true_result, false_result)
Nah untuk menggantikan cara ini anda bisa menggunakan IFERROR
=IFERROR(expression, result)
(Kembali lagi, harus dilihat apakah memungkinkan untuk mengganti kan IF dengan IFERROR ini)

-. Jika memungkinkan ganti formula sumproduct dengan sumifs dan countifs
-. Untuk calculation range tertentu gunakan trik ini , block cell tertentu kemudian tekan CTRL + H find "=" kemudian replace "="

Nah apalagi ya yang lain ?

Excel Lambat ??? (Series 1)

Pernah mengalami performa file excel anda lambat ?
Jika anda bekerja dengan jumlah data yang banyak, dan menggunakan perhitungan yang banyak pula , maka file excel anda akan mengalami penurunan kecepatan dalam perhitungannya...

Kira kira penyebabnya apa ya ?

Pernah dengan istilah Volatile Function ? fungsi-fungsi yang dikategorikan sebagai volatile ini sifatnya akan dikalkulasi ulang setiap :
1. Klik pada Row dan Column divider
2. Insert, Delete Baris dan Kolom
3. Membuat, menghapus dan mengubah Define Name
4. Mengubah nama Worksheet
5. Mengubah urutan Worksheet
6. Menjalankan Filter, Hide & Unhide Baris dan Kolom
7. Open dan Save Workbook

WOW ??? mengagetkan bukan ?

Nah fungsi apa saja sih yang dikategorikan sebagai Volatile Function :
1. RAND()
2. NOW()
3. TODAY()
4. OFFSET()
5. CELL()
6. INDIRECT()
7. INFO()

Selain fungsi-fungsi diatas ternyata Conditional Formating juga mempunyai sifat Volatile.

Penyebab kelemotan excel anda berikutnya adalah Link data ke File Excel lain, dan juga Link data ke Worksheet yang lain, oleh sebab itu selalu disarankan untuk meletakkan semua perhitungan dalam 1 Worksheet (jika memungkinkan)

Untuk penyebab kelemotan lainnya akan saya posting pada artikel selanjutnya.

GET.WORKBOOK Syntax

GET.WORKBOOK Returns information about a workbook.

Syntax
GET.WORKBOOK(type_num, name_text)

Type_num    is a number that specifies what type of workbook information you want.

Type_num	Returns
1	The names of all sheets in the workbook, as a horizontal array of text values. Names are returned as [book]sheet.
2	This will always return the #N/A error value.
3	The names of the currently selected sheets in the workbook, as a horizontal array of text values.
4	The number of sheets in the workbook.
5	TRUE if the workbook has a routing slip; otherwise, FALSE.
6	The names of all of the workbook routing recipients who have not received the workbook, as a horizontal array of text values.
7	The subject line for the current routing slip, as text.
8	The message text for the routing slip, as text.
9	If the workbook is to be routed to recipients one after another, returns 1. If it is to be routed all at once, returns 2.
10	TRUE, if the Return When Done check box in the Routing Slip dialog box is selected; otherwise, FALSE.
11	TRUE, if the current recipient has already forwarded the current workbook; otherwise, FALSE.
12	TRUE, if the Track Status checkbox in the Routing Slip dialog box is selected; otherwise, FALSE.
13	Status of the workbook routing slip: 0 = Unrouted 1 = Routing in progress, or the workbook has been routed to a user 2 = Routing is finished
14	TRUE, if the workbook structure is protected; otherwise, FALSE.
15	TRUE, if the workbook windows are protected; otherwise, FALSE.
16	Name of the workbook as text. The workbook name does not include the drive, directory or folder, or window number.
17	TRUE if the workbook is read only; otherwise, FALSE. This is the equivalent of GET.DOCUMENT(34).
18	TRUE if sheet is write-reserved; otherwise, FALSE. This is the equivalent of GET.DOCUMENT(35).
19	Name of the user with current write permission for the workbook. This is the equivalent of GET.DOCUMENT(36).
20	Number corresponding to the file type of the document as displayed in the Save As dialog box. This is the equivalent of GET.DOCUMENT(37).
21	TRUE if the Always Create Backup check box is selected in the Save Options dialog box; otherwise, FALSE. This is the equivalent of GET.DOCUMENT(40).
22	TRUE if the Save External Link Values check box is selected in the Calculation tab of the Options dialog box. This is the equivalent of GET.DOCUMENT(43).
23	TRUE if the workbook has a PowerTalk mailer; otherwise, FALSE. Returns #N/A if no OCE mailer is installed.
24	TRUE if changes have been made to the workbook since the last time it was saved; FALSE if book is unchanged (or when closed, will not prompt to be saved).
25	The recipients on the To line of a PowerTalk mailer, as a horizontal array of text.
26	The recipients on the Cc line of a PowerTalk mailer, as a horizontal array of text.
27	The recipients on the Bcc line of a PowerTalk mailer, as a horizontal array of text.
28	The subject of the PowerTalk mailer, as text.
29	The enclosures of the PowerTalk mailer, as a horizontal array of text.
30	TRUE, if the PowerTalk mailer has been received from another user (as opposed to just being added but not sent). FALSE, if the mailer has not been received from another user.
31	The date and time the PowerTalk mailer was sent, as a serial number. Returns the #N/A error value if the mailer has not yet been sent.
32	The sender name of the PowerTalk mailer, as text. Returns the #N/A error value if the mailer has not yet been sent.
33	The title of the workbook as displayed on the Summary tab of the Properties dialog box, as text.
34	The subject of the workbook as displayed on the Summary tab of the Properties dialog box, as text.
35	The author of the workbook as displayed on the Summary tab of the Properties dialog box, as text.
36	The keywords for the workbook as displayed on the Summary tab of the Properties dialog box, as text.
37	The comments for the workbook as displayed on the Summary tab of the Properties dialog box, as text.
38	The name of the active sheet.

Name_text    is the name of an open workbook. If name_text is omitted, it is assumed to be the active workbook.

Source : Link

Membuat Daftar Sheet Dengan Formula

Terkadang kita bekerja dengan menggunakan banyak Sheet, dan untuk mempermudah kita membuka sheet tertentu, akan lebih baik jika kita mempunyai Sheet Menu, dimana di dalam sheet tersebut berisikan daftar Sheet yang ada.
Proses manual tetap saja bisa dilakukan, tetapi untuk orang "malas" sudah bukan jamannya lagi pakai cara manual...

Nah bagaimana cara membuatnya ?
Ada 2 cara, yang pertama menggunakan macro, bagi macro mania pasti mudah sekali ya, cukup looping sejumlah sheet, kemudian tuliskan pada sheet Menu.
Cara kedua yang akan saya bahas disini adalah menggunakan Formula.

Caranya adalah sebagai berikut :
1. Buatlah Name dengan nama DaftarSheet, scopenya Workbook dan Formulanya adalah :

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Syntax GET.WORKBOOK bisa anda lihat disini

Match With Multi Criteria

Fungsi MATCH berfungsi untuk mendapatkan posisi suatu nilai dalam suatu array.

Syntax : MATCH(Lookup_Value, Array_Data, Match_Type)

Lookup_Value : Nilai yang akan dicari posisinya
Array_Data : Array (sekumpulan) data

http://excelformulaandmacro.blogspot.com/2010/10/fungsi-match.html

Bagaimana jika ada lebih dari 1 kondisi ?

Menghitung Jumlah Data Dengan Lebih dari 1 Kriteria / Multi Criteria

Jika anda mempunyai data dengan lebih dari 1 kolom, kemudian anda ingin menghitung berapa jumlah data dengan kriteria tertentu, dimana kriteria tersebut lebih dari 1, beberapa hal yang bisa anda lakukan adalah
1. Menggunakan Pivot
2. Menggunakan Fungsi COUNTIFS
3. Menggunakan Array

Kali ini yang akan saya bahas adalah penggunaan Array.
Caranya adalah sebagai berikut

Menghitung Jumlah Data Yang Tidak Kembar / Unik (Unique)

Seringkali kita mempunyai data yang berulang didalam suatu kumpulan data, sehingga pada saat kita ingin mengetahui berapa jumlah data yang tidak kembar / unik kita tidak bisa dengan segera mengetahuinya , tanpa mengolah data tersebut terlebih dahulu.
Ada 3 cara untuk mengetahui berapa jumlah data tidak kembar anda
1. Teliti satu persatu , hapus yang kembar (sisakan 1 data saja jika data tersebut muncul lebih dari 1 kali), jika sudah selesai, block data anda, kemudian akan muncul jumlah data anda pada status bar dibawah layar excel. Hal ini adalah cara paling sederhana dan tentunya paling manual, sehingga akan berpotensi kesalahan penghitungan dan juga tidak efisien.

2. Dengan menggunakan pivot, caranya block data anda, kemudian pilih insert Pivot Table, tarik kolom yang akan di hitung ke dalam Row, kemudian block data yang muncul dan lihat jumlah data pada status bar dibawah layar excel. Cara ini yang paling cepat dan akurat, bahkan anda tidak perlu mengetik apapun dengan cara ini.

Manfaat Excel Macro + Database

Excel ternyata mempunyai banyak kegunaan jika kita bisa menggunakan secara maksimal.
Contoh nyata yang sudah banyak dilakukan oleh perusahaan, adalah membuat pekerjaan yang awalnya manual menjadi otomatis, dengan cara ini keuntungan yang didapatkan adalah :
1. Kecepatan Proses
Dengan melakukan otomatisasi maka proses pengerjaan data lebih cepat, dan tentunya lebih akurat, karena proses otomatisasi tersebut dibuat berdasarkan rule rule yang sama dengan proses manual
2. Akurasi
Dengan proses otomatisasi maka akurasi juga diharapkan lebih meningkat, karena sudah tidak ada lagi proses pemindahan data secara manual, yang biasanya juga menjadi salah satu faktor kesalahan.
3. Single Source
Dengan menambahkan database pada excel, maka source data bisa di sentralisasi , sehingga hal ini akan menghilangkan masalah yang berkaitan dengan perbedaan data.

Macro Excel Project - Payroll Module

Macro Excel Project - Payroll Module

Telah selesai Payroll Module untuk perusahaan yang bergerak dibidang jasa, berlokasi di Kota Semarang.
Module Payroll ini berisikan :
1. Data Absensi
2. Transaksi Kasbon
3. Transaksi Lain-Lain
4. Data Karyawan
5. Data Karyawan Per Area
6. Data Area
7. Rekap Gaji
8. Print Slip Gaji
9. Data Estimasi THR

 Sebelum dibuatnya aplikasi ini, kesulitan yang dihadapi oleh client adalah :
1. Melakukan grouping untuk karyawan berdasarkan area, karena seringnya karyawan yang keluar masuk (resign)
2. Melakukan perhitungan gaji dengan cara manual, sehingga cukup banyak memakan waktu
3. Absensi dihitung secara manual
4. Print Slip Gaji dipetakan secara manual, dengan banyaknya karyawan maka proses ini akan memakan waktu yang lama.
5. Kasbon dan Lain-Lain dicatat dengan buku manual , sehingga harus input lagi saat perhitungan gaji

Setelah menggunakan aplikasi :
1. Grouping karyawan otomatis
2. Perhitungan Absensi cepat, menggunakan macro
3. Absensi diinput, dan perhitungan dilakukan oleh aplikasi
4. Print Slip tidak perlu memetakan secara manual lagi
5. Kasbon dan Lain-Lain dicatat didalam aplikasi, sehingga bisa langsung mengurangi gaji karyawan

Dengan menggunakan aplikasi ini harapan saya adalah waktu anda untuk bekerja bisa menjadi lebih efisien, sehingga tidak disibukkan dengan pekerjaan yang sifatnya administrative.

Pertanyaan
Berapa biaya pembuatan aplikasi ?
Biaya pembuatan aplikasi tidak bisa dipastikan, karena tingkat kesulitan suatu aplikasi berbeda dengan aplikasi lainnya. Hanya saja biaya bisa di negosiasikan dengan penyesuaian fitur dari aplikasi.

Untuk jenis usaha apa yang cocok menggunakan aplikasi berbasis Excel ?
Jenis usaha apapun cocok, hanya saja dengan menggunakan database access, maka aplikasi ini lebih cocok untuk usaha dengan skala menengah kebawah, dan tidak memerlukan database yang terpusat (1 komputer saja)

Kelebihannya apa ?
Aplikasi ini sifatnya portable, bisa di simpan di flashdisk, sehingga mudah digunakan dikomputer manapun, yang penting sudah terinstal Microsoft Excel dan Access dengan versi sesuai pada saat pembuatan.

Mengapa perlu membuat aplikasi ?
Pembuatan aplikasi bertujuan untuk membantu manusia dalam pekerjaan, aplikasi/software sebenarnya adalah cara berpikir manusia yang di terjemahkan ke dalam bentuk sistem otomatis.
Jika cara berpikir / alur proses sudah fix/tetap, maka alur ini bisa diterjemahkan menjadi suatu software, yang akan membantu manusia dalam memproses data dalam jumlah yang besar. Sehingga tidak diperlukan lagi pekerjaan yang berulang dilakukan oleh manusia. Dan waktu anda bisa digunakan untuk hal hal lain yang lebih penting daripada mengerjakan sesuatu yang berulang.

Untuk menjaga kerahasiaan data client, maka contact number tidak saya tampilkan di Gambar. Bagi rekan rekan yang berminat untuk membuat aplikasi menggunakan Microsoft Excel, silahkan kontak saya di 089610077999(Ferry)