Untuk memakai conditional formatting di Excel, pilih cell Anda, buka Home → Conditional Formatting (Beranda → Pemformatan Bersyarat), lalu pilih aturan seperti Highlight Cells Rules → Greater Than, Data Bars, atau Color Scales. Untuk hal yang tidak tercakup menu, seperti menyorot satu baris penuh atau membandingkan dua kolom, pilih New Rule → Use a formula to determine which cells to format lalu ketik rumus yang menghasilkan TRUE untuk cell yang ingin diformat.
Kebanyakan masalah berasal dari satu detail: dalam aturan berbasis rumus, tanda $ menentukan cell mana yang dilihat rumus. Jika benar (biasanya $C2, dengan kolom dikunci dan baris bebas), aturan berfungsi di setiap baris. Jika salah, Excel menyorot cell yang keliru, atau tidak menyorot sama sekali.
Catatan: nama menu ditulis dalam bahasa Inggris, dengan terjemahan dalam tanda kurung saat pertama kali muncul. Di Excel berbahasa Indonesia labelnya bisa sedikit berbeda, tetapi letaknya sama. Perhatikan juga pemisah argumen: jika pengaturan regional Anda memakai titik koma, ganti koma di rumus dengan titik koma (misalnya =AND($B2<>"";$B2<TODAY())).
| Yang Anda inginkan | Lokasi | Contoh |
|---|---|---|
| Menyorot nilai di atas, di bawah, atau di antara angka tertentu | Highlight Cells Rules | Jumlah lebih dari 5.000 |
| Menyorot teks atau tanggal tertentu | Highlight Cells Rules → Text that Contains / A Date Occurring | "Overdue", atau tanggal dalam 7 hari terakhir |
| Menemukan entri yang berulang | Highlight Cells Rules → Duplicate Values | Alamat email ganda |
| Menyorot nilai tertinggi atau terendah | Top/Bottom Rules | 10 item teratas, di atas rata-rata |
| Menampilkan ukuran atau tren sekilas | Data Bars, Color Scales, Icon Sets | Batang di dalam cell penjualan |
| Menyorot satu baris penuh atau membandingkan cell | New Rule → Use a formula… | =$C2="Overdue" |
Memakai aturan conditional formatting bawaan
Langkah-langkah ini sama di Excel untuk Microsoft 365, 2024, 2021, dan 2019 di Windows.
- Pilih cell yang akan diformat, misalnya kolom Amount (D2:D10), tanpa header.
- Buka Home → Conditional Formatting. Di Windows Anda juga bisa menekan
Alt,H,Lsatu per satu (urutan ini berlaku untuk Excel berbahasa Inggris). - Arahkan ke Highlight Cells Rules (Aturan Sorot Sel) dan pilih aturan:
- Greater Than, Less Than, Between, atau Equal To untuk angka
- Text that Contains untuk kata seperti "Overdue"
- A Date Occurring untuk tanggal seperti Yesterday, In the last 7 days, Next week, atau This month
- Duplicate Values untuk menandai data berulang (atau ubah drop-down menjadi Unique)
- Ketik nilainya, pilih format dari daftar with (seperti Light Red Fill with Dark Red Text) atau Custom Format.
- Klik OK.
Bagian menu lainnya bekerja dengan cara yang sama:
- Top/Bottom Rules menyorot Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, atau nilai Above Average dan Below Average. Angka 10 bisa diubah menjadi angka apa pun.
- Data Bars menggambar batang di dalam setiap cell, lebih panjang untuk nilai yang lebih besar (Gradient Fill atau Solid Fill).
- Color Scales mewarnai cell dari satu warna ke warna lain, misalnya merah untuk rendah dan hijau untuk tinggi.
- Icon Sets menambahkan panah, lampu lalu lintas, atau peringkat berdasarkan posisi tiap nilai dalam rentang.
Untuk menemukan atau menghapus data ganda, lihat panduan menghapus data duplikat di Excel.
Menyorot satu baris penuh berdasarkan nilai cell
Aturan bawaan hanya memformat cell yang memenuhi kondisi. Untuk mewarnai seluruh baris ketika Status berisi "Overdue", Anda memerlukan aturan berbasis rumus. Misalkan data Anda ada di A1:D100: Client di kolom A, Due date di B, Status di C, Amount di D.
- Pilih seluruh rentang data tanpa baris header, mulai dari cell kiri atas: klik A2 lalu seret ke D100.
- Buka Home → Conditional Formatting → New Rule.
- Di bawah Select a Rule Type, pilih Use a formula to determine which cells to format.
- Di Format values where this formula is true, ketik:
=$C2="Overdue" - Klik Format, buka tab Fill, pilih warna merah muda, lalu klik OK.
- Pastikan Preview menampilkan format Anda, lalu klik OK.
Setiap baris yang kolom C-nya berisi "Overdue" kini berubah merah dari A sampai D, dan warnanya ikut berubah saat status berubah. Perbandingannya tidak peka huruf besar-kecil. Jika orang mengetik status secara manual, drop down list menjaga ejaan tetap konsisten sehingga aturan tidak pernah melewatkan baris.
Kenapa tanda $ penting (kesalahan nomor 1)
Excel menulis rumus Anda untuk active cell, yaitu cell pertama dari pilihan Anda (A2 di atas), lalu menyesuaikannya untuk setiap cell lain, persis seperti menyalin rumus ke bawah dan ke samping.
| Referensi | Saat aturan bergerak di rentang | Dipakai untuk |
|---|---|---|
$C2 | Kolom tetap C, baris berubah | Aturan satu baris penuh (pilihan yang umum) |
C2 | Kolom dan baris sama-sama berubah | Memformat cell berdasarkan cell di sebelahnya |
$C$2 | Tidak pernah berubah | Satu ambang batas atau cell input |
C$1 | Kolom berubah, baris tetap 1 | Membandingkan tiap kolom dengan nilai di baris 1 |
Dengan =C2="Overdue", cell A2 memeriksa C2 tetapi B2 memeriksa D2, sehingga hanya sebagian baris yang menyala. Dengan =$C$2="Overdue", setiap cell memeriksa C2, sehingga seluruh rentang menjadi merah semua atau tidak sama sekali.
Perhatikan: jika Anda mengklik sebuah cell saat mengetik rumus, Excel menyisipkan referensi absolut seperti $C$2. Tekan F4 (di Mac, Cmd+T atau Fn+F4) untuk berputar melalui $C$2, C$2, $C2, dan C2 sampai Anda mendapat yang dibutuhkan.
Contoh rumus lainnya
Gunakan langkah yang sama dengan rumus-rumus ini. Semuanya mengasumsikan pilihan Anda dimulai dari baris 2.
Format cell berdasarkan cell lain. Di sheet penjualan dengan Actual di kolom D dan Target di kolom E, pilih D2:D100 dan gunakan:
=$D2<$E2
Setiap realisasi di bawah targetnya berubah merah. Pilih A2:E100 sebagai gantinya untuk menyorot satu baris penuh.
Bandingkan dengan satu cell input. Taruh ambang batas di H1, pilih D2:D100 dan gunakan =$D2>$H$1. $H$1 sepenuhnya absolut sehingga setiap baris melihat cell yang sama.
Sorot tanggal yang sudah lewat. Pilih A2:D100 dan gunakan:
=$B2<TODAY()
Cell kosong dihitung sebagai nol (tanggal di tahun 1900), jadi tanggal jatuh tempo yang kosong juga menyala. Untuk melewati cell kosong dan faktur yang sudah dibayar, gunakan =AND($B2<>"",$B2<TODAY(),$C2<>"Paid").
Warnai baris selang-seling. Pilih data Anda dan gunakan:
=MOD(ROW(),2)=0
Ini mewarnai baris genap (=1 untuk baris ganjil). Home → Format as Table dengan Banded Rows menghasilkan hal yang sama tanpa aturan.
Gabungkan kondisi. Gunakan AND atau OR, misalnya =AND($C2="Pending",$D2>5000) untuk faktur pending yang besar. Anda tidak perlu IF: rumus hanya perlu menghasilkan TRUE atau FALSE.
Nama fungsi di atas (AND, OR, TODAY, MOD, ROW, TRIM) tetap dalam bahasa Inggris, karena Excel berbahasa Indonesia juga memakai nama fungsi bahasa Inggris.
Mengelola aturan, urutan aturan, dan Stop If True
Buka Home → Conditional Formatting → Manage Rules (Kelola Aturan) untuk membuka Conditional Formatting Rules Manager.
- Di Show formatting rules for, pilih This Worksheet untuk melihat setiap aturan di sheet.
- Gunakan Edit Rule atau Delete Rule, atau perbaiki rentang aturan di Applies to (misalnya
=$A$2:$D$100) lalu klik Apply. - Gunakan panah Move Up dan Move Down untuk mengurutkan ulang aturan. Aturan yang lebih atas dalam daftar yang menang.
Jika dua aturan mengatur hal yang sama secara berbeda, seperti dua warna isian, aturan yang lebih atas yang menang. Jika tidak bertentangan (satu membuat font tebal, satu lagi mengisi cell dengan warna merah), keduanya berlaku. Format dari aturan juga menimpa format yang Anda terapkan secara manual.
Centang Stop If True agar Excel berhenti memeriksa aturan di bawahnya setelah aturan itu terpenuhi, berguna untuk pengecualian seperti "jika Status adalah Paid, jangan tampilkan apa pun lagi". Opsi ini tidak tersedia untuk aturan data bar, color scale, atau icon set.
Untuk menghapus format, gunakan Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells atau Clear Rules from Entire Sheet.
Menyalin conditional formatting ke cell lain
- Format Painter: pilih cell yang sudah diformat, klik Home → Format Painter, lalu seret di atas cell baru. Klik dua kali untuk mewarnai beberapa rentang; tekan
Escuntuk berhenti. - Paste Special: salin cell, pilih target, lalu Home → Paste → Paste Special → Formats.
- Perluas aturannya saja: ubah rentang Applies to di Manage Rules. Dengan begitu Anda punya satu aturan yang bersih, bukan beberapa salinan.
Excel untuk Mac dan Excel untuk web
Excel untuk Mac memiliki menu Home → Conditional Formatting yang sama. Untuk aturan berbasis rumus, pilih New Rule, ubah Style menjadi Classic, pilih Use a formula to determine which cells to format di kotak di bawahnya, ketik rumusnya, dan atur Format with ke Custom Format.
Excel untuk web juga memakai Home → Conditional Formatting, tetapi aturan terbuka di panel Conditional Formatting, bukan dialog. Untuk aturan berbasis rumus, pilih New Rule dan atur jenis aturan ke Formula. Aturan yang dibuat di desktop berfungsi di browser dan sebaliknya.
Kenapa conditional formatting tidak berfungsi
Menyorot baris yang salah (meleset satu baris). Jika pilihan Anda dimulai di A2, rumus harus merujuk baris 2. Ini biasanya terjadi saat Anda ikut memilih baris header atau memilih dari bawah ke atas.
Hanya satu kolom yang tersorot, atau semuanya. Periksa tanda dolarnya. Gunakan $C2, bukan C2 atau $C$2, untuk aturan satu baris penuh.
Angka tersimpan sebagai teks. Nilai hasil impor bisa tampak seperti angka padahal teks (sering ada segitiga hijau kecil di sudutnya), sehingga aturan seperti Greater Than tidak berfungsi dengan benar. Pilih cell, klik ikon peringatan, dan pilih Convert to Number.
Teks tidak cocok. Spasi di akhir ("Overdue ") atau ejaan yang berbeda tidak akan sama dengan "Overdue". Bersihkan data dengan TRIM, atau gunakan Text that Contains alih-alih Equal To.
Aturan saling tumpang tindih atau bertentangan. Buka Manage Rules, tampilkan This Worksheet, dan periksa urutan serta rentang Applies to. Menyalin, menempel, dan menyisipkan baris sering memecah satu aturan menjadi banyak potongan.
Workbook menjadi lambat. Ratusan aturan yang terpecah, aturan pada seluruh kolom (=$A:$D), dan fungsi volatile seperti TODAY() atau INDIRECT menambah beban penghitungan ulang. Hapus duplikat dan batasi Applies to pada baris yang Anda pakai.
Pertanyaan yang Sering Diajukan
Bagaimana cara menyorot baris berdasarkan nilai cell di Excel? Pilih data tanpa header, pilih Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format, masukkan =$C2="Overdue", dan pilih warna isian. Tanda $ sebelum huruf kolom membuat seluruh baris berubah warna.
Bagaimana cara menerapkan conditional formatting berdasarkan cell lain? Gunakan aturan berbasis rumus yang menunjuk ke cell tersebut, misalnya =$D2<$E2 untuk menyorot nilai di bawah target, atau =$D2>$H$1 untuk membandingkan setiap baris dengan satu cell input.
Bisakah conditional formatting merujuk ke sheet lain? Bisa, misalnya =$D2>Settings!$B$1. Referensi ke workbook lain tidak diperbolehkan.
Bisakah saya mengurutkan atau memfilter berdasarkan warna conditional formatting? Bisa. Aktifkan Data → Filter, buka panah filter kolom lalu pilih Filter by Color atau Sort by Color. Ini juga berfungsi dengan warna dari aturan.
Bagaimana cara menghapus conditional formatting tanpa menghapus data? Pilih cell lalu pilih Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells. Nilai Anda tetap utuh.
Biarkan AI memberi tahu apa yang perlu disorot
Conditional formatting menampilkan hal yang sudah Anda putuskan untuk dicari. CoreGPT untuk Excel bekerja sebaliknya: tanyakan "apa yang menonjol di penjualan kuartal 3?" dan CoreGPT membaca data Anda lalu menunjukkan performa terbaik, penurunan tak biasa, dan tren, sehingga Anda tahu aturan mana yang layak dibuat. Gratis untuk dicoba dan berjalan di dalam Excel untuk Windows, Mac, dan web.
CoreGPTSorotan:
- Eropa memimpin dengan $184K, 23% di atas target
- 4 dari 6 wilayah melampaui target
- Amerika Utara kurang $7K dari target
Coba sekarang: CoreGPT for Excel
Bacaan terkait:
