Lompat ke konten
Anda disini: M Jurnal » Analisis Data Excel » Panduan Conditional Formatting Microsoft Excel Lengkap

Panduan Conditional Formatting Microsoft Excel Lengkap

Cara menggunakan conditional formatting Microsoft Excel

Para Expert Excel sering menggunakan fitur Conditional Formatting dalam mengolah data. Fitur ini bisa digunakan untuk mempercantik sekaligus mempermudah dalam membaca data Anda.

Namun, untuk menguasai cara menggunakan Conditional Formatting, Anda harus paham aturan penggunaannya terlebih dahulu. Untuk itu, Panduan ini Saya susun sebagai teknik dasar penggunaan Conditional Formatting.

Jika Anda sudah paham teknik dasar ini, silahkan masuk ke SUB-BAB selanjutnya untuk memantapkan kemampuan Anda.

Apa itu Conditional Formatting ?

Conditional Formatting Excel adalah fitur untuk mengubah Format Cell (seperti warna cell, font, border dll) secara otomatis sesuai dengan kriteria yang ditentukan.

Dengan kata lain, Anda bisa memberikan tanda / mengubah warna cell secara otomatis ketika nilai dalam cell tersebut memenuhi kriteria yang telah Anda tentukan.

Sebetulnya yang mana sih Fitur Conditional Formatting ini ?

Ini loh, coba lihat Ribbon dan klik Tab Home, kemudian fokus pada Styles Group. Anda akan menemukan fitur Conditional Formatting seperti gambar berikut:

Apa itu conditional formatting excel ?

Conditional Formatting lebih sering digunakan untuk mengubah warna Range / Cell tertentu secara otomatis jika memenuhi kriteria yang ditentukan.

Misalnya, apabila nilai pada suatu Table Excel sama dengan atau lebih dari 20.000.000, maka secara otomatis warna Cell tersebut berubah menjadi hijau.

Sementara itu, Cell yang berisi nilai kurang dari 20.000.000 tidak berubah warna. Inilah salah satu kegunaan Conditional Formatting.

Bukan cuma untuk angka saja. Anda juga bisa menggunakan kriteria lainnya.

Contoh lainnya, jika Anda ingin cell yang berisi kata “Lulus” berubah menjadi Warna hijau dengan warna font berubah menjadi putih.

Sementara cell yang berisi kata “Gagal” berubah menjadi warna merah dengan warna font menjadi putih.

Untuk kebutuhan-kebutuhan seperti ini, silahkan pahami lebih dalam tahap demi tahap penggunaan Conditional Formatting berikut:

sponsored-unipin

Teknik Dasar Conditional Formatting

Biar semakin mudah dipahami, silahkan download atau gunakan File Excel Online berikut sebagai bahan latihan Anda:

Kemudian silahkan ikuti cara membuat, edit dan hapus conditional formatting berikut:

#1 Membuat Conditional Formatting

Silahkan buka Worksheet / Sheet Teknik Dasar pada Excel Online yang sudah Saya berikan di atas.

Terdapat sekelompok Angka Acak pada kolom A. Terlihat nilai terkecil adalah 16 dan terbesar adalah 100.

Dalam contoh ini, Saya ingin mengubah warna cell jika nilai pada Cell tersebut lebih besar dari 70. Silahkan ikuti tahap-tahap membuat conditional formatting berikut:

Cara membuat conditional formatting Microsoft Excel
  1. Pilih (blok) semua Cell yang ingin Anda ubah. Dalam contoh ini, berarti cell A2 sampai A11.
  2. Klik Tab Home kemudian klik Conditional Formatting pada Styles Group.
  3. Karena kita ingin memberikan tanda / mengubah warna cell, maka pilih Highlight Cells Rules.
  4. Untuk mengubah cell yang memiliki nilai lebih dari 50, maka pilih Greater Than…
  5. Pada Window Greater Than, masukkan value yaitu 70 pada kolom Format cells that are GREATER THAN: Anda bisa ketik manual angka 70. Atau jika kriteria terdapat pada Cell lainnya, silahkan klik Cell tersebut. Misalnya, kriteria pada contoh ini terdapat pada cell D2. Sehingga kolom Format cells that are GREATER THAN berisi =$D$2.
  6. Silahkan ubah warna yang Anda inginkan. Anda bisa pilih format yang sudah tersedia ataupun Custom Format. Saya memilih Light Red Fill with Dark Red Text.
  7. Klik Ok untuk melanjutkan. Berikut hasilnya:
Conditional Formatting dengan Excel

Secara otomatis, Excel mengubah warna cell yang memiliki nilai lebih besar dari 50 yaitu cell A3 dan A8.

Coba kita buktikan, jika kriteria diubah, apakah warna Cell juga ikut berubah ?

Karena kriteria formating pada contoh ini terdapat pada cell D2, maka Anda hanya perlu mengubah nilai pada Cell D2.

Coba ubah menjadi 50, hasilnya sudah pasti sama dengan gambar berikut:

Cara mengubah warna cell secara otomatis dengan syarat

Benar bukan ? Excel hanya mengubah warna cell yang memenuhi kriteria yaitu lebih dari 50.

Namun jika sebelumnya kriteria conditional formatting di ketik manual, silahkan ubah atau hapus dengan cara berikut:

#2 Edit Conditional Formatting

cara mengubah / edit conditional formatting excel
  1. Pilih (blok) semua cell yang ingin Anda hapus Conditional Formatting-nya. Dalam contoh ini Range A2:A11.
  2. Klik Tab Home dan klik Conditional Formatting.
  3. Klik Manage Rules.
  4. Pada Window Conditional Formatting Rules Manager, pilih Rule yang ingin Anda ubah kemudian klik Edit Rule.
  5. Muncul Window Edit Formatting Rule. Silahkan ubah sesuai yang Anda inginkan.
  6. Klik Ok pada Window Edit Formatting Rule dan Window Conditional Formatting Rules Manager.

Jika sudah, otomatis format cell akan berubah seperti yang telah Anda tentukan.

#3 Hapus Conditional Formatting

Ada 2 cara untuk menghapus Conditional Formatting, yaitu dengan cara Manage Rules dan Clear Rules. Gunakan cara yang tepat tergantung kebutuhan Anda.

Berikut contoh menghapus Conditional Formatting menggunakan Clear Rules:

Cara menghapus conditional formatting excel
  1. Pilih (blok) cell yang ingin Anda hapus Conditional Formatting nya kemudian klik Tab Home dan Conditional Formatting seperti cara sebelumnya.
  2. Klik Clear Rules.
  3. Pilih 1 dari 4 pilihan Clear Rules From. Dalam contoh ini, Saya memilih Clear Rules From Selected Cells.

Berikut Saya jelaskan Tujuan ke 4 pilihan clear rules from tersebut:

  • Selected Cells: Menghapus Conditional Formatting untuk cell yang di pilih (blok) saja.
  • Entire Sheet: Menghapus Conditional Formatting untuk Sheet yang sedang aktif (sheet yang Anda buka saat ini).
  • This Table: Menghapus Conditional Formatting untuk Table yang Anda pilih.
  • This PivotTable: Menghapus Conditional Formatting untuk PivotTable yang terpilih.

Cara menghapus Conditional Formatting yang kedua yaitu melalui Manage Rules seperti berikut:

Cara menghapus conditional formatting Excel melalui manage rules
  1. Silahkan buka Window Conditional Formatting Rules Manager seperti cara Edit Conditional Formatting sebelumnya.
  2. Pilih Conditional Formatting yang ingin Anda hapus.
  3. Klik Delete Rules.
  4. Klik Ok untuk menyimpan.

Penting! Sebetulnya masih banyak yang bisa Anda pelajari dari Manage Rules Conditional Formatting. Selengkapnya akan Saya bahas pada SUB-BAB Manage Rules (Lihat Pintasan Panduan).

Highlight Cell Rules

Pada bagian ini, Saya jelaskan selengkapnya tentang Conditional Formatting tipe Highlight Cell Rules. Karena tipe ini merupakan salah fitur yang paling favorit di Excel.

7 Syarat Pada Highlight Cells Rules

Sebelumnya kita hanya membuat Conditional Formatting dengan syarat nilai pada Cell lebih dari 50. Sementara, ada 7 syarat (kriteria / kondisi) untuk Highlight Cell sebagai berikut:

  • Greater Than: cell berisi nilai lebih dari (…)
  • Less Than: cell berisi nilai kurang dari (…)
  • Between Than: cell berisi nilai antara (…) dan (…)
  • Equals to: cell berisi nilai sama dengan (…)
  • Text That Contains: cell berisi teks (bisa huruf saja, gabungan huruf dan angka, serta gabungan huruf dan karater tertentu)
  • A Date Occurring: Tanggal tertentu (besok, hari ini, kemarin, 7 hari terakhir, minggu terakhir dll).
  • Duplicate Values: untuk cek data duplikat (ganda) pada sekelompok cell.

Contoh Semua Syarat Highlight Cells Rules

Sebagai bahan latihan, silahkan buka Sheet Highlight Cells pada Excel Online yang Saya berikan sebelumnya. Penampakan data sebelum menggunakan Conditional Formatting sebagai berikut:

Mengubah warna cell dengan highlight cells rules

Kemudian, silahkan buat conditional formatting untuk masing-masing Syarat dengan Values berikut:

SyaratValues
Greater Than50
Less Than50
Between20 dan 70
Equals to100
Text That ContainsLulus
A Date OccurringLast Week
Duplicated ValuesUnique
Tabel: Highlight Cells Rules

Jika sudah, hasilnya kurang lebih seperti gambar berikut:

Cara mengubah warna cell secara otomatis dengan highlight cells rules

Mudah bukan ? Semua conditional formatting – highlight cells rules sesuai masing-masing syarat & value.

Note: Untuk Syarat Duplicated Values, pada Contoh ini Saya menggunakan Syarat dengan Value Unique. Sehingga hanya cell yang berisi nilai unik (tidak duplikat) yang akan berubah warna.

Selengkapnya tentang duplicate values bisa Anda pelajari pada SUB-BAB Duplicated Values (Lihat Pintasan Panduan di akhir halaman ini).

Top/Bottom Rules

Conditional Formatting tipe Top / Bottom Rules juga cukup sering digunakan. Tipe ini lebih sering digunakan untuk menentukan ranking tertinggi (misal 3 besar), mengetahui cell yang memiliki nilai di bawah rata-rata dan lain sebagainya.

6 Syarat Pada Top/Bottom Rules

  • Top 10 Item: Cell yang berisi nilai 10 tertinggi.
  • Top 10%: Cell yang berisi nilai 10% tertinggi.
  • Bottom 10 Item: Cell yang berisi nilai 10 terendah.
  • Bottom 10%: Cell yang berisi nilai 10% terendah.
  • Above Average: Cell yang berisi nilai di atas rata-rata
  • Below Average: Cell yang berisi nilai di bawah rata-rata.

Note: Anda bisa mengubah Top 10 Item menjadi Top 5 atau Top 3 sesuai keinginan. Begitu juga dengan Top 10%, Bottom 10 Item dan Bottom 10%

Contoh Semua Top/Bottom Rules

Sebagai contoh, silahkan buka sheet Top Bottom. Penampakan awal data tanpa Conditional Formatting sebagai berikut:

Cara mengubah warna cell secara otomatis dengan top / bottom rules

Silahkan buat conditional formatting tipe Top / Bottom dengan Syarat dan Value berikut:

SyaratValues
Top 10 Item4
Top 10%20%
Bottom 10 Item5
Bottom 10%30%
Above AverageNilai Rata-rata sesuai data
Below AverageNilai Rata-rata sesuai data
Tabel: Top/Bottom Rules

Pastikan hasil Anda sama dengan gambar berkut:

Cara mengubah warna cell secara otomatis dengan top / bottom rules

Note: Value pada masing-masing Syarat Top/Bottom tidak bisa menggunakan angka yang sudah ada pada cell lain. Jadi harus Anda ketik secara manual ketika membuat Conditional Formatting.

Sedikit Saya jelaskan kenapa hanya cell pada gambar tersebut yang berubah untuk masing-masing Syarat sebagai berikut:

Top 10 Item dan Top 10%

Top 10 Item menggunakan value 4. Artinya, contoh ini memberikan tanda pada 4 Cell dengan nilai tertinggi saja.

Sedangkan untuk Top 10% menggunakan value 20%. Sedangkan banyak data (dari Cell B3 sampai B12) adalah data 10. Maka 20% dari 10 adalah 2.

Dengan kata lain Top 10% dengan Value 20% akan memberikan tanda pada 2 Cell dengan nilai tertinggi.

Bottom 10 Item dan Bottom 10%

Maksudnya sama dengan Top 10 Item dan Top 10%. Bedanya, Bottom 10 Item dengan Value 5 akan memberikan tanda untuk 5 cell dengan nilai terendah. Sedangkan Bottom 10% dengan value 30% akan memberikan tanda pada 3 Cell dengan nilai terendah.

Above Average dan Below Average

Kolom Above Average dan Below Average menggunakan data yang sama. Sehingga nilai rata-rata kedua data ini sama yaitu 49.7.

Above Average akan memberikan tanda pada Cell yang memiliki nilai lebih tinggi dari rata-rata (49.7) yaitu Cell E4, E5, E7, E9, dan E11.

Sementara cell yang memiliki nilai lebih rendah dari rata-rata (Below Average) adalah cell E3, E6, E8, E10, dan E12

Bonus: Conditional Formatting Dengan Rumus Excel

Anda juga bisa menggunakan Rumus Excel untuk menentukan Conditional Formatting. Tapiii… ada ketentuan khusus.

Anda hanya bisa menggunakan fungsi / rumus yang menghasilkan TRUE atau FALSE seperti Fungsi ISODD, ISNA, ISBLANK, ISERROR, AND, OR, dan lain sebagainya.

Karena jika rumus menghasilkan TRUE, maka Excel akan mengubah format cell tersebut. Jika rumus menghasilkan FALSE, maka Excel tidak mengubah format cell tersebut

Supaya tidak bingung, langsung saja lihat contoh berikut:

Contoh 1: Conditional Formatting Excel dengan Fungsi

Silahkan buka Sheet Rumus pada Excel Online yang Saya berikan sebelumnya. Terdapat sekumpulan angka acak dan cell kosong pada Range A1:E5. Penampakan data awal seperti gambar berikut:

Cara membuat conditional formatting dengan rumus / fungsi Excel

Pada contoh ini, Saya akan memberikan tanda / mengubah warna cell yang berisi angka ganjil saja.

Sementara itu, rumus Excel yang bisa menentukan apakah cell berisi angka ganjil adalah Fungsi ISODD. Silahkan ikuti tahap-tahap berikut:

Cara membuat conditional formatting dengan Rumus / Fungsi Excel
  1. Pilih (blok) range data yaitu A1:E5.
  2. Klik Tab Home dan klik Conditional Formatting.
  3. Klik New Rule….
  4. Pada Window New Rule, pilih Use a Formula to Determine which cells to format.
  5. Pada Form Format values where this formula is true, masukkan fungsi ISODD seperti berikut: =ISODD(.
  6. Ketik secara manual nama Cell paling sudut kiri atas dari data yang Anda pilih (blok). Dalam contoh ini, Range data adalah A1:E5, maka Cell paling sudut kiri atas adalah Cell A1. Di ketik saja, jangan di klik.
  7. Klik Format. Kemudian muncul Window Format Cells. Silahkan pilih Warna Cell yang Anda inginkan. Contoh, Saya menggunakan warna biru.
  8. Klik Ok pada Window Format Cells, dan Klik Ok pada Window New Formatting Rules. Berikut hasilnya:

Note: Jika Range data Anda C2:H5, maka cell paling sudut kiri atas adalah cell C2.

Contoh conditional formatting dengan rumus / fungsi Excel

Kenapa harus ketik nama cell paling sudut kiri atas ?

Ini Alasannya… (Alur Pemrosesan)

Coba perhatikan alur pemrosesan rumus pada Conditional Formatting Excel berikut:

Pertama, kita memilih sebuah range (data yang di blok A1:E5).

Kedua, kita memasukkan rumus ISODD melalui Conditional Formatting untuk range data tersebut (bukan per Cell).

Ketiga, Excel akan menerapkan rumus ISODD yang dimasukkan pada conditional formatting ke cell paling sudut kiri atas (sesuai range data yang dipilih). Sehingga Excel menerapkan rumus =ISODD(A1) ke cell A1.

Keempat, jika cell A1 berisi angka ganjil, maka Excel akan mengubah warna cell tersebut. Jika tidak berisi angka ganjil, maka Excel mengabaikan Cell tersebut.

Kelima, Excel menyalin rumus =ISODD(A1) ke cell A2, sehingga rumus berubah menjadi =ISODD(A2).

Kenapa demikian ? Konsepnya sama ketika Anda lakukan AutoFill atau copy-paste rumus ke cell lain.

Karena pada Rumus ISODD kita menggunakan referensi biasa yaitu A1, maka secara otomatis rumus berubah menjadi =ISODD(A2) untuk cell A2.

Penting! Jika Anda belum tahu apa saja Cell References pada Excel, silahkan kunjungi Panduan Cell References Excel M Jurnal

Keenam, Excel akan menentukan kembali apakah cell A2 berisi angka ganjil atau tidak, kemudian mengubah warna cell jika berisi angka ganjil.

Ketujuh, Excel akan mengulangi tahap kelima dan keenam sampai Cell terakhir (sudut kanan paling bawah) yaitu Cell E5

Kurang lebih seperti itu proses Rumus pada Conditional Formatting Excel. Biar pemahaman Anda semakin klop, Saya berikan contoh yang berbeda dan lebih kompleks lagi.

Contoh 2: Conditional Formatting Excel dengan Rumus

Contoh ini sedikit lebih kompleks dari sebelumnya. Namun Saya yakin Anda bisa melakukannya.

Silahkan fokus ke Sheet Rumus dan Range G1:J7. Terdapat data Total Penjualan berdasarkan Pulau dan Kuartal. Penampakan data awal seperti gambar berikut:

Contoh Conditional Formatting dengan Rumus / Fungsi Excel

Tujuan Saya adalah ingin mengubah warna penjualan dari Pulau Sumatera (termasuk Nama Salesman, Penjualan dan Kuartal)

Maksud Saya begini.. Fokus utamanya adalah kolom Pulau. Saya akan mengubah warna cell yang berisi kata Sumatera SEKALIGUS mengubah warna cell yang satu baris dengan cell yang berisi kata Sumatera.

Misalnya, Cell I3 berisi kata Sumatera, maka warna cell G3 (Kolom Salesman), cell H3 (Kolom Penjualan) dan Cell J3 (Kolom Kuartal) juga ikut berubah. Caranya, ikuti tahap – tahap berikut:

Cara membuat conditional formatting dengan rumus / fungsi excel
  1. Pilih (blok) range data yaitu Range G2:J2. Note: Judul tabel tidak termasuk data.
  2. Buka Window New Rule. Note: Ulangi tahap 2 sampai 4 pada contoh 1 di atas.
  3. Pada Form Format values where this formula is true, masukkan rumus berikut: =$I2="Sumatera" (Note: Ketik manual).
  4. Tentukan format cell seperti sebelumnya (Tahap 7)
  5. Klik Ok pada Window Format Cells, dan Klik Ok pada Window New Formatting Rules. Berikut hasilnya:
Cara membuat conditional formatting dengan rumus / fungsi excel

Gimana Alur Pemrosesannya ?

Pertama-tama, Saya jelaskan terlebih dahulu makna rumus tersebut:

Tulis Rumus:
=$I2=“Sumatera”

Artinya:
Apakah Cell $I2 berisi Sumatera

Maksudnya begini…

Jika Cell I2 berisi Sumatera, maka Excel memberikan hasil TRUE. Jika tidak, maka Excel memberikan hasil FALSE. Kemudian apa ?

Coba Anda ingat kembali bagian Conditional Formatting Excel Menggunakan Rumus di atas. Bahwa pada Conditional Formatting, Anda hanya bisa menggunakan rumus / fungsi yang menghasilkan TURE / FALSE.

Sampai disini pasti udah paham dong yaa..

Next, tentang alur pemrosesan rumus. Secara garis besar, alurnya sama saja dengan contoh 1. Perbedaannya hanya pada Cell Referensi.

Note: Pada contoh ini Saya menggunakan Absolute References (lambang dollar $) pada rumus, yaitu $I2.

Jadi, Excel akan menyalin rumus =$I2="Sumatera" ke setiap cell seperti berikut:

G2 =$I2="Sumatera", Cell G3: =$I3="Sumatera" dan seterusnya sampai Cell G7: =$I7="Sumatera".

Sejauh ini perubahan rumus terdapat pada nomor baris yaitu $I2, $I3, $I4 dst…

Perbedaan Contoh 1 dan Contoh 2 adalah Ketika Excel menyalin rumus ke kolom yang berbeda…

Rumus pada Kolom H adalah…

H2: =$I2="Sumatera", H3: =$I3="Sumatera" dan seterusnya sampai H7: =$I7="Sumatera".

Loh kok bisa sama dengan rumus pada kolom G ? Bukannya rumus pada kolom seharusnya H2: =$J2="Sumatera", H3: =$J3="Sumatera" dan seterusnya.

Jika Anda berpikir seperti itu, tentu salah. Kenapa ? Karena ada lambang dollar ($) itu loh…

Pada bagian alasan kenapa ketik manual nama cell paling sudut kiri atas untuk contoh 1, Saya sudah jelaskan pentingnya memahami Cell Reference Excel.

Jika Anda sudah paham tentang cell referensi, tentu Anda paham maksud Saya tentang contoh ke dua untuk Conditional Formatting Excel dengan Rumus ini.

Namun, jika Anda masih bingung dan sudah mentok, silahkan coret-coret kolom komentar.

Jika sudah paham isi panduan ini, silahkan tambah ilmu Anda. Gunakan Pintasan Panduan Conditional Formatting Excel berikut.

Pintasan Panduan Conditional Formatting Excel

Bab sebelumnya: Sort & Filter.

  1. Conditional Formatting: (Anda Disini).
  2. Data Bars: Membuat Data Bar pada Cell untuk memberikan gambaran tentang data Anda dalam bentuk Bar Chart Mini.
  3. Color Scales: Membuat Color Scales untuk memberikan gambaran tentang data Anda dalam bentuk warna visual.
  4. Icon Sets: Membuat Icon Sets untuk memberikan gambaran tentang data Anda dalam bentuk Icon.
  5. Find Duplicates: Mencari Data yang Sama dan Memberikan Tanda (Menyorotinya).
  6. Compare Data: Langkah-langkah membandingkan data dari dua atau lebih tabel berbeda.

Bab berikutnya: Format Tabel.

3 tanggapan pada “Panduan Conditional Formatting Microsoft Excel Lengkap”

  1. Tolong saya dibuatkan tutorial vlookup yang datanya berupa gambar (foto) atau suara (suara).

    Misalnya saya akan membuat KTP, berarti ada data foto yang harus mengisi secara otomatis cell tertentu.

  2. APA RUMUS AGAR NILAI CEL YANG MEMENUHI KRITERIA SAJA YANG BERUBAH DAN YANG TIDAK MEMENUHI KRITERIA NILAINYA TETAP.
    CONTOH: CEL YANG BERNILAI >=1.96 MAKA NILAINYA BERUBAH JADI 1.95, DAN CEL YANG BERNILAI <=1.95 MAKA NILAINYA TETAP

  3. kak kalo untuk top 10% tp bukan banyaknya data yg dihitung melainkan jumlah dr nilai datanya bagaimana ya?

Komentar Anda:

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *