Highlight Data Otomatis Dengan Conditional Formatting

Mau spreadsheet yang langsung ngomong ke kita? Biar gampang lihat yang penting (overdue, stok menipis, nilai jelek, atau top performer), pakai Conditional Formatting — fitur yang bikin sel berubah warna, muncul ikon, atau pakai bar otomatis berdasarkan aturan yang kita kasih.

Artikel ini lengkap banget: dari dasar, contoh nyata (tabel siap copy-paste), rumus custom, trik UX-friendly, hingga troubleshooting.

Ditulis santai buat anak remaja Indonesia supaya gampang dimengerti dan langsung bisa dipraktikkan. Yuk langsung ke inti!

Ringkasan singkat sebelum mulai

• Conditional Formatting = format otomatis berdasarkan kondisi.
• Bisa dipakai di Excel dan Google Sheets (langkah mirip).
• Tipe umum: Highlight Cells, Data Bars, Color Scales, Icon Sets, dan Custom formula rules.
• Trik kuat: pakai formula (mis. `=$C2="Late"`) untuk kondisi baris/kolom kompleks.
• Gunakan tabel (`Ctrl+T` di Excel) supaya range dinamis.
• Hati-hati performa kalau aturan banyak atau range besar.

1. Apa itu Conditional Formatting & kenapa penting?

Conditional Formatting (CF) adalah cara otomatis untuk menandai nilai penting dalam data. Daripada buka data baris per baris, kamu bisa langsung melihat:

• Mana invoice yang telat,
• Produk dengan stok ≤ 5,
• Siswa yang nilainya di bawah passing grade,
• Bulan dengan penjualan terendah/tertinggi.

CF membantu UX data — bikin tabel lebih scan-able dan cepat dipahami. Untuk SEO, kata kunci penting: "conditional formatting", "highlight otomatis", "rules Excel", "format sel berdasarkan kondisi", "format dinamis".

2. Aturan dasar sebelum pakai CF

Sebelum bikin rules, siapkan data dengan benar:

• Header jelas di baris pertama.
• Kolom bertipe sesuai (tanggal sebagai Date, angka sebagai Number).
• Jangan ada baris kosong di tengah dataset.
• Kalau di Excel, ubah range jadi Table (`Ctrl+T`) supaya aturan ikut saat data bertambah.
• Simpan salinan file kalau mau eksperimen.

Contoh data yang bakal kita pakai banyak kali (salin ke sheet):

Keterangan: Kolom DueDate buat contoh pengecekan overdue, Status untuk cek Unpaid/Paid, Qty dan Harga untuk cek stok/nominal.

3. Lokasi menu & jenis rules (Excel vs Google Sheets)

Excel (Windows / Mac)

Menu: Home → Conditional Formatting
Jenis built-in:

• Highlight Cells Rules (Greater Than, Less Than, Between, Text that Contains, A Date Occurring, Duplicate Values)
• Top/Bottom Rules (Top 10 Items, Above/Below Average)
• Data Bars
• Color Scales
• Icon Sets
• New Rule → Use a formula to determine which cells to format (kustom)

Google Sheets

Menu: Format → Conditional formatting
Di panel kanan: Format rules (dropdown):

• Cell is not empty / Text contains / Date is / Greater than / Custom formula is (pilihan paling kuat)
• Style: fill color, text color, bold, strikethrough

Catatan: fitur agak beda nama tapi konsep sama. Kita akan bahas langkah di masing-masing.

4. Contoh dasar: Highlight sel angka lebih besar / lebih kecil

Tujuan: tandai transaksi di atas Rp15.000.

Excel (langkah):

1. Pilih kolom Total (misal kolom G) atau seluruh range G2:G100.
2. Home → Conditional Formatting → Highlight Cells Rules → Greater Than...
3. Isi 15000, pilih format (Light Red Fill with Dark Red Text). Klik OK.

Google Sheets:

1. Pilih range G2:G100.
2. Format → Conditional formatting → Format cells if → Greater than → value 15000.
3. Pilih formatting style, klik Done.

Hasil: semua sel & 15000 berwarna, jadi gampang ketahuan.

5. Highlight teks tertentu (contoh: status Unpaid)

Tujuan: tandai baris yang Status = Unpaid.

Highlight SEL saja (Excel):

• Pilih kolom Status (F2:F100).
• Conditional Formatting → Highlight Cells Rules → Text that Contains → ketik Unpaid → pilih warna.

Highlight ENTIRE ROW (Excel) — lebih keren:

1. Pilih seluruh range data, misal A2:H100 (pastikan A2 adalah titik aktif pertama dari pilihan).
2. Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Masukkan formula:

=$G2="Unpaid"

Keterangan: $G mengunci kolom Status; 2 cocok dengan baris awal dari selection.

4. Klik Format → pilih Fill warna, OK → OK.

Google Sheets (entire row):

1. Pilih range, misal A2:H100.
2. Format → Conditional formatting → Custom formula is → masukkan:

=$G2="Unpaid"

3. Pilih style → Done.

Catatan: penggunaan $ kunci kolom. Pastikan nomor baris sesuai baris teratas pilihan.

6. Tanggal: Highlight overdue dan near-due

Contoh nyata: tandai invoice yang sudah lewat DueDate dan belum Paid.

Rules:

• Overdue: DueDate < TODAY() dan Status <> "Paid".
• Near due (warning 3 hari): DueDate <= TODAY()+3 dan Status <> "Paid".

Excel: Overdue row

Select A2:H100 → New Rule → Use formula:

=AND($H2 < TODAY(), $G2 <> "Paid")

Format fill merah.

Excel: Near due (warning)

New Rule formula:

=AND($H2 >= TODAY(), $H2 < = TODAY()+3, $G2 <> "Paid")

Format fill orange.

Google Sheets:

• Gunakan Custom formula is dengan rumus sama (Google Sheets pakai fungsi TODAY juga).
• Ingat: Google Sheets peka pada tanda kutip dan pemformatan.

Hasil: invoice yang sudah lewat merah, yang hampir jatuh tempo orange — jelas dan berguna.

7. Highlight duplicate & unique values

Contoh: cari OrderID duplikat

Excel:

• Pilih kolom OrderID (A2:A100).
• Conditional Formatting → Highlight Cells Rules → Duplicate Values → pilih warna.
  Excel akan tandai nilai yang muncul lebih dari 1.

Google Sheets:

• Pilih A2:A100 → Format → Conditional formatting → Custom formula:

=COUNTIF($A:$A,$A2)>1

• Pilih style → Done.

Untuk unique values gunakan =COUNTIF($A:$A,$A2)=1.

8. Highlight top / bottom values (Top 10%, Top 3 items)

Excel (built-in)

• Conditional Formatting → Top/Bottom Rules → Top 10 Items / Bottom 10 Items / Top 10% / Above Average / Below Average.

Google Sheets (custom)

• Untuk top 3 values (kolom G):

=RANK($G2,$G$2:$G$100)<=3

• Untuk top 10%:

=$G2 >= PERCENTILE($G$2:$G$100, 0.9)

Catatan: RANK function default may have ties; gunakan RANK.EQ / RANK.AVG di Excel.

9. Data Bars (bar di dalam sel) — membuat progress visual cepat

Data Bars memberi efek batang kecil di setiap sel — bagus buat representasi kuantitas.

Excel:

• Pilih range G2:G100.
• Conditional Formatting → Data Bars → pilih fill (Gradient atau Solid).
• Atur Minimum/Maximum jika perlu (Number, Percentile, Formula).

Google Sheets:

• Belum ada Data Bars built-in, tapi bisa tiru dengan formula REPT:
• Buat kolom helper Bar:

=REPT("▮", ROUND(G2/1000,0))

• Format font monospace, warna, lalu pakai conditional formatting untuk warnai teks jika perlu.

10. Color Scales (heatmap) — lihat intensitas cepat

Color Scales mewarnai sel berdasarkan posisi relatif nilai (mis. low→green, mid→yellow, high→red).

Excel:

• Pilih range angka → Conditional Formatting → Color Scales → pilih skema.

Google Sheets:

• Pilih range → Format → Conditional formatting → Color scale (tab).

Aplikasi: visualisasi momen penjualan, rating, skor, dsb.

11. Icon Sets — tanda visual (panah, stoplight, star)

Icon Sets di Excel memberi ikon (panah naik/turun, traffic light, stars) berdasar nilai.

Excel:

• Pilih range → Conditional Formatting → Icon Sets → pilih style.
• Klik Manage Rules → Edit Rule → atur thresholds (percentile / number / formula) → centang hide value jika mau.

Google Sheets:

• Tidak ada Icon Sets built-in; bisa pakai emoji lewat REPT/IFS atau pakai add-on.

Contoh Excel:
Aturan:

• Green up arrow if value >= 90% of max, yellow if between 70-90%, red down arrow if <70%.

12. Custom formula rules — ini yang paling powerful

Custom formula memungkinkan logika kompleks: kombinasi AND/OR, lookup, referencing other columns, named ranges.

Contoh 1: Highlight row jika Region = "JKT" dan Qty >= 5

Select A2:H100 → New Rule → Use a formula:

=AND($C2="JKT", $E2>=5)

Contoh 2: Highlight jika produk sudah pernah terjual di region lain — pakai COUNTIFS

Highlight Order row bila ada produk yang sudah pernah terjual di region lain (duplicate product across region):

=COUNTIFS($D:$D, $D2, $C:$C, "<>"&$C2) > 0

Artinya: hitung produk (kolom D) dengan region berbeda. Jika >0, berarti ada penjualan produk serupa di region lain → tandai.

Contoh 3: Highlight Top salesperson via lookup table

Misal ada sheet Targets dengan kolom nama & target. Untuk tandai salesperson yg melebihi target, pakai:

=VLOOKUP($B2, Targets!$A$2:$B$100, 2, FALSE) <= $G2

Atau dengan INDEX/MATCH:

=$G2 >= INDEX(Targets!$B$2:$B$100, MATCH($B2, Targets!$A$2:$A$100, 0))

Penting: Gunakan absolute references $ dengan cermat. Kolom dikunci, baris relatif ke baris awal selection.

13. Conditional formatting pada PivotTable

CF juga bisa diaplikasikan ke PivotTables, tapi ada beberapa catatan:

• Rule yang diterapkan langsung ke Pivot cell bisa hilang saat pivot di-refresh atau struktur berubah.
• Lebih aman: apply CF ke Pivot field via Format → Conditional Formatting → New Rule → Use a formula referencing GETPIVOTDATA or cell values.
• Di Excel, kamu bisa apply rule ke All cells showing "Sum of Revenue" dsb. Gunakan applies to area yang sesuai.

Contoh: Highlight pivot cells > 50.000

1. Pilih pivot value area (klik salah satu value → Select → Entire PivotTable values).
2. Conditional Formatting → New Rule → Format only cells that contain → Cell Value > 50000 → format.

14. Visual KPI dashboard: combine CF types

Contoh dashboard sederhana di sheet KPI:

Aturan:

• Jika Value >= Target → hijau (pakai formula =$B2 >= $C2).
• Jika Value < Target tapi >= 90% target → kuning.
• Jika < 90% target → merah.

Bisa gunakan Icon Sets untuk tanda ✔ / ⚠ / ✖.

15. Advanced tricks — progress bars, Gantt, alternate row color by group

Progress bar (visual, tanpa conditional formatting built-in)

• Gunakan kolom helper ProgressPct (mis. =B2/C2) lalu format number sebagai percentage.
• Di Excel: apply Data Bars ke ProgressPct range. Atau buat custom cell yang menampilkan block:

=REPT("█", ROUND($B2/$C2*20,0))

Beri warna teks hijau.

Gantt-style bar di sel

• Buat kolom Start, Duration.
• Gunakan conditional formatting with formula yang memplot warna jika tanggal dalam baris jangkauan:

• Let sheet punya header dates di row 1 (D1:AN1 = sequential dates).
• Pilih area D2:AN10 → New Rule → Use formula:

=AND(D$1 &>= $B2, D$1 &< ($B2 + $C2))

• Format fill color → hasil: bar Gantt sederhana.

Alternate row color by group (UX)

Biasanya kita striping per baris. Untuk striping dinamis per Region (grouped zebra), gunakan CF:

• Select A2:H100 → New Rule → Use formula:

=MOD(SUMPRODUCT(--(ROW($A$2:$A2))) , 2) = 0

But better: use helper column that counts group changes and use MOD on it. Simpler: use Excel Table & Format as Table (built-in alternate rows).

16. Performance considerations & best practices

• Batasi range: jangan pakai whole-column references (A:A) di CF kalau datanya besar. Gunakan named ranges atau table.
• Kurangi jumlah rules: gabungkan rules jika memungkinkan. Lebih sedikit rules = lebih cepat.
• Avoid volatile formulas in CF (INDIRECT, OFFSET) karena bikin recalculation sering.
• Use helper columns: jika logic kompleks, hitung boolean di helper column lalu pakai CF sederhana =$I2=TRUE. Ini lebih cepat.
• Order of rules matters: di Excel, rules diproses dari atas; jika ada overlap, yang di atas mungkin menutupi. Gunakan Stop If True (Excel) atau atur order di Manage Rules. Di Google Sheets rules dieksekusi top-down juga; rules yang terakhir akan terlihat jika tumpang tindih (behavior bisa berbeda jadi cek manual).
• Testing: selalu uji rule di sample data sebelum apply ke seluruh dataset.

17. Accessibility & warna (UX)

• Gunakan kombinasi warna yang ramah buta warna: hindari merah-hijau semata. Pilih palet seperti (blue, orange, gray).
• Jangan hanya mengandalkan warna — tambahkan ikon atau teks untuk pembaca buta warna.
• Kontras: pastikan teks tetap terbaca di atas background warna.
• Tambahkan legend di samping sheet untuk menjelaskan arti warna.

18. Troubleshooting umum (FAQ)

Q: Kenapa rule tidak berlaku saat saya masukkan data baru?
A: Pastikan range CF mencakup baris baru; gunakan Table object agar range auto-extend.

Q: Formula CF memberi error atau tidak bekerja?
A: Cek referensi absolut/relatif ($), pastikan nomor baris di formula sama dengan baris awal dari selection.

Q: CF hilang setelah refresh Pivot?
A: Terapkan CF pada pivot value area atau pakai pivot-based rule. Atau gunakan VBA untuk reapply.

Q: Format teks ="2025-09-01" dianggap teks bukan tanggal?
A: Convert jadi date pakai DATEVALUE atau parsing. CF berbasis tanggal butuh nilai tanggal.

19. Contoh kasus nyata lengkap (download & praktek)

Berikut beberapa skenario lengkap + tabel + rumus yang bisa langsung kamu salin dan coba.

Kasus A — Overdue invoice & near due

Tabel contoh (copy A1:H8 dari artikel di awal)

Rules:

• Overdue: =AND($H2< TODAY(), $G2 <> "Paid") → fill merah.
• Near due 3 hari: =AND($H2 >= TODAY(), $H2 <= TODAY()+3, $G2 <> "Paid") → fill orange.

Kasus B — Stok rendah

Tabel stok:

Rule: highlight Produk dengan stok < SafetyStock
Select A2:C4 → New Rule → Use formula:

=$B2 < $C2

Format fill merah.

Kasus C — Nilai ujian: grade coloring

Tabel nilai:

Rules:

• >= 85 → hijau
• 70–84 → kuning
• < 70 → merah

Gunakan Highlight Cells Rules atau formula:

=$B2>=85
=AND($B2>=70,$B2<85)
=$B2<70

Kasus D — Top 3 salesperson

Tabel penjualan per sales:

Rule (Top 3):

=RANK($B2,$B$2:$B$5)<=3

Kasus E — Highlight duplicated products across regions

=COUNTIFS($D:$D,$D2,$C:$C,<>"&$C2) > 0

Format: pilih seluruh table, apply CF.

20. Latihan (bikin sendiri) + jawaban singkat

Latihan 1: Dari tabel transaksi awal, tandai:

• Semua transaksi Unpaid → row merah.
• Semua transaksi di Region BDG → row biru.
• Highlight top 2 OrderQty → bold & warna.

Latihan 2: Buat dashboard inventory:

• Highlight produk stok < 5.
• Data bar untuk stok (visual).
• Warnai produk yang pernah sold-out (Qty = 0) dengan grey + strikethrough.

Jawaban: lihat rumus dan langkah di atas — ikuti contoh rules "Unpaid", "Region", "Top N", "Stok rendah".

21. rekomendasi praktis

Conditional Formatting bikin spreadsheet jadi hidup — kamu bisa langsung lihat masalah dan insight tanpa scroll lama. Kunci sukses:

• Siapkan data rapi, gunakan Table.
• Mulai dari rules sederhana (lebih besar dari, contains) lalu upgrade ke custom formula.
• Gunakan helper columns kalau logika rumit
• Batasi range agar performa tetap oke.
• Tambah legend & gunakan warna aksesibel.

Praktek terbaik: ambil data nyata (mis. tugas, toko online, projek sekolah), coba 3 rules yang paling berguna buatmu, dan perhatikan perubahan kecepatan kerja.

Kalo butuh contoh rumus spesifik untuk kasus kamu, tinggal copas data dan coba rumus-rumus di artikel ini — pasti langsung kelihatan hasilnya.

Selamat ngoprek — bikin spreadsheetmu lebih pintar dan eye-catching!