Wednesday, January 31, 2018

Cara Efektif Memisahkan Konten Sel

Apa gunanya split cell dan bagaimana caranya memisahkan content sel menjadi beberapa bagian?  Prosedur ini sangat berguna untuk membantu mempermudah analisa data. Ada bebagai alternatif cara untuk melakukan split cells. Kita bisa menggunakan fitur Text to Columns, Flash Fill dan Formula. Lebih lanjut dalam catatan pelajaran excel kali ini akan dibahas berbagai alternative cara memisahkan konten sel sehingga kita bisa memilih tehnik terbaik dan cocok sesuai kebutuhan serta pola data yang dihadapi.

  • Split Cell Menggunakan Fitur Text To Columns
  • Memisahkan Konten Sel Menggunakan Fill Flash (Excel 2013 Atau Lebih Baru)
  • Split Data menggunakan Formula

Pada umumnya, pemisahan konten sel excel diperlukan pada dua kasus. 

Yang paling sering adalah ketika kita meng-import data dari sumber external dimana semua informasi berada dalam satu kolom. Mau tidak mau data tersebut perlu dipecah atau dipisahkan menjadi beberapa kolom supaya bisa diolah lebih lanjut.

Atau mungkin kita memiliki sebuah tabel, tetapi ada satu kolom dalam tabel tersebut yang berisi gabungan beberapa informasi.  Nah, supaya tabel lebih mudah dianalisa, (misalnya menggunakan filtering, sorting dan pivot table) maka  kolom yang berisi informasi gabungan ini perlu di pecah menjadi beberapa kolom yang berisi informasi tunggal.

Cara Memisahkan Konten Sel Menggunakan Text To Columns.


Fitur Text to Columns ini sepertinya tersedia pada semua versi excel, setidaknya pada versi excel yang sudah saya cek yaitu versi excel 2003, 2007, 2010, 2013 dan 2016.

Fitur ini memungkinkan kita untuk memisahkan data text berdasarkan pemisah text (delimiter) yang digunakan seperti koma, titik koma, spasi atau gabungan beberapa karakter yang berperan sebagai delimiter.

Mari kita lihat bagaimana fitur ini bekerja.

Memisahkan Cells Content berdasarkan delimiter

Anggaplah kita memliki data yang di-import dari program external. Data tersebut berisi list nama product, ID product, konsumen dan sebagainya. Satu baris data tersimpan dalam satu sel. Ada beberapa baris data dengan sruktur yang sama, semuanya terletak dalam kolom yang sama

Contoh Data Import Excel

Perhatikan contoh tabel di atas. Semua data terletak pada satu sel dalam kolom A. Tiap item data dipisahkan oleh delimiter berupa tanda koma (,). 

Kemudian kita ingin memidahkan data tersebut menjadi beberapa kolom sehingga memudahkan dalam proses olah data selanjutnya.

Untuk memisahkan data menjadi beberapa kolom, maka lakukan langkah berikut.

  • Seleksi kolom A (atau tergantung lokasi kolom data)
  • Kemudian dari tab Data, grup Data Tools, klik Text To Columns

Langkah 1 Text To Columns

  • Langkah selanjutnya: pilih opsi Delimited, kemudian klik tombol NEXT


  • Centang checkbox Comma, kemudian perhatikan pada kotak Data Preview untuk memastikan bahwa pemisahan konten sel sudah sesuai dengan yang diharapkan.
  • Jika yakin sudah benar, maka selanjutnya klik tombol Next.

Langkah 2 Text To Columns


Setelah itu maka kita akan dibawa pada kotak dialog berikutnya yang berguna untuk pengaturan format dan destinasi sel untuk menyimpan hasil pemisahan konten.

Langkah 3 Excel To Columns


Format Data: 

Secara default, excel membaca semua format sel sebagai general. Namun kita bisa memberitahu excel jika ada beberapa fragmen data yang harus diperlakukan sebagai format lainnya. Misalnya sebagai format tanggal. Ini diperlukan jika ada bagian kontent sel yang berupa tanggal. 

Misalnya pada contoh di atas, tanggal terletak pada bagian akhir atau kolom paling kanan. Pada Data preview, seleksi kolom tersebut, kemudian pada opsi date, pilih format tanggal yang sesuai. Dalam contoh diatas adalah YMD. 

Destination: 

Secara default, range destinasi akan menimpa kolom original dengan penambahan kolom berikutnya sesuai jumlah kolom hasil pemisahan. Namun destinasi ini dapat kita rubah dengan cara klik collapse dialog icon yang terletak disebelah kanan kotak destination. Kemudian dengan menggunakan mouse, pilih cell pada range distinasi. Kita bisa juga mengetik referensi sel secara langsung dalam box.

Catatan:
Jika beberapa kolom tidak kita perlukan, maka kita dapat mencegah kolom tertentu supaya tidak di-import sebagai hasil pemisahan. Caranya seleksi kolom tersebut pada data preview, kemudian tick opsi Do Not Import Column (skip) pada bagian data format. 

Langka terakhir: klik tombol Finish. Sampai pada tahap ini, kita sudah berhasil memisahkan konten sel menjadi beberapa bagian dalam kolom terpisah, seperti dapat dilihat pada screenshot di bawah ini.

Hasil Text To Columns


Untuk memudahkan analisa dan olah data lebih lanjut, kita bisa menambahkan header kolom yang sesuai terhadap tabel di atas.

Bagaimana memisahkan text berdasarkan lebar (fixed width)

Bagian ini menjelaskan bagaimana untuk membagi konten sel berdasarkan jumlah karakter yang ditentukan. Untuk memudahkan pemahaman, silahkan perhatikan contoh berikut:

Anggaplah kita memiliki data Nomor Induk Karyawan (NIK) dan nama karyawan yang terletak dalam satu kolom. NIK dan nama karyawan dipisahkan oleh spasi.

Data Text To Columns Fixed Width

Kemudian kita ditugaskan untuk memisahkan NIK dan Nama Karyawan pada dua kolom yang terpisah. Karena semua NIK terdiri dari jumlah angka yang konstan yaitu 6 karakter, maka salah satu cara yang efektif untuk mengerjakan tugas ini adalah menggunakan Text to Columns Fixed width.

Mulai Convert text to columns sebagaimana dijelaskan pada contoh pertama. Kemudian pilih Fixed width and click Next.

Langkah 1 Text To Columns Fixed Width


Set lebar masing – masihg kolom dengan menggunakan Data Preview, sebagaimana diperlihakan dalam screenshot di bawah. 

Langkah 2: Text To Columns Fixed Width

Perhatikan garis vertikal pada gambar di atas. Garis tersebut mewakili pemisah kolom. Untuk menciptakan sebuah garis pemisah baru maka anda cukup klik posisi yang diinginkan.

