10 Contoh Conditional Formatting Dengan Rumus Excel

10 Contoh Conditional Formatting Dengan Rumus Excel

Contoh penerapan conditional formatting dengan custom formula atau rumus excel
Contoh Conditional Formatting Dengan Rumus Excel

Conditional Formatting Dengan Custom Formula - Sepertinya masih banyak yang penasaran dengan cara menggunakan custom formula atau rumus excel pada conditional formatting excel.

Pada tutorial excel sebelumnya bisa anda pelajari mengenai tips serta teknik untuk mewarnai cell dengan rumus excel. Pada seri tutorial excel ini Anda akan saya ajak untuk mempelajari beberapa contoh kasus penerapannya.

Tutorial kali ini dimaksudkan untuk melengkapi panduan sebelumnya tentang bagaimana menggunakan rumus excel untuk mewarnai cell atau lebih tepatnya untuk mengatur format cell secara otomatis menggunakan fitur conditional formatting yang sudah microsoft excel sediakan. Untuk itu sebelum melanjutkan membaca panduan excel ini sebaiknya anda baca panduan sebelumnya tersebut supaya pemahaman kita bisa nyambung.

Jika sudah, mari kita lanjutkan.

Cara Membuat Baris Selang-seling

Anda bisa memanfaatkan conditional formatting pada microsoft excel untuk membuat baris berwarna selang-seling. Cara ini tentu saja lebih mudah dan cepat dibandingkan jika anda harus menyeleksi setiap baris kemudian memberikan fill warna secara manual.

Perhatikan contoh berikut:

Membuat Baris Selang-seling Pada Microsoft Excel

Rumus yang digunakan untuk membuat warna kuning pada baris-baris dengan nomor ganjil pada range B3:H12 di atas adalah:

=MOD(ROW();2)=1
Custom Formula Conditional Formatting Excel

Untuk baris genap dengan hasil seperti dibawah ini,

Warna Baris Selang-seling Pada Microsoft Excel

Custom formula conditional formatting yang digunakan adalah:

=MOD(ROW();2)=0

Pada kasus ini fungsi yang digunakan adalah fungsi MOD excel dan fungsi ROW yang sudah kami jelaskan pada tutorial tersendiri.

Cara Membuat Kolom Selang-seling

Selain membuat baris selang-seling, kita juga bisa memanfaatkan fitur conditional formatting untuk membuat kolom selang-seling.

Warna Kolom Selang-seling Pada Microsoft Excel

Untuk contoh di atas rumus excel yang digunakan pada conditional formattingnya adalah:

=MOD(COLUMN();2)=0

Meskipun conditional formatting bisa digunakan untuk membuat baris atau kolom selang-seling semacam di atas, sacara pribadi saya sarankan untuk dihindari kalau tujuannya hanya sekedar membuat tampilan selang-seling saja. Sebab dalam jumlah tertentu fitur ini bisa memberatkan excel anda saat dijalankan.

Kalau masih ada alternatif lain yang lebih baik kenapa tidak di coba?

Selain menggunakan conditional formatting, anda juga bisa memanfaatkan fitur Format Tabel pada excel untuk membuat baris atau kolom selang-seling yang tentunya lebih mudah lagi untuk diterapkan.

Mewarnai Cell Berdasarkan Nilai Cell Tunggal

Anda ingin menguabah format warna cell yang nilainya sesuai dengan sebuah cell tertentu? manfaatkanlah fitur conditional formatting dengan custom formula.

Mewarnai Cell Hanya Yang Sesuai Dengan Isi Cell Tertentu

Pada contoh di atas hanya cell pada range D4:G13 yang nilainya sama dengan cell E1 akan diwarnai.

Rumus excel pada conditional formattingnya adalah:

=D4=$E$1

Selain nilai sama persis Anda bisa juga menggunakan operator perbandingan lainnya, misalnya yang ingin di tandai adalah nilai cell yang lebih dari nilai cell E1.

Menandai Cell Berdasarkan Nilai Cell Tertentu

Rumus conditional formattingnya adalah:

=D4>$E$1

Pada kedua contoh di atas, saat Anda mengubah nilai Cell E1, maka setiap cell yang sesuai pada range D4:G13 akan berubah pula warnanya.

Contoh ini bisa Anda jadikan sebagai referensi untuk membuat sebuah kotak pencarian sederhana.

Mewarnai Cell Berdasarkan Nilai Sebuah Range Cell

Jika warna atau tanda yang kita buat sebelumnya hanya berdasarkan satu cell, bisakah kriterianya berdasarkan banyal cell atau sebuah range tertentu?

Tentu bisa donk. perhatikan contoh penerapan conditional formatting excel ini.

Menandai cell yang sesuai dengan nilai range excel tertentu

Untuk kasus conditional formatting semacam ini kita bisa memanfaatkan fungsi COUNTIF. Rumus conditional formatting untuk contoh di atas adalah sebagai berikut:

=COUNTIF($E$1:$H$1;D4)>0

Yang perlu anda perhatikan bahwa rumus diatas digunakan untuk mengatur conditional formatting excel pada range D4:G13.

Mewarnai Baris Berdasarkan Nilai Cell Yang Sebaris Pada Kolom Tertentu

Pada contoh sebelumnya cell yang diwarnai adalah cell yang memenuhi kriteria tertentu. Bagaimana jika kita ingin menandai cell-cell lain yang sebaris namun kriterianya hanya untuk sebuah cell pada kolom tertentu.

Bingung ya? Pertanyaan ini sebenarnya sering saya dapatkan. Agar lebih mudah memahami maksud saya di atas perhatikan contoh ini:

Menandai baris berdasarkan nilai cell excel tertentu

Pada contoh diatas, jika nilai cell pada kolom jumlah sama dengan nilai pada cell E1, maka sebaris cell tersebut akan ditandai. Yang yang berisi nilai 349 adalah baris dengan nomor 6,8, dan 9. Bagaimana rumus conditional Formattingnya?

Custom formula yang digunakan untuk conditional formatting range B4:H13 tersebut adalah:

=$H4=$E$1

Simpel saja kan?

Yang perlu anda perhatikan pada kasus ini adalah penggunaan tanda $ pada rumus excel diatas.

Menandai Baris Dengan 2 Kriteria

Bagaimana custom formula untuk conditional formatting dengan dua kriteria atau lebih?

Pada contoh berikut baris dengan transaksi "Penjualan" dan Nilai Jumlahnya lebih dari "750.000" akan ditandai.

Menandai Baris Dengan Dua Kriteria

Untuk kasus semacam ini kita menggunakan fungsi AND Excel sebagai custom formulanya.

=AND($D3="Penjualan";$G3>750000)

Kriteria Penjualan dan nilai 750.000 pada rumus diatas tentu saja bisa juga anda gantikan dengan referensi sel seperti pada contoh kasus kita yang ke-3.

Contoh kasus ini tentunya bisa juga anda kembangkan dengan menggunakan fungsi OR jika kriteria yang ingin diterapkan berupa pilihan.

Misal Anda ingin menandai baris dengan berat lebih dari 80 atau Jumlah lebih dari 1.000.000, maka rumus conditional formattingnya sebagai berikut:

=OR($E3>80;$G3>1000000)

Hasilnya adalah sebagai berikut:

Mewarnai Baris Dengan Dua Kriteria

Menandai Baris Kolom Yang Berbeda

Conditional formatting juga sering digunakan untuk membandingka 2 Data atau tabel excel.

Menandai dua kolom yang berbeda di excel

Jika anda bermaksud membandingkan 2 kolom yang bersesuaian dan ingin menandai baris-baris yang berbeda Anda bisa mencontoh penerapan conditional formatting dengan rumus excel berikut:

=$B3<>$C3

Menandai Baris Kolom Yang Sama

Menandai dua kolom yang sama di excel

Kebalikan dari sebelumnya untuk menandai kolom yang sama, conditional formattingnya bisa menggunakan rumus excel berikut:

=$B3=$C3

Cukup mudah bukan? Jika masih bingung juga, sekali lagi silahkan perhatikan operator perbandingan dan penggunaan tanda $ pada rumus-rumus excel di atas.

Menandai Cell Yang Tidak terdapat Pada Kolom Lainnya

Contoh penggunaan custom formula berikutnya adalah untuk menandai nilai cell sebuah kolom yang nilai tersebut tidak terdapat pada range kolom lainnya.

Menandai kolom excel yang tidak terdapat di kolom lain

Pada contoh di atas, setiap nilai Data A yang tidak terdapat pada daftar Data B akan diwarnai atau ditandai.

Custom formula conditional formatting yang digunakan pada range B3:B12 pada contoh kasus ini adalah sebagai berikut:

=COUNTIF($D$3:$D$8;B3)=0

Sampai disini ada yang ingin ditanyakan?

Jika Anda mendapatkan manfaat dari tutorial ini jangan ragu untuk berbagi dengan teman-teman lainnya ya.

LANGGANAN GRATIS!

Dapatkan update tutorial excel gratis langsung ke email anda.