Conditional Formatting Excel: 8 Trik biar Data Langsung Kebaca

Conditional formatting adalah fitur Excel yang otomatis mengubah warna, ikon, atau bar sebuah sel berdasarkan isinya atau hasil sebuah rumus.

Gunanya satu: bikin angka penting kelihatan tanpa kamu harus baca 500 baris satu per satu.

Di bawah ini 8 trik yang beneran kepakai di laporan sehari-hari, dari yang paling gampang sampai aturan berbasis rumus. Plus alasan kenapa file kamu bisa jadi lemot gara-gara ini.

Di mana letak menu conditional formatting?

Tab Home, grup Styles, tombol Conditional Formatting. Di Google Sheets, menunya di Format lalu Conditional formatting.

Semua trik di bawah masuk lewat pintu yang sama. Yang beda cuma jenis aturannya.

Satu kebiasaan yang bikin hidup gampang: blok dulu range datanya sebelum buka menunya. Excel bakal otomatis nge-set range aturannya sesuai yang kamu blok.

Trik 1: Highlight nilai di atas atau di bawah ambang

Ini yang paling sering dipakai. Contoh: tandai transaksi di atas Rp 5 juta.

1. Blok kolom omzet.
2. Conditional Formatting, Highlight Cells Rules, Greater Than.
3. Ketik 5000000, pilih warnanya.

Trik kecil yang jarang dipakai orang: alih-alih ngetik angka, tunjuk ke sel. Kalau kamu tulis =$H$1, ambangnya bisa diubah dari satu sel tanpa buka menu lagi. Ini pakai absolute reference, jadi dollarnya wajib.

Trik 2: Data bars buat ngebandingin besaran

Data bars naruh batang di dalam sel yang panjangnya sebanding sama nilainya. Mata kamu bisa nangkap urutan besarnya tanpa baca angkanya.

Conditional Formatting, Data Bars, pilih warnanya.

Yang harus kamu ubah dari default: klik More Rules, lalu centang Show Bar Only kalau kamu mau angkanya disembunyiin. Kolom jadi bersih dan cuma nampilin bar.

Kalau ada nilai negatif, Excel otomatis bikin batangnya ke arah kiri dengan warna merah. Ini kepakai banget buat kolom margin atau selisih target.

Trik 3: Color scales buat heatmap

Color scales mewarnai sel dengan gradasi dari nilai terkecil ke terbesar. Pas buat matriks penjualan per cabang per bulan.

Conditional Formatting, Color Scales, pilih skema 3 warna.

Saran praktis: hindari skema merah-kuning-hijau kalau laporannya dibaca banyak orang. Sekitar 8% laki-laki punya kesulitan bedain merah dan hijau. Pakai skema biru ke oranye, informasinya tetap sampai.

Trik 4: Icon sets buat status

Icon sets naruh simbol berdasarkan ambang batas. Panah naik-turun, lampu lalu lintas, atau tanda seru.

Defaultnya membagi data jadi 3 bagian berdasarkan persentil, dan itu hampir selalu salah buat kasus nyata. Kamu harus atur ambangnya sendiri.

1. Conditional Formatting, Icon Sets, pilih setnya.
2. Balik lagi ke Conditional Formatting, Manage Rules, Edit Rule.
3. Ubah Type dari Percentile jadi Number.
4. Isi ambangnya sesuai aturan bisnis kamu. Misalnya hijau kalau margin di atas 0,3 dan merah kalau di bawah 0.

Langkah 3 itu yang paling sering dilewatin, dan itu yang bikin icon set kelihatan asal-asalan.

Trik 5: Tandai data duplikat

Conditional Formatting, Highlight Cells Rules, Duplicate Values.

Ini cuma ngecek satu kolom. Kalau kamu mau ngecek duplikat berdasarkan kombinasi dua kolom (misalnya tanggal plus kode produk), pakai kolom bantu yang isinya =A2 & "|" & B2, terus cek duplikat di kolom bantu itu.

