7++ Trik untuk Conditional Formatting di Excel

Conditional formatting membantu kita untuk menyorot informasi tertentu pada lembar kerja dengan lebih cepat. Dengan menguasai trik-trik dalam conditional formatting akan membuat pekerjaan yang menggunakan Microsoft Excel lebih mudah dan hemat waktu.

Bila Anda belum pernah menggunakannya, coba trik di artikel ini yang membahas cara menyorot data tertentu dan cara membuat Gantt Chart dengan conditional formatting.

Dasar-dasar penggunaan conditional formatting dapat dibaca di artikel berikut:

 

Cara Menggunakan Formula di Conditional Formatting

Catatan : Cara ini akan digunakan untuk trik no 1-3.

  • Excel 2007, 2010, 2013:
    1. Pada tab Home, grup Styles, klik Conditional Formatting, dan pilih New Rule.
    2. Pada Select a Rule Type, klik Use a formula to determine which cells to format.
    3. Pada bagian Format values where this formula is true, masukkan formula yang diinginkan.
    4. Klik tombol Format, pada tab Fill pilih warna yang diinginkan dan klik OK.
    5. Klik OK 2 kali untuk menutup kotak dialog.
    6. Conditional Formatting Excel 2007
  • Excel 2003:
    1. Pada menu Menu, Format, pilih Conditional Formatting.
    2. Pada Condition 1, pilih Formula is.
    3. Pada kotak di sampingnya, masukkan formula yang diinginkan.
    4. Klik tombol Format, pada tab Patterns pilih warna yang diinginkan dan klik OK.
    5. Klik OK untuk menutup kotak dialog.
    6. Conditional Formatting Excel 2003

 

Trik 1: Menyorot Baris dan Kolom Tertentu

Misalnya kita ingin menyorot baris genap atau kolom ganjil saja dengan memberi warna seperti contoh berikut.

Warna pada baris genap
Warna pada baris genap

Warna pada kolom ganjil
Warna pada kolom ganjil

Langkah-Langkah:

  1. Pilih range (kumpulan sel) yang akan diberi format.
  2. Buat conditional formatting dengan formula berikut:
    Lihat cara menggunakan formula.
    • Memberi warna pada setiap baris genap:
      =MOD(ROW(),2)=0 atau =ROW()=EVEN(ROW())
    • Memberi warna pada setiap baris ganjil:
      =MOD(ROW(),2)=1 atau =ROW()=ODD(ROW())
    • Memberi warna pada setiap kolom genap:
      =MOD(COLUMN(),2)=0 atau =COLUMN()=EVEN(COLUMN())
    • Memberi warna pada setiap kolom ganjil:
      =MOD(COLUMN(),2)=1 atau =COLUMN()=ODD(COLUMN())
    • Bagaimana bila ingin memberi warna pada setiap baris ketiga?
      Gunakan formula berikut:
      =MOD(ROW(),3)=0

      Hasilnya akan seperti ini:
      Cell Shading pada setiap baris ke-tiga
    • Memberi warna pada baris dan kolom
      Gunakan dua formula berikut: =MOD(COLUMN(),2)=0 dan =MOD(ROW(),3)=0
      (Perlu memasukkan rule dua kali di Excel 2007, 2010, 2013 atau dua conditon di Excel 2003).

      Hasilnya akan seperti ini:
      Cell Shading pada baris dan kolom

 

Trik 2: Membuat Gantt Chart dengan Conditional Formatting

Membuat Gantt Chart dengan conditional formatting akan sangat efisien karena bila ada perubahan nilai di kolom Start dan End (lihat gambar), kita tidak perlu menghapus atau mengisi warna pada sel lagi. Warna pada kotak di samping akan otomatis berubah bila ada perubahan nilai.
Gantt Chart

Langkah-Langkah:

  1. Buat tabel dengan data seperti pada gambar (aktivitas, start, end, dan angka 1 s/d 10).
  2. Pilih range dari E2 sampai dengan N6.
  3. Buat conditional formatting dengan formula berikut: =AND(E$1>=$C2, E$1<=$D2). Lihat cara menggunakan formula.

 

Trik 3: Menyorot Data yang Error

Trik ini sangat membantu untuk menemukan error dalam lembar kerja yang memiliki banyak data.
Data Error

Langkah-Langkah:

  1. Klik sel C12 (lihat gambar).
  2. Buat conditional formatting dengan formula berikut: =ISERROR(C12). Lihat cara menggunakan formula.
  3. Copy C12 dan paste ke baris C13 sampai dengan C17.

 

Trik 4: Menyorot Data yang Sama Dengan Tanggal Hari ini

Misalkan kita ingin menyorot data pembayaran yang jatuh tempo pada hari ini. Untuk itu, setiap data yang jatuh tempo akan disorot (diberi warna pada sel), sehingga mempermudah kita melihatnya.
Menyorot Data Tanggal

Langkah-Langkah:

  1. Pilih range data.
  2. Bila menggunakan:
    • Excel 2007, 2010, 2013:
      1. Pada tab Home, grup Styles, klik Conditional Formatting, dan pilih New Rule.
      2. Pada Select a Rule Type, klik Format only cells that contain.
      3. Pada bagian Format only cells with, pilih Cell Value, equal to dan masukkan formula =TODAY() pada kotak di sampingnya.
      4. Beri format yang diinginkan.
        Conditional formatting perbandingan tanggal
    • Excel 2003:
      1. Pada menu Menu, Format, pilih Conditional Formatting.
      2. Pada Condition 1, pilih Cell Value is dan equal to.
      3. Pada kotak di sampingnya, masukkan formula =TODAY().
      4. Beri format yang diinginkan.

 

Trik 5: Menyorot Nilai Duplikat atau Nilai Unik (Excel 2007, 2010, 2013)

Conditional formatting Data Duplikat

Langkah-Langkah:

  1. Pilih range data.
  2. Pada tab Home, grup Styles, klik Conditional Formatting, dan pilih New Rule.
  3. Pada Select a Rule Type, klik Format only unique or duplicate values.
  4. Di bagian Format all, pilih:
    • Duplicate, bila ingin menyorot data duplikat.
    • Unique, bila ingin menyorot data yang unik (tidak ada nilai yang sama dengannya dalam tabel).
  5. Beri format yang diinginkan.

 

Trik 6: Menyorot Nilai Di Bawah atau Di Atas Rata-Rata (Excel 2007, 2010, 2013)

Conditional formatting data di atas/bawah rata-rata

Langkah-Langkah:

  1. Pilih range data.
  2. Pada tab Home, grup Styles, klik Conditional Formatting, dan pilih New Rule.
  3. Pada Select a Rule Type, klik Format only values that are above or below average.
  4. Di bagian Format values that are, pilih below (bawah rata-rata) atau above (atas rata-rata).
  5. Beri format yang diinginkan.

 

Trik 7: Menyorot Nilai Top atau Bottom (Excel 2007, 2010, 2013)

Misalnya kita ingin menyorot data yang memiliki nilai ranking 5 atas atau 5 bawah.

Conditional formatting Top Bottom Data

Langkah-Langkah:

  1. Pilih range data.
  2. Pada tab Home, grup Styles, klik Conditional Formatting, dan pilih New Rule.
  3. Pada Select a Rule Type, klik Format only top or bottom ranked values.
  4. Di bagian Format values that rank in the, pilih Top atau Bottom.
  5. Isi angka di sampingnya. Bila ingin menggunakan persen, centang kotak % of the selected range.
  6. Beri format yang diinginkan.

Share this

Related Posts

Previous
Next Post »

9 comments

Tulis Komentar
Anonymous
29 July, 2009 16:52 delete

great article...

Reply
Anonymous
09 December, 2010 09:25 delete

Very usefull for me. Thanks....

Reply
Anonymous
28 September, 2011 14:29 delete

terus buat artikel menarik lainnya, bro, trim

Reply
Anonymous
31 January, 2014 21:57 delete

Makasihhh!! Ngebantu banget gan!

Reply
05 January, 2016 09:41 delete

kalo caranya nandain kolom yang ga sama nilainya sama kolom sebelahnya gimana ? aku bisa bikin kalo satu sel aja, tp giliran di blok sampe sel paling bawah malah ga bisa keluar hasilnya, katanya change reference cell to single cell. masa di formatting satu satu. tolong bantuannya mkasih ^^

Reply
06 January, 2016 16:21 delete

@cynthia mustika,

Saya tidak menemukan masalah saat menandai sel yang isinya berbeda untuk lebih dari satu kolom. Apakah Anda mengikuti cara seperti pada Trik 5: Menyorot Nilai Duplikat atau Nilai Unik di atas?

Reply
Yeda Dresty
08 March, 2016 15:03 delete

Sore artikelnya ngebantu banget,
mau tanya jika ingin menandai sel tetapi menggunakan kumulasi bagaimana? misal 1,2,3,4 yang nilainya kurang dari 8, maka yang tertandai sel nilai 4 karena 1+2=3, 1+2+3=6, 1+2+3+4=10 jadi yang tertandai sel bernilai 4. Terimakasih :)

Reply
08 March, 2016 17:59 delete

Halo Yeda Dresty,

Misal data ada di kolom A.
1. Klik sel pertama (A1) dan gunakan formula berikut untuk Conditional Formatting:
=SUM($A$1:A1)>=10

2. Salin formula ke sel berikutnya dengan Format Painter.

Reply