Tutorial ini membahas cara otomatis merubah warna baris (row) Excel, dimana kita akan mewarnai sel-sel yang sebaris berdasarkan nilai sel tertentu dalam baris tersebut.
Ada 6 contoh kriteria yang digunakan, yang bisa Anda lihat pada daftar isi berikut.
DAFTAR ISI
- Cara Memasukkan Rumus pada Conditional Formatting
- 1. Mewarnai Baris yang Tidak Kosong
- 2. Mewarnai Baris Bila Ada Sel Kosong
- 3. Mewarnai Baris Bila Sel Memiliki Angka Tertentu
- 4. Mewarnai Baris Bila Sel Memiliki Huruf atau Kata Tertentu
- 5. Mewarnai Baris berdasarkan Panjang Teks
- 6. Mewarnai Baris Berdasarkan 2 Kriteria atau Lebih
- Solusi Error Warna Baris
Lihat juga ini: 7++ Trik untuk Conditional Formatting di Excel, berisi petunjuk mewarnai baris dan kolom ganjil genap, membuat Gantt Chart, menyorot data yang error, menandai data duplikat atau unik.
Cara Memasukkan Rumus pada Conditional Formatting
Untuk secara otomatis mewarnai baris Excel, maka kita akan memasukkan formula atau rumus pada Conditional Formatting.
Jika Anda belum familiar menggunakan rumus Conditional Formatting, silakan baca petunjuknya pada langkah-langkah di bawah ini.
Langkah-langkah:
- Pilih kumpulan baris yang akan diberi warna bila kriteria yang ditentukan terpenuhi.
- Di tab Home, grup Styles, klik Conditional Formatting, dan pilih New Rule.
- Di bagian:
- Select a Rule Type: pilih Use a formula to determine which cells to format.
- Di kotak Format values where this formula is true, masukkan formula atau rumus.
- Klik tombol Format, pada tab Fill pilih warna yang diinginkan.
- Klik OK untuk menutup semua kotak dialog.
Tips:
Bila ingin sekaligus merubah warna font, maka pada langkah 3c, pilih juga tab Font dan pilih warna yang diinginkan.
Selanjutnya gunakan rumus-rumus di bawah ini untuk mewarnai baris sesuai kriteria yang ditentukan.
1. Mewarnai Baris yang Tidak Kosong
Misalnya kita memiliki data pada sel A1:C6. Selanjutnya kita akan mewarnai baris bila sel kolom C tidak kosong.
Rumus:
=LEN(TRIM($C2))>0
Keterangan Rumus:
- Fungsi LEN berguna untuk menghitung jumlah karakter.
- Fungsi TRIM berguna untuk menghapus spasi kosong di awal, tengah dan akhir teks. Fungsi ini juga sangat berguna untuk sel yang terlihat kosong tapi sesungguhnya berisi spasi. Karena hal ini akan menyebabkan baris tidak akan diwarnai.
Lihat di sini:
- 2 Cara Menghapus Spasi pada Angka Excel
- 2 Cara Menghapus Spasi Ekstra pada Teks Excel
- Cara Menghapus Spasi dan Tanda Penghubung di Akhir Sel Excel
2. Mewarnai Baris Bila Ada Sel Kosong
Di cara ini, kita akan mewarnai baris bila ada sel yang kosong.
Seperti contoh data pada sel A1:C6, kita akan mewarnai baris yang memiliki sel kosong di kolom C.
Rumus:
=LEN(TRIM($C2))<1
3. Mewarnai Baris Bila Sel Memiliki Angka Tertentu
Misalkan kita akan mewarnai baris bila nilai sel di kolom B lebih besar dari 15.
Untuk itu, masukkan rumus berikut.
=$B2>15
4. Mewarnai Baris Bila Sel Memiliki Huruf atau Kata Tertentu
Misalkan kita akan mewarnai baris bila sel kolom C memiliki huruf Y.
Untuk itu, masukkan rumus berikut.
=$C2="Y"
Catatan:
Berbeda dengan angka, kriteria berupa huruf atau teks dalam rumus harus diberi tanda petik dua.
5. Mewarnai Baris berdasarkan Panjang Teks
Misalkan kita akan mewarnai baris bila sel pada kolom A memiliki jumlah karakter di atas 6.
Rumus:
=LEN(TRIM($A2))>6
6. Mewarnai Baris Berdasarkan 2 Kriteria atau Lebih
Pada contoh ini, baris akan diwarnai apabila kedua kriteria berikut terpenuhi:
- Kolom B memiliki nilai lebih besar dari 15
- DAN kolom C memiliki huruf Y
Untuk itu, gunakan fungsi AND dalam rumus.
=AND($B2>15;$C2="Y")
Atau gunakan rumus berikut bila komputer Anda menggunakan tanda pemisah koma.
=AND($B2>15,$C2="Y")
Bagaimana bila kita ingin salah satu kriteria saja yang terpenuhi? Jadi tidak harus kedua-duanya.
Misalnya, warnai baris apabila:
- Kolom B memiliki nilai lebih besar dari 15
- ATAU kolom C memiliki memiliki huruf Y
Contoh hasilnya seperti pada gambar berikut.
Untuk itu, gunakan fungsi OR dalam rumus.
=OR($B2>15;$C2="Y")
Atau gunakan rumus ini bila komputer Anda menggunakan tanda pemisah koma.
=OR($B2>15,$C2="Y")
Solusi Error Warna Baris
Masalah:
Anda mungkin menemukan masalah dimana warna tidak diterapkan pada baris, tapi pada sel tertentu saja.
Contohnya seperti gambar berikut. Kolom C diwarnai semuanya, sedangkan kolom B diloncat alias tidak ada warna sama sekali.
Penyebab:
Masalah ini bisa terjadi apabila tidak menyertakan tanda $ (tanda dolar) di depan nama kolom.
Contoh, rumus harusnya seperti ini.
=LEN(TRIM($C2))>0
Namun yang dimasukkan adalah ini.
=LEN(TRIM(C2))>0
Solusi:
Perbaiki rumus Conditional Formatting untuk menyelesaikan masalah ini, dengan menambahkan tanda $ di depan nama kolom.
Sebagai tambahan, cek juga apakah sel yang ditulis sudah benar. Contoh, harusnya sel C2, tapi tertulis C12 atau A2.
