Conditional Formatting Excel: Cara Mewarnai Sel Otomatis Berdasarkan Nilai, Teks, Tanggal, dan Rumus — Lengkap + Contoh Tabel
Diperbarui: 12 Maret 2026
Punya ratusan baris data di Excel tapi bingung mana yang perlu diperhatikan? Atau ingin sel otomatis berubah warna merah kalau nilainya di bawah target, tanpa harus cek satu per satu secara manual? Conditional Formatting adalah fitur Excel yang memungkinkan sel berubah tampilan — warna latar, warna teks, ikon, atau data bar — secara otomatis berdasarkan kondisi yang kamu tentukan. Di panduan ini kamu akan belajar cara menggunakan Conditional Formatting dari yang paling dasar sampai kombinasi dengan rumus custom, lengkap dengan contoh tabel siap ketik. Cocok untuk pelajar, mahasiswa, admin, HR, staf UMKM, guru, dan siapa pun yang ingin datanya lebih mudah dibaca sekilas.
- Conditional Formatting ada di tab Home → Conditional Formatting
- Highlight Cells Rules: warnai sel berdasarkan nilai angka, teks, atau tanggal — paling cepat dipakai
- Top/Bottom Rules: otomatis tandai 10 nilai tertinggi, terendah, atau di atas rata-rata
- Data Bars & Color Scales: visualisasi mini tanpa perlu bikin grafik
- Icon Sets: tampilkan ikon ✅ ⚠️ ❌ otomatis berdasarkan nilai
- New Rule + Formula: kondisi paling fleksibel — warnai seluruh baris berdasarkan satu kolom
- Kalau warna tidak muncul padahal rumus sudah benar: cek apakah referensi sel di rumus sudah pakai
$yang tepat
- Apa itu Conditional Formatting?
- Di Mana Letak Menu Conditional Formatting
- Highlight Cells Rules: Warnai Berdasarkan Nilai, Teks, dan Tanggal
- Top/Bottom Rules: Tandai Nilai Tertinggi dan Terendah Otomatis
- Data Bars dan Color Scales: Visualisasi Tanpa Grafik
- Icon Sets: Ikon Otomatis Berdasarkan Kondisi
- New Rule + Formula: Warnai Seluruh Baris Berdasarkan Satu Kolom
- Mengelola dan Menghapus Conditional Formatting
- Kesalahan Umum dan Cara Mengatasinya
- Kesimpulan
- FAQ
- Baca Juga
Apa itu Conditional Formatting?
Conditional Formatting (Format Bersyarat) adalah fitur Microsoft Excel yang mengubah tampilan sel — seperti warna latar, warna teks, tebal, atau ikon — secara otomatis berdasarkan kondisi atau aturan yang kamu tentukan. Tampilan berubah sendiri setiap kali nilai di sel tersebut memenuhi atau tidak memenuhi kondisi yang sudah diset.
Contoh penggunaan sehari-hari: sel nilai siswa di bawah 70 otomatis merah, stok barang yang hampir habis otomatis kuning, status "Lunas" otomatis hijau. Semua itu bisa berjalan tanpa kamu perlu mewarnai manual satu per satu.
Conditional Formatting hanya mengubah tampilan sel, bukan isinya. Data asli tidak berubah sama sekali — yang berubah hanya warna atau ikon yang terlihat.
Di Mana Letak Menu Conditional Formatting
Menu Conditional Formatting ada di tab Home → grup Styles → klik tombol Conditional Formatting. Setelah diklik, akan muncul menu dropdown berisi semua pilihan aturan yang bisa dipakai.
| Menu | Fungsi Singkat | Cocok untuk |
|---|---|---|
| Highlight Cells Rules | Warnai sel berdasarkan nilai, teks, tanggal, atau duplikat | Penanda cepat kondisi tunggal |
| Top/Bottom Rules | Tandai N nilai tertinggi/terendah atau di atas/bawah rata-rata | Analisis performa cepat |
| Data Bars | Batang mini di dalam sel sebanding dengan nilainya | Perbandingan visual antar sel |
| Color Scales | Gradasi warna dari rendah ke tinggi | Heatmap data |
| Icon Sets | Ikon (panah, lampu, centang) berdasarkan nilai | Dashboard atau laporan ringkas |
| New Rule (Formula) | Aturan bebas berbasis rumus Excel | Kondisi kompleks atau warnai seluruh baris |
Highlight Cells Rules: Warnai Berdasarkan Nilai, Teks, dan Tanggal
Ini adalah cara tercepat memakai Conditional Formatting. Cocok untuk penanda kondisi sederhana: nilai di bawah target, teks tertentu, tanggal yang sudah lewat, atau duplikat data.
Contoh 1: Warnai Nilai Siswa di Bawah 70 (Merah)
Buat tabel ini mulai dari sel A1:
| A | B |
|---|---|
| Nama | Nilai |
| Ahmad | 82 |
| Budi | 65 |
| Citra | 70 |
| Dian | 55 |
| Eka | 90 |
Langkah-langkahnya:
- Blok sel
B2:B6 - Klik Home → Conditional Formatting → Highlight Cells Rules → Less Than
- Isi angka 70, pilih format Light Red Fill with Dark Red Text
- Klik OK
Hasilnya: Budi (65) dan Dian (55) otomatis merah. Ahmad, Citra, dan Eka tidak berubah.
Contoh 2: Warnai Status "Piutang" (Kuning)
| A | B | C |
|---|---|---|
| Pelanggan | Tagihan | Status |
| Toko Barokah | 500000 | Lunas |
| CV Amanah | 750000 | Piutang |
| Bu Fatimah | 200000 | Piutang |
| Pak Hasan | 300000 | Lunas |
- Blok sel
C2:C5 - Klik Home → Conditional Formatting → Highlight Cells Rules → Text that Contains
- Ketik Piutang, pilih format Yellow Fill with Dark Yellow Text
- Klik OK
Contoh 3: Tandai Duplikat Data
- Blok kolom yang ingin dicek duplikatnya
- Klik Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
- Pilih format warna yang diinginkan → OK
Untuk menemukan data duplikat di kolom ID atau kode barang, fitur Duplicate Values ini jauh lebih cepat daripada menelusuri manual atau pakai COUNTIF.
Top/Bottom Rules: Tandai Nilai Tertinggi dan Terendah Otomatis
Top/Bottom Rules berguna saat kamu ingin secara otomatis menandai N baris teratas, N baris terbawah, atau yang ada di atas/bawah rata-rata — tanpa perlu tahu dulu apa nilai tepatnya.
Contoh: Tandai 3 Nilai Penjualan Tertinggi
| A (Sales) | B (Omzet) |
|---|---|
| Nama | Omzet |
| Hasan | 18000000 |
| Husain | 24000000 |
| Siti | 9000000 |
| Maryam | 31000000 |
| Zainab | 15000000 |
| Ridwan | 27000000 |
- Blok sel
B2:B7 - Klik Home → Conditional Formatting → Top/Bottom Rules → Top 10 Items
- Ubah angka 10 menjadi 3, pilih format hijau → OK
Hasilnya: Maryam (31jt), Ridwan (27jt), dan Husain (24jt) otomatis hijau sebagai 3 teratas.
| Pilihan | Fungsi |
|---|---|
| Top 10 Items | N nilai terbesar (angkanya bisa diganti) |
| Top 10% | N% nilai teratas |
| Bottom 10 Items | N nilai terkecil |
| Above Average | Semua nilai di atas rata-rata range |
| Below Average | Semua nilai di bawah rata-rata range |
Data Bars dan Color Scales: Visualisasi Tanpa Grafik
Data Bars
Data Bars menampilkan batang mini di dalam sel yang panjangnya sebanding dengan nilai sel tersebut dibandingkan sel lain dalam range yang sama. Efeknya seperti grafik batang mini yang langsung ada di dalam sel.
- Blok kolom nilai yang ingin divisualisasikan (contoh:
B2:B7dari tabel omzet di atas) - Klik Home → Conditional Formatting → Data Bars
- Pilih warna Gradient Fill atau Solid Fill sesuai selera
Color Scales
Color Scales memberi gradasi warna dari satu ujung ke ujung lain — misalnya merah untuk nilai terendah, kuning untuk tengah, hijau untuk tertinggi. Ini efektif untuk melihat distribusi data sekilas, seperti heatmap sederhana.
- Blok range data
- Klik Home → Conditional Formatting → Color Scales
- Pilih kombinasi warna yang diinginkan (paling populer: hijau–kuning–merah)
Data Bars dan Color Scales sangat berguna untuk laporan cepat tanpa perlu insert Chart. Cocok untuk tabel absensi, nilai, stok, atau rekap penjualan bulanan.
Icon Sets: Ikon Otomatis Berdasarkan Kondisi
Icon Sets menambahkan ikon visual di dalam sel — seperti panah naik/turun, lampu hijau/kuning/merah, atau tanda centang/silang — berdasarkan nilai sel dibandingkan dengan range-nya.
- Blok range data
- Klik Home → Conditional Formatting → Icon Sets
- Pilih set ikon yang diinginkan
Untuk mengatur batas nilai secara manual (misalnya ikon hijau jika ≥80, kuning jika ≥60, merah jika di bawah 60):
- Klik Home → Conditional Formatting → Manage Rules
- Pilih rule Icon Sets yang sudah dibuat → klik Edit Rule
- Ubah tipe dari Percent menjadi Number
- Isi batas nilai sesuai kebutuhan → OK
| Set Ikon | Cocok untuk |
|---|---|
| 3 Traffic Lights (🔴🟡🟢) | Status proyek, stok barang |
| 3 Arrows (↑ → ↓) | Tren naik/stagnan/turun |
| 3 Symbols (✔ ! ✘) | Checklist, audit data |
| 5 Rating Stars | Penilaian atau skor |
New Rule + Formula: Warnai Seluruh Baris Berdasarkan Satu Kolom
Ini adalah fitur Conditional Formatting yang paling powerful. Dengan menggunakan rumus Excel sebagai kondisi, kamu bisa mewarnai seluruh baris — bukan hanya satu sel — berdasarkan nilai di kolom tertentu. Kuncinya ada di penggunaan tanda $ yang tepat pada referensi sel di rumus.
Contoh 1: Warnai Seluruh Baris Jika Status = "Piutang"
Gunakan tabel berikut mulai dari A1:
| A | B | C |
|---|---|---|
| Pelanggan | Tagihan | Status |
| Toko Barokah | 500000 | Lunas |
| CV Amanah | 750000 | Piutang |
| Bu Fatimah | 200000 | Piutang |
| Pak Hasan | 300000 | Lunas |
- Blok seluruh tabel data:
A2:C5 - Klik Home → Conditional Formatting → New Rule
- Pilih "Use a formula to determine which cells to format"
- Isi rumus:
=$C2="Piutang" - Klik Format → pilih warna kuning di tab Fill → OK → OK
Rumus
=$C2="Piutang" artinya: kolom C dikunci ($C) tapi nomor baris bebas bergerak (2 tanpa $). Ini yang membuat seluruh baris diperiksa berdasarkan nilai di kolom C.Jangan pakai
=$C$2 karena itu mengunci baris juga — akibatnya semua baris hanya dicek dari C2 saja, tidak ikut baris masing-masing.
Contoh 2: Warnai Seluruh Baris Jika Nilai < 70 (Dari Kolom B)
- Blok
A2:C6(seluruh data) - New Rule → Use a formula
- Isi rumus:
=$B2<70 - Pilih format warna merah muda → OK
Contoh 3: Warnai Baris Berselang (Zebra Stripes) Tanpa Tabel Excel
Kalau kamu tidak pakai fitur Excel Table (Ctrl+T), kamu bisa buat efek warna selang-seling manual:
- Blok seluruh data
- New Rule → Use a formula
- Isi rumus:
=MOD(ROW(),2)=0 - Pilih warna latar yang diinginkan → OK
Rumus ini memberi warna pada semua baris genap. Untuk baris ganjil, ganti =0 menjadi =1.
Mengelola dan Menghapus Conditional Formatting
Melihat Semua Rule yang Aktif
- Klik Home → Conditional Formatting → Manage Rules
- Di dropdown "Show formatting rules for", pilih This Worksheet untuk melihat semua rule di sheet aktif
- Dari sini kamu bisa Edit, Delete, atau ubah urutan prioritas rule
Jika ada dua rule yang berlaku pada sel yang sama, rule yang posisinya lebih atas di Manage Rules akan diprioritaskan. Kamu bisa atur ulang urutannya dengan tombol panah atas/bawah di jendela Manage Rules.
Menghapus Conditional Formatting
| Tujuan | Caranya |
|---|---|
| Hapus dari sel yang dipilih saja | Blok sel → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells |
| Hapus semua di sheet aktif | Conditional Formatting → Clear Rules → Clear Rules from Entire Sheet |
| Hapus rule tertentu saja | Manage Rules → pilih rule → klik Delete Rule |
Kesalahan Umum dan Cara Mengatasinya
Penyebab: Salah penempatan tanda
$ di rumus — misalnya pakai =$C$2 (baris ikut terkunci) padahal seharusnya =$C2.Solusi: Pastikan hanya kolom yang dikunci (
$C), bukan baris. Cek di Manage Rules → Edit Rule.
Penyebab: Range yang diblok saat membuat rule terlalu luas, atau rumus mengacu ke sel yang salah.
Solusi: Cek di Manage Rules — pastikan kolom "Applies to" sudah sesuai range data yang diinginkan.
Penyebab: Ada spasi tersembunyi di sel data (misal "Piutang " dengan spasi di belakang).
Solusi: Bersihkan data dengan TRIM terlebih dahulu, atau pakai rumus
=TRIM($C2)="Piutang" di New Rule.
Penyebab: Saat copy-paste, format dari sumber ikut terbawa dan menimpa Conditional Formatting yang sudah ada.
Solusi: Gunakan Paste Special → Values Only (Ctrl+Shift+V) agar format tidak ikut tertimpa.
Penyebab: Terlalu banyak rule Conditional Formatting yang diterapkan ke range sangat besar (misal seluruh kolom A:A).
Solusi: Batasi range hanya ke area data nyata, misal
A2:A1000, bukan seluruh kolom. Hapus rule yang tidak terpakai lewat Manage Rules.
Kesimpulan
Conditional Formatting adalah salah satu fitur Excel paling berguna untuk membuat data lebih mudah dibaca dan dianalisis sekilas — tanpa perlu rumus kompleks atau grafik terpisah. Berikut ringkasan kapan memakai masing-masing fitur:
| Kebutuhan | Fitur yang Tepat |
|---|---|
| Warnai sel berdasarkan nilai, teks, tanggal | Highlight Cells Rules |
| Tandai N nilai terbesar/terkecil atau di atas rata-rata | Top/Bottom Rules |
| Visualisasi perbandingan nilai antar sel | Data Bars / Color Scales |
| Tampilkan ikon status otomatis | Icon Sets |
| Warnai seluruh baris berdasarkan satu kolom | New Rule + Formula (dengan $ yang tepat) |
Ingat satu prinsip penting: Conditional Formatting hanya mengubah tampilan, bukan data. Ia adalah alat bantu visual untuk mempermudah pengambilan keputusan — bukan pengganti audit data yang sesungguhnya. Gunakan secara bijak agar file tidak menjadi berat, dan batasi range hanya ke area data yang benar-benar dibutuhkan.
FAQ — Pertanyaan yang Sering Muncul
1. Apakah Conditional Formatting bisa dipakai di Google Sheets?
Ya, Google Sheets punya fitur serupa dengan nama yang sama. Caranya: pilih sel → Format → Conditional Formatting. Opsinya hampir sama dengan Excel, namun beberapa fitur lanjutan seperti Icon Sets lebih terbatas di Google Sheets dibanding Excel desktop.
2. Bisa tidak Conditional Formatting diterapkan ke seluruh kolom sekaligus?
Bisa, tapi tidak disarankan karena akan memperlambat file. Lebih baik batasi range ke baris data aktual, misalnya A2:A1000, daripada A:A yang mencakup jutaan sel kosong.
3. Kenapa Conditional Formatting saya hilang setelah file disimpan dan dibuka lagi?
Kemungkinan file disimpan dalam format .csv (yang tidak mendukung formatting) atau ada konflik format. Pastikan file disimpan sebagai .xlsx atau .xlsm agar semua formatting tersimpan dengan benar.
4. Bisa tidak dua rule Conditional Formatting diterapkan ke sel yang sama?
Bisa. Jika dua rule berlaku di sel yang sama, rule yang posisinya lebih atas di Manage Rules akan diprioritaskan. Kamu bisa centang opsi "Stop If True" pada rule teratas agar rule di bawahnya tidak ikut dijalankan.
5. Bagaimana cara menyalin Conditional Formatting ke range lain?
Klik sel yang sudah punya Conditional Formatting → klik Format Painter (ikon kuas di tab Home) → drag ke range tujuan. Atau blok sel sumber → Copy → blok range tujuan → Paste Special → Formats Only.
6. Apakah Conditional Formatting bisa dipakai bersamaan dengan rumus IF?
Keduanya bisa dipakai bersamaan tapi untuk tujuan berbeda. Rumus IF menghasilkan nilai teks atau angka di dalam sel, sementara Conditional Formatting hanya mengubah tampilan sel. Untuk menampilkan label sekaligus memberi warna otomatis, pakai keduanya secara paralel di kolom yang sama.
Baca Juga
- Rumus IF 4 Kondisi di Excel: IF Bertingkat, IFS, AND/OR + Contoh Tabel per Sel
- Rumus Excel Basic untuk Pemula: 12 Rumus Dasar yang Paling Sering Dipakai
- Cara Membuat Dropdown List di Excel (Data Validation)
- Contoh Data Excel untuk Latihan: Tantangan Rumus Bertahap dari Pemula sampai Dashboard
- Cara Freeze Panes di Excel: Membekukan Baris, Kolom, dan Keduanya Sekaligus
Comments
Post a Comment