3 Fitur Canggih Excel Menjadikan Pekerjaan Lebih Mudah

Tahukah anda bahwa aplikasi Ms Excel  menyediakan fitur yang sangat powerfull dan canggih, yang jika kita menguasainya akan menjadikan pekerjaan olah data lebih efektif dan efisien. Jika anda sedang belajar excel, berikut adalah tiga fitur excel yang wajib dikuasai supaya pekerjaan olah data lebih efisien.

1. Formula dan Fungsi

2. Pivot Table

3. VBA dan Macro

Formula dan Function

Pemahaman formula / rumus excel yang masih terbatas seringkali membuat pekerjaan rekapitulasi  dan  olah data menjadi pekerjaan yang lama dan membosankan.  Excel menyediakan ratusan fungsi siap pakai (worksheet function) yang dapat dimanfaatkan dalam pembuatan formula atau rumus excel. Namun sayangnya masih banyak pengguna yang sangat minim dalam menggunakan fungsi excel. Saya bahkan masih menjumpai beberapa pengguna excel yang hanya puas menggunakan rumus kali ( * ), bagi ( / ), tambah ( + ), dan kurang ( - ),  serta sesekali SUM.

Apakah perlu kita menghapal semua fungsi excel ? kalau bisa hapal semua, itu bagus sekali. Tetapi bagi saya, yang terpenting adalah menguasai dan mengetahui cara mencari fungsi yang tepat dan bisa digunakan dalam formula kita sesuai kebutuhan.  Tekan toolbar  Fx maka anda akan dibawa pada dialog box Insert Function, dari sana kita bisa mencari fungsi-fungsi excel dan keterangan kegunaan dan cara menggunakannya.  Excel  sendiri  mengelompokan fungsi formula dalam beberapa kategori seperti Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trigonometry, serta fungsi-fungsi lainnya.

Pivot Table

Pivot table merupakan fitur excel lainnya yang sangat powerfull, namun sayangnya sejauh pengamatan saya terhadap rekan-rekan kerja saya sendiri, kebanyakan mereka masih sangat jarang yang memanfaatkan fasilitas ini.  Padahal penggunaan pivot table sangat mudah, hanya tinggal geser menggeser  field pada row, kolom dan data.

Pivot table pada dasarnya berfungsi untuk mengolah dan menampilkan data tanpa merubah data aslinya. Dalam hal ini, data asli bertindak sebagai database dan dengan bantuan pivot table kita dapat meng-extract data dari database sesuai yang kita perlukan. Pivot table dapat menampilkan report data dan grafik yang lebih dinamis, mempermudah pengelompokan dan sortir data, serta mempermudah layout data sesuai keperluan laporan.

Macro dan VBA

Macro merupakan prosedur yang didefinisikan sendiri oleh user baik dengan cara record macro maupun dengan bantuan VBA editor. Kemampuan menggunakan macro dan VBA menjadikan efisiensi kerja yang berlipat ganda. Betapa tidak, pekerjaan yang mungkin memerlukan waktu seharian dan dikerjakan oleh beberapa orang, jika menggunakan macro memungkinkan dapat diselesaikan dengan hanya dengan satu klik saja. Dalam hal ini macro dan VBA sangat berguna untuk automatisasi prosedur excel. Cara mudah untuk membuat prosedur macro adalah dengan cara record macro. Untuk tingkat lanjut bisa dengan cara mengetikan code melalui VBA editor. Beberapa contoh macro sudah ditampilkan pada postingan-postingan sebelumnya seperti bagaimana membuat hyperlink secara cepat, convert text comment dan prosedur seputar comment excel.

Dalam postingan berikutnya, kita akan belajar bersama bagaimana penerapan ketiga fitur ini Formula & Function, Pivot Table dan Macro/VBA secara lebih terperinci.  Jika Microsoft Excel adalah “Senjata” anda di pekerjaan, maka alangkah baiknya untuk menguasai tehnik “permainan”  Formula, Pivot Table, dan Macro/VBA.

