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:

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 Formating → New Rule

• 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 Description → Format 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.

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

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.

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.

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.

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

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…

Masukan nilai yang diinginkan dan kemudian klik tombol Find All.

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.

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. 

• 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 Formmating → New 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:

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

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.

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

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

• Cara Efektif Menggunakan Format Painter Secara Berulang
• Menghitung Jumlah Sel Berwarna Tertentu.
• Cara Cepat Menghapus Baris Kosong
• Cara Cepat Mengisi Sel Kosong

Referensi:

https://www.ablebits.com/office-addins-blog/2013/10/18/change-background-color-excel-based-on-cell-value/

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