Cara Menggunakan Conditional Formatting di Excel

Kita sering berhadapan dengan kondisi di mana perlu secepat mungkin menemukan informasi yang butuh perhatian segera.

Artikel ini membahas bagaimana cara menggunakan Conditional formatting di Excel untuk memberi tanda pada informasi yang kita butuhkan sesuai dengan kriteria yang ditetapkan.

Kami telah menyiapkan data sebagai bahan latihan pada contoh-contoh di artikel ini. Bila suka, silahkan download. Tapi, kalau mau pakai data sendiri, ya, monggo aja.

File: 00-conditional-formatting.xlsx.

Apa Itu Conditional Formatting Pada Excel?

Conditional formatting Excel atau pemformatan dengan kondisi adalah penerapan pemformatan terhadap cell dan range sesuai kriteria tertentu.

Seperti pemformatan cell lain, conditional formatting menggunakan fitur pemformatan standar Excel. Kita bisa mengubah warna cell, format font, border, dan format angka bila sesuai dengan kondisi yang ditetapkan.

Penerapan format oleh conditional formatting akan berubah saat kondisi berubah.

Di Mana Letak Conditional Formatting Pada Excel?

Lokasi conditional formatting pada Excel bisa ditemui di tab Home Ribbon, pada kelompok perintah Styles.

Cara Menggunakan Conditional Formatting di Excel

Bila teman-teman perhatikan, ada beberapa aturan siap pakai yang bisa diakses lewat Highlight Cell Rules dan Top/Bottom Rules.

Untuk contoh di sini, kami akan menggunakan salah satu aturan siap pakai tersebut untuk menunjukkan bagaimana conditional formatting digunakan.

Tujuan demo ini adalah untuk memberi tanda pada penjualan di bawah Target Minimal, yaitu 2500.

(Demo 01) Berikut bagaimana cara menggunakan Conditional Formatting di Excel.

1. Pilih cell atau range yang ingin diformat.
2. Buka Conditional Formatting.
3. Pilih Highlight Cells Rules.
4. Pilih Less Than.

   

5. Hapus isi Format cells that are LESS THAN.
6. Klik cell B1.
7. Pada combo box With, pilih Light Red Fill with Dark Red Text.
8. Klik OK.

   

Hasil pemformatan bisa dilihat pada Gambar 05.

Catatan: Pada Langkah 5 dan 6, kami menggunakan nilai dari cell B1 yang bernilai 2500.

Kita bisa mengetikkan langsung nilai 2500 bila memang dibutuhkan.

Conditional formatting berdasarkan cell lain sebagai referensi seperti pada Langkah 6 lebih fleksibel. Kita hanya perlu mengubah nilai cell B1 bila terjadi perubahan nilai acuan.

Bagaimana Cara Membuat Custom Format Conditional Formatting

Bila teman-teman perhatikan isi combo box With pada Langkah 7 di atas, di dalamnya terdapat Custom Format.

Mengklik pilihan ini akan membuka kotak dialog Format Cells.

Artinya, alih-alih menggunakan format siap pakai seperti Light Red Fill with Dark Red Text, kita bisa membuat format sendiri. Caranya adalah dengan utak-atik format angka, format font, border, dan warna cell.

Cara Menghapus Conditional Formatting Excel

Ada dua cara menghapus conditional formatting Excel, yaitu dengan Clear Rules dan Rules Manager.

Cara Menghapus Conditional Formatting dengan Clear Rules

(Demo 02) Berikut cara menghapus Conditional Formatting dengan perintah Clear Rules.

1. Pilih cell atau range yang akan dihapus formatnya.
2. Buka Conditional Formatting.
3. Klik Clear Rules.
4. Klik Clear Rules from Selected Cells.

Macam-macam Perintah Clear Rules Excel

Secara keseluruhan, Excel menawarkan empat cara untuk menghapus conditional formatting, yaitu:

• Clear Rules from Selected Cells, seperti yang ditunjukkan pada demo di atas, akan menghapus conditional formatting Excel dari cell atau range terpilih dari worksheet aktif.
• Clear Rules from Entire Sheet akan menghapus seluruh conditional formatting dari worksheet aktif.
• Clear Rules from This Table akan menghapus conditional formatting dari tabel data aktif.
• Clear Rules from This PivotTable akan menghapus conditional formatting dari PivotTable.

Cara Menghapus Conditional Formatting dengan Rules Manager

(Demo 03) Berikut cara menghapus conditional formatting Excel dengan perintah Rules Manager:

1. Pilih cell atau range yang akan dihapus formatnya.
2. Buka Conditional Formatting.
3. Klik Manage Rules.

   

4. Pastikan Show formatting rules for menampilkan Current Selection.

Catatan: Kita juga bisa menghapus semua conditional formatting yang berada pada worksheet tertentu dengan memilih nama worksheet tersebut.

5. Pilih aturan yang ingin dihapus.
6. Klik Delete Rule.
7. Klik Apply atau OK untuk menerapkan perubahan.

Cara Membuat Conditional Formatting di Excel

Membuat conditional formatting di Excel perlu dilakukan saat format-format bawaan tidak ada yang sesuai dengan kebutuhan kita.

Tujuan demo ini adalah untuk memberi warna latar merah pada penjualan di antara 0 sampai 2500.

(Demo 04) Berikut cara membuat conditional formatting di Excel.

1. Pilih cell atau range yang ingin diformat.
2. Buka Conditional Formatting.
3. Klik New Rule untuk membuka kotak dialog Formatting Rule.

   

4. Pada kotak dialog Formatting Rule, pada Select a Rule Type, pilih Format only cells that contain.
5. Pada Edit the Rule Description, pada Format only cells with, pilih:
   1. Cell Value.
   2. Between.
   3. 0 (angka nol)
   4. Referensikan ke cell B1.
6. Klik Format untuk membuka Format Cells, lalu klik tab Fill untuk memilih warna merah.
7. Klik OK untuk menutup Formatting Rule.

Hasil dari membuat conditional formatting baru bisa dilihat pada gambar di bawah.

Cara Mengedit Conditional Formatting Excel

Seiring berjalan waktu, mungkin saja ada perubahan terhadap conditional formatting. Entah itu kondisi atau formatnya.

Tujuan demo berikut adalah memberi warna latar merah pada penjualan di bawah atau sama dengan 2500.

(Demo 05) Berikut cara mengedit conditional formatting Excel.

1. Klik Manage Rules di Conditional Formatting.
2. Pada Show formatting rules for, pilih This Worksheet.

Catatan: Bila teman-teman memilih cell atau range sebelum membuka Manage Rules, pilihan otomatis mengarah pada Current Selection.

Bila memilih This Worksheet, pastikan worksheet aktif memiliki aturan conditional formatting yang kita inginkan.

3. Pilih aturan yang ingin diubah.
4. Klik Edit Rule (atau klik ganda pada aturan tersebut) untuk membuka kotak dialog Formatting Rule.

   

5. Pada Edit the Rule Description, pada Format only cells with, pilih:
   1. Cell Value.
   2. Less than or equal to.
   3. Buat referensi ke cell B1.

6. Klik OK untuk menutup Formatting Rule.

   

7. Klik Apply atau OK untuk menerapkan perubahan aturan.

Secara logika, ada perbedaan antara aturan sebelum dan setelah diubah. Namun, keduanya punya pengaruh yang sama terhadap data. Karena itu tidak ada perbedaan hasil.

Menambah Conditional Formatting Excel Pada Cell-cell yang Sama

Kita bisa menerapkan lebih dari satu aturan conditional formatting pada cell yang sama.

Pada kesempatan ini, kami juga akan menunjukkan bagaimana menambah aturan baru dengan membuat dari awal dan dengan cara mengcopy conditional formatting di Excel.

Tujuan akhir dari demo kita adalah menampilkan hasil seperti Gambar 16.

Kriteria untuk menampilkan data seperti di atas adalah:

• Bila nilai penjualan sama atau di bawah 2500, maka tampilkan dengan warna latar merah.
• Bila nilai penjualan sama atau di bawah 7500, maka tampilkan dengan warna latar kuning.
• Bila nilai penjualan sama atau di bawah 10000, maka tampilkan dengan warna latar biru.

(Demo 06) Berikut cara menambah conditional formatting Excel pada cell-cell yang sama.

1. Pilih cell atau range yang ditambahkan aturannya.
2. Klik Manage Rules di Conditional Formatting.
3. Klik aturan yang sudah ada.
4. Klik Duplicate Rule. Excel akan meletakkan aturan baru di posisi teratas. Dengan demikian, aturan lama berada di posisi bawah.

   

5. Edit aturan baru.
6. Pada Edit the Rule Description, pada Format only cells with, pilih:
   1. Cell Value.
   2. Less than or equal to.
   3. Referensikan ke cell B2.

7. Klik Format untuk membuka Format Cells dan berikan warna latar kuning lewat tab Fill.
8. Klik OK untuk menutup Formatting Rule.
9. Klik New Rule.
10. Pada Select a Rule Type, pilih Format only cells that contain.
11. Pada Edit the Rule Description, pada Format only cells with, pilih:
    1. Cell Value.
    2. Less than or equal to.
    3. Referensikan ke cell B3.

12. Klik Format untuk membuka Format Cells dan berikan warna latar biru lewat tab Fill.
13. Klik OK untuk menutup Formatting Rule.

    

14. Klik OK untuk menerapkan aturan baru dan menutup Rules Manager.

Hasil akhir dari penerapan conditional formatting terlihat pada Gambar 19.

Looh. Kok biru semua? Apa yang terjadi? Aya naon? Apa yang salah dengan penerapan aturan kita?

Cara Mengubah Urutan Logika Conditional Formatting

Hasil pada Gambar 19 terjadi karena aturan-aturan yang kita gunakan tidak berada pada urutan logika yang benar.

Bila suatu cell atau range memiliki lebih dari satu aturan conditional formatting, maka aturan teratas akan diproses terlebih dahulu dan aturan terbawah terakhir.