Buat nyari data yang gak ada di daftar lain, pakai aturan rumus dengan COUNTIF. Contohnya ada di trik 7.

Trik 6: Highlight baris penuh, bukan satu sel

Ini trik yang paling banyak nolong dan paling banyak bikin orang bingung.

Kasusnya: kamu punya tabel transaksi A sampai F, dan kolom E isinya status. Kamu mau seluruh baris jadi merah kalau statusnya "Rugi".

1. Blok seluruh area data, dari A2 sampai F500. Bukan cuma kolom E.
2. Conditional Formatting, New Rule, Use a formula to determine which cells to format.
3. Ketik: =$E2="Rugi"
4. Pilih format warnanya, klik OK.

Kuncinya di penulisan $E2. Dollar cuma di depan huruf E, gak ada dollar di depan angka 2.

Artinya: kolomnya dikunci ke E (jadi waktu aturan diterapkan ke sel B2, C2, D2, dia tetap ngecek E2), tapi barisnya bebas geser (jadi baris 3 ngecek E3, baris 4 ngecek E4).

Kalau kamu tulis $E$2, semua baris bakal ngecek E2 doang. Kalau kamu tulis E2 tanpa dollar sama sekali, tiap kolom bakal ngecek kolom sebelahnya dan hasilnya kacau.

Satu tanda dollar. Itu bedanya antara jalan dan berantakan.

Trik 7: Aturan pakai rumus custom

Ini yang bikin conditional formatting jadi fleksibel. Beberapa rumus yang sering aku pakai.

Tandai baris yang tanggalnya udah lewat 30 hari:
=$B2 < TODAY() - 30

Tandai produk yang gak ada di daftar master:
=COUNTIF(master!$A:$A, $A2) = 0

Tandai sel yang isinya di atas rata-rata kolomnya:
=C2 > AVERAGE($C$2:$C$500)

Tandai baris genap biar tabel lebih enak dibaca:
=MOD(ROW(), 2) = 0

Tandai sel yang kosong padahal harusnya diisi:
=AND($D2 <> "", $E2 = "")

Aturan mainnya: rumusnya harus menghasilkan TRUE atau FALSE, dan referensinya ditulis dari sudut pandang sel kiri-atas dari range yang kamu blok.

Kalau aturannya gak jalan, 90% kemungkinan salahnya di posisi tanda dollar.

Trik 8: Contoh kasus dashboard toko_berkah

Dataset toko_berkah punya 1.200 transaksi dari 6 cabang UMKM di Yogyakarta selama 2025. Kolomnya: tanggal (A), cabang (B), produk (C), omzet (D), margin_pct (E).

Empat aturan yang aku pasang di sheet itu, urut prioritasnya.

1. Baris merah kalau margin negatif:
   Range: $A$2:$E$1201
   Rumus: =$E2 < 0

2. Baris kuning kalau margin di bawah 15%:
   Range: $A$2:$E$1201
   Rumus: =AND($E2 >= 0, $E2 < 0.15)

3. Data bars di kolom omzet:
   Range: $D$2:$D$1201

4. Highlight cabang yang lagi dipilih di sel H1:
   Range: $A$2:$E$1201
   Rumus: =$B2 = $H$1

Aturan nomor 4 itu yang bikin sheetnya kerasa kayak dashboard. Ganti isi H1 ke nama cabang, dan barisnya langsung ke-highlight tanpa filter apa pun.

Hasil yang langsung kelihatan setelah aturan dipasang: 47 baris merah, dan 41 di antaranya dari cabang Kotagede. Sebelumnya angka itu ketutupan di antara 1.200 baris dan gak pernah ketahuan sampai tutup buku.

Butuh 4 menit buat masang aturannya. Butuh 3 bulan sebelumnya buat nemuin masalah yang sama secara manual.

Kesalahan umum yang bikin file lemot

Aturan yang beranak-pinak

Tiap kali kamu copy-paste baris, Excel sering memecah aturan lama jadi beberapa aturan baru dengan range terpisah. File yang tadinya punya 5 aturan bisa jadi 300 tanpa kamu sadari.

Cek: Conditional Formatting, Manage Rules, ubah dropdown-nya ke This Worksheet. Kalau daftarnya panjang banget, itu penyebab lemotnya. Hapus semuanya, pasang ulang dari awal.

Range aturan diset ke seluruh kolom

Range $A:$E berarti Excel ngecek 1 juta baris tiap kali ada perubahan. Batasi ke baris terakhir data kamu.

Rumus volatile di dalam aturan

Fungsi kayak TODAY(), NOW(), OFFSET(), dan INDIRECT() dihitung ulang tiap kali ada perubahan apa pun di workbook. Satu aturan pakai TODAY() di 10.000 baris bikin Excel kerja keras terus-terusan. Taruh =TODAY() di satu sel, terus rujuk sel itu di aturannya.

Urutan aturan yang salah

Aturan dijalankan dari atas ke bawah di Manage Rules. Aturan yang lebih spesifik harus di atas. Kalau aturan "margin di bawah 15%" ditaruh di atas aturan "margin negatif", baris rugi bakal kena kuning duluan dan gak pernah jadi merah. Centang Stop If True kalau kamu mau aturan di bawahnya gak dievaluasi lagi.

Kebanyakan warna

Sheet dengan 7 warna berbeda gak ngasih informasi apa-apa, cuma bikin mata capek. Batasi 3 warna maksimal. Referensi lengkap soal aturan dan batasannya bisa kamu baca di dokumentasi conditional formatting di support Microsoft.

FAQ

Gimana cara mewarnai satu baris penuh berdasarkan isi satu kolom?

Blok seluruh area datanya, pilih New Rule lalu Use a formula, dan tulis rumusnya dengan mengunci kolomnya doang, misalnya =$E2="Rugi". Dollar cuma di depan huruf kolom, gak di depan angka barisnya.

Kenapa conditional formatting bikin Excel jadi lemot?

Biasanya karena aturannya numpuk gara-gara copy-paste. Buka Manage Rules, ganti dropdown ke This Worksheet, dan hapus yang duplikat. Cek juga apakah range-nya diset ke seluruh kolom.

Apa bedanya data bars, color scales, dan icon sets?

Data bars nampilin batang sebanding nilainya. Color scales bikin gradasi warna buat heatmap. Icon sets naruh simbol berdasarkan ambang batas, paling pas buat status 3 kategori.

Bisa gak conditional formatting ngacu ke sel di sheet lain?

Sejak Excel 2010, bisa langsung. Kalau bermasalah, akalin pakai Named Range yang nunjuk ke sel di sheet lain, lalu pakai nama itu di rumus aturannya.

Gimana cara hapus semua conditional formatting sekaligus?

Blok areanya, Home, Conditional Formatting, Clear Rules, lalu Clear Rules from Selected Cells. Simpan file dulu, soalnya di beberapa versi Excel ini gak bisa di-undo.

Penutup

Tiga hal yang perlu kamu ingat. Aturan berbasis rumus itu yang paling kepakai, dan kuncinya cuma di posisi tanda dollar. Aturan yang numpuk itu penyebab file lemot nomor satu. Dan tiga warna udah cukup, lebih dari itu malah bikin datanya susah dibaca.

Buka laporan kamu sekarang, blok seluruh tabelnya, dan pasang satu aturan =$E2 < 0. Lima detik, dan kamu langsung lihat baris mana yang bermasalah.

Kalau kamu masih bingung kenapa tanda dollar harus ditaruh di situ, baca dulu panduan absolute reference Excel. Itu satu-satunya konsep yang bikin trik nomor 6 dan 7 jalan.