Untuk menghilangkan garis pemisah, cukup lakukan double klik garis yang akan dihapus tersebut. Untuk memindahkan garis ke posisi lainnya tinggal geser atau drag menggunakan mouse.

Langkah selanjutnya, pilih data format dan destination untuk memisahkan sel secara tepat sebagaimana dilakukan pada contoh sebelumnya, dan klik tombol Finish untuk menyelesaikannya.


Memisahkan konten sel menggunakan Fill Flash

Jika anda menggunakan excel 2013 atau versi yang lebih baru, maka anda akan sangat terbantu dengan adanya fitur Flash Fill. Fitur ini tidak hanya bisa mengisi sel dengan data, tetapi juga bisa memecah konten sel menjadi beberapa bagian. Dan hebatnya lagi, kita bisa menyisipkan text tertentu.

Fitur Flash Fill bekerja dengan cara menganalisa pola data yang anda input pada sebuah sel, kemudian meneruskan fill sel lainnya mengikuti pola tersebut.

Dengan menggunakan contoh sebelumnya berupa data Nomor Induk + Nama Karyawan.

  • Ketik pada salah satu sel disamping kolom data tersebut, misalnya ketik nama depan.
  • Seleksi range yang ingin diisi sampai baris akhir tabel. 
  • Dari tab Data, klik Flash Fill


Perhatikan screenshot berikut:

Tombol Fill Flash Excel


Maka baris lainnya akan terisi data mengikuti pola data yang sudah diketik duluan. 


contoh fill flash excel

Cobalah untuk berexperimen mengetikan pola text tertentu. Misalnya menambahkan text tambahan seperti Sdr pada salah satu sel dilanjutkan dengan proses Flash Fill. Maka hasilnya sangat mengagumkan: semua sel akan mendapatkan tambahan text Sdr. 


Langkah-Langkah Fill Flash

Untuk mengambil bagian data lainnya maka dapat melakukan flash fill lagi pada kolom selanjutnya. Misalnya untuk mengambil Nomor Induk Karyawan (NIK)

Ketik Nomor induk karyawan pada satu sel yang sesuai atau sebaris dengan data originalnya. Kemudian seleksi range yang akan diisi, dan klik tombol Flash Fill seperti diilustrasikan dalam gambar berikut:

Cara Fill Flash


Shortcut Flash Fill.

Selain dengan cara klik tombol Flash Fill, untuk menjalan kan fitur ini, kita juga bisa menggunakan shortcut keyboard CTR + E. Cara ini tentu saja sangat menghemat waktu anda. 

Alternative kedua adalah dengan menggunakan kursor mouse. Caranya posisikan kursor pada bagian pojok bawah sel yang sudah diisi sampai kursor berubah menjadi tanda plus (+), kemudkan klik kanan dan drag ke arah bawah. Selanjutnya lepaskan mouse, makan akan muncul contextual menu. Lalu klik Flash Fill pada contextual menu.

Untuk lebih jelasnya perhatikan sreenshot berikut:


Cara Drag Fill Flash Excel

Drag Fill Flash



Flash Fill juga dapat bekerja secara otomatis. Kita cukup menyeleksi range disamping kolom data original, kemudian ketikan 2 atau 3 sel, maka otomatis excel akan memperlihatkan list pada sel berikutnya. Jika anda merasa cocok dengan pola data tersebut, langsung saja tekan tombol Enter.


Cara Fill Flash Otomatis

Catatan:
Jika fitur Flash Fill otomatis tidak aktif, maka kita bisa mengatifkannya melalui Tab File > Options, kemudian klik Advanced. Selanjutnya pada bagian Opsi Editing, centang checkbox Automatically Flash Fill 


Memisahkan Konten Sel Menggunakan Formula.

Cara ketiga untuk memisahkan konten sel adalah menggunakan rumus atau formula. Tentu saja fungsi yang digunakan dalam formula adalah fungsi-fungsi yang terkait untuk mengolah atau manipulasi text seperti LEFT, RIGHT, MID, LEN, SEARCH dan FINDPembahasan mengenai fungsi - fungsi tersebut dapat dilihat pada artikel Fungsi Pengolah Text


Formula untuk memisahkan konten sel jika jumlah karakter diketahui.

Dengan menggunakan contoh data dari pembahasan sebelumnya yaitu berupa NIK dan Nama karyawan, maka rumus yang dapat digunakan untuk memisahkan atau mengambil fragmen data NIK dan nama karyawan adalah sebagai berikut:

Data original (gabungan) terletak pada kolom A

Maka rumus untuk mendapatkan data NIK pada baris pertama adalah:
=LEFT(A1,6)

Sedangkan rumus untuk mendapatkan data nama adalah:
=MID(A1,8,100)

Atu seperti dapat dilihat pada gambar dibawah ini.

Rumus untuk memisahkan konten sel

Contoh di atas merupakan yang paling sederhana dari cara menggunakan formula untuk memisahkan konten sel. Hal ini karena pola data originalnya sederhana dan relatif teratur, dimulai dengan data NIK dengan jumlah karakter yang sama sehingga dapat ditentukan jumlahnya secara explicit yaitu 6 karakter sebagai parameter num_chars dalam fungsi LEFT

Demikian pula fungsi MID dapat menggunakan angka 8 sebagai parameter start_num. Hal ini karena posisi huruf pertama dari Nama karyawan semuanya sama yaitu karakter ke-8 dalam text konten sel original. Sedangkan bilangan 100 sebagai parameter num_chars dalam fungsi MID diasumsikan bahwa nama karyawan tidak ada yang lebih dari 100 karakter.

Formula untuk memisahkan konten sel jika jumlah karakter tidak diketahui.

Misalnya kita memiliki data awal berupa nama Perusahaan dan Kota. Data terletak dalam satu kolom, dipisahkan oleh koma, seperti gambah di bawah ini.

contoh pemisahan data excel

Selanjutnya kita ingin memisahkan data nama perusahaan dan lokasi kotanya pada dua kolom yang berbeda. 

Karena jumlah karakter penyusun nama perusahaan berbeda-beda, maka kita tidak bisa menentukan angka konstan sebagai parameter num_chars dalam fungsi LEFT. 

Untungnya jumlah karakter penyusun nama perusahaan dapat dicari dengan memanfaatkan keberadaan tanda koma yang posisinya tepat satu karakter setelah nama perusahaan. Untuk tugas ini, kita bisa memanfaat kan fungsi FIND atau fungsi SEARCH.

Misalnya:

Rumus  =FIND(",",A1)-1  atau  =SEARCH(",",A1)-1  akan menghasilkan bilangan 8 yang merupakan jumlah karakter nama perusahaan yang terletak pada sel A1 yaitu “CV Setia”