Demikian semoga bermanfaat, jika ada salah kate, mohon dikoreksi ya... :-)

Belajar Excel...!  Excellent...!  Salam Sukses...!

Insert Comment Lebih Cepat Menggunakan INCOME-C

Halo Sahabat Belajar Excel, sibuknya pekerjaan menggunakan aplikasi excel sebaiknya mendorong kita selalu mengasah kemampuan kita sehingga pekerjaan akan terasa lebih mudah.

Melanjutkan postingan sebelumnya perihal konversi data range dan table menjadi comment, pada kesempatan ini saya akan sharing sebuah alat bantu untuk insert coment lebih cepat. Alat ini mengkombinasikan macro yang sudah dijelaskan sebelumnya dan dengan sedikit modifikasi.

Silahkan download  Income-C

Code VBA tidak diproteksi, sehingga anda bebas untuk mengubek-ubek nya. Silahkan dimodifikasi lagi.

Silahkan berikan feedback sahabat Belajar Excel dengan meninggalkan komentar.

Belajar Excell.. Excellent.. !
Salam.

Cara Cepat Membuat Hyperlink Dalam Ms Excel

Cara yang umum dilakukan pengguna excel untuk membuat hyperlink adalah dengan klik kanan pada sel yang akan dibuat hyperlink dan dilanjutkan klik Hyperlink. Cara lainnya adalah melalui menu atau tab Insert dan pilih hyperlink.  Sayangnya, cara tersebut hanya dapat membuat satu hyperlink saja per sekali proses. Bagaimana jika kita bekerja dengan ratusan sampai ribuan data dimana setiap data harus di hyperlink dengan data lainnya? Membuat hyperlink satu persatu, meskipun sangat mudah, tentunya bukan pilihan yang tepat kecuali Pak Bos mau membayar uang lembur  lebih untuk cara kerja yang sebenarnya tidak efisien.

Misalnya kita memiliki serangkaian data 1 s.d 100.   Untuk memudahkan review, data dirangkum dalam sebuah sheet, sebut saja sheet “Review”.  Setiap data harus dibuat hyperlink ke rincian detail pada sheet lainnya, anggaplah nama sheetnya “Rincian”. Lalu bagaimana cara membuat hyperlinknya dengan cepat?

Dalam hal ini saya akan sharing sebuah alat sederhana yang sudah saya buat. Sebut saja namanya “LinKer”.   Code VBA-nya sengaja tidak diproteksi,  jadi teman bebas untuk mengubek-ubek dan mengembangkannya.

Silahkan didownload di sini : LinKer.zip

LinKer

Cara kerja alat ini sederhana saja, yaitu dengan cara mencari text yang sama antara sumber dengan target hyperlink pada kolom dan baris yang sudah ditentukan. Alat LinKer ini hanya menggunakan range excel untuk menyimpan parameter-parameter yang diperlukan untuk membuat hyperlink. 

Parameter yang diperlukan mencakup data sumber hyperlink dan target hyperlink yaitu berupa nama file, nama sheet, kolom, dan baris. Tanda digunakan untuk menandai baris tertentu apakah harus di buat hyperlink atau tidak.Kolom cek (paling kiri) digunakan untuk menentukan apakah baris parameter akan dieksekusi atau tidak.

Untuk lebih mudah memahaminya dapat dicoba dengan mengetes file contoh yang disertakan dalam link download diatas. Cukup merubah data parameter-parameter pada alat LinKer dan kemudian tekan tombol Set Hyperlink dan perhatikan hasilnya pada file yang dieksekusi. Jangan lupa untuk hapus hyperlink terlebih dahulu pada file contoh.

Demikian semoga dapat bermanfaat...:)

Mengatasi Error "Too Many Different Cell Formats"

Pesan Error "Too Many Different Cell Format" mulai sering saya jumpai beberapa tahun lalu ketika saya harus merekap data excel dari beberapa unit kerja. Ketika data dikumpulkan menjadi 1 file excel dengan cara copy paste, tiba-tiba muncul pesan error seperti ini.

Pesan error tersebut muncul jika file dibuka/diedit menggunakan komputer perusahaan yang masih menggunakan Office 2003. Jika file tersebut dibuka di komputer pribadi yang menggunakan excel 2007, pesan error tidak muncul dan pekerjaan dapat dilanjutkan. Sayangnya ketika file tersebut dibuka kembali menggunakan excel 2003, error kembali muncul dan otomatis komputer melakukan proses recovery, dan hasilnya : file rusak, semua formatnya cell-nya hilang. :-(

Hal ini cukup memusingkan kepala dan karena ketidaktahuan saya, dulu saya mengira ini adalah akibat ulah virus. Mungkin ada orang yang iseng membuat virus cara merusak file excel dan menyampaikan pesan too many different cell formats setiap kali akan menambahkan format baru. Itu fikiran jelek saya, tapi itu dulu... :-)

Dan kasihan Si Virus jadi tertuduh, padahal ia sama sekali tidak berdosa atas masalah ini :-). Ada juga teman yang mengatakan, wah ini karena terlalu kebanyakan rumus ...! Atau kebanyakan link..!

Penyebab  "To Many Different Cell Format"

Usut punya usut, setelah browsing di internet dan bertanya pada halaman bantuan excel, ternyata penyebabnya sangat jelas dan sesuai pesan error dari excel. Yaitu "Too Many Different Cell Formats", artinya "Terlalu Banyak Format Sel Yang Berbeda".  Sebelumnya kok enggak geh ya dengan arti pesan error tersebut??

Dan ternyata untuk kasus yang saya alami, masalahnya terletak pada versi Excel yang digunakan. Microsoft Excel 2003 hanya dapat menampung 4000 cell style yang berbeda/unik. Cell style atau cell format adalah kombinasi pengaturan format sell termasuk number, alignment, font, border, fill dan protection. Jika satu property saja dari format cell yang berbeda, maka dapat dikatakan bahwa cell tersebut memiliki style yang berbeda. Hal ini dapat diilustrasikan sebagai berikut:

Format Cell yang Berbeda

Gambar diatas mengilustrasikan format cell yang berbeda-beda, meskipun berisi text yang sama terdiri satu karakter huruf A, namun masing-masing cell memiliki format yang berbeda dan unik satu sama lainnya. Karena kreatifitas user, biasanya tanpa disadari semakin banyak format cell yang berbeda-beda dan pada akhirnya akan mencapai batas 4000 format cell yang berbeda.

Microsoft excel 2007, dapat menampung lebih banyak format cell yang berbeda yaitu sebanyak 64.000. Sehingga jika jika file excel 2003 yang sudah over format dibuka menggunakan excel 2007, maka pesan error tidak akan muncul. Namun celakanya jika file tersebut kembali dibuka menggunakan versi 2003, maka excelnya pun menjadi "mabuk" format.

Pantesan saja, masalah ini sering muncul, setelah munculnya excel-excel versi yang baru yang lebih canggih dan mampu menampung format yang lebih banyak. Teman-teman yang menggunakan laptop atau komputer pribadi sudah menggunakan versi excel yang lebih tinggi biar tidak ketinggalan zaman.

"Karena loyalitas yang sangat tinggi, banyak teman-teman menggunakan komputer pribadi, untuk menyelesaikan pekerjaan admin kantor. Sangat  Salut ... dan Hebat lah :-) "

Bagaimana Cara Mengatasinya.

• Jika kita bekerja dalam tim sebaiknya dibuat kesepakatan untuk menggunakan 1 versi excel saja untuk keperluan pengolahan data. Apalagi jika petugas yang mengumpulkan data final masih menggunakan excel jadul 2003, sementara data unit menggunakan excel mutakhir seperti versi 2007, 2010 maupun 2013. Sudah dapat dipastikan bahwa problem to many different cell format akan terjadi.
• Gunakan jenis font yang sama untuk semua data, dan hindari penggunaan size font yang bervariasi.
• Jangan terlalu banyak variasi dalam warna, alignment, border
• Dan lain-lain pokoknya jangan terlalu banyak variasi format dech... Memang cukup menggangu kreatifitas, tapi pilih mana? tampilan bagus dan ngejreng tapi error dan file rusak? atau tampilan sederhana tapi aman dan nyaman?
• Jika terlanjur error, buka file yang error tersebut menggunakan OpenOffice dan save sebagai excel 2000. Cara ini bisa sedikit membantu terutama jika kerusakan file belum begitu kompleks.
• Atau jika ada cukup waktu clear semua format dan ganti menjadi format yang lebih sederhana dan konsisten.
• Usulkan ke Bos Anda supaya dapat disetujui budget IT berupa instalasi program microsoft excel yang paling mutakhir di semua komputer perusahaan / instansi dimana anda bekerja... Nah kalau yang ini mungkin agak sulit...

Demikian semoga bermanfaat :-)

 

Waspadalah! : Macro Excel Dapat Merusak Data Anda

Dengan kemampuannya yang luar biasa untuk meningkatkan efisiensi dan efektifitas kerja microsoft excel, macro/vba juga membuka peluang untuk orang yang tidak bertanggung jawab untuk menyisipkan script berbahaya yang dapat merusak data excel, dan bahkan menghilangkannya sama sekali.

Hal ini harus benar-benar diwaspadai karena karena saking mudahnya, orang yang baru belajar makro pun dapat membuat script yang merusak ini. Cukup dengan record macro, maka code vba yang bersifat merusak data dapat dibuat.

Sebagai contoh yang sangat sederhana adalah sbb:

• Kebanyakan pengguna excel, termasuk saya lebih suka menggunakan tombol shortcut "CTR + S" pada saat menyimpan hasil perubahan/edit pada file excel. Shortcut "CTR + S" ini berfungsi untuk memerintahkan komputer untuk menyimpan hasil akhir atas perubahan yang kita lakukan pada file excel tersebut. Kalau kita lakukan record macro atas proses ini, maka script yang kita peroleh adalah sbb: 

Sub Macro1()
    ActiveWorkbook.Save
End Sub

• Jika kita kembali melakukan record macro untuk proses sbb:  select All (tekan "Ctr + A") ---> Delete (Tekan tombol Delete)--> Save (tekan "Ctr + S"), maka script macro/vba yang kita peroleh adalah sbb: 

Sub Macro2()
    Cells.Select
    Range("A1").Activate
    Selection.ClearContents
    ActiveWorkbook.Save
End Sub

• Jika untuk Macro2 tersebut kemudian kita buat shortcut  "Ctr + S", walhasil pada saat kita tekan "Ctr +S",  maka komputer akan menjalankan Macro2, yaitu menyeleksi semua cell dalam activesheet (select all), kemudian menghapus semua datanya (clear contents) dan dilanjutkan dengan proses save file. Data yang sudah terhapus tidak dapat dikembalikan karena file sudah di-save (proses terakhir)
• Meskipun script macronya tersimpan di file lain, asalkan file tersebut dibuka dan macro enable, maka proses macro2  juga dapat merusak file excel lainnya yang akan di-save menggunakan short cut "Ctr+S".

Jadi apakah makro berbahaya?
Bisa dikatakan iya jika kita tidak berhati-hati.

Bagaimana supaya terhindar dari bahaya macro?
Pastikan dulu code macro aman sebelum dijalankan.

Demikian semoga bermanfaat, dan lebih waspada...:-)

Mengatur Target Hyperlink Pada Posisi Yang Tepat

Masih membahas perihal hyperlink pada microsoft Excel. Ketika kita membuat hyperlink dengan target hyperlinknya adalah area data dalam file yang sama dengan sel yang di-hyperlink-kan (anchor area). Bagaimana caranya supaya target hyperlink akan terbuka pada posisi yang tepat ketika hyperlink di click (follow hyperlink).

Cara berikut mudah-mudahan bermanfaat.

1. Cara 1: Buat Target Hyperlik Pada Range Row

• Klik kanan pada sel yang akan dibuat hyperlink
• Klik Hyperlink...
• Pilih : Place In This Document
• Select : Worksheet yang akan menjadi target hyperlink
• Pada text box "type cell reference" biasanya sudah terisi "A1" secara default.
• Ganti "A1" sesuai target hyperlink yang kita inginkan, misalnya jika fokus hyperlink kita adalah sel B100, maka text box diisi dengan "A100:A200" tanpa tanda kutip.   Jika target kita adalah sel G150, maka text box diisi dengan "A150:A250" tanpa tanda kutip.  
• Hide Kolom A Pada worksheet yang menjadi target hyperlink, Hal ini berguna supaya tidak nampak selected range pada saat follow hyperlink.
• Dengan metode ini maka target hyperlik akan diposisikan tepat pada bagian atas layar ketika hyperlink di-klick (follow hyperlink)
• nb: kelemahan cara ini adalah mungkin target hyperlink akan bergeser jika melakukan  insert row pada   worksheet yang menjadi target hyperlink.

2. Cara 2: Buat Target Hyperlink Pada Nama Range

• Buat Nama Range yang akan menjadi referensi target hyperlink. Misalnya jika fokus target hyperlink-nya adalah sel B100,  maka range yang harus diberinama adalah  range  A100:200. misalnya namanya  adalah "targetHyperlink" tanpa tanda kutip  (cara membuat nama range dapat dilihat di artikel Memberi Nama Cell dan Range Pada Microsoft Excel Menggunakan Name Box)
• Klik kanan pada sel yang akan dibuat hyperlink
• Klik Hyperlink...
• Pilih : Place In This Document
• dari dropdown define name pilih nama range yg sudah dibuat sebagai target hyperlink.  Ok
• Hide Kolom A Pada worksheet yang menjadi target hypgterlink, Hal ini berguna supaya tidak nampak selected range pada saat follow hyperlink.
• Dengan metode ini maka target hyperlik akan diposisikan tepat pada bagian atas layar ketika hyperlink di-klick (follow hyperlink)
• nb: dengan cara ini target hyperlink tidak akan bergeser meskipun kita melakukan  insert row pada   worksheet yang menjadi target hyperlink.

Semoga bermanfaat...:-)

Salam

Macro untuk Menghapus Hyperlink Tanpa Merubah Format

Pada postingan sebelumnya kita sudah belajar bagaimana caranya untuk Menghapus Hyperlink Tanpa Merubah Format pada Microsoft Excel. Proses tersebut dibuat secara manual dengan cara menghapus seluruh data yang ada hyperlinks-nya (clear contents), dilanjutkan dengan mengembalikan data tersebut dari data backup dengan cara copy paste formula.

Proses tersebut  sebenarnya dapat dibuat lebih praktis dengan menggunakan macro/vba. Berikut adalah contoh script-nya.

'----------------------------------------------------

Sub clearHyperlinkWithoutFormat()

'je270215-remove hyperlinks tanpa merubah format

Dim sel As Range, tempFormula As String

Application.ScreenUpdating = False

For Each sel In Selection

    If sel.Hyperlinks.Count > 0 Then

        tempFormula = sel.Formula

        sel.ClearContents

        sel.Formula = tempFormula

    End If

Next

End Sub

'----------------------------------------------------

Cara menggunakan macro tersebut sebagai berikut:

• copy script di atas ke module standar dan buatkan shortcutnya
• Sorot/Seleksi range excel yang hyperlink-nya akan di hapus
• Jalankan macro tadi (clearHyperlinkWithoutFormat)

Semoga bermanfaat... :-)