Mewarnai data yang sama di excel

Pada tutorial ini, kamu akan mempelajari cara mencari data duplikat di excel menggunakan beberapa cara.

Ketika memproses data di excel, terkadang kita butuh menemukan data duplikat untuk menandai, merevisi, ataupun menghapusnya. Ini agar mereka tidak berefek negatif pada pekerjaan yang kita lakukan selanjutnya di excel.

Kita dapat menemukan mereka menggunakan fitur conditional formatting atau memakai rumus-rumus tertentu di excel. Setelah mempelajari tutorial ini, kamu seharusnya akan bisa mengaplikasikan metode-metode tersebut dengan mudah dan lancar!

Disclaimer: Artikel ini mungkin mengandung link afiliasi dari mana kami akan mendapatkan komisi untuk setiap transaksi/aksi terkualifikasi tanpa adanya biaya tambahan bagimu. Pelajari lebih lanjut

Daftar Isi:

• Cara mencari data duplikat di excel dengan conditional formatting
• Cara mencari data duplikat di excel dengan COUNTIF
• Cara mencari data duplikat dalam dua kolom di excel dengan VLOOKUP
• Cara mencari data duplikat dalam dua kolom di excel per baris
• Cara menemukan duplikasi suatu baris di excel
• Latihan
• Catatan tambahan

Cara Mencari Data Duplikat di Excel dengan Conditional Formatting

Cara pertama mencari data duplikat di excel adalah dengan menggunakan fitur conditional formatting.

Conditional formatting memiliki menu khusus untuk mengidentifikasi duplikasi yang kita punya dalam suatu cell range. Menu khusus ini membuat kita menjadi lebih mudah menemukan data duplikat kita dengan menggunakan fiturnya.

Langkah-langkah menggunakannyapun terbilang cukup simpel dan kamu dapat menguasainya secara cepat. Kita akan mendiskusikan langkah-langkah tersebut di bawah ini dengan sebuah contoh untuk membantumu memahaminya dengan lebih mudah!

Untuk contohnya sendiri, misalkan kita punya tabel data seperti ini dalam file excel kita.

Misalkan kita ingin mengidentifikasi data duplikat pada kolom “Nama Awal” di sana. Bagaimana cara kita melakukannya menggunakan fitur conditional formatting?

Pertama, kamu sorot cell range di mana kamu ingin mengecek data duplikatmu. Pada contoh ini, itu berarti kita menyorot data-data pada kolom nama awal di tabel data kita.

Kemudian, klik tombol Conditional Formatting di tab Home. Taruh pointermu di pilihan Highlight Cell Rules pada daftar yang muncul.

Ini akan memunculkan pilihan-pilihan lain di dalam Highlight Cell Rulesnya. Pilih yang memiliki label Duplicate Values…

Excel akan menunjukkan dialog box New Formatting Rule padamu. Pastikan dropdown di sana mengindikasikan bahwa kamu bermaksud menandai duplikasi dalam cell rangemu.

Kamu juga bisa mengganti gaya penandaan duplikatmu dengan mengaturnya di dropdown Format with…

Setelah kamu mengaturnya, klik OK. Duplikasi-duplikasi dalam cell rangemu akan ditandai dengan gaya penandaan yang kamu pilih!

Cukup mudah menggunakan fitur conditional formatting untuk mengidentifikasi data duplikasinya, bukan? Gunakan cara conditional formatting ini jika kamu memang lebih menyukainya dibandingkan dengan cara-cara lain yang diajarkan di tutorial ini!

Cara Mencari Data Duplikat di Excel dengan COUNTIF

Selain conditional formatting, kamu juga bisa menggunakan rumus-rumus excel untuk membantumu menemukan duplikasi dalam datamu. Salah satu rumus yang bisa kamu gunakan untuk itu adalah COUNTIF.

Untuk menggunakan cara COUNTIF, kamu perlu membuat kolom helper yang akan menjadi tempat penandaan data duplikatmu. Buat kolom helper ini sejajar dan sama besar dengan cell range datamu. Kamu akan menuliskan sejumlah COUNTIF dalam kolom helper tersebut yang akan membantumu mengidentifikasi duplikasi dalam cell rangemu.

Secara umum, COUNTIF yang kamu tulis untuk kebutuhan pengecekan duplikasi tersebut adalah sebagai berikut.

=COUNTIF(cell_range_data, data_untuk_dicek_duplikasinya) > 1

Masukkan cell range yang berisi semua data di mana kamu ingin mengidentifikasi duplikasinya ke dalam COUNTIFmu. Lalu masukkan cell yang sejajar dengan cell di kolom helper di mana kamu mengetikkan COUNTIFmu. Tuliskan COUNTIF ini di cell pertama kolom helpermu.

Jangan lupa menambahkan simbol dollar di depan koordinat baris dan kolom dari input cell range COUNTIFmu. Ini agar kamu dapat dengan mudah menyalin COUNTIFmu nanti ke cell-cell lain dari kolom helpermu setelah kamu menuliskan satu.

Setelah itu, jangan lupa menuliskan kondisi “>1” setelah kamu menuliskan COUNTIFmu. Ini untuk mengecek apakah ada duplikasi dari data yang menjadi input COUNTIFmu atau tidak.

Jika hasil COUNTIFnya lebih dari satu, maka hasil penulisan rumusmu akan menjadi TRUE, yang berarti ada paling tidak satu data duplikat. Jika FALSE, maka itu berarti tidak ada data duplikat.

Dapatkan TRUE dan FALSE dari semua data dalam cell rangemu menggunakan rumus ini. Kamu akan mengidentifikasi duplikat dalam cell rangemu dengan cepat!

Jika kamu mau, maka kamu juga bisa melabeli data duplikatmu dengan teksmu sendiri (tidak dengan TRUE/FALSE) dengan mengkombinasikan COUNTIFmu dan IF. Kita dapat menuliskan kombinasi COUNTIF dan IF ini secara umumnya sebagai berikut.

=IF(COUNTIF(cell_range_data, data_untuk_dicek_duplikasinya) > 1, “Duplikat”, “Bukan Duplikat”)

Kamu bisa mengganti label “Duplikat” dan “Bukan Duplikat”nya di sana dengan label yang kamu inginkan.

Untuk lebih mudah memahami mengenai penggunaan COUNTIF untuk menemukan duplikat di excel, perhatikan screenshot-screenshot berikut.

COUNTIF:

IF COUNTIF:

Kamu bisa mengecek penulisan rumus pada masing-masing screenshot di formula bar. Seperti dapat kamu lihat, kita membuat sebuah kolom helper untuk menjadi tempat di mana kita akan menuliskan COUNTIF kita.

Dalam screenshot-screenshot tersebut, kita mencoba mengidentifikasi duplikasi data pada kolom “Nama Awal”. Untuk itu, kita menuliskan COUNTIF pada baris pertama kolom helpernya. Input COUNTIFnya adalah cell range kolom “Nama Awal” dan cell baris pertama dari kolom tersebut.

Kita juga menuliskan kondisi “>1” setelah COUNTIFnya untuk mendapatkan hasil pengecekan duplikasi data dari rumusnya.

Kedua screenshot tersebut juga menunjukkan perbedaan menggunakan COUNTIF saja dan menggunakan COUNTIF dengan IF. Jika kita hanya menggunakan COUNTIF, maka duplikatnya akan ditandai dengan nilai logika TRUE. Jika kita menggunakan COUNTIF dengan IF, maka kita dapat menandainya dengan cara apa saja yang kita inginkan.

Setelah kita menuliskan COUNTIF pada baris pertama, salin rumusnya ke bawah sampai kita dapat mengecek semua data kita. Setelah itu, kita akan mendapatkan penandaan duplikasi untuk data-data kita dengan cepat!

Cara Mencari Data Duplikat dalam Dua Kolom di Excel dengan VLOOKUP

Bagaimana jika kita ingin mengecek duplikasi dari data suatu kolom di kolom lainnya? Apa ada cara yang bisa kita gunakan untuk melakukan proses pengecekan duplikasi seperti itu?

Kita bisa menggunakan rumus VLOOKUP untuk membantu kita melakukannya. Ia akan mengecek keberadaan data dari satu kolom di kolom lainnya dan memberikan hasil pengecekannya kepadamu.

Cara umum penulisan VLOOKUP untuk membuatnya melakukan itu bagi kita adalah sebagai berikut.

=VLOOKUP(data_dari_kolom_1, cell_range_kolom_2, 1, FALSE)

Dalam VLOOKUP, kita memasukkan data yang kita ingin cek kemungkinan duplikasinya sebagai nilai referensi pencariannya. Kolom di mana kita ingin mengecek duplikasinya menjadi input cell range dari VLOOKUPnya.

Kita memasukkan 1 sebagai indeks kolom hasilnya karena kita hanya mempunyai 1 kolom dalam input cell range VLOOKUP kita. Selanjutnya, kita memasukkan FALSE sebagai mode pencariannya karena kita menginginkan kecocokan sama persis untuk memastikan benar ada duplikasi atau tidak.

Hasil dari penulisan VLOOKUP tersebut adalah sebuah data atau error jika duplikasinya tidak ditemukan. Untuk mendapatkan bentuk hasil yang lebih baik, kita bisa menggunakan IF dan ISERROR untuk memberikan label yang kita inginkan.

Secara umum, kita dapat menuliskan kombinasi dari ketiga rumus tersebut sebagai berikut.

=IF(ISERROR(VLOOKUP(data_dari_kolom_1, cell_range_kolom_2, 1, FALSE)), “Bukan Duplikat”, “Duplikat”)

Jika VLOOKUP tidak menghasilkan error, maka itu berarti ada duplikat pada kolom lainnya tersebut. Oleh sebab itu, hasil ISERRORnya akan menjadi FALSE. Jika tidak terdapat duplikasi, maka hasilnya akan menjadi TRUE.

Kita dapat memberikan label bukan duplikat/duplikat sesuai yang kita inginkan dalam IFnya. Kamu dapat mengganti tulisan “Bukan Duplikat” dan “Duplikat” di sana untuk melakukannya.

Contoh implementasi VLOOKUP untuk menemukan duplikat di excel adalah sebagai berikut.

Kamu dapat melihat penulisan rumusnya pada formula bar di screenshotnya. Kita menuliskan kombinasi IF ISERROR VLOOKUP dalam satu kolom untuk mengecek duplikasi nama awal pada kolom nama tengahnya.

Kita memasukkan data nama awal dan kolom nama tengahnya dalam VLOOKUPnya di sini. Kita mengecek hasil VLOOKUPnya menggunakan ISERROR dan melabeli hasil pengecekan duplikatnya menggunakan IF.

Jangan lupa memasukkan simbol dollar ke dalam input cell range VLOOKUPnya. Ini untuk mempermudah penyalinan rumusnya ke cell-cell lain yang membutuhkannya nanti.

Tuliskan kombinasi rumusnya dengan benar dan kamu akan mendapatkan hasil yang kamu inginkan (seperti yang dapat kamu lihat pada screenshot)!

Cara Mencari Data Duplikat Dalam Dua Kolom di Excel per Baris

Kita mungkin ingin mengecek duplikasi dalam dua kolom tapi duplikasinya harus berada di baris yang sama. Jika kita mempunyai kondisi seperti itu, maka kita dapat menggunakan tanda sama dengan di keyboard kita untuk melakukannya.

Cara penulisan rumus dengan bantuan tanda sama dengannya tersebut adalah seperti ini.

=data_kolom_1 = data_kolom_2

Kedua data kolomnya sebaiknya sejajar satu sama lain untuk mempermudah kita ketika melihat hasilnya.

Penulisan rumus tersebut akan menghasilkan TRUE jika mereka adalah duplikat dan FALSE jika bukan. Jika kamu ingin melabeli duplikat/bukan duplikatnya, maka kamu bisa menambahkan IF dalam penulisanmu.

=IF(data_kolom_1 = data_kolom_2, “Duplikat”, “Bukan Duplikat”)

Seperti biasa, kamu bisa mengganti kata “Duplikat” dan “Bukan Duplikat” di sana menjadi label yang kamu inginkan.

Contoh implementasi penulisan tersebut di excel sendiri adalah sebagai berikut.

Seperti dapat kamu lihat, kita mengecek duplikasi pada setiap baris dari nama awal dan akhir dalam contohnya. Untuk itu, kita memasukkan nama awal, tanda sama dengan, dan nama akhir dalam input pertama IFnya. Input nama awal dan nama akhirnya sejajar satu sama lainnya.

Lalu, kita memasukkan label yang kita inginkan untuk duplikat/bukan duplikatnya. Tekan enter dan kita akan mendapatkan hasil pengecekan duplikat dari rumus kita!

Untuk baris lainnya dalam kolom-kolom tersebut, salin rumus yang sudah kita tuliskan untuk pasangan pertama datanya. Jika kolom-kolomnya sama besarnya dan sejajar satu sama lain (seperti umumnya), maka penyalinan tersebut seharusnya cukup.

Cara Menemukan Duplikasi Suatu Baris di Excel

Bagaimana jika kita ingin mengecek duplikasi satu baris secara keseluruhan barisnya? Ini berarti jika ada satu kolom saja dalam baris tersebut yang tidak sama dengan baris lainnya, maka itu tidak dianggap duplikasi.

Kita bisa melakukan pengecekan duplikasi seperti itu dengan menggabungkan semua data kolom dari setiap barisnya terlebih dahulu. Setelah itu, kita bisa mengecek duplikasinya menggunakan cara conditional formatting atau COUNTIF.

Ini akan lebih mudah dijelaskan dengan sebuah contoh. Untuk itu, mari kita lihat tabel data nama yang sudah kita gunakan beberapa kali sebelumnya.

Kita ingin menghitung duplikasi nama lengkapnya di sana, Bagaimana cara kita melakukan perhitungannya?

Pertama, kita buat sebuah kolom helper dahulu yang sejajar dengan kolom-kolom lainnya yang berisi data kita.

Lalu, kita gabungkan semua data yang kita inginkan sebagai bagian baris yang kita cek duplikasinya. Kita menggabungkannya menggunakan rumus CONCATENATE/CONCAT atau dengan tanda dan (&).

Dalam contoh ini, kita menggunakan tanda dan untuk membantu kita. Untuk menggunakannya, taruh semua data yang ingin kita gabungkan dengan tanda dan di antara mereka.

Kamu dapat melihat bagaimana penulisan untuk menggabungkan datanya di formula bar.

Kemudian, kita dapat mengecek baris-baris yang mempunyai duplikat pada kolom helpernya. Kamu bisa menggunakan cara conditional formatting atau COUNTIF untuk itu.

Ini adalah hasil dari masing-masing cara tersebut jika kita menggunakannya dalam contoh kita.

Conditional Formatting:

COUNTIF:

Kedua cara tersebut akan menandai duplikat dalam baris-baris kita. Ini membuat kita dapat menemukan mereka sesuai dengan apa yang kita butuhkan!

Latihan

Setelah kamu mempelajari cara mencari duplikat di excel menggunakan berbagai cara, sekarang saatnya mengerjakan sebuah latihan! Hal ini agar kamu dapat mengasah pemahamanmu secara praktek,

Unduh file latihannya dan lakukan instruksi-instruksi di bawah ini. Unduh file kunci jawabannya juga jika kamu sudah menyelesaikan latihannya dan ingin mengecek jawabanmu. Atau mungkin ketika kamu bingung bagaimana cara menjalankan instruksinya!

Link file latihan:
Unduh di sini

Instruksi

Jawab pertanyaan-pertanyaan di bawah ini pada sheet mereka masing-masing sesuai dengan nomornya! (1 = Sheet1, 2 = Sheet2, 3 = Sheet3)

1. Temukan duplikasi pada kolom namanya menggunakan conditional formatting! Tandai data duplikatnya dengan warna cell kuning dan warna tulisan merah!
2. Temukan duplikasi pada kolom umur menggunakan COUNTIF! Tandai data duplikatnya dengan TRUE dan bukan duplikat dengan FALSE!
3. Temukan duplikasi baris dalam tabel datanya (menggunakan conditional formatting atau COUNTIF)!

Link file kunci jawaban:
Unduh di sini

Catatan Tambahan

Conditional formatting, COUNTIF, dan VLOOKUP tidak membedakan huruf kecil dan besar ketika mereka mengecek duplikasi dalam cell rangemu.

Tutorial lainnya untuk kamu pelajari:

Bagaimana cara menemukan data yang sama di Excel?

Apa yang dimaksud dengan duplikasi data?

Rumus match untuk apa?

Bagaimana cara mewarnai cell di Excel?