Rumus  =FIND(",",B1)-1  atau  =SEARCH(",",B1)-1  akan menghasilkan bilangan 10 yang merupakan jumlah karakter nama perusahaan yang terletak pada sel B1 yaitu “CV Mandiri”

Perbedaan fungsi FIND dan SEARCH adalah bagaimana kedua fungsi tersebut mendeteksi huruf kapital dan huruf kecil. FIND menganggap berbeda antara huruf kapital dan huruf kecil. Sebaliknya SEARCH menganggap huruf kapital dan kecil sama saja. Dikarenakan tidak ada jenis kapital dan kecil untuk tanda koma, maka dalam contoh kasus ini penggunaan fungsi FIND dan SEARCH sama saja dan bisa saling menggantikan.

Selanjutnya rumus FIND atau SEARCH ini bisa dimasukan dalam fungsi LEFT untuk menempati argumen num_chars. Sehingga rumus untuk memisahkan nama perusahaan dapat dituliskan sebagai berikut:
=LEFT(A1,FIND(",",A1)-1)


contoh rumus pemisahan data excel

Pertanyaan berikutnya: rumus macam mana yang bisa dipake untuk memisahkan nama Kota? Dikarenakan nama Kota terletak di sebelah kanan, dan tidak ada data lain di sebelah kanan nama kota, maka untuk tugas ini kita bisa menggunakan fungsi RIGHT

Fungsi RIGHT berguna untuk mengambil beberapa karakter dari sebelah kanan. Jumlah karakter penyusun nama Kota dapat dihitung dari jumlah karakter peyusun sel original dikurangi nomor urut posisi tanda koma, dan dikurangi 1 lagi yaitu tanda spasi.

Rumus untuk mendapatkan jumlah karakter penyusun nama kota dapat dituliskan sebagai berikut
LEN(A1)-FIND(",",A1)-1

Rumus di atas akan menghasilkan bilangan 7 yang merupakan jumlah karakter penyusun nama kota Bandung yang terletak pada Sel A1. Adapun cara kerja rumus tersebut dapat dijelaskan sebagai berikut.

LEN(A1) digunakan untuk menghitung jumlah karakter pada sel A1

FIND(",",A1) digunakan untuk mencari posisi tanda koma pertama pada sel A1

Angka 1 digunakan sebagai pengurang karena adanya tanda spasi yang bukan bagian dari nama kota.
Sehingga jika fragmen-fragmen rumus tersebut diganti menjadi value yang merupakan hasil kalkulasinya maka dapat diuraikan sebagai berikut:

= LEN(A1)-FIND(",",A1)-1
= 17 – 9 – 1
= 7

Selanjutnya, rumus LEN(A1)-FIND(",",A1)-1  dapat ditempatkan sebagai argumen num_chars dalam fungsi RIGHT untuk mendapatkan nama kota. Sehingga rumusnya dapat dituliskan seperti dibawah ini:
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)

contoh rumus pemisahan konten sel

Dalam menggunakan rumus untuk memisahkan konten sel, kita bisa mencoba dengan berbagai variasi gabungan fungsi. Misalnya bisa juga menggunakan gabungan fungsi MID, LEN dan FIND untuk mendapatkan nama kota. Disitulah logika excel kita bermain.

Sampai disini pembahasan cara memisahkan konten sel pada excel. Anda bisa memilih mana yang sesuai dan mana yang paling efektif apakah menggunakan fitur Text To Columns, Flash Fill, ataupun dengan Formula / rumus. Tentu saja anda yang lebih tahu mana yang paling efektif sesuai kebutuhan dan kasus data yang dihadapi.

Demikian semoga bermanfaat.


Monday, January 8, 2018

Cara Efektif Menggeser Kolom Pada Excel

Sebuah tabel biasanya terdiri atas beberapa kolom dan urutan kolom disusun berdasarkan kondisi logis supaya memudahkan dalam analisa.  Namun adakalanya kita perlu melakukan penyusunan atau re-order ulang kolom untuk keperluan analisa yang lebih baik. Dengan kata lain kita perlu menggeser atau memindahkan kolom tertentu sehingga dapat menampilkan tabel dengan urutan kolom yang lebih sesuai untuk keperluan analisa.

Sebagai contoh, kita mungkin perlu menggeser dan menggandengkan sejumlah kolom untuk melihat data side by side. Tentu saja kita bisa meng-hide kolom tetangganya yang tidak diperlukan untuk sesaat, akan tetapi cara ini tidak selalu yang terbaik. Karena kita mungkin saja masih perlu melihat data dari sudut pandang kolom yang di-hide.

Nah, dalam kesempatan ini akan dibahas berbagai alternatif cara untuk memindahkan atau menggeser kolom. Kelihatannya mungkin sepele, tetapi yakinlah.. ini akan sangat membantu efektifitas kerja jika bisa mempraktekannya dengan tepat.

Secara garis besar, pembahasan kali ini mencakup:

  • Menggeser kolom menggunakan drag & drop.
  • Memindahkan kolom dengan cara  cut dan paste
  • Memindahkan kolom dengan cara  copy, Paste dan delete
  • Re-order kolom menggunakan macro


Cara Menggeser  Kolom Dengan Tehnik Drag & Drop


Tehnik drag & drop untuk memindahkan range mungkin bukan hal yang asing bagi sebagian besar pengguna excel. Namun cara yang umum biasanya range yang dipindahkan akan menimpa dan menghapus data pada range lokasi dropnya. 

Cara yang akan dijelaskan di sini adalah bagaimana drag & drop untuk menggeser kolom ke lokasi lainnya tanpa menghapus / menimpa data pada kolom lainnya.

Dengan menggunakan contoh tabel pada artikel sebelumnya mengenai sortir dan filter data berdasarkan warna, mari kita praktekan bagaimana cara memindahkan / menggeser kolom pada excel.

Geser Kolom Tabel Excel


Perhatikan gambar contoh tabel di atas.

Anggaplah kita ingin memindahkan kolom Kategori ke bagian awal tabel:

  • Seleksi kolom D, kemudian sambil menekan tombol SHIFT, gerakan pointer pada bagian sisi kolom yang diseleksi sampai kursor berubah menjadi tanda panah menunjuk 4 arah mata angin.
  • Klik tombol mouse kiri. Lalu sambil tetap menekan tombol SHIFT, drag kolom ke lokasi yang diinginkan. Lepaskan klik mouse pada lokasi yang sesuai, misalnya ke kolom pertama seperti contoh dibawah ini:


Carag Drag & Drop Kolom Excel


  • Sehingga hasilnya kolom Kategori bergeser menjadi kolom pertama dalam tabel seperti gambar berikut:

drag & drop kolom excel


Catatan: Penting diperhatikan bahwa proses Drag & Drop ini harus dilakukan sambil menekan tombol SHIFT. Jika tidak, maka kolom yang di geser akan menimpa / menghapus kolom lainnya. 

Tehnik ini juga dapat digunakan untuk menggeser beberapa kolom yang bersebelahan sekaligus.  Sayangnya metode ini tidak dapat digunakan untuk menggeser beberapa kolom yang tidak bersebelahan secara langsung, atau beberapa kolom yang diseleksi dengan cara klik header kolom sambil menekan tombol CTR.

Selain untuk menggeser kolom, tehnik ini juga sebenarnya bisa digunakan juga untuk menggeser baris. Silahkan dicoba sendiri.


Menggeser kolom dengan cara Cut & Paste


Jika cara drag & drop ada kendala, misalnya problem mouse, maka anda dapat menggunakan cara cara cut & paste. Tehnik ini juga akan sangat tergantung pada kebutuhan apakah memindahkan kolom tunggal atau beberapa kolom.

Memindahkan Kolom Tunggal

  • Seleksi kolom dengan cara klik pada header kolom.
  • Tekan CTRL + X untuk menjalankan command Cut pada kolom terpilih. Bisa juga dilakukan dengan cara Klik kanan, kemudian klik Cut.


tehnik memindahkan kolom excel

  • Seleksi kolom dimana anda ingin memindahkan kolom yang di-Cut ke lokasi tersebut, kemudian klik kanan dan klik Insert Cut Cells.

menggeser kolom cut and paste

  • Maka hasil akhirnya kolom berpindah sesuai order yang dikehendaki.

hasil penggeseran kolom


Jika anda merasa lebih nyaman menggunakan shortcut dan keyboard, mungkin cara berikut akan lebih anda sukai.

  • Seleksi satu sel atau lebih secara horizontal sesuai jumlah kolom yang bersebelahan yang ingin anda pindahkan, kemudian tekan CTRL + SPACE untuk menyeleksi kolom penuh.
  • Tekan CTRL + X untuk menggunting atau Cut kolom terpilih
  • Seleksi lokasi kolom lainnya sebagai lokasi pemindahan kolom, kemudian tekan CTRL + SPACE untuk menyeleksi kolom secara penuh. Bisa juga dengan hanya menyeleksi satu sel teratas saja.
  • Lalu tekan CTRL + SHIFT bersama dengan menekan tombol plus (+). Jika anda menggunakan keyboard yang ada tombol numeriknya, bisa menekan CTRL dan tanda plus (+) tanpa perlu menekan tombol SHIFT.

Cara Memindahkan Beberapa Kolom Pada Excel


Tehnik cut/paste hanya dapat bekerja pada satu kolom saja atau beberapa kolom yang bersebelahan. Jika tehnik ini dicoba untuk beberapa kolom yang tidak bersebelahan langsung makan cara ini akan berakhir dengan error.

Untuk rerorder beberapa kolom kita dapat melakukan salah satu cara atau kombinasi berikut:

Drag & Drop kolom satu  persatu seperti dijelaskan pada bagian awal artikel ini. Cara ini sepertinya paling cepat.

Cut dan Paste masing – masing kolom. Cara ini mungkin bukan pilihan terbaik jika bekerja dengan banyak kolom yang harus di geser. Tetapi mungkin akan menjadi pilihan terbaik jika posisi kolom yang akan digeser berjauhan dengan posisi barunya sehingga tidak nampak dalam satu layar komputer.

Copy, Paste dan Delete: cara ini mirip dengan cut dan paste, hanya saja sedikit lebih panjang prosesnya karena kolom awal tidak terhapus, sehingga harus di delete kolom lagi. Caranya dijelaskan di bawah ini.

Menggeser Kolom Dengan Cara Copy, Paste dan Delete.


Jika dalam kondisi tertentu tehnik drag kolom menggunakan mouse tidak bekerja, maka anda bisa mencoba penyusunan kolom menggunakan cara ini.

Seleksi kolom yang ingin di pindahkan dengan cara mengklik header kolom, atau  dengan cara seleksi salah satu sel pada kolom yang ingin dipindahkan, kemudian tekan CTRL + SPACE.

Copy kolom terpilih dengan cara klik kanan dilanjutkan klik copy.  Atau bisa juga dengan menekan tombol short cut CTRL + C.

menggeser kolom copy dan paste

  • Lalu klik kanan pada kolom tujuan, dan klik Insert Copied Cells.

tehnik copy paste memindahkan kolom


  • Maka akan muncul duplikat dari kolom yang dicopy tadi.



copy paste geser kolom

Selanjutnya kita bisa menghapus (delete) kolom awal yang tidak diperlukan.

Perlu diperhatikan: Hasil dari tehnik copy dan insert copied cells mungkin akan memberikan hasil yang yang tidak diiharapkan misalnya bisa saja data pada kolom hasil copy tidak sama dengan data pada kolom awalnya, atau bahkan error. Ini terjadi biasanya jika kolom yang dicopy berisi formula. 


Merubah Susunan Kolom Menggunakan Macro / VBA


Jika ada banyak sekali tabel serupa namun terletak dalam banyak sheet atau workbook yang berbeda, kemudian kita perlu merubah susunan kolom semua tabel tersebut. Cara apa yang paling efektif?. 
Mengedit satu persatu tabel dengan cara drag & drop, cut/paste maupun copy/paste/delete nampaknya hanya akan menghabiskan hari-hari anda. 

Dalam hal ini Macro mengambil peranannya. Tidak perlu pusing meskipun anda belum memahami bahasa vba. Karena disini kita cukup melakukan record macro untuk proses geser kolom  pada tabel pertama. Sedangkan untuk tabel berikutnya cukup jalankan makro.

  • Masuk ke salah satu sheet yang berisi tabel untuk diatur susunan kolomnnya
  • Masuk ke tab Developer, kemudian klik tombol Record Macro.
  • Maka akan muncul Kotak Dialog Record Macro
  • Pada kotak isian Macro Name, ketik nama macro yang anda kehendaki, misalnya: AturKolom
  • Tentukan shortcut key, misalnya CTRL + q ,  saya pilih huruf “q”  dengan pertimbangan kemudahan untuk mengaksesnya menggunakan jari tangan kiri.
  • Store Macro In, menentukan lokasi file penyimpanan code macro, saya pilih Personal Macro Workbook.
  • Klik tombol OK.
  • Bagan alur untuk memulai record Macro digambarkan sebagai berikut:


record macro geser kolom

  • Selanjutnya lakukan tahapan menggeser kolom dengan tehnik yang sudah dijelaskan sebelumnya. Anda bisa menggunakan salah  satu  atau gabungan dari tehnik drag & drop, cut & paste, maupun copy, paste & delete.
  • Jika pengaturan kolom sudah selesai, maka klik tombol Stop Recording.


tombol record macro

Sekarang anda sudah memiliki macro AturKolom yang siap digunakan untuk mengatur  susunan kolom tabel-tabel lainnya yang serupa.

Cara menggunakannya simple saja, cukup masuk tiap-tiap sheet yang berisi tabel serupa, kemudian tekan CTRL + q  (atau shortcut lainnya sesuai pengaturan yang anda buat), maka macro AturKolom() akan bekerja untuk anda.


Mau lebih cepat lagi?


Jika anda tidak mau membuka sheet satu persatu untuk menjalankan makro AturKolom(), maka dengan sedikit code VBA, anda bisa melakukannya sekaligus terhadap semua sheet dalam satu file.

Kode vba berikut akan bekerja dengan asumsi semua sheet dalam workbook berisi tabel yang sama susunan kolomnya dan akan dirubah menjadi susunan tertentu.

Sub AturKolomSekaligus()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    AturKolom
Next
End Sub

Jika tidak semua sheet berisi tabel yang sama, dan anda hanya ingin mengedit sebagian sheet saja yang berisi tabel, maka anda harus menamai sheet dimaksud secara terstruktur.

Misalnya: semua sheet berisi tabel harus diberinama sheet dengan awalan tabel seperti: tabel1, tabel2, tabel3, tabel4, dan seterusnya.

Dan contoh code VBA yang bisa digunakan untuk kasus ini adalah sebagai berikut:

Sub AturKolomSekaligus()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If Left(ws.Name, 5) = "tabel" Then
        ws.Activate
        AturKolom
    End If
Next
End Sub

Supaya bisa digunakan, code tersebut harus diketik atau di copy di module yang sama dengan lokasi code makro AturKolom() yang sudah kita buat  menggunakan record macro sebelumnya.

Caranya :

  • Klik tombol Visual Basic yang ada pada Tab Developer, atau tekan key short cut ALT + F11.  Langkah ini akan membawa kita pada jendela Visual Basic Editor
  • Pada bagian project explorer cari module yang merupakan lokasi penempatan code. Dalam hal ini module terdapat dalam workbook PERSONAL.XLSB, module1.
  • Double klik module1 tersebut untuk masuk ke jendela kodenya.
  • Di jendela kode, kita akan melihat kode AturKolom() yang ter-generate secara otomatis sesuai langkah-langkah yang kita record sebelumnya. (Dalam contoh yang ditampilkan codenya singkat dan sederhana, dalam aktualnya mungkin lebih panjang tergantung berapa banyak proses drag & drop kolom atau copy/cut paste yang di-record.
  • Ketik atau copy code macro AturKolomSekaligus() pada module tersebut.
  • Perhatikan gambar berikut untuk lebih jelasnya.


module vba atur susunan kolom



Setelah code AturKolomSekaligus() di copy atau diketik di dalam module VBA, maka tahap selanjutnya adalah membuat shorcut supaya code dapat lebih mudah diakses dan cepat dijalankan.
  • Keluar dari jendela VBA dan kembali ke spreadsheet.
  • Dari tab Developer, klik Macros, maka selanjutnya akan muncul kotak dialog Macro.
  • Pada List Macro Name, pilih PERSONAL.XLSB!AturKolomSekaligus, nama ini sesuai nama file dimana code macro tersimpan, dan nama macronya itu sendiri.
  • Klik tombol Options
  • Pada jendela Macro Options, tentukan key shortcut yang diinginkan. Contoh CTRL + SHIFT + C . lagi-lagi shortcut ini saya buat dengan pertimbangan kemudahan akses oleh jari tangan kiri. Tanda shift akan muncul otomatis jika kita mengetikan huruf kapital pada kotak isian key shortcut.
  • Klik tombol OK
  • Selanjutnya Close dari  kotak dialog macro.

Perhatikan kembali ilustrasi langkah-langkah pembuatan shortcut macro berikut:


cara membuat jalan pintas macro


Setelah langkah – langkah diatas dilakukan tanpa kecuali, maka kita sudah bisa menjalankan macro AturKolomSekaligus() untuk merubah susunan kolom semua tabel dalam sebuah file.

Caranya sangat mudah. Cukup buka file yang terdiri atas sheet  berisi tabel, dimana nama sheet sudah diatur secara terstruktur. Dalam contoh ini tabel1, tabel2, tabel3  dst,

Lalu tekan key shortcut CTRL + SHIFT + C atau shortcut lain sesuai pengaturan yang anda sukai. Maka selanjutnya macro AturKolomSekaligus() yang akan menyelesaikan tugas anda dengan cepat.

Demikian pembahasan mengenai beberapa tehnik pemindahan dan penyusunan kolom pada tabel dalam microsoft excel. Semoga bermanfaat.

Baca juga catatan pelajaran excel lainnya:
Dan mohon dicek juga semua artikel dalam blog ini melalui Daftar Isi.

Referensi:

Monday, January 1, 2018

Sortir & Filter Berdasarkan Warna Cell, Font dan Icon

Pada artikel terdahulu, sudah dibahas bagaimana menghitung sel dengan kriteria warna tertentu. Kali ini kita akan kembali bermain-main dengan warna, yaitu bagaimana menyortir dan memfilter data berdasarkan warna background sel dan warna font. Sebagai pelengkap, juga akan dibahas sedikit mengenai bagaimana sortir data berdasarkan icon yang dibuat melalui conditional formating.

Pembahasan mencakup:


  • Cara sortir berdasarkan warna background cell.
  • Cara sortir berdasarkan warna font
  • Cara sortir berdasarkan icon
  • Cara filter berdasarkan warna pada excel
  • Cara filter 2 warna atau lebih


Sortir Data Berdasarkan Warna Sel 


Mulai diperkenalkan pada excel versi 2007, tugas menyortir data berdasarkan warna dapat dilakukan dengan sangat mudah. Tidak perlu code VBA ataupun formula, tetapi cukup hanya dengan menggunakan fitur Custom Sort.

Seleksi tabel atau range sel.
Pada tab Home, pada bagian Editing Group, klik Sort & Filter, kemudian klik Custom Sort…

excel how to custom sort

Setelah muncul kotak dialog Sort, tentukan setting berikut dari kiri ke kanan
  • Column : Merupakan Kolom yang yang ingin anda sortir (contoh : Kategori)
  • Sort on : Cell Color
  • Order : Pilih Warna yang ingin diposisikan bagian atas
  • Pilih On Top Position.


Perhatikan screen shot dibawah, dan pastikan setting dibuat seperti bagian dalam kotak coklat

cara sortir berdasarkan warna


  • Selanjutnya klik tombol Copy Level untuk menambahkan satu atau beberapa level dengan setting yang sama seperti yang sudah dibuat pertama kali. Kemudian dibawah order, seleksi warna kedua level sortir.
  • Ulangi langkah di atas untuk menambahkan level sortir sesuai keperluan
  • Screenshot berikut menggambarkan setting sortir sampai dengan 4 level warna.

cara sortir berdasarkan warna sel

  • Langkah berikutnya : Klik Ok dan verifikasi jika baris data sudah di sortir dengan benar.

Setelah melakukan langkah di atas maka kita sudah berhasil mensortir data berdasarkan warna sel seperti gambar berikut:


hasil sortir bersarkan warna sel


Catatan: Sortir hanya berdasarkan warna kolom yang ditentukan dalam tabel, jadi tidak perlu khawatir meskipun warna sel antara kolom yang satu dengan yang lainnya beraneka ragam. Kita tidak perlu menentukan rule di setiap kolom, tetapi cukup mengatur rule sortir pada kolom yang diperlukan saja. Dalam contoh di atas, misalnya kita bisa mengatur rule pada kolom NIS, Nama, Nilai Ujian, atau Kategori saja.

Jika kita menginginkan sortir hanya berdasarkan satu warna saja, maka kita dapat melakukannya dengan lebih cepat. Carannya cukup menggunakan autofilter.

Untuk memunculkan autofilter, seleksi range tabel atau satu sel dalam tabel dan tekan shortcut CTR + SHIFT + L, kemudian klik tanda segitiga kecil pada judul kolom, lalu  klik Sort by Color dan selanjutnya pilih warna. Warna terpilih akan otomatis berada pada level paling atas.

autofilter sortir berdasarkan warna


Sortir Data Berdasarlan Warna Font

Sortir berdasarkan warna font, prinsipnya sama dengan cara sortir berdasarkan warna background cell seperti yang sudah dijelaskan di atas. Hanya satu hal yang  membedakannya, yaitu pada bagian Sort on. Pada bagian ini pastikan dipilih Font Color, seperti ditunjukan dalam screenshot berikut:

sortir berdasarkan warna font

Jika hanya perlu mengecek satu warna saja pada bagian paling atas,  maka kita bisa menggunakan fitur autofilter. Untuk memunculkan autofilter: seleksi tabel, kemudian tekan CTR+Shift+L. klik tanda segitiga kecil pada header tabel, kemudian klik Sort by Font Color  dan pilih warna yang diinginkan.

cara autofilter untuk sortir berdasarkan warna font

Selain sortir berdasarkan cell color maupun font color, kita juga dapat melakukan tehnik sortir lainnya berdasarkan skenario yang sesuai.

Sortir Data Berdasarkan Icon


Misalnya kita menerapkan conditional formating icon bedasarkan nilai ujian masing masing siswa seperti terlihat pada gambar berikut:

cara sortir berdasarkan icon

Dapat kita lihat  pada kolom nilai dibagi menjadi 3  bagian, nilai yang tinggi ditandai icon warna hijau, bagian pertengahan ditandai icon kuning dan bagian nilai bawah ditandai icon merah. Karena belum dilakukan sortir, maka pengelompokan berdasarkan icon belum tersusun dalam order yang sesuai.

Cara melakukan sortir icon ini tidak jauh berbeda dengan 2 cara yang sudah dibahas sebelumnya. Yang membedakan adalah pengaturan pada bagian sort order, harus dipilih Cell Icon.
Kemudian pada bagian Order pilih atau tentukan icon mana yang akan disusun sesuai urutan levelnya.

Perhatikan screenshot berikut:

cara sortir dengan icon

Dan hasil akhir setelah dilakukan sortir maka data akan tersusun  berdasarkan icon seperti diperlihatkan dalam screenshot contoh dibawah ini.

Hasil Sortir Icon

Cara Filter Berdasarkan Warna Pada Excel


Jika kita ingin memfilter baris data dalam tabel berdasarkan warna pada kolom tertentu, maka kita dapat melakukannya dengan sangat mudah menggunakan fitur AutoFilter >> Filter By Color.
Kemudian kita bisa memilih jenis filternya apakah berdasarkan warna sel (Filter by Cell Color) atau Warna font (Filter by Font Color).

Pilihan warna cell atau warna font akan muncul secara otomatis tergantung warna yang tersedia dalam kolom terpilih.

Autofilter by font color


Cara Filter Beberapa Warna Sekaligus


Sayangnya fitur Filter by Color ini masih memiliki keterbatasan yaitu hanya mengizinkan filter 1 warna saja. Sebagai solusinya kita bisa menggunakan User Defined Function (UDF) sederhana untuk mengambil data numerik warna dan kolom bantu sehingga dapat melakukan filter terhadap dua warna atau lebih.

Berikut contoh code VBA super singkat dan sederhana untuk membuat UDF yang saya berinama WarnaSel()

Function WarnaSel(sel)
WarnaSel = sel.Interior.Color
End Function

Supaya UDF ter sebut bisa digunakan maka Copy code tersebut harus di copy atau diketik pada module standar VBA.

Caranya:

Tekan ALT + F11 untuk masuk ke jendela VBA  , kemudian klik menu Insert  >> Module , lalu ketik atau copy code diatas seperti dicontohkan pada gambar berikut:


vba untuk ambil warna sel


Kembali ke tabel yang akan difilter, tambahkan kolom bantu dengan nama  header kolom Filter Warna. Kemudian tekan dua kali shortcut CTR+SHIFT+L untuk menambahkan autofilter pada kolom bantu tersebut.

Masukan formula menggunakan fungsi WarnaSel pada kolom bantu. Dalam contoh berikut pada sel E2 masukan rumus =WarnaSel(D2).  Rumus tersebut akan menghasilkan bilangan yang menunjukan code warna sel D2. Lalu copy rumus tersebut sampai baris akhir tabel.


cara menggunakan vba untuk ambil warna cell


Kemudian lakukan filter dengan cara klik tanda segitiga kecil pada kolom Filter Warna. Selanjutnya pilih code warna tertentu yang akan difilter, kemudian tekan tombol OK. Perhatikan kembali langkah-langkah tersebut seperti dicontohkan dalam gambar di bawah ini.

auto filter beberapa warna

Maka hasilnya adalah tabel di filter hanya memunculkan warna terpilih. Dalam contoh kita dapat melihat tabel hanya menampilkan dua warna terpilih.

Hasil  filter beberapa warna sekaligus

Dengan cara yang serupa selanjutnya kita juga bisa memfilter data berdasarkan warna font. Tentu saja dengan menggunakan bantuan UDF. Berikut code VBA-nya.

Function WarnaFont(sel)
WarnaFont = sel.Font.Color
End Function

--------

Demikian panduan singkat bagaimana cara sortir dan filter data berdasarkan warna sel, warna font, dan icon. Semoga bermanfaat.

Terimakasih.

Artikel terkait:

Referensi:

Monday, December 18, 2017

Merubah Warna Sel Berdasarkan Value dan Formula.

Merubah warna sel? Saya rasa, hal ini bukanlah hal yang asing bagi setiap pengguna excel. Selain untuk  estetika, pewarnaan sel juga dapat membantu mempermudah membaca, menemukan dan menganalisa data tertentu. Cukup dengan seleksi sel atau range tertentu, kemudian dengan menggunakan tombol fill color”, ataupun dengan memilih style tertentu maka kita sudah bisa menghiasi spreadsheet dengan aneka warna.

Kalau sudah tahu bahwa mewarnai sel bukanlah hal yang asing, jadi kenapa artikel ini masih membahas perihal merubah warna sel?

Nah makanya artikel ini dibuat yaitu untuk membahas beberapa tehnik mewarnai cell dengan cara yang agak jarang digunakan oleh kebanyakan user.

Adapun pembahasan difokuskan pada 2 hal:

  1. Merubah warna sesuai nilai bilangan dalam sel 
  2. Merubah warna sel kosong dan sel error.

Dan pada masing masing 2 hal tersebut dibagi lagi 2 pembahasan yaitu :

  • Tehnik conditional formating yang menghasilkan perubahan warna yang bersifat dinamis artinya warna akan berubah secara otomatis mengikuti kriteria atau kondisi yang ditetapkan.
  • Tehnik pencarian dan seleksi sel khusus sehingga dapat dilakukan formating secara sekaligus pada seleksi sel dengan kriteria atau kondisi tertentu. Formating dengan tehnik ini bersifat statis, artinya warna sel tidak bisa berubah secara otomatis jika ada perubahan kondisi pada nilai sel.

Baiklah, mari kita bahas satu per satu.

Bagaimana merubah warna sel berdasarkan value dalam sel.


Conditional Formating : Merubah Warna Secara Dinamis Berdasarkan Cell Values.


Misalnya kita memiliki sebuah tabel berisikan sejumlah data dan kita ingin mewarnai atau meng-highlight sel berdasarkan kriteria nilai atau kondisi tertentu. Sayangnya data tersebut bisa berubah sewaktu-waktu dan kita tidak ingin repot merubah warna secara manual setiap kali ada perubahan data

Anggaplah tabel tersebut berupa data jumlah penjualan produk tertentu di lokasi pemasaran yang dibagi menjadi beberapa area. Selanjutnya kita ingin meng-highlight sel berisi data penjualan yang lebih dari 250 unit.

Di sinilah conditional formating memainkan perannya.

Perhatikan screenshot berikut:

Merubah Warna Cell Cepat Sesuai Warna


Catatan: screenshots dalam artikel ini diambil menggunakan excel 2010, akan tetapi tombol, dialog box dan settingnya hampir saja baik menggunakan excel 2010, 2013 maupun 2016.

Lakukan langkah - langkah berikut untuk membuat rule conditional formating merubah warna sesuai value.

Seleksi tabel atau range dimana kita ingin merubah warna background sel. Dalam hal contoh yang kita gunakan dalam pembahasan ini, lakukanlah seleksi pada range B2:H8 (baris pertama yang berisi nama kolom dan kolom pertama yang berisi nama area tidak termasuk area yang diseleksi).

Masuk ke tab Home, kemudian grup Style dan pilih Conditional FormatingNew Rule


Cara Menggunakan Conditional Formating Excel

  • Di dalam kotak dialog New Formating Rule, pada bagian “Select a Rule Type” pilih “Format only cells that contain” 
  • Kemudian lanjutkan ke bagian bawah dialog box pada bagian Edit Rule DescriptionFormat Only Cells With dimana terdapat 3 field yang perlu dipilih atau diisi. 
    • Pada bagian paling kiri Pilih Cell Value
    • Pada bagian tengah pilih greater than 
    • Dan pada bagian paling kanan  ketikan angka 250
  • Kemudian klik tombol  format untuk memilih warna background yang akan diaplikasikan ketika kondisi terpenuhi.
  • Perhatikan langkah-langkah nya dalam screenshot di bawah ini.


Menggunakan Conditional Formating Cell Value

Di dalam kotak dialog format cells, masuk ke tab Fill dan kemudian pilih warna yang anda inginkan, misalnya hijau, dan kemudian klik OK.

set warna conditional formating

Setelah menekan tombol OK, maka kita akan kembali melihat jendela Edit Formating Rule untuk melihat preview dari format yang sudah dibuat. Jika dirasa sudah oke, maka klik tombol OK.


Cara Conditional Formating

Dan hasilnya adalah seperti terlihat seperti screenshot di bawah ini. Semua sel berisi value lebih dari 250 akan berwarna hijau atau warna lain sesuai pengaturan pada langkah sebelumnya. Warna hijau tersebut akan kembali menjadi putih jika kita merubah value sel menjadi 250 atau kurang. Dengan kata lain warna sel bersifat dinamis.


Trik Conditional Formating


Seandainya ada lebih dari satu kondisi yang harus ditampilkan dalam format berbeda, maka kita dapat menambahkan format lainnya dengan cara yang sama dengan langkah sebelumnya yaitu dari tab Home, kemudian klik Conditional Formating.

Pada dialog box Edit Formating Rule pilih Format Only Cells That Contain, dan pda bagian Format Only Cells With pilih Cell Value , dan Less Than Or Equal to, kemudian ketikan angka misalnya 150. Rule ini untuk memberikan format pada sel yang berisi angka 150 atau kurang.


Tips Conditional Formating


Setelah menekan tombol OK, maka kita akan mendapatkan tabel dengan warna sel berwarna merah muda untuk data penjualan dengan nilai 150 atau kurang.


Conditional Formating Untuk Warna Dinamis


Dengan cara yang sama, kita juga dapat menambah dan mengatur format yang diinginkan. Untuk memprcantik tampilan data, selain merubah warna background sel, kita juga dapat mengatur format lainnya seperti font, warna font, border dan sebagainya.

Cara Menemukan dan Menyeleksi Semua Sel Berisi Nilai Tertentu dan Kemudian Mewarnainya.


Tehnik ini mencoba memberikan alternative lain seleksi sel tertentu menggunakan fitur Find & Select. 

Fitur ini sebenarnya sudah cukup populer digunakan untuk mencari data tertentu dalam spreadsheet.

Namun langkah menyeleksi sel dengan kriteria tertentu menggunakan fitur ini masih sering luput dari perhatian. Hal ini wajar saja karena kebanyakan user lebih senang menyeleksi range atau sel tertentu satu persatu menggunakan mouse kemudian merubah format sesuai yang diinginkan. 

Sedangkan dengan fitur yang akan dijelaskan ini, kita bisa bisa mencari nilai dengan kriteria tertentu dalam range  data, kemudian menerapkan format secara bersamaan. Penerapannya Ada beberapa skenario tergantung jenis data atau nilai yang kita cari.

Misalnya kita perlu menemukani sel dengan nilai tertentu, anggaplah 100 Maka cukup masuk ke tab Home, Editing group dan klik Find & Select → Find…


Menggunakan Find and Select


Masukan nilai yang diinginkan dan kemudian klik tombol Find All.


Menggunakan Find and Replace


Tip: Klik tombol Options  untuk mendapatkan opsi pencarian yang lebih advanced seperti “Match Case”, “Match entire cell contents”. 

Untuk mendapatkan hasil pencarian dengan scope yang lebih lebar, kita bisa menggunakan karakter wildcard seperti asterisk (*) untuk menemukan beberapa karakter tertentu atau tanda tanya (?) untuk menemukan karakter tunggal. Karakter wildcard tersebut diketikan pada field Find What bersamaan dengan angka kunci.

Misalnya kita ingin mencari dan menyeleksi sel berisi nilai 200 atau lebih tetapi kurang dari 300.

  • Seleksi range B2:H8
  • Dari Tab Home, kemudian klik tombol Find & Select, klik Find.  Atau bisa juga menggunakan shorcut CTR + F
  • Pada kotak isian Find What, ketik 2??
  • Klik Tombol Find All
  • Tekan keyboard CTR + A untuk menyeleksi semua sel yang memenuhi kritera pencarian
  • Close jendela Find & Replace
  • Maka kita akan mendapatkan semua nilai yang memenuhi kriteria nilai 200 lebih tetapi kurang dari 300 yang sudah terseleksi.
  • Lakukan edit format seperti biasa, misalnya dengan fitur autofill atau dengan klik kanan Format Cell atau melalui command-command lainnya yang kita sukai.


Find and Select Untuk Mewarnai Sel

Merubah Warna Background Sel Khusus (Blanks dan Formula Error)


Seperti pada contoh sebelumnya pada section ini juga akan dibahas bagaimana merubah warna secara dinamis dan secara statis. Conditional Formating untuk merubah warna secara dinamis. Sedangkan untuk perubahan warna secara statis, kita gunakan bantuan alat Go to Special untuk menyeleksi sel dengan kriteria yang kita inginkan dan kemudian merubah formatnya dengan cara-cara yang sudah kita ketahui.

Conditional Formating : Menggunakan Formula Untuk Merubah Warna  Sel Kosong dan Sel Error.


Metode ini bisa menjadi solusi yang kemungkinan besar sangat anda perlukan dan banyak kasus. 
Kita akan menggunakan table nilai penjualan di beberapa area dan beberapa bulan seperti contoh sebelumnya, dengan perubahan beberapa kasus data dimana terdapat bebeapa sel kosong. Perhatikan bagaimana kita bisa mendeteksi semua sel kosong tersebut dan merubah warnanya secara bersamaan. 

Contoh Tabel Untuk Conditional Formating


  • Seleksi range sel yang akan kita atur warnanya sesuai kondisi blank atau error, misalnya range B2:H8
  • Masuk ke Tab Home, kemudian pada grup Style, klik Conditional FormmatingNew Rule
  • Kemudian di dalam kotak dialog “New Formating Rule”, pilih option “Use a formula to determine which cells to format”.  Lalu masukan salah satu formula berikut di dalam field “Format falues where this formula is true” 
    • Untuk merubah warna sel kosong (blank cells) rumusnya =ISBLANK()
    • Untuk merubah warna sel yang mengandung nilai error =ISERROR() 
  • Anggaplah kita lebih tertarik untuk merubah warna sel kosong maka masukan formula =ISBLANK(), kemudian tempatkan kursor di antara tandak kurung dan dengan menggunakan mouse, klik sel paling pojok kiri atas dari range yang diseleksi sebelumnya, kemudian delete tanda dolarnya, sehingga rumus menjadi ISBLANK(B2). 
  • Kita juga dapat mengetikan referensi B2 secara langsung sebagai parameter fungsi ISBLANK()
  • Kemudian tekan tombol format, dan atur formatnya pada bagian Fill, kemudian tekan tombol OK, dan Tekan tombol Ok lagi.

Untuk lebih jelasnya perhatikan langkah – langkah berikut berikut:

Membuat Formula Untuk Conditional Formating


Perlu diperhatikan, bahwa B2 merupakan alamat sel pertama atau sel yang paling kiri dan paling atas dari range yang diseleksi. Penulisan referensi alamat sel tidak menggunakan tanda dolar artinya alamat tersebut bersifat relatif agar formula pada conditional formating setiap sel mengambil referensi dari sel itu sendiri. Jika referensi ditulis $B$1 maka perubahan pada sel B1 akan berdampak pada format seluruh sel dalam lingkup conditional formating.

Screenshot berikut menunjukan hasil dari conditonal formating menggunakan formula untuk mewarnai sel kosong atau blank cells

Contoh Tabel Conditional Formating


Dengan tehnik serupa kita juga bisa mewarnai sel yang mengandung formula dengan nilai error. Caranya  cukup dengan mengganti rumus ISBLANK(B2) menjadi ISERROR(B2). Silahkan dicoba.

Cara Menyeleksi Seleksi Seluruh Cell Kosong atau Cell Error Dan Kemudian Mewarnainya.

Selain menggunakan conditional formating yang dapat merubah warna sel secara dinamis mengikuti kondisi blank atau nilai error, kita juga dapat menyeleksi sel kosong ataupun error menggunakan alat Go To Special dan kemudian mewarnainya dangan fill color untuk menghasilkan format warna yang statis.

Caranya cukup mudah

  • Seleksi range yang akan dicari sel kosong atau error nya, misalnya range B2:H8
  • Kemudian dari Tab Home, grup Editing, klip Tombol Find & Select dan klik Go To Special.. ini untuk memunculkan kotak dialog Go To Special.


Menggunakan Fitur Go To Special


Tips: Selain menggunakan cara di atas, kotak dialog Go To Special juga dapat dimunculkan dengan menggunakan shortcut CTR+G   atau F5.


Go To Special Dialog Blox


  • Untuk menyeleksi sel kosong, pilih opsi Blanks
  • Untuk menyeleksi formula error, pilih opsi Formula, kemudian aktifkan centang pada cek box Error, dan cek box lainnya non aktifkan.
  • Klik tombol Ok
  • Terakhir : Warnai menggunakan Fill Color ataupun format sel lainnya.
  • Selesai.

----
Sampai disini pembahasan mudah mudahan bermanfaat. Yaitu Mewarnai sel secara dinamis menggunakan conditional formating. Juga mewarnai sel secara statis dengan cara-cara yang umum dilakukan, namun cara menyeleksi sel yang sedikit berbeda sehingga dapat melakukan formating secara sekaligus terhadap sel yang memenuhii kriteria.

Silahkan di cek juga catatan pelajaran excel lainnya:

Referensi: