Conditional formatting membantu kita untuk menyorot informasi tertentu pada lembar kerja dengan lebih cepat. Dengan menguasai trik-trik dalam conditional formatting akan membuat pekerjaan yang menggunakan Microsoft Excel lebih mudah dan hemat waktu.
Bila Anda belum pernah menggunakannya, coba trik di artikel ini yang membahas cara menyorot data tertentu dan cara membuat Gantt Chart dengan conditional formatting.
DAFTAR ISI
Cara Menggunakan Formula di Conditional Formatting
Catatan : Cara ini akan digunakan untuk trik no 1-3.
- Excel 2007, 2010, 2013, 2016, 2019, 2021, 365:
- Pada tab Home, grup Styles, klik Conditional Formatting, dan pilih New Rule.
- Pada Select a Rule Type, klik Use a formula to determine which cells to format.
- Pada bagian Format values where this formula is true, masukkan formula yang diinginkan.
- Klik tombol Format, pada tab Fill pilih warna yang diinginkan dan klik OK.
- Klik OK 2 kali untuk menutup kotak dialog.
- Excel 2003:
- Pada menu Menu, Format, pilih Conditional Formatting.
- Pada Condition 1, pilih Formula is.
- Pada kotak di sampingnya, masukkan formula yang diinginkan.
- Klik tombol Format, pada tab Patterns pilih warna yang diinginkan dan klik OK.
- Klik OK untuk menutup kotak dialog.
Cara Memberi Warna pada Tabel Excel Selang-Seling
Misalnya kita ingin menyorot baris genap atau kolom ganjil saja dengan memberi warna seperti contoh berikut.
Langkah-langkah:
- Pilih range (kumpulan sel) yang akan diberi format.
- Buat conditional formatting dengan formula berikut:
Lihat cara menggunakan formula.
Cara Memberi Warna pada Baris Excel (Genap atau Ganjil)
- Memberi warna pada setiap baris genap:
=MOD(ROW(),2)=0 atau =ROW()=EVEN(ROW())
- Memberi warna pada setiap baris ganjil:
=MOD(ROW(),2)=1 atau =ROW()=ODD(ROW())
Baca juga: Cara Membuat dan Menandai Angka Ganjil Genap di Excel
Cara Memberi Warna pada Kolom Excel (Genap atau Ganjil)
- Memberi warna pada setiap kolom genap:
=MOD(COLUMN(),2)=0 atau =COLUMN()=EVEN(COLUMN()) - Memberi warna pada setiap kolom ganjil:
=MOD(COLUMN(),2)=1 atau =COLUMN()=ODD(COLUMN())
Cara Memberi Warna pada Setiap Baris Ketiga Excel
Gunakan formula berikut:
=MOD(ROW(),3)=0
Hasilnya akan seperti ini:
Cara Memberi Warna pada Baris dan Kolom Excel
Gunakan dua formula berikut: =MOD(COLUMN(),2)=0 dan =MOD(ROW(),3)=0
(Perlu memasukkan rule dua kali di Excel 2007 – 2021 atau dua conditon di Excel 2003).
Hasilnya akan seperti ini:
Catatan: ganti tanda koma dalam formula dengan tanda titik koma bila komputer Anda menggunakan tanda pemisah tersebut.
Lihat juga: Cara Merubah Warna Cell Aktif di Excel
Cara Membuat Gantt Chart dengan Conditional Formatting di Excel
Membuat Gantt Chart dengan conditional formatting akan sangat efisien karena bila ada perubahan nilai di kolom Start dan End (lihat gambar), kita tidak perlu menghapus atau mengisi warna pada sel lagi.
Warna pada kotak di samping akan otomatis berubah bila ada perubahan nilai.
Langkah-langkah:
- Buat tabel dengan data seperti pada gambar (aktivitas, start, end, dan angka 1 s/d 10).
- Pilih range dari E2 sampai dengan N6.
- Buat conditional formatting dengan formula berikut: =AND(E$1>=$C2, E$1<=$D2). Lihat cara menggunakan formula.
Cara Sorot Data yang Error di Excel
Trik ini sangat membantu untuk menemukan error dalam lembar kerja yang memiliki banyak data.
Langkah-langkah:
- Klik sel C12 (lihat gambar).
- Buat conditional formatting dengan formula berikut: =ISERROR(C12). Lihat cara menggunakan formula.
- Copy C12 dan paste ke baris C13 sampai dengan C17.
Cara Sorot Data yang Sama dengan Tanggal Hari Ini di Ecxel
Misalkan kita ingin menyorot data pembayaran yang jatuh tempo pada hari ini.
Untuk itu, setiap data yang jatuh tempo akan disorot (diberi warna pada sel), sehingga mempermudah kita melihatnya.
Langkah-langkah:
- Pilih range data.
- Bila menggunakan:
- Excel 2007, 2010, 2013, 2016, 2019, 2021, 365:
- Pada tab Home, grup Styles, klik Conditional Formatting, dan pilih New Rule.
- Pada Select a Rule Type, klik Format only cells that contain.
- Pada bagian Format only cells with, pilih Cell Value, equal to dan masukkan formula =TODAY() pada kotak di sampingnya.
- Beri format yang diinginkan.
- Excel 2003:
- Pada menu Menu, Format, pilih Conditional Formatting.
- Pada Condition 1, pilih Cell Value is dan equal to.
- Pada kotak di sampingnya, masukkan formula =TODAY().
- Beri format yang diinginkan.
- Excel 2007, 2010, 2013, 2016, 2019, 2021, 365:
Cara Sorot Nilai Duplikat atau Nilai Unik di Excel
Tutorial: Excel 2007, 2010, 2013, 2016, 2019, 2021, 365.
Langkah-langkah:
- Pilih range data.
- Pada tab Home, grup Styles, klik Conditional Formatting, dan pilih New Rule.
- Pada Select a Rule Type, klik Format only unique or duplicate values.
- Di bagian Format all, pilih:
- Duplicate, bila ingin menyorot data duplikat.
- Unique, bila ingin menyorot data yang unik (tidak ada nilai yang sama dengannya dalam tabel).
- Beri format yang diinginkan.
Cara Sorot Nilai Di Bawah atau Di Atas Rata-Rata Excel
Tutorial: Excel 2007, 2010, 2013, 2016, 2019, 2021, 365.
Langkah-langkah:
- Pilih range data.
- Pada tab Home, grup Styles, klik Conditional Formatting, dan pilih New Rule.
- Pada Select a Rule Type, klik Format only values that are above or below average.
- Di bagian Format values that are, pilih below (bawah rata-rata) atau above (atas rata-rata).
- Beri format yang diinginkan.
Cara Sorot Nilai Top atau Bottom Excel
Tutorial: Excel 2007, 2010, 2013, 2016, 2019, 2021, 365.
Misalnya kita ingin menyorot data yang memiliki nilai ranking 5 atas atau 5 bawah.
Langkah-langkah:
- Pilih range data.
- Pada tab Home, grup Styles, klik Conditional Formatting, dan pilih New Rule.
- Pada Select a Rule Type, klik Format only top or bottom ranked values.
- Di bagian Format values that rank in the, pilih Top atau Bottom.
- Isi angka di sampingnya. Bila ingin menggunakan persen, centang kotak % of the selected range.
- Beri format yang diinginkan.
Penutup
Dasar-dasar penggunaan conditional formatting dapat dibaca di artikel berikut:
- Cara Membuat Conditional Formatting di Excel 2007 – 2021
- Cara Membuat Conditional Formatting Berdasarkan Nilai dari Sel yang Lain
40 Komentar
Saya punya masukan sbb:
1. Pada lembar kerja ingin saya, yaitu dalam sel satu kolom jika terdapat karakter yang sama dalam sel berbeda dapat dikondisikan berwarna, misal merah atau lainnya.
2. Masih dalam satu lembar kerja tersebut, jika dalam kolom yanga sama memiliki jumlah karakter nilainya melebihi dan kurang dari yang telah diorogram pada lembar kerja yang lain bagaimana cara mengkondisikannya misal lebih kuning dan kurang hijau
Terima kasih sebelumnya
1. Warnai Karakter yang sama: bisa menggunakan Trik 5: Menyorot Nilai Duplikat atau tutorial ini: 3 Cara Menemukan Data Duplikat Excel.
2. Untuk jumlah karakter bisa menggunakan fungsi LEN. Cara menggunakan formula ada di awal tutorial.
Contoh formula kurang dari =LEN($A$2))<1
Contoh formula lebih dari =LEN($A$2))>1
Bila menggunakan kriteria dari lembar kerja lain, ganti angka 1 dengan dengan sel di lembar tersebut, contoh Sheet2!$C$1
Halo, sy punya kendala ketika menggunakan conditional formating tsb. Misalnya kolom A 1000, B kosong/tidak diisi, C 2000. Rules yg dibuat adalah membandingkan harga dr kolom2 tsb naik (merah) atau turun (hijau). Namun pd kasus ini B kosong sehingga harga C harus dibandingkan dengan harga A. Bagaimana cara membandingkan harga C & A tsb? Apakah rumus tetap menggunakan conditional formating? Mohon bimbingannya dan arahannya.
Masukkan formula conditional formatting ini untuk warna merah
=AND(A1=MIN($A1;$B1;$C1);A1<>“”)
Dan ini untuk hijau
=AND(A1=MAX($A1;$B1;$C1);A1<>“”)
Ganti tanda ; dg tanda , pd formula bila komputer anda menggunakan settingan tsb.
Mohon maaf masih kurang mengerti. Blhkah sy emailkan pertanyaannya by email langsung? karna di sini tdk bs melampirkan foto.
Anda bisa mengikuti petunjuk di bagian awal tutorial di atas (Cara Menggunakan Formula di Conditional Formatting).
Sedangkan formula yg saya berikan sebelumnya adalah membandingkan sel A B C dan memberi warna pd nilai terendah & tertinggi, jd tidak masalah sel mana pun yang kosong.
Mohon maaf, yang perlu sy bandingkan bukan nilai terendah dan tertinggi. Yang perlu dibandingkan adalah harga di Hari ke1, Hari ke2, Hari ke3 sampai dengan hari ke 31. Misalnya hari ke1 1000, hari ke2 1000, hari ke3 2000, hari ke4 tidak membeli, hari ke5 3000. Dikarenakan hari ke4 tidak ada pembelian maka harga hari ke5 harus dibandingkan dengan hari ke3. Dengan kondisi yang sama naik (merah) atau turun (hijau). Hari ke5 dibandingkan hari ke3 harusnya berwarna merah. Rumus utk mentandainya bagaimana ya? Mohon bimbingan dan arahannya.
“Dikarenakan hari ke4 tidak ada pembelian maka harga hari ke5 harus dibandingkan dengan hari ke3…”
Ini harus menggunakan If then else. Sepengetahuan saya di conditional formatting tidak bisa menggunakan If then else pada 1 rule.
Coba tanyakan di forum excel seperti di sini. Mungkin ada yg bisa bantu dg vba
Baik, terima kasih atas arahannya. Sangat membantu. Semoga sukses selalu.
numpang tanya Mimin,
bagaimana cara merubah warna secara otomatis pada seluruh ROW (bukan cell) disaat pada cell tertentu berisi drop down list? contohnya bila diisi “YES” maka seluruh ROW akan berubah warna hijau dan bila diisi “NO” maka berubah warna merah.
terimakasih banyak ya Mimin…
Misalkan drop down list ada di sel A1.
1. Pilih seluruh row yg akan diberi warna
2. Di Home | Conditional Formatting, pilih New Rule
3. Pilih Use a formula to determine which cells to format dan masukkan formula =$A$1=”YES”
4. Klik Format, pilih tab Fill dan pilih warna hijau
5. Klik OK
Ulangi langkah di atas untuk warna merah dengan formula =$A$1=”NO”
langsung aja mo nanya nih…
bagaimana ngasih warna pada teks yang melebihi 100 karakter dengan merah sementara yang di bawah 100 karakter diwarna hitam, mohon pencerahannya…
Bisa dg cara ini:
1. Pilih semua sel yag akan diberi Conditional Formatting.
2. Di Conditional Formatting, klik New Rule dan pilih Use a formula to determine which cells to format.
3. Masukkan formula =LEN(A1)>= 100 dan beri format warna merah. Catatan: A1 adalah sel pertama yang berisi karakter
Trimakasi ilmunya. Numpang tanya , bagaimana jika angka 95. Atau lebih akan diganti otomatis ke 100. Bagai mana rumusnya diconditional format. Trimakasi banyak
Conditional Formatting hanya untuk format warna font, sel, dsb.
Bila ingin bulatkan angka ke 100, gunakan formula ini =IF(A1>=95;ROUND(A1/100;0)*100;A1)
Ganti tanda titik koma pd formula dgn tanda koma, bila komputer anda menggunakan settingan tsb
izin Admin.
saya sementara membuat tabel skor. misalnya untk pertandingan sepakbola. dimana ketika salah satu pemain mendapat 2 kartu kuning (akumilasi) atau mendapat 1 kartu merah, maka ada tanda bahwa pemain tersebut mendapat sanksi yang harus diselesaikan kepada panitia. ketika pemain tersebut sudah menyelesaikan sanksi tersebut, maka tanda tersebut akan dihilangkan. begitu seterusnya untuk pemain yang lain.
bagaimana rumus excelnya ya??? mohon bantuannya. terima kasih.
Coba cara seperti di sini: Cara Menampilkan dan Menyembunyikan Isi Sel Berdasarkan Nilai Sel Lain
Hallo saya putra
mau minta bantuannya
Jika saya ingin diatas 10% muncul tanda arah panah keatas warna merah, di bawah 10% muncul tanda panah ke bawah warna hujau, sedangkan jika 0% maka muncul tanda panah ke samping kanan warna kuning.
Terima kasih.
Coba cara di sini
Kalau mau setting untuk pemantauan tanggal expired dalam beberapa kondisi gimana ya?
– Kalau misalnya dia pas hr ni atau uda lewat dr tanggal hr ni berarti kadaluarsa kan wrnnya otomatis jd merah
– Kalau misalnya masih dalam jangka waktu 60hr dr tgl hr ni dia masih wrn kuning
– Tapi kalau masih lewat dr 60hari tuh dia ga kenapa2 gitu. Tetap putih aja tanggalnya.
Mohon bantuan dan pencerahannya.
Pembahasan yg detil di sini mungkin bisa membantu Anda
Untuk kolom yang sudah diberi warna dengan conditional formatting (misal: red), agar bisa diberi nilai tertentu (misal: 1) bagaimana pak? Terima kasih
Coba cara di sini
HALOO MINTA ILMUNYA PAK BOSS
BAGAIMANA CARANYA MEMBERI WARNA OTOMATIS UNTUK TANGGAL MERAH
Coba cara di sini
Halo
Nama saya Laurindo
saya mau tanya:
Bagaimana caranya kita membuat conditional formatting pada cell excel dengan warna sesuai dengan kehendak kita, misalnya kita mau memberi rank berdasarkan persentase:
1. lebih dari 100% dengan warna hijau
2. 80 – 100% dengan warna biru
3. 60 – 79% dengan warna kuning
4. dibawah 60% dengan warna merah
mohon bantuannya
Terima kasih
Halo Laurindo
Pilih range data yg ingin diwarnai. Pada Conditional Formatting, pilih Highlight Cells Rules.
Kemudian untuk:
1. lebih dari 100% dengan warna hijau (Pilih Greater Than). Dan pilih Custom Format untuk pemberian warna.
Ulangi lagi untuk 3 yg lain. Total ada 4 Conditional Formatting.
2. 80 – 100% dengan warna biru (Pilih Between)
3. 60 – 79% dengan warna kuning (Pilih Between)
4. dibawah 60% dengan warna merah (Pilih Less Than)
Bagaimana cara mewarnai cell yang angkanya tidak sama dengan cell yang dijadikan pembanding?
Misal :
cell A2 angkanya 23 (sell pembanding)
cell C2 angkanya 19, di cell ini kan angkanya tidak sama dengan cell pembanding, nah di cell ini ( C2 ) karna angkanya tidak sama jadi mau diberi warna, bagaimana caranya yaaa… Atas bantuannya sya ucapkan banyak terimakasih
Coba langkah-Langkah ini:
1. Pilih range data sel C2 dan sel lainnya yg ingin diwarnai (tidak termasuk sel A2 yg berisi angka pembanding).
2. Pada tab Home, grup Styles, klik Conditional Formatting, dan pilih New Rule.
3. Pada Select a Rule Type, klik Format only cells that contain.
4. Di bagian bawahnya, yaitu Edit the rule description: pada kotak tengah pilih Not Equal to dan di kotak kanan pilih sel A2.
5. Selanjutnya beri format warna yang diinginkan.
Helo Mbak Imeily, saya ingin buat tools dimana satu cell akan terupdate warna nya ketika kita memasukan tanggal pada cell lain nya example.
Shipement : Ready = Kirim = Terima = Check = Konfirmasi
SEPATU : 2 April = 3 April = 4 April = 5 April = 6 April
kira-kira skets format nya seperti itu, saya ingin cell SEPATU terupdat dengan warna berbeda secara otomatis mengikuti perubahan kolum proses pengiriman di atas.
Mohon dibantu ya Mbak
Trims
Sore artikelnya ngebantu banget,
mau tanya jika ingin menandai sel tetapi menggunakan kumulasi bagaimana? misal 1,2,3,4 yang nilainya kurang dari 8, maka yang tertandai sel nilai 4 karena 1+2=3, 1+2+3=6, 1+2+3+4=10 jadi yang tertandai sel bernilai 4. Terimakasih :)
Halo Yeda Dresty,
Misal data ada di kolom A.
1. Klik sel pertama (A1) dan gunakan formula berikut untuk Conditional Formatting:
=SUM($A$1:A1)>=10
2. Salin formula ke sel berikutnya dengan Format Painter.
kalo caranya nandain kolom yang ga sama nilainya sama kolom sebelahnya gimana ? aku bisa bikin kalo satu sel aja, tp giliran di blok sampe sel paling bawah malah ga bisa keluar hasilnya, katanya change reference cell to single cell. masa di formatting satu satu. tolong bantuannya mkasih ^^
@cynthia mustika,
Saya tidak menemukan masalah saat menandai sel yang isinya berbeda untuk lebih dari satu kolom. Apakah Anda mengikuti cara seperti pada Trik 5: Menyorot Nilai Duplikat atau Nilai Unik di atas?
Mungkin sebelum disalin tanda? “$” dihulangkan dulu. Misal $a$12 jadi a12 baru di copy…
Makasihhh!! Ngebantu banget gan!
terus buat artikel menarik lainnya, bro, trim
Very usefull for me. Thanks….
thank you…
great article…