Pemformatan Bersyarat di Excel: Aturan, Formula, dan Contoh

By Mirko Peters · 2026-11-12

Pemformatan bersyarat di Excel memberi warna pada sel berdasarkan aturan. Pelajari Aturan Sel Sorot, Atas/Bawah, batang data, skala warna, kumpulan ikon, formula, urutan, dan cara menghapusnya.

Automatically translated with AI from the original post. Read the English original

Pemformatan bersyarat di Excel mengubah tampilan sel, seperti warna isian, font, atau ikon, berdasarkan isinya. Anda memilih sel, membuka tab Beranda, memilih Pemformatan Bersyarat, lalu memilih aturan seperti Lebih Dari atau Nilai Duplikat. Ketika data berubah, pemformatannya ikut berubah. Artikel ini ditujukan bagi pengguna Excel yang membuat laporan, pelacak, atau daftar, dan ingin tanggal jatuh tempo, data duplikat, atau hasil teratas tampil menonjol tanpa memeriksa setiap baris secara manual. Anda akan mempelajari jenis aturan bawaan, cara menulis aturan berbasis formula, cara kerja urutan aturan, serta cara menghapus dan memecahkan masalah aturan.

Poin Penting

• Pilih sel Anda lalu pilih Pemformatan Bersyarat di tab Beranda. Anda juga dapat menggunakan tombol Analisis Cepat atau tekan Ctrl+Q.
• Aturan Sel Sorot dan Aturan Atas/Bawah mencakup sebagian besar kebutuhan harian: Lebih Dari, Antara, Teks yang Berisi, Nilai Duplikat, Di Atas Rata-rata.
• Untuk logika Anda sendiri, pilih Aturan Baru, lalu Gunakan formula untuk menentukan sel yang akan diformat. Formula harus diawali dengan = dan menghasilkan TRUE atau FALSE.
• Aturan dijalankan dari atas ke bawah di Kelola Aturan. Jika dua aturan bertentangan, aturan yang lebih tinggi yang menang, dan Hentikan Jika Benar akan menghentikan evaluasi.
• Pemformatan bersyarat tidak dapat merujuk ke buku kerja lain, dan kriteria teks dibatasi hingga 255 karakter.

Sumber utama sebagian besar detail di sini adalah halaman Microsoft tentang cara menggunakan pemformatan bersyarat untuk menyorot informasi di Excel. Jika saya menambahkan sesuatu yang tidak ada di halaman tersebut, saya akan menyebutkannya.

Cara menerapkan pemformatan bersyarat di Excel

Microsoft menyebutkan bahwa Anda dapat menerapkan pemformatan bersyarat pada rentang yang dipilih, rentang bernama, atau tabel Excel. Di Excel untuk Windows, fitur ini juga berfungsi pada laporan PivotTable. Ada dua cara untuk memulainya.

Dari pita.

1. Pilih sel.
2. Pada tab Beranda, pilih Pemformatan Bersyarat.
3. Arahkan kursor ke jenis aturan, pilih aturan, dan ikuti kotak dialog yang terbuka. Untuk sebagian besar aturan, Anda diminta memasukkan nilai atau teks serta format.

Dengan Analisis Cepat.

1. Pilih data Anda. Tombol Analisis Cepat kecil akan muncul di sudut kanan bawah pilihan.
2. Pilih tombol tersebut, atau tekan Ctrl+Q.
3. Pada tab Pemformatan, arahkan kursor ke opsi untuk melihat pratinjaunya, lalu klik. Jika kotak dialog Teks yang Berisi muncul, masukkan teksnya dan pilih OK.

Opsi pada tab tersebut bergantung pada data. Pilihan yang hanya berisi teks menawarkan pilihan seperti Teks, Duplikat, Unik, Sama Dengan, dan Hapus. Pilihan numerik menawarkan Batang Data, Warna, Kumpulan Ikon, Lebih Dari, 10% Teratas, dan Hapus. Ini adalah cara tercepat untuk mencoba tampilan tertentu sebelum Anda memutuskan menggunakannya.

Menu di tab Home adalah kotak perkakas yang sebenarnya, dan sisa artikel ini mengikutinya: Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets, New Rule, Manage Rules dan Clear Rules. Jika Anda ingin latar belakang tentang fiturnya sendiri, lihat halaman conditional formatting.

Menyorot sel dengan aturan sederhana

Highlight Cells Rules adalah aturan yang cepat. Microsoft mencantumkan aturan seperti Between, Equal To, Text that Contains, A Date Occurring, dan Duplicate Values (Greater Than dan Less Than ada di menu yang sama). Setiap aturan mengajukan satu pertanyaan sederhana tentang sebuah sel, lalu menyorot sel yang jawabannya ya.

Penggunaan umum:

• Greater Than pada kolom penjualan untuk menandai semua nilai di atas target.
• Between untuk menampilkan nilai dalam rentang yang dapat diterima, dan aturan yang sama di luar rentang jika Anda ingin melihat nilai pencilan.
• Equal To untuk nilai yang tepat, misalnya kode status atau skor 100.
• Text that Contains untuk menandai setiap sel dengan kata seperti "late" atau nama produk. Kata yang hanya sebagian juga akan cocok, karena aturan ini mencari teks di mana saja dalam sel.
• A Date Occurring untuk menandai tanggal yang jatuh dalam periode tertentu. Excel menyediakan preset di kotak dialog, jadi pilih salah satu di sana alih-alih mengetik tanggal secara manual.
• Duplicate Values untuk menandai data yang berulang dalam sebuah daftar. Ini adalah pemeriksaan tercepat sebelum Anda membuat PivotTable atau lookup.

Top/Bottom Rules bekerja berdasarkan peringkat: Top 10 Items, Bottom 10 %, Above Average, dan Below Average. Batasan Microsoft untuk aturan ini: angka top atau bottom berkisar dari 1 hingga 1000, atau 1 hingga 100 jika menggunakan persen, dan persentil berkisar dari 0 hingga 100 (ketik angkanya tanpa tanda persen).

Sebuah contoh kecil akan memperjelas hal ini. Misalkan kolom C berisi nilai pesanan. Pilih C2 hingga baris terakhir, pilih Highlight Cells Rules > Greater Than, masukkan target Anda, dan pilih isian hijau. Tambahkan Less Than dengan nilai batas bawah dan isian oranye pada sel yang sama. Sekarang pesanan terkuat dan terlemah akan menonjol, dan yang di tengah tetap polos. Ubah sebuah angka, dan warnanya ikut berubah.

Data bar, color scale, dan icon set

Ketiganya menunjukkan bagaimana nilai dibandingkan dalam sebuah rentang, bukan apakah nilai tersebut lolos suatu uji. Halaman Microsoft menjelaskannya seperti ini:

• Data Bars menempatkan batang di setiap sel. Batang yang lebih panjang berarti nilai yang lebih besar. Anda dapat memilih isian gradien atau solid, menampilkan batas, menampilkan batang saja, dan memformat nilai negatif.
• Color Scales menggunakan dua atau tiga warna. Pada color scale dua warna, satu warna menandai nilai tinggi dan warna lainnya menandai nilai rendah. Color scale tiga warna menambahkan warna tengah.
• Icon Sets menggunakan tiga hingga lima kategori, dan setiap ikon mewakili rentang nilai tertentu. Anda dapat membalik urutan ikon, menampilkan ikon saja, atau menyembunyikan ikon pada sel tertentu dengan No Cell Icon.

Langkahnya sama untuk ketiganya: pilih sel, buka Home > Conditional Formatting, arahkan ke jenisnya, lalu klik sebuah gaya. Halaman Microsoft tentang data bars, color scales and icon sets memberi satu tips praktis: melebarkan kolom yang berisi data bar membuat perbedaan antarnilai lebih mudah dilihat. Icon set dapat dikombinasikan dengan format bersyarat lainnya.

Gunakan dengan hati-hati. Kolom yang penuh color scale tiga warna di atas isian dan ikon sulit dibaca. Satu sinyal per kolom adalah kebiasaan yang baik. Warna saja juga sebaiknya tidak membawa makna, karena sebagian pembaca tidak dapat membedakan merah dari hijau. Ikon atau kolom teks membantu hal itu, dan saya akan menambahkannya pada setiap laporan yang ditujukan kepada audiens luas.

Untuk batasan color scale, Microsoft menyatakan bahwa nilai minimum harus lebih kecil dari nilai maksimum, dan titik tengah secara default adalah 50 persen.

Buat aturan Anda sendiri dengan rumus

Ketika aturan bawaan tidak cukup, gunakan Aturan Baru. Perintah ini membuka dialog Aturan Pemformatan Baru, tempat Anda memilih Gunakan rumus untuk menentukan sel mana yang akan diformat. Aturan Microsoft: rumus harus diawali dengan = dan menghasilkan TRUE atau FALSE. Sel yang menghasilkan TRUE akan mendapatkan format tersebut. Uji rumus Anda untuk mencari kesalahan terlebih dahulu, dan gunakan fungsi IS atau IFERROR jika kesalahan mungkin muncul.

Tulis rumus untuk sel pertama dari rentang yang dipilih. Jika rentang dimulai dari B2, tulis rumus seolah-olah Anda sedang melihat B2, dan Excel akan menerapkan logika yang sama ke sel lainnya. Bagian ini sering membingungkan, jadi tanda dolar sangat penting: $B2 mengunci kolom dan membiarkan baris bergerak, itulah cara memformat seluruh baris berdasarkan satu kolom.

Contoh dari Microsoft menunjukkan apa saja yang mungkin dilakukan:

• Tanggal kedaluwarsa. =B2<TODAY() untuk isian merah pada lencana yang sudah kedaluwarsa, dan =B2<TODAY()+60 untuk kuning pada lencana yang kedaluwarsa dalam 60 hari. Aturan merah harus berada di atas aturan kuning dalam daftar, atau aturan kuning juga akan mencakup yang sudah kedaluwarsa.
• Baris bergantian. =MOD(ROW(),2)=1 memberi warna pada setiap baris bernomor ganjil.
• Duplikat. =COUNTIF($A$2:$A$400,A2)>1 menandai duplikat di A2:A400.
• Dua kondisi. Contoh anggaran dari Microsoft menggunakan AND dan OR untuk memberi warna hijau pada hasil ketika uang muka dan cicilan bulanan sama-sama sesuai anggaran, dan merah ketika salah satunya tidak sesuai.

Perhatikan pola pada contoh COUNTIF: rentang bersifat absolut ($A$2:$A$400) dan sel yang diuji bersifat relatif (A2). Bentuk seperti inilah yang menjadi dasar sebagian besar aturan rumus yang berguna.

Jika aturan tampaknya tidak berfungsi, periksa apakah rumus benar-benar menghasilkan TRUE untuk sel pertama, dan apakah Anda menulisnya untuk sel kiri atas dari pilihan. Aturan yang ditulis untuk sel yang salah akan bergeser satu baris dan terlihat acak.

Contoh lengkap: pelacak tugas

Berikut pengaturan yang dapat Anda salin dalam lima menit. Kolom A berisi nama tugas, kolom B berisi tanggal jatuh tempo, kolom C berisi status seperti Open atau Done, dan kolom D berisi jam kerja.

1. Pilih B2 hingga baris terakhir. Tambahkan aturan rumus dengan =B2<TODAY() dan isian merah. Ini menandai semua yang sudah melewati tenggat.
2. Pada sel yang sama, tambahkan =B2<TODAY()+7 dengan isian kuning, lalu pindahkan aturan merah ke atasnya di Manage Rules. Sekarang tugas yang terlambat berwarna merah, tugas yang jatuh tempo dalam seminggu berwarna kuning, dan sisanya tetap polos.
3. Pilih seluruh tabel, A2 hingga D100, lalu tambahkan aturan rumus seperti =$C2="Done" dengan font abu-abu. Tanda dolar mengunci kolom, sehingga seluruh baris berubah abu-abu ketika statusnya Done. Rumus itu adalah pola saya sendiri, bukan salah satu contoh Microsoft, tetapi mengikuti aturan yang sama: diawali dengan tanda sama dengan dan menghasilkan TRUE atau FALSE.
4. Pilih D2 ke bawah dan terapkan Data Bars agar tugas-tugas yang paling berat mudah terlihat.

Ada dua hal yang bisa salah. Jika aturan merah berada di bawah aturan kuning, tugas yang terlambat akan tampak kuning, karena keduanya bernilai benar dan aturan yang lebih tinggi menang. Jika aturan Done berada di atas dan Anda mengatur Stop If True, warna tanggal pada tugas yang sudah selesai akan hilang, yang mungkin memang diinginkan. Coba dulu pada salinan dan ubah urutannya untuk melihat efeknya.

Mengatur urutan aturan

Buka Conditional Formatting > Manage Rules untuk melihat Conditional Formatting Rules Manager. Microsoft menjelaskan cara kerjanya:

• Aturan dievaluasi dari atas ke bawah, dan aturan baru secara default ditempatkan di atas. Move Up dan Move Down digunakan untuk mengubah urutannya.
• Ketika aturan tidak saling bertentangan, semuanya diterapkan. Misalnya, satu aturan dapat mengatur font tebal dan aturan lain mengatur isian merah.
• Ketika aturan saling bertentangan, aturan yang lebih tinggi yang menang.
• Format bersyarat yang bernilai benar akan menggantikan format manual pada sel yang sama. Jika Anda menghapus aturan tersebut, format manual akan kembali.
• Stop If True menghentikan evaluasi setelah sebuah aturan bernilai benar. Microsoft menyatakan fitur ini meniru perilaku Excel versi lama. Anda tidak dapat mengatur opsi ini pada aturan data bar, color scale, atau icon set.

Jika Anda melihat warna yang tidak diharapkan, Rules Manager adalah tempat untuk memeriksanya.

Menyalin, mencari, dan menghapus format bersyarat

• Salin ke sel lain. Gunakan Format Painter pada tab Beranda. Klik dua kali untuk mengecat beberapa area, lalu tekan Esc untuk berhenti. Jika aturannya menggunakan rumus, periksa kembali referensinya setelahnya.
• Temukan sel yang diformat. Gunakan Find & Select > Conditional Formatting, atau Go To Special > Conditional formats.
• Hapus aturan. Pilih sel, lalu pilih Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells, atau Clear Rules from Entire Sheet.

Satu batasan dari Microsoft: menempelkan sel yang diformat ke instans Excel lain tidak membawa aturannya, jadi salin di dalam satu instans jika Anda ingin aturannya tetap ada.

Batasan dan alasan aturan mungkin tidak berfungsi

• Buku kerja lain. Conditional formatting tidak dapat merujuk ke buku kerja eksternal. Pindahkan data ke dalam file terlebih dahulu.
• PivotTable. Di area Values pada PivotTable, Anda tidak dapat memformat berdasarkan teks, tanggal, atau nilai unik dan duplikat. Hanya aturan berbasis angka yang berfungsi di sana.
• Teks panjang. Kriteria teks dibatasi hingga 255 karakter.
• Urutan. Aturan yang berada di atas akan menimpa aturan di bawahnya. Pindahkan aturan yang lebih spesifik ke atas.
• Isian manual. Jika sel memiliki isian manual dan aturannya bernilai benar, aturan yang menang. Jika tampilannya salah, periksa apakah aturannya memang benar.
• Excel untuk web. Halaman yang saya gunakan tidak mencantumkan langkah atau batasan khusus web. Saya tidak akan menjamin bahwa setiap jenis aturan berperilaku sama di browser. Uji pada salinan.

Kesalahan umum

• Menulis rumus untuk sel yang salah, sehingga sorotannya meleset satu baris.
• Menggunakan referensi absolut ($A$1) ketika Anda membutuhkan referensi relatif, dan memberi warna pada semua sel atau tidak sama sekali.
• Menumpuk aturan di atas aturan sampai tidak ada yang tahu mengapa sebuah sel berwarna merah. Buka Manage Rules dan bersihkan.
• Menempelkan sel yang diformat ke jendela Excel lain dan berharap aturannya ikut. Microsoft menyatakan aturannya tidak ikut.
• Hanya mengandalkan warna merah dan hijau.
• Menerapkan aturan ke seluruh kolom padahal hanya 200 baris yang membutuhkannya.

Topik terkait untuk dijelajahi

• Sorot duplikat sebelum Anda menghapusnya. Aturan Duplicate Values adalah pemeriksaan yang baik sebelum Anda membersihkan daftar.
• Daftar dropdown dan aturan secara bersamaan. Tandai baris berdasarkan status yang dipilih seseorang dari daftar.
• Conditional formatting di PivotTable. Hanya aturan berbasis angka yang berfungsi di area Values, jadi rencanakan tata letak Anda.
• Dashboard. Perpaduan data bars, ikon, dan slicer membuat laporan mudah dibaca sekilas.
• Performa pada lembar besar. Jaga rentang tetap ringkas dan jumlah aturan tetap sedikit. Halaman Microsoft tidak menerbitkan angka, jadi uji dengan data Anda sendiri.

Langkah berikutnya

Buka lembar kerja sungguhan dan terapkan satu aturan dengan Highlight Cells Rules. Lalu coba aturan rumus seperti =B2<TODAY() pada kolom tanggal, dan buka Manage Rules untuk melihat cara kerja urutannya. Buat conditional formatting di Excel tetap sederhana: satu sinyal yang jelas per kolom lebih baik daripada pelangi warna.