Menggunakan Rumus Conditional Formatting Berdasarkan Cell Lain

Conditional formatting Excel sarat dengan format-format siap pakai dan cocok untuk banyak kondisi.

Bila belum cukup, Excel memungkinkan kita untuk membuat custom conditional formatting, di mana kita menerapkan aturan-aturan dan format sendiri.

Bila format siap pakai dan custom tetap belum sesuai kebutuhan, kita bisa menggunakan rumus conditional formatting berdasarkan cell lain.

Perhatian: Bila teman-teman belum paham dasar conditional formatting, kami sarankan untuk menunda baca artikel ini dan memahami dulu dasar-dasarnya.

Data pada demo-demo di bawah terdapat pada file berikut:

File: conditional-formatting-formula-data.xlsx.

Apa Itu Menggunakan Rumus Conditional Formatting Berdasarkan Cell Lain?

Menggunakan rumus conditional formatting berdasarkan cell lain adalah cara menggunakan conditional formatting dengan menambah rumus atau formula untuk mengolah nilai yang berada pada cell tertentu.

Dengan memasukkan suatu cell sebagai referensi, kita terhindar dari keharusan mengganti besaran nilai setiap kali terjadi perubahan.

Mengapa Menggunakan Rumus Conditional Formatting?

Format-format siap pakai conditional formatting amat memadai untuk kebutuhan dasar.

Pada kasus lebih kompleks, conditional formatting punya keterbatasan. Contoh: Penggunaan kriteria kompleks untuk menandai suatu cell.

Beberapa format siap pakai seperti icon sets dan data bars bisa diperluas kemampuannya menggunakan rumus atau formula yang mengacu pada nilai cell tertentu.

Fleksibilitas rumus conditional formatting hanya terbatas pada kemampuan kita dalam menyusun logika pengaturan.

Cara Menggunakan Rumus Conditional Formatting Berdasarkan Cell Lain

Menggunakan rumus conditional formatting berdasarkan cell lain memerlukan langkah singkat (tapi putar otak yang lebih panjang).

Demo berikut bertujuan untuk:

• Mencari salesman dengan sales call nol di pekan awal Januari.
• Bila ada, warnai cell di mana sales call nol tersebut berada.

(Demo 01) Berikut cara menggunakan rumus conditional formatting berdasarkan celll lain.

1. Pilih range $C$3:$C$14.
2. Klik Conditional Formatting → New Rule.
3. Pada Select a Rule Type, pilih Use a formula to determine which cells to format.
4. Masukkan rumus yang diinginkan, yaitu =$C3=0.
5. Klik tombol Format untuk memilih format yang diinginkan.
6. Klik tombol OK.

Tips: Gunakan mode Edit dengan menekan kunci F2 pada saat memasukkan rumus di Langkah 4. Dengan demikian, Excel tidak memasukkan cell-cell referensi yang tidak kita butuhkan saat kita menekan kunci panah di keyboard.

Gambar di bawah adalah hasil Demo 01.

Catatan: Tanda $ pada $C3 adalah tanda absolut. Secara keseluruhan $C3 pada rumus di atas dibaca “Periksa data hanya pada kolom C dan dimulai dari baris 3″.

Syarat Rumus Conditional Formatting Excel

Rumus apa saja yang bisa digunakan di dalam conditional formatting?

Perhatikan caption text box rumus pada Gambar 04.

Di situ tertulis Format values where this formula is true.

Secara prinsip, rumus yang bisa digunakan pada conditional formatting adalah rumus Boolean.

Rumus Boolean adalah rumus yang menghasilkan nilai TRUE (Benar) atau FALSE (Salah).

Bila rumus Boolean di conditional formatting menghasilkan TRUE, maka format akan dijalankan. Bila tidak, maka cell akan dibiarkan apa adanya.

Ada dua cara untuk membentuk rumus Boolean di Excel, yaitu:

1. Dengan membandingkan nilai dari suatu cell atau fungsi dengan nilai lain. Contoh: =$A1="Nina".
2. Dengan fungsi-fungsi yang mengembalikan nilai Boolean seperti NOT, OR, AND, ISBLANK, atau dan lain-lain. Contoh: =OR($A1=4;$A1=8).

Silahkan teman-teman baca artikel berikut untuk penjelasan lebih detail mengenai rumus Boolean.

Baca: Konsep Dasar Logika di Excel.

Tips Rumus Excel Agar Hasil Sesuai Rencana

Bila teman-teman biasa bekerja dengan rumus, pasti kenal dengan pusing kepala dan mata perih karena hasil rumus tidak sesuai harapan.

Resiko yang sama juga akan teman-teman temui saat bekerja menggunakan conditional formatting dengan rumus.

Berikut beberapa tips rumus Excel untuk menghindarinya.

1. Conditional formatting banyak menggunakan alamat cell absolut. Pastikan teman-teman sudah menggunakannya dengan benar.

Perhatikan bahwa =C17=T4, =$C17=T4, =C$17=T4, dan =$C$17=T4 akan atau bisa memberikan hasil yang berbeda.

2. Pastikan range yang kita masukkan ke dalam rumus sudah benar.

Menggunakan alamat range yang salah mudah dilakukan saat bekerja dengan kelompok data besar.

3. Sertakan cell paling kiri atas range data saat menuliskan alamat referensi (Lihat rumus pada Gambar 02).

Sebagai contoh, bila range data adalah B2:D10 dan kita berniat memeriksa cell kosong pada kolom tertentu dengan ISBLANK, maka rumus yang digunakan adalah =ISBLANK($B2). Excel akan memeriksa range data dari posisi terkiri atas. Jadi, jangan khawatir data teman-teman tidak diperiksa oleh Excel.

4. Cek ulang referensi cell saat copas (copy paste).

Tidak memeriksa referensi cell setelah copas atau menggunakan Format Painter adalah salah satu penyakit yang paling sering kami temui.

5. Pecah rumus kompleks menjadi potongan-potongan sederhana yang lebih mudah dimengerti. Lakukan hal ini di program terpisah seperti Notepad atau Microsoft Word.

Tes pecahan-pecahan rumus dan masukkan hasil ke rumus di atasnya ketimbang langsung uji rumus secara keseluruhan.

6. Bila punya lebih dari satu aturan conditional formatting, cek ulang aturan-aturan tersebut. Bisa jadi ada aturan lain menghalangi rumus kita untuk bekerja sebagaimana mestinya.

Juga, uji setiap aturan satu per satu sebelum digabungkan.

7. Periksa ulang semuanya. Tidak rugi untuk memeriksa ulang semua aturan, rumus, referensi cell, format, dan lain-lain.

Ingat. Tidak akan ada yang bilang keren dengan utak-atik rumus saat presentasi di depan klien.

8. Latihan terus-menerus akan membuat kita terhindar dari keharusan menghapal setiap tips serta mengasah intuisi dalam menggunakan conditional formatting dengan rumus.

Membandingkan Data Text di Excel dengan Rumus Conditional Formatting

Membandingkan teks bisa dilakukan dengan membandingkan referensi cell dengan nilai tertentu. Bisa juga dengan mengambil nilai tertentu lewat fungsi-fungsi teks seperti SEARCH, CONCAT, MID, TRIM, dan lain-lain.

Demo berikut bertujuan untuk:

• Mencari salesman dengan sales code yang diawali huruf C.
• Bila ada, tandai nama salesman tersebut.

(Demo 02) Cara membandingkan data text di Excel dengan rumus conditional formatting.

1. Pilih range A3:A14.
2. Klik Conditional Formatting → New Rule.
3. Klik Use a formula to determine which cells to format.
4. Masukkan rumus yang dibutuhkan, yaitu =LEFT($B3; 1)="C".
5. Klik Format untuk memilih format yang diinginkan.
6. Klik OK.

Perhatikan hasilnya pada gambar di bawah ini.

Membandingkan Angka di Excel dengan Rumus Conditional Formatting

Membandingkan angka menggunakan conditional formatting dengan rumus bisa dilakukan dengan menggunakan operator-operator aritmetika.

Demo berikut bertujuan untuk:

• Mencari salesman yang memiliki sales call nol selama dua pekan berturut-turut.
• Bila ada, warnai sales call bersangkutan.

(Demo 03) Cara membandingkan angka di Excel dengan rumus conditional formatting.

1. Klik Conditional Formatting → Manage Rules → New Rule.
2. Klik Use a formula to determine which cells to format.
3. Masukkan rumus yang dibutuhkan, yaitu =$C3+$D3=0.
4. Klik tombol Format untuk memilih format yang diinginkan.
5. Klik OK.

   

6. Masukkan range atau cell yang akan diformat, yaitu $C$3:$D$14.
7. Klik Apply.

   

Hasilnya adalah seperti pada gambar di bawah ini.

Dengan mengulang kembali langkah-langkah di atas untuk tujuan demo yang sama, apakah teman-teman bisa menghasilkan format seperti di bawah ini?

Tips: Kami menggunakan lebih dari satu aturan.

Cara Menggunakan AND di Excel dengan Rumus Conditional Formatting

Demo ini bertujuan untuk:

• Mencari salesman dengan sales code diawali dengan huruf C (gunakan fungsi LEFT).
• Memiliki sales call di bawah rata-rata sales call pada pekan pertama Januari (gunakan fungsi AVERAGE).
• Bila dua kriteria tersebut terpenuhi (dengan fungsi AND), beri warna latar pada nilai sales call bersangkutan.

(Demo 04) Cara menggunakan AND di Excel dengan rumus conditional formatting.

1. Pilih cell atau range yang akan diformat, yaitu C3:C14.
2. Klik Conditional Formatting → New Rule.
3. Klik Use a formula to determine which cells to format.
4. Masukkan rumus yang dibutuhkan, yaitu =AND(LEFT($B3;1)="C";AVERAGE($C$3:$C$14)>$C3).
5. Klik tombol Format untuk memilih format.
6. Klik OK.

Hasil Demo 04 bisa dilihat pada gambar berikut.

Penutup

Download file berikut untuk melihat hasil demo-demo di atas.

File: conditional-formatting-formula-hasil.xlsx.