3 Cara Menandai Duplikat di Excel dengan Conditional Formatting

Topik kita kali ini mengurai cara menandai duplikat di Excel dengan conditional formatting.

Duplikasi adalah salah satu hal yang tak terhindarkan saat bekerja dengan data dan pencegahan perlu dilakukan. Mungkin untuk menghindari pemberian bonus ganda. Bisa juga untuk menghindari membebankan kewajiban berulang terhadap seseorang.

Dengan conditional formatting kita menentukan apa yang menjadi kriteria sekumpulan data dianggap duplikat dan menentukan format untuk menandainya.

Setiap demo di artikel  ini menggunakan data pada file berikut.

File: 00-conditional-formatting-duplicate-data.xlsx.

Cara Menandai Duplikat di Excel dengan Preset

Preset adalah format conditional formatting siap pakai.

(Demo 01) Ikuti langkah-langkah di bawah ini sebagai cara menandai duplikat di Excel.

1. Pilih cell-cell di mana duplikasi data akan diperiksa. Kami menggunakan B3:F14 sesuai data contoh.
2. Klik Conditional Formatting → Highlight Cell Rules → Duplicate Values.

   

3. Pilih Duplicate pada Format cells that contain.
4. Pada Values with, pilih format untuk menandai data duplikat.
5. Klik OK.

Pada Gambar 02, Live Preview memberikan contoh seperti apa format sebelum conditional formatting diterapkan.

Cara Menandai Duplikat di Excel dengan Format Custom

Custom berarti terserah kita.

Excel memberikan kita kebebasan membuat aturan untuk menandai data duplikat, alih-alih menggunakan format preset atau siap pakai.

(Demo 02) Berikut cara menandai duplikat di Excel dengan conditional formatting custom.

1. Pilih range data di mana dicurigai ada duplikasi data. Sesuai data contoh, kami menggunakan B3:F14.
2. Klik Conditional Formatting → New Rule.
3. Pada Select a Rule Type, pilih aturan Format only unique or duplicate values.
4. Pada Format all, pastikan duplicate terpilih.
5. Untuk menentukan format penanda data duplikat, klik Format. Tombol ini akan menampilkan kotak dialog Format Cells.
6. Klik OK.

Hasil penerapan aturan dan custom format yang kita telah kita lakukan bisa dilihat di bawah ini.

Custom conditional formatting punya keunggulan, yaitu kemampuan menentukan sendiri format yang kita inginkan untuk data duplikat.

Dengan preset, format yang disiapkan adalah format-format siap pakai.

Catatan: Saat kita menggunakan preset dan menentukan format, Excel menyediakan pilihan Custom Format.

Dengan memilih Custom Format, alih-alih format seperti Light Red Fill, kita menggunakan custom conditional formatting.

Cara Menandai Duplikat di Excel dengan Rumus

Kita sudah merasakan fleksibilitas dengan custom conditional formatting.

Rumus di sisi lain, menawarkan fleksibilitas lebih tinggi.

Walau pun menggunakan rumus conditional formatting hanya terbatas pada kemampuan kita menggunakan rumus itu sendiri, kita tetap harus mengetahui dan menyusun rumus yang tepat untuk menemukan data duplikat di Excel.

Fungsi COUNTIF Excel

Salah satu cara menemukan duplikasi data adalah dengan fungsi COUNTIF.

Sintaks

COUNTIF(Parameter Range; Parameter Nilai)

Contoh penggunaannya: =COUNTIF(A2:A11; "Kelapa").

Rumus di atas dibaca mencari teks Kelapa di range A2:A11.

Bila range tersebut tidak memiliki teks yang dimaksud, COUNTIF akan mengembalikan nilai 0.

Bila ada, COUNTIF akan mengembalikan nilai sejumlah teks Kelapa yang berada di range A2:A11.

Duplikasi diartikan sebagai suatu nilai ganda dalam suatu range. Dalam konteks artikel ini, suatu nilai dalam suatu range dianggap mempunyai duplikat bila COUNTIF mengembalikan nilai lebih besar dari 1.

Cara Menandai Duplikat di Excel dengan Rumus COUNTIF

(Demo 03) Berikut langkah-langkah menemukan data duplikat dengan rumus COUNTIF Excel di conditional formatting:

1. Pilih cell atau range yang akan diformat, yaitu B3:F14.
2. Klik Conditional Formatting → New Rule.
3. Pada Select a Rule Type, pilih Use a formula to determine which cells to format.
4. Masukkan rumus yang diinginkan, yaitu =COUNTIF($B$20:$F$31; B20)>1.
5. Klik tombol Format untuk menentukan format bila ada nilai yang memenuhi kriteria.
6. Klik OK.

Hasilnya akan tampak seperti pada gambar di bawah ini.

Tips: Dengan mengubah range dan nilai pembanding, kita bisa menentukan berapa nilai yang dianggap sebagai duplikasi pada suatu range tertentu.

Pada demo di atas kami menggunakan seluruh range data. Tapi bila teman-teman hanya ingin melihat duplikasi pada kolom atau baris tertentu, ubah saja range pada parameter COUNTIF.

Bila duplikasi diartikan sebagai data ganda yang terjadi lebih dari 2 atau 3 kali, kita hanya perlu mengubah nilai 1 menjadi 2 atau 3, atau sebesar yang dibutuhkan.

Rangkuman

Demikian bahasan kami kali ini mengenai bagaimana cara menandai duplikat di Excel dengan conditional formatting.

Sederhananya, data duplikat adalah data yang muncul lebih dari satu kali. Berdasarkan definisi ini, kita bisa menggunakan format siap pakai atau custom conditional formatting.

Bila ada kriteria lain yang tidak bisa dilakukan (atau bisa tapi tidak efektif) oleh format siap pakai, penggunaan rumus conditional formatting mungkin bisa jadi jawaban.