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…

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

• 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.

• 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:

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.

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:

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.

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:

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:

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

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.

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:

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.

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.

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

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:

Rumus Untuk Menghitung Sel Dengan Warna Tertentu

Merubah Warna Sel Berdasarkan Value dan Formula

Cara Cepat Hapus Baris Kosong

Cara Cepat Isi Sel Kosong

Referensi:

https://www.ablebits.com/office-addins-blog/2013/12/18/sort-by-color-excel/

Sertifikatkan kemampuan Komputer dan Bahasa Inggris anda
Sertifikat Komputer dan TOEFL tanpa ikut kursus, Ikuti Paket Ujian Saja
Klik Disini