TL;DR
Conditional formatting Excel adalah fitur yang otomatis mengubah warna, ikon, atau bar sebuah sel berdasarkan isinya atau hasil sebuah rumus. Kamu atur lewat menu Home lalu Conditional Formatting, dan aturannya bisa berupa nilai ambang, teks tertentu, tanggal, atau rumus custom. Aturan berbasis rumus adalah yang paling fleksibel karena bisa mewarnai satu baris penuh berdasarkan isi satu kolom.
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.
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.
Ini yang paling sering dipakai. Contoh: tandai transaksi di atas Rp 5 juta.
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.
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.
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.
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.
Langkah 3 itu yang paling sering dilewatin, dan itu yang bikin icon set kelihatan asal-asalan.
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.
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".
=$E2="Rugi"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.
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.
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$1Aturan 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.
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 $A:$E berarti Excel ngecek 1 juta baris tiap kali ada perubahan. Batasi ke baris terakhir data kamu.
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.
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.
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.
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.
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.
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.
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.
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.
Aturan berbasis rumus itu yang paling kepakai, dan kuncinya cuma di posisi tanda dollar. Aturan yang numpuk itu penyebab file lemot nomor satu. 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.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Cara pakai rumus FV dan PV Excel buat hitung nilai uang di masa depan dan sekarang. Lengkap dengan contoh tabungan dan target modal UMKM.
Investasi kelihatan untung di atas kertas, tapi nilai uang berubah tiap tahun. NPV dan IRR Excel bantu kamu nilai apakah proyek beneran layak, bukan cuma kelihatan untung.
Sebelum ambil pinjaman, kamu bisa hitung sendiri cicilan bulanannya pakai satu rumus. Ini cara PMT Excel bekerja, lengkap dengan contoh modal usaha dan jebakan bunga tahunan.