Aturan dengan kondisi penjualan di bawah 10000 berada di posisi teratas. Sebagai aturan yang diproses pertama kali, aturan tersebut akan memberikan latar biru pada semua penjualan di bawah 10000, termasuk di dalamnya penjualan di bawah 7500 dan 2500.

(Demo 07) Berikut cara mengubah urutan logika conditional formatting.

1. Pilih cell atau range yang menggunakan penerapan conditional formatting.
2. Klik Manage Rules di Conditional Formatting.
3. Klik aturan dengan format latar merah.
4. Klik tanda panah (di sebelah Duplicate Rule) atas dua kali untuk memindahkan aturan tersebut ke posisi teratas.
5. Pindahkan aturan dengan format latar kuning ke posisi tengah.

   

6. Klik OK untuk mengkonfirmasi perubahan dan menutup Rules Manager.

Dengan urutan seperti pada Gambar 20, maka:

• Penjualan lebih kecil atau sama dengan 2500 akan diproses pertama kali.
• Penjualan lebih kecil atau sama dengan 7500 akan diproses setelahnya (tanpa mengindahkan hasil pemrosesan aturan pertama).
• Penjualan lebih kecil atau sama dengan 10000 akan diproses terakhir (tanpa melihat hasil pemformatan dua kriteria di atasnya).

Cara Menggunakan Stop If True Conditional Formatting Excel

Pada Demo 07 kami menggunakan Stop If True.

Stop If True adalah berguna saat menggunakan lebih dari satu aturan conditional formatting untuk cell atau range yang sama.

Stop If True akan mencegah aturan lain untuk diproses saat suatu cell atau range memenuhi kriteria dari dua atau lebih aturan.

Aturan-aturan dengan penerapan Stop If True harus berada lebih di atas aturan-aturan lain.

Bonus akan diberikan bagi mereka yang penjualannya di atas 2500. Karena itu, demo kita kali ini akan menandai mereka dengan penjualan di atas angka tersebut, sekaligus latihan membuat aturan custom sendiri.

(Demo 08) Berikut cara menggunakan Stop If True conditional formatting Excel.

1. Pilih cell atau range yang ingin diformat.
2. Buka Manage Rules dan buat aturan dan format untuk penjualan di atas 2500.
3. Klik Apply (perhatikan semua penjualan memiliki format yang sama)
4. Buat aturan baru untuk penjualan lebih besar dari 0 dan lebih kecil atau sama dengan 2500 dan tanpa format.
5. Klik Apply.

Terlihat bahwa hasil tidak sesuai dengan rencana. Penjualan-penjualan di bawah 2500 masih memiliki format.

(Demo 09) Mari kita modifikasi aturan-aturan yang ada. Ikuti langkah-langkah berikut:

1. Gunakan kembali pilihan cell atau range pada Langkah 1 di atas.
2. Buka Manage Rules dan klik Stop If True pada aturan No Format Set.
3. Klik Apply.

Perhatikan Gambar 22. Penjualan-penjualan di bawah 2500 kini tidak lagi memiliki format apa pun.

Stop If True akan mencegah aturan di bawahnya bekerja bila kriteria dari kedua aturan berlaku pada cell-cell yang sama. Pada Gambar 22, cell C8 berada di bawah 2500 dan juga di atas 0.

Pertanyaannya, mengapa tidak sejak awal saja membuat format dengan kriteria di atas 2500? Mengapa perlu lebih besar dari 0?

Jawabannya:

• Demo di atas hanya contoh bagaimana menggunakan Stop If True (Menyebalkan, ya, jawabannya).
• Stop If True bisa berfungsi sebagai eksepsi atau pengecualian dari suatu kriteria. Bila suatu saat tidak butuh lagi, kita bisa mencabut eksepsi tersebut tanpa mengubah kriteria asli. Contoh: Boss memutuskan bahwa penjualan, selama tidak 0, akan diberikan bonus.

Pertanyaan: Coba berikan format pada aturan dengan No Format Set dan non aktifkan Stop If True. Lihat apa yang terjadi. Mengapa seperti itu? Jawabannya sudah kami siratkan di bagian mengubah urutan logika. Sampaikan jawaban teman-teman di kolom komentar.

Penutup

Demikian bahasan kami mengenai cara menggunakan conditional formatting di Excel.

Conditional formatting pada dasarnya adalah pemformatan Excel dengan menggunakan kondisi. Saat kondisi terpenuhi, Excel akan menerapkan pemformatan sesuai pengaturan yang ditentukan.

Bila teman-teman bingung dengan penggunaan logika, saran kami adalah jangan langsung utak-atik Excel.

Tuliskan dulu urutan logika di kertas, langkah demi langkah. Jangan lompat-lompat. Kemudian uji logika tersebut dengan data.

Kadang, membuat flowchart juga bisa membantu menguraikan isi pikiran dengan baik.

Selain itu, penting untuk menguasai penggunaan Format Cells.

Berikut beberapa artikel lain mengenai penggunaan conditional formatting secara khusus:

• Color Scales.
• Data Bars.
• Icon Sets.
• Mencari Cell Kosong.
• Menandai Data Duplikat.
• Menandai Data Unik.
• Tanggal.
• Conditional Formatting dengan Rumus.