Cara Mencari Data Duplikat di Excel dengan Rumus dan Fitur Bawaan

anaksmanda.com - Data ganda sering muncul tanpa disadari, misalnya nomor rekening yang terinput dua kali atau nama peserta yang tercatat berulang. Kalau dibiarkan, laporan bisa salah hitung dan pembayaran bisa dobel. Untungnya, mencari data duplikat di Excel bisa dilakukan dengan beberapa cara, mulai dari satu rumus sederhana sampai fitur bawaan yang tinggal klik.

Versi lama artikel ini memakai rumus MAX(COUNTIF(...)) yang ternyata keliru, karena hasilnya menandai semua baris sebagai duplikat begitu ada satu saja data ganda. Di artikel ini rumus tersebut sudah diganti dengan versi yang benar, ditambah beberapa metode lain yang bisa kalian pilih sesuai kebutuhan.

Mencari data duplikat atau double di Microsoft Excel.

Ringkasan Metode

Contoh Data yang Dipakai

Sebagai contoh, kita memakai tabel berisi kolom No, Nama, No Rekening, dan Status. Data berada di baris 5 sampai 19, nomor rekening ada di kolom E, dan hasil pengecekan akan ditulis di kolom F (Status).

Contoh tabel: nomor rekening di kolom E dan status di kolom F.

Catatan penting soal pemisah argumen: Excel dengan pengaturan wilayah Indonesia memakai titik koma (;), sedangkan pengaturan bahasa Inggris memakai koma (,). Semua rumus di artikel ini memakai titik koma. Jika Excel kalian menolak rumusnya, ganti titik koma dengan koma.

1. Menandai Duplikat dengan Rumus COUNTIF

Ketik rumus berikut di sel F5, lalu tarik ke bawah sampai F19.

=IF(COUNTIF($E$5:$E$19;E5)>1;"Data Duplikat";"-")

Cara kerjanya begini: COUNTIF menghitung berapa kali nilai di E5 muncul di seluruh rentang E5:E19. Jika hasilnya lebih dari 1, berarti nilai itu ganda dan sel akan berisi "Data Duplikat". Tanda dolar ($) mengunci rentang supaya tidak ikut bergeser saat rumus ditarik ke bawah, sedangkan E5 dibiarkan relatif agar berubah menjadi E6, E7, dan seterusnya.

Kesalahan rumus versi lama ada pada bagian MAX(COUNTIF($E$5:$E$19;$E$5:$E$19)). Bagian itu memeriksa seluruh tabel sekaligus, bukan baris yang sedang dicek, sehingga hasilnya sama di setiap baris.

2. Menandai Hanya Data Kedua dan Seterusnya

Kadang kalian ingin mempertahankan data pertama dan hanya menandai salinannya. Gunakan rentang yang melebar mengikuti baris:

=IF(COUNTIF($E$5:E5;E5)>1;"Data Duplikat";"-")

Di baris 5, rentangnya hanya E5:E5. Di baris 10, rentangnya menjadi E5:E10. Jadi, kemunculan pertama sebuah nomor rekening selalu bernilai "-", dan baru kemunculan berikutnya yang ditandai. Hasil ini memudahkan kalian menyaring lalu menghapus baris salinan.

Menyaring hasil pengecekan

Setelah kolom Status terisi, klik judul tabel lalu tekan Ctrl + Shift + L untuk memasang filter. Klik panah di kolom Status dan centang "Data Duplikat" saja. Sekarang hanya baris ganda yang tampil, sehingga kalian bisa memeriksa satu per satu apakah data itu memang salah input atau justru sah, misalnya satu orang yang memang memiliki dua transaksi. Setelah selesai, hapus filter dengan menekan kombinasi tombol yang sama.

3. Mewarnai Duplikat dengan Conditional Formatting

Kalau hanya perlu melihat data ganda tanpa kolom bantu, cara ini paling cepat.

1. Blok rentang data, misalnya E5:E19.
2. Buka tab Home, klik Conditional Formatting.
3. Pilih Highlight Cells Rules > Duplicate Values.
4. Pilih warna, lalu klik OK. Semua nilai yang muncul lebih dari sekali akan berwarna.

Pada Excel berbahasa Indonesia, nama menunya bisa berbeda, misalnya Pemformatan Bersyarat. Kalian juga bisa memakai aturan berbasis rumus lewat New Rule > Use a formula dengan rumus =COUNTIF($E$5:$E$19;E5)>1 untuk mewarnai seluruh baris.

4. Menghapus Duplikat dengan Remove Duplicates

Fitur ini langsung menghapus baris ganda, jadi salin dulu sheet-nya sebagai cadangan.

1. Klik salah satu sel di dalam tabel.
2. Buka tab Data, lalu klik Remove Duplicates.
3. Centang kolom yang dijadikan patokan. Jika yang dicentang hanya No Rekening, baris dengan nomor rekening sama akan dihapus meskipun namanya berbeda.
4. Klik OK. Excel akan memberi tahu berapa baris yang dihapus dan berapa yang tersisa.

Jangan centang kolom No (nomor urut), karena nilainya selalu berbeda sehingga tidak ada baris yang dianggap ganda.

5. Duplikat Berdasarkan Beberapa Kolom (COUNTIFS)

Bila sebuah data baru dianggap ganda ketika Nama dan No Rekening sama-sama cocok, gunakan COUNTIFS. Contoh berikut mengasumsikan Nama ada di kolom D.

=IF(COUNTIFS($D$5:$D$19;D5;$E$5:$E$19;E5)>1;"Data Duplikat";"-")

Kalian bisa menambah pasangan rentang dan kriteria sebanyak yang diperlukan, misalnya kolom tanggal transaksi.

6. Membuat Daftar Unik dengan UNIQUE

Pengguna Excel 2021, Microsoft 365, atau Excel untuk web bisa memakai fungsi UNIQUE. Cukup ketik di satu sel kosong, dan hasilnya akan "tumpah" ke sel di bawahnya.

=UNIQUE(E5:E19)
=UNIQUE(E5:E19;;TRUE)
=FILTER(E5:E19;COUNTIF(E5:E19;E5:E19)>1)

Rumus pertama menampilkan setiap nomor rekening satu kali. Rumus kedua hanya menampilkan nilai yang muncul tepat satu kali. Rumus ketiga melakukan kebalikannya, yaitu menampilkan semua nilai yang ganda. Fungsi ini tidak tersedia di Excel 2019 ke bawah; di versi tersebut, gunakan metode COUNTIF atau Remove Duplicates.

Hati-Hati dengan Nomor Rekening yang Panjang

Excel hanya menyimpan angka sampai 15 digit. Nomor rekening, NIK, atau nomor kartu yang lebih panjang akan dibulatkan menjadi nol di bagian belakang jika diketik sebagai angka. Selain itu, COUNTIF memperlakukan teks yang berisi angka sebagai angka, sehingga dua nomor yang 15 digit pertamanya sama bisa terhitung duplikat padahal berbeda.

Solusinya, format kolom sebagai Text sebelum memasukkan data, dan untuk nomor panjang pakai rumus pembanding langsung berikut sebagai pengganti COUNTIF.

=IF(SUMPRODUCT(--($E$5:$E$19=E5))>1;"Data Duplikat";"-")

Kalau masih belajar dasar-dasar Excel, latihan seperti ini juga berguna untuk pekerjaan administrasi lain, misalnya merekap data saat lapor SPT Tahunan atau mencatat keuangan pribadi seperti di artikel mengatur keuangan anak kost.

Pertanyaan yang Sering Diajukan

Mengapa semua baris tertulis Data Duplikat?
Biasanya karena rumus memakai MAX(COUNTIF(rentang;rentang)) seperti versi lama, atau rentang kriterianya ikut terkunci. Pastikan argumen kedua COUNTIF adalah sel di baris itu sendiri, misalnya E5 tanpa tanda dolar.

Rumus saya error, apa penyebabnya?
Paling sering karena pemisah argumen. Excel dengan pengaturan wilayah Indonesia memakai titik koma, sedangkan pengaturan bahasa Inggris memakai koma.

Apakah Remove Duplicates bisa dibatalkan?
Bisa dengan Ctrl + Z selama file belum ditutup. Meski begitu, tetap buat salinan sheet sebelum menjalankannya.

Apakah Conditional Formatting membedakan huruf besar dan kecil?
Tidak. Duplicate Values dan COUNTIF menganggap "Budi" dan "BUDI" sama. Jika perlu membedakannya, gunakan rumus dengan fungsi EXACT.

Kesimpulan

Untuk menandai data duplikat di Excel, gunakan =IF(COUNTIF($E$5:$E$19;E5)>1;"Data Duplikat";"-") lalu tarik ke bawah. Jika hanya ingin menandai salinannya, pakai rentang bertahap $E$5:E5. Conditional Formatting cocok untuk pengecekan cepat, Remove Duplicates untuk membersihkan data, COUNTIFS untuk patokan beberapa kolom, dan UNIQUE untuk membuat daftar tanpa data ganda.

Apa pun metodenya, biasakan menyimpan salinan data sebelum menghapus apa pun, dan perhatikan format kolom untuk nomor yang panjang agar hasil pengecekan tetap akurat.

Rujukan

• Lapor SPT Tahunan 1770S
• Microsoft Copilot
• Keuangan Anak Kost Tanggal Tua