7++ Trik untuk Conditional Formatting di Excel 2003/2007

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:

• Menggunakan Conditional Formatting - Excel 2003
• Membuat Conditional Formatting - Excel 2007

Cara Menggunakan Formula di Conditional Formatting

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

• Excel 2007:
  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.
  
• 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.
  

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 kolom ganjil

• Pilih range (kumpulan sel) yang akan diberi format.
• 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:
    
  • 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 atau dua conditon di Excel 2003).
    
    Hasilnya akan seperti ini:
    

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.

• Buat tabel dengan data seperti pada gambar (aktivitas, start, end, dan angka 1 s/d 10).
• Pilih range dari E2 sampai dengan N6.
• 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.

• Klik sel C12 (lihat gambar).
• Buat conditional formatting dengan formula berikut: =ISERROR(C12). Lihat cara menggunakan formula.
• 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.

• Pilih range data.
• Bila menggunakan:
  • Excel 2007:
    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.
       
  • 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)

• Pilih range data.
• Pada tab Home, grup Styles, klik Conditional Formatting, dan pilih New Rule.
• Pada Select a Rule Type, klik Format only unique or duplicate values.
• 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).
• Beri format yang diinginkan.

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

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

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

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

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