Merubah Warna Sel Menurut Value Dan Formula.

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

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

Nah artikel ini dibentuk yaitu untuk membahas beberapa tehnik mewarnai cell dengan cara yang agak jarang dipakai 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 sanggup dilakukan formating secara sekaligus pada seleksi sel dengan kriteria atau kondisi tertentu. Formating dengan tehnik ini bersifat statis, artinya warna sel tidak sanggup berubah secara otomatis kalau ada perubahan kondisi pada nilai sel.

Baiklah, mari kita bahas satu per satu.

Bagaimana merubah warna sel menurut value dalam sel.

Conditional Formating : Merubah Warna Secara Dinamis Berdasarkan Cell Values.

Misalnya kita mempunyai sebuah tabel berisikan sejumlah data dan kita ingin mewarnai atau meng-highlight sel menurut kriteria nilai atau kondisi tertentu. Sayangnya data tersebut sanggup 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 memakai excel 2010, akan tetapi tombol, obrolan box dan settingnya hampir saja baik memakai excel 2010, 2013 maupun 2016.

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

Seleksi tabel atau range dimana kita ingin merubah warna background sel. Dalam hal pola 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 obrolan New Formating Rule, pada bab “Select a Rule Type” pilih “Format only cells that contain” 
• Kemudian lanjutkan ke bab bawah obrolan box pada bab Edit Rule Description → Format Only Cells With dimana terdapat 3 field yang perlu dipilih atau diisi. 
• Pada bab paling kiri Pilih Cell Value, 
• Pada bab tengah pilih greater than 
• Dan pada bab paling kanan  ketikan angka 250, 
• Kemudian klik tombol  format untuk menentukan warna background yang akan diaplikasikan dikala kondisi terpenuhi.
• Perhatikan langkah-langkah nya dalam screenshot di bawah ini.

Di dalam kotak obrolan format cells, masuk ke tab Fill dan kemudian pilih warna yang anda inginkan, contohnya 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 kesudahannya ialah ibarat terlihat ibarat 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 kalau 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 sanggup menambahkan format lainnya dengan cara yang sama dengan langkah sebelumnya yaitu dari tab Home, kemudian klik Conditional Formating.

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

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

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

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

Tehnik ini mencoba menunjukkan alternative lain seleksi sel tertentu memakai fitur Find & Select. 

Fitur ini sebetulnya sudah cukup terkenal dipakai untuk mencari data tertentu dalam spreadsheet.

Namun langkah menyeleksi sel dengan kriteria tertentu memakai fitur ini masih sering luput dari perhatian. Hal ini masuk akal saja alasannya ialah kebanyakan user lebih bahagia menyeleksi range atau sel tertentu satu persatu memakai mouse kemudian merubah format sesuai yang diinginkan. 

Sedangkan dengan fitur yang akan dijelaskan ini, kita sanggup 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 mendapat opsi pencarian yang lebih advanced ibarat “Match Case”, “Match entire cell contents”. 

Untuk mendapat hasil pencarian dengan scope yang lebih lebar, kita sanggup memakai aksara wildcard ibarat asterisk (*) untuk menemukan beberapa aksara tertentu atau tanda tanya (?) untuk menemukan aksara 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 sanggup juga memakai 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 mendapat semua nilai yang memenuhi kriteria nilai 200 lebih tetapi kurang dari 300 yang sudah terseleksi.
• Lakukan edit format ibarat biasa, contohnya 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 pola 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 pertolongan 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 sanggup menjadi solusi yang kemungkinan besar sangat anda perlukan dan banyak kasus. 

Kita akan memakai table nilai penjualan di beberapa area dan beberapa bulan ibarat pola sebelumnya, dengan perubahan beberapa kasus data dimana terdapat bebeapa sel kosong. Perhatikan bagaimana kita sanggup mendeteksi semua sel kosong tersebut dan merubah warnanya secara bersamaan. 

• Seleksi range sel yang akan kita atur warnanya sesuai kondisi blank atau error, contohnya range B2:H8
• Masuk ke Tab Home, kemudian pada grup Style, klik Conditional Formmating → New Rule
• Kemudian di dalam kotak obrolan “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 memakai mouse, klik sel paling pojok kiri atas dari range yang diseleksi sebelumnya, kemudian delete tanda dolarnya, sehingga rumus menjadi ISBLANK(B2). 
• Kita juga sanggup mengetikan rujukan B2 secara eksklusif sebagai parameter fungsi ISBLANK()
• Kemudian tekan tombol format, dan atur formatnya pada bab 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 rujukan alamat sel tidak memakai tanda dolar artinya alamat tersebut bersifat relatif supaya formula pada conditional formating setiap sel mengambil rujukan dari sel itu sendiri. Jika rujukan ditulis $B$1 maka perubahan pada sel B1 akan berdampak pada format seluruh sel dalam lingkup conditional formating.

Screenshot berikut mengambarkan hasil dari conditonal formating memakai formula untuk mewarnai sel kosong atau blank cells

Dengan tehnik serupa kita juga sanggup 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 memakai conditional formating yang sanggup merubah warna sel secara dinamis mengikuti kondisi blank atau nilai error, kita juga sanggup menyeleksi sel kosong ataupun error memakai 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, contohnya range B2:H8
• Kemudian dari Tab Home, grup Editing, klip Tombol Find & Select dan klik Go To Special.. ini untuk memunculkan kotak obrolan Go To Special.

Tips: Selain memakai cara di atas, kotak obrolan Go To Special juga sanggup dimunculkan dengan memakai 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 memakai Fill Color ataupun format sel lainnya.
• Selesai.

----

Sampai disini pembahasan gampang mudahan bermanfaat. Yaitu Mewarnai sel secara dinamis memakai conditional formating. Juga mewarnai sel secara statis dengan cara-cara yang umum dilakukan, namun cara menyeleksi sel yang sedikit berbeda sehingga sanggup melaksanakan 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/