10 Contoh Conditional Formatting Dengan Rumus Excel

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

Pada tutorial excel sebelumnya bisa anda pelajari mengenai tips dan 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 yg sdh microsoft excel sediakan. Untuk itu sebelum melanjutkan membaca panduan excel ini sebaiknya anda baca panduan sebelumnya tersebut supaya pemahaman kita bisa nyambung.

Jika sdh, mari kita lanjutkan.

1. Membuat Baris Selang Seling
2. Membuat Kolom Selang Seling
3. Mewarnai Cell Berdasarkan Nilai Cell Tunggal
4. Mewarnai Cell Berdasarkan Nilai Sebuah Range Cell
5. Mewarnai Baris Berdasarkan 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 Membuat Baris Selang-seling

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

Perhatikan contoh brkut:



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

=MOD(ROW();2)=1

Untuk baris genap dengan hasil sprti dibawah ini,



Custom formula conditional formatting yg digunakan adalah:

=MOD(ROW();2)=0

Pada kasus ini fungsi yg digunakan adalah fungsi MOD excel & fungsi ROW yg sdh kmi 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.



Untuk contoh di atas rumus excel yg 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 dlm jumlah tertentu fitur ini bisa memberatkan excel anda saat dijalankan.

Kalau masih ada alternatif lain yg 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 yg tentunya lebih mudah lagi untuk diterapkan.

Mewarnai Cell Berdasarkan Nilai Cell Tunggal

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



Pada contoh di atas hanya cell pada range D4:G13 yg 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 yg ingin di tandai adalah nilai cell yg lebih dari nilai cell E1.



Rumus conditional formattingnya adalah:

=D4>$E$1

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

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

Mewarnai Cell Berdasarkan Nilai Sebuah Range Cell

Jika warna atau tanda yg 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.



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

=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 yg diwarnai adalah cell yg memenuhi kriteria tertentu. Bagaimana jika kita ingin menandai cell-cell lain yg 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:



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

Custom formula yg 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 brkut baris dengan transaksi "Penjualan" & Nilai Jumlahnya lebih dari "750.000" akan ditandai.



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

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

Kriteria Penjualan & nilai 750.000 pada rumus diatas tentu saja bisa juga anda gantikan dengan referensi sel sprti pada contoh kasus kita yg ke-3.

Contoh kasus ini tentunya bisa juga anda kembangkan dengan menggunakan fungsi OR jika kriteria yg 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 sbgai brkut:

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

Hasilnya adalah sbgai brkut:

Menandai Baris Kolom Yang Berbeda

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



Jika anda bermaksud membandingkan 2 kolom yg bersesuaian & ingin menandai baris-baris yg berbeda Anda bisa mencontoh penerapan conditional formatting dengan rumus excel brkut:

=$B3<>$C3

Menandai Baris Kolom Yang Sama

Kebalikan dari sebelumnya untuk menandai kolom yg sama, conditional formattingnya bisa menggunakan rumus excel brkut:

=$B3=$C3

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

Menandai Cell Yang Tidak terdapat Pada Kolom Lainnya

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



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

Custom formula conditional formatting yg digunakan pada range B3:B12 pada contoh kasus ini adalah sbgai brkut:

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

Sampai disini ada yg ingin ditanyakan?

Jika Anda mendapatkan manfaat dari tutorial ini jangan ragu untuk berbagi 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 yg Berisi Angka Tertentu
3. Cara Menandai Otomatis Sel yg Berisi Teks Tertentu
4. Cara Menemukan Data Ganda & Data Unik Pada Excel
5. Cara Mewarnai Cell Dengan Rumus Excel
6. Contoh Conditional Formatting Dengan Rumus Excel

Sumber https://www.kelasexcel.id

Bagikan Artikel ini

Berlangganan update artikel terbaru via email: