10 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 acuan kasus penerapannya.

Tutorial kali ini dimaksudkan untuk melengkapi panduan sebelumnya perihal bagaimana memakai 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.

1. Membuat Baris Selang Seling
2. Membuat Kolom Selang Seling
3. Mewarnai Cell menurut Nilai Cell Tunggal
4. Mewarnai Cell menurut Nilai Sebuah Range Cell
5. Mewarnai Baris menurut Nilai Cell Yang Sebaris Pada Kolom Tertentu
6. Menandai Baris Dengan 2 Kriteria
7. Menandai Baris Kolom Yang Sama
8. Menandai Baris Kolom Yang Berbeda
9. Menandai Cell Yang Nilainya Tidak Terdapat Pada Kolom Lain

Cara menciptakan Baris Selang-seling

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

Perhatikan teladan berikut:



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

=MOD(ROW();2)=1

Untuk baris genap dengan hasil seakan-akan dibawah ini,



Custom formula conditional formatting yang digunakan ialah:

=MOD(ROW();2)=0

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

Cara menciptakan Kolom Selang-seling

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



Untuk acuan di atas rumus excel yang dipakai pada conditional formattingnya adalah:

=MOD(COLUMN();2)=0

Meskipun conditional formatting bisa digunakan untuk menciptakan baris atau kolom selang-seling semacam di atas, sacara langsung kami sarankan untuk dihindari kalau tujuannya hanya sekedar membuat tampilan selang-seling saja. sebab dalam jumlah tertentu fitur ini bisa memberatkan excel anda ketika 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 simpel 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.



Pada teladan 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 memakai operator perbandingan lainnya, misalnya yang ingin di tandai ialah nilai cell yang lebih dari nilai cell E1.



Rumus conditional formattingnya yaitu:

=D4>$E$1

Pada kedua contoh di atas, dikala 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 menurut 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 acuan penerapan conditional formatting excel ini.



Untuk perkara conditional formatting semacam ini kita bisa memanfaatkan fungsi COUNTIF. Rumus conditional formatting untuk teladan di atas yaitu 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 menurut Nilai Cell Yang Sebaris Pada Kolom Tertentu

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

Bingung ya? Pertanyaan ini bergotong-royong sering admin dapatkan. biar lebih praktis memahami maksud saya di atas perhatikan pola ini:



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

Custom formula yang dipakai untuk conditional formatting range B4:H13 tersebut yaitu:

=$H4=$E$1

Simpel saja kan?

Yang perlu anda perhatikan pada masalah ini yaitu 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.



Untuk perkara semacam ini kita memakai fungsi AND Excel sebagai custom formulanya.

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

Kriteria Penjualan dan nilai 750.000 pada rumus diatas tentu saja mampu juga anda gantikan dengan referensi sel seakan-akan pada contoh perkara kita yang ke-3.

Contoh perkara ini tentunya bisa juga anda kembangkan dengan menggunakan fungsi OR bila 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 yaitu sebagai berikut:

Menandai Baris Kolom Yang Berbeda

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



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

=$B3<>$C3

Menandai Baris Kolom Yang Sama

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

=$B3=$C3

Cukup simpel 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.



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

Custom formula conditional formatting yang dipakai pada range B3:B12 pada acuan kasus ini yaitu sebagai berikut:

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

Sampai disini ada yang ingin ditanyakan?

Jika Anda menerima manfaat dari tutorial ini jangan ragu untuk mengembangkan dengan teman-teman lainnya ya.

Berikut beberapa panduan Conditional Formatting Excel Lainnya:

1. Panduan Conditional Formatting Pada Microsoft Excel
2. Cara Memberi Warna Otomatis Sel yang Berisi Angka Tertentu
3. Cara Menandai Otomatis Sel yang Berisi Teks Tertentu
4. Cara Menemukan Data Ganda dan Data Unik Pada Excel
5. Cara Mewarnai Cell Dengan Rumus Excel
6. Contoh Conditional Formatting Dengan Rumus Excel