Menandai Data yang Muncul 2 kali, 3 Kali, atau lebih

Bagaimana cara menemukan dan menandai data yang muncul dua kali, tiga kali, empat kali atau lebih?  Hal ini dapat dengan mudah dilakukan menggunakan fitur conditional formating.  Tehnik ini juga bisa diterapkan baik menggunakan excel versi baru maupun versi jadul.

Video >>

Pada catatan sebelumnya tentang cara mencari data duplikat di excel, sudah disampaikan bagaimana menggunakan fitur conditional formating untuk menemukan data ganda, yaitu data yang muncul lebih dari satu kali. Namun dengan tehnik tersebut  pencarian data ganda tidak membedakan seberapa banyak kemunculan data duplikat. Data yang muncul 2 kali, 3 kali, 4 kali atau lebih teridentifikasi sebagai data duplikat dan divisualisasikan dengan warna yang sama.

Bagaimana jika kita ingin membedakan warna sel untuk data yang muncul 2 kali, 3 kali, 4 kali dan seterusnya. Caranya cukup mudah, kita hanya tinggal memodifikasi rumus pada conditional formating.

Adapun langkah-langkahnya sebagai berikut:

 Excel Versi Baru (2007 atau lebih baru)

Note: Ilustrasi disini menggunakan excel versi 2016

Seleksi tabel data dimana akan dicari dan ditandai data ganda yang muncul 2 kali atau lebih, misal range A1:H20

Dari tab Home ➜ Klik Conditional Formating ➜ Manage Rules ➜ sehingga masuk ke jendela Conditional Formating Rules Manager ➜

1. Setting rule conditional formating untuk data yang muncul dua kali

• Klik New Rule ➜ muncul kotak dialog New Formating Rule
• Pada bagian Select a Rule Type, pilih Use Formula to determine which cell format
• Pada bagian Edit the Rule Description, ketikan rumus berikut: =COUNTIF($A$1:$H$20,A1)=2
• Rumus tersebut untuk mengecek data yang muncul 2 kali.
• Klik tombol format ➜ masuk ke tab Fill ➜ filih warna fill yang diinginkan (misal: kuning) ➜ klik Ok ➜ kembali ke jendela Conditional Formating Rules Manager

2. Setting conditional formating untuk data yang muncul 3 kali

• Klik New Rule ➜ muncul kotak dialog New Formating Rule
• Pada bagian Select a Rule Type, pilih Use Formula to determine which cell format
• Pada bagian Edit the Rule Description, kektikan rumus berikut: =COUNTIF($A$1:$H$20,A1)=3
• Rumus tersebut untuk mengecek data yang muncul 3 kali.
• Klik tombol format ➜ masuk ke tab Fill ➜ filih warna fill yang diinginkan (misal: biru) ➜ klik Ok ➜ kembali ke jendela Conditional Formating Rules Manager

3. Setting conditional formating untuk data yang muncul 4 kali

• Klik New Rule ➜ muncul kotak dialog New Formating Rule
• Pada bagian Select a Rule Type, pilih Use Formula to determine which cell format
• Pada bagian Edit the Rule Description, kektikan rumus berikut: =COUNTIF($A$1:$H$20,A1)=4
• Rumus tersebut untuk mengecek data yang muncul 4 kali.
• Klik tombol format ➜ masuk ke tab Fill ➜ filih warna fill yang diinginkan (misal: hijau) ➜ klik Ok ➜ kembali ke jendela Conditional Formating Rules Manager
• Setelah menentukan rule conditional formating untuk data dengan kemunculan 2 kali, 3 kali, dan 4 kali, maka tampilan jendela Conditional Formating Rules Manager mungkin akan tampak seperti berikut:

Selanjutnya klik Ok untuk menerapkan Conditional formating pada range yang sudah kita A1:H20, yaitu yang yang kita seleksi sebelumnya. Pada akhirnya data - data yang muncul  2 x, 3x dan 4x akan ditandai dengan warna yang berbeda seperti gambar berikut:

• Warna kuning, merupakan data yang muncul 2 kali
• Warna biru, merupakan data yang mucul 3 kali
• Warna hijau, merupakan data yang muncul 4 kali.

Contoh diatas menggambarkan cara menandai data yang muncul 2 kali, 3 kali dan 4 kali.  Pada excel versi baru, anda dapat menambahkan rule lagi untuk menandai data yang muncul 5 kali, 6 kali dan seterusnya.

Excel Versi Lama (2003)

Di excel 2003, kita juga bisa menandai data yang muncul 2 kali (kondisi 1), 3 kali (kondisi 2) dan 4 kali (kondisi 3). Sayangnya pada versi ini, conditional formating dibatasi 3 format, sehingga kita tidak bisa membuat rule untuk kondisi ke-4.

Adapun langkah nya sebagai berikut:

• Seleksi tabel data dimana akan dicari dan ditandai data  yang muncul 2 kali (kondisi ke-1), 3 kali (kondisi ke-2) dan 4 kali (kondisi ke-3), misal range A1:H20.
• Klik menu Format ➜ Conditional Formating ➜ muncul jendela Conditional Formating
• Pada Condition 1, pilih Formula Is, dan ketikan rumus berikut: COUNTIF($A$1:$H$20,A1)=2
• Klik Format ➜ Pattern ➜ Tentukan warna yang anda inginkan ➜ klik OK
• Klik Add untuk untuk menambahkan kondisi 2 dan ulangi langkah di atas, dan add lagi untuk menambahkan kondisi 3 dan ulangi langkah tadi, sehingga akhirnya akan diperoleh 3 kondisi dengan pengaturan conditional formating seperti gambah di bawah ini:

Klik Ok, sehingga diperoleh hasil seperti di bawah ini:

Pada akhirnya kita bisa melihat bahwa data yang muncul 2 kali, 3 kali dan 4 kali dikenali dengan warna yang berbeda.

Sampai di sini pembahasan mengenai cara mencai dan menandai data yang muncul 2 kali atau lebih di excel, baik excel versi baru maupun versi lama. Semoga bermanfaat.

Catatan Pelajaran Excel Terkait:

• Menemukan Data Duplikat dengan Conditional Formating
• Merubah Warna Sel Berdasarkan Value dan Formula
• Rumus Excel Menghitung Nilai Unik.