Untuk menandai data duplikat di Excel, pilih selnya lalu buka Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, kemudian tekan OK. Untuk menentukan sendiri apa yang dihitung sebagai duplikat (baris penuh, hanya salinan kedua, dua kolom sekaligus), pakai aturan rumus: =COUNTIF($A$2:$A$9,A2)>1 mewarnai setiap sel yang nilainya muncul lebih dari sekali di A2:A9.
| A | |
|---|---|
| 1 | Customer |
| 2 | Ana |
| 3 | Ben |
| 4 | Cara |
| 5 | Ana |
| 6 | Dan |
| 7 | Ben |
| 8 | Ana |
| 9 | Eve |
Ana dan Ben diwarnai di setiap baris tempat mereka muncul: A2, A3, A5, A7, dan A8. Aturannya ditampilkan di bawah kisi. Ubah A4 menjadi Eve dan kedua sel Eve ikut menyala.
Menandai duplikat dengan aturan Duplicate Values
- Pilih sel yang akan dicek, misalnya A2:A9. Jangan sertakan header-nya.
- Buka Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Biarkan Duplicate di kotak pertama, atau ubah menjadi Unique untuk mewarnai nilai yang hanya muncul sekali.
- Pilih formatnya (bawaannya Light Red Fill with Dark Red Text) lalu tekan OK.
Aturan ini mengabaikan huruf besar kecil, jadi ana dan Ana diwarnai sebagai duplikat. Aturan ini selalu mewarnai setiap salinan, termasuk yang pertama, dan hanya membandingkan sel tunggal: tidak bisa mewarnai satu baris penuh atau mengecek dua kolom bersamaan. Untuk itu, pakai aturan rumus.
Menandai duplikat dengan rumus
Aturan rumus mewarnai sebuah sel saat rumusnya menghasilkan TRUE untuk sel itu. Kamu menulis rumus untuk sel pertama dari pilihan, lalu Excel menerapkannya ke setiap sel lainnya dengan cara yang sama seperti rumus yang disalin ke bawah.
- Pilih A2:A9, dengan A2 sebagai sel aktif.
- Buka Home > Conditional Formatting > New Rule.
- Pilih Use a formula to determine which cells to format.
- Ketik
=COUNTIF($A$2:$A$9,A2)>1. - Klik Format, pilih warna isian di tab Fill, lalu tekan OK dua kali.
Rentang $A$2:$A$9 memakai tanda dolar, jadi setiap sel menghitung di daftar yang sama. A2 tidak memakainya, jadi di baris 5 aturannya membaca A5. Di lembar kerja di halaman ini, kamu bisa mengklik aturan di bawah kisi, mengeditnya, dan melihat sel mana yang berubah. Jenis aturan lainnya ada di halaman conditional formatting.
Menandai hanya kemunculan kedua
Untuk membiarkan salinan pertama tanpa warna dan hanya mewarnai pengulangannya, akhiri rentang yang dihitung di baris saat ini: =COUNTIF($A$2:A2,A2)>1. Di A5, aturannya menjadi =COUNTIF($A$2:A5,A5)>1, dan Ana sudah muncul dua kali di A2:A5.
| A | |
|---|---|
| 1 | Customer |
| 2 | Ana |
| 3 | Ben |
| 4 | Cara |
| 5 | Ana |
| 6 | Dan |
| 7 | Ben |
| 8 | Ana |
| 9 | Eve |
A5, A7, dan A8 diwarnai: baris-baris yang akan dihapus oleh Remove Duplicates. Ini aturan yang tepat kalau kamu ingin mengecek apa yang akan dihapus tombol itu sebelum menekannya.
Mewarnai satu baris penuh kalau sebuah nilai duplikat
Pilih seluruh tabel, A2:C8, lalu kunci kolom kuncinya dengan $ hanya sebelum hurufnya: =COUNTIF($B$2:$B$8,$B2)>1. Di C5, aturannya membaca $B5, jadi setiap sel di baris 5 menanyakan hal yang sama tentang email di kolom B.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Signed up | |
| 2 | Ana | ana@mail.com | 2026-03-02 |
| 3 | Ben | ben@mail.com | 2026-03-04 |
| 4 | Ana P. | ana@mail.com | 2026-03-09 |
| 5 | Cara | cara@mail.com | 2026-03-11 |
| 6 | Dan | dan@mail.com | 2026-03-15 |
| 7 | Benjamin | ben@mail.com | 2026-03-20 |
| 8 | Eve | eve@mail.com | 2026-03-21 |
Baris 2, 3, 4, dan 7 diwarnai: dua orang mendaftar dua kali dengan nama yang berbeda. Tanpa $ sebelum B, referensinya bergeser mengikuti kolom: sel di kolom B akan menguji tanggal di C, sel di C akan menguji kolom D yang kosong, dan hanya kolom A yang diwarnai.
Menandai baris duplikat (dua kolom harus sama)
Kalau sebuah baris baru dianggap duplikat jika dua kolomnya sama-sama berulang, hitung dengan COUNTIFS. Di sini sebuah pesanan dianggap duplikat kalau pelanggan yang sama memesan produk yang sama: =COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,$B2)>1.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Product | Qty |
| 2 | Ana | Apple | 3 |
| 3 | Ben | Pear | 1 |
| 4 | Ana | Pear | 2 |
| 5 | Cara | Apple | 5 |
| 6 | Ana | Apple | 3 |
| 7 | Ben | Kiwi | 4 |
| 8 | Dan | Pear | 2 |
Hanya baris 2 dan 6 yang diwarnai. Ana muncul tiga kali, tetapi hanya dua pesanannya untuk apel. Untuk mewarnai hanya yang kedua dari pasangan itu, pakai juga rentang yang bertambah di sini: =COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)>1.
Menandai duplikat di dua kolom
Untuk mewarnai nama di satu daftar yang juga muncul di daftar lain, hitung setiap sel di kolom yang lain. Ada dua aturan: satu di A2:A7 yang melihat ke C, dan satu di C2:C7 yang melihat ke A. >0 berarti "ditemukan minimal sekali".
| A | B | C | |
|---|---|---|---|
| 1 | January | February | |
| 2 | Ana | Dan | |
| 3 | Ben | Fay | |
| 4 | Cara | Ana | |
| 5 | Dan | Gus | |
| 6 | Eve | Hal | |
| 7 | Fay | Ivy |
Ana, Dan, dan Fay ada di kedua bulan, jadi mereka diwarnai di kedua kolom. Aturan Duplicate Values dengan kedua kolom terpilih (tahan Ctrl, atau Cmd di Mac, untuk memilih A2:A7 dan C2:C7) memberikan warna yang sama di sini, tetapi aturan itu juga akan mewarnai nama yang berulang di dalam satu kolom. Untuk menampilkan daftar kecocokannya, bukan mewarnainya, lihat membandingkan dua kolom.
Menghitung duplikat yang diwarnai
Conditional formatting mewarnai sel tetapi tidak memberimu angka. Uji yang sama di dalam SUMPRODUCT bisa menghitungnya: COUNTIF(A2:A9,A2:A9) menghasilkan berapa kali nilai setiap sel muncul, >1 mengubahnya menjadi TRUE atau FALSE, dan -- mengubahnya menjadi 1 dan 0.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Highlighted | |
| 2 | Ana | ||
| 3 | Ben | ||
| 4 | Cara | ||
| 5 | Ana | ||
| 6 | Dan | ||
| 7 | Ben | ||
| 8 | Ana | ||
| 9 | Eve |
Giliranmu: Di C2, hitung berapa sel di A2:A9 yang diwarnai oleh aturan itu.
Jawabannya 5: tiga Ana dan dua Ben. Di Excel 365 dan 2021, =SUM(--(COUNTIF(A2:A9,A2:A9)>1)) memberikan hasil yang sama; di Excel 2019, versi itu butuh Ctrl+Shift+Enter (Cmd+Shift+Enter di Mac), sedangkan SUMPRODUCT tidak.
Kenapa aturannya mewarnai sel yang salah
Penyebab yang biasa adalah $ yang terlupa. Tanpa tanda itu, rentang yang dihitung ikut bergeser ke bawah untuk setiap sel: di A8, aturan =COUNTIF(A2:A9,A2)>1 menjadi =COUNTIF(A8:A15,A8)>1, yang hanya melihat dari baris 8 ke bawah dan menemukan satu Ana saja.
| A | |
|---|---|
| 1 | Customer |
| 2 | Ana |
| 3 | Ben |
| 4 | Cara |
| 5 | Ana |
| 6 | Dan |
| 7 | Ben |
| 8 | Ana |
| 9 | Eve |
Hanya A2, A3, dan A5 yang diwarnai, sedangkan A7 dan A8 tetap tanpa warna walaupun keduanya duplikat. Klik aturan di bawah kisi lalu ubah menjadi =COUNTIF($A$2:$A$9,A2)>1: kelima sel langsung menyala. Penyebab umum lainnya:
- Sel aktifnya bukan sel pertama dari pilihan saat aturan ditulis. Excel membaca rumusnya relatif terhadap sel aktif, jadi aturan yang ditulis saat A9 aktif akan bergeser tujuh baris. Cek di Home > Conditional Formatting > Manage Rules, tempat Applies to menampilkan rentangnya.
- Nilainya sebenarnya tidak sama:
Anadengan spasi di akhir adalah nilai yang berbeda. Bersihkan datanya dulu dengan=TRIM(A2).
Pertanyaan yang sering diajukan
Bagaimana cara menandai data duplikat di Excel?
Pilih rentangnya, lalu buka Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values dan tekan OK. Setiap sel yang nilainya muncul lebih dari sekali di pilihan itu mendapat warna isian merah muda.
Rumus apa yang menandai duplikat di conditional formatting?
=COUNTIF($A$2:$A$9,A2)>1, dimasukkan di Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, dengan A2:A9 terpilih. Rentangnya dikunci dengan $, sedangkan referensi sel A2 tidak.
Bagaimana cara menandai hanya duplikat kedua, bukan yang pertama?
Pakai =COUNTIF($A$2:A2,A2)>1. Rentangnya berakhir di baris saat ini, jadi salinan pertama dihitung 1 dan tetap tanpa warna, sedangkan setiap salinan sesudahnya dihitung 2 atau lebih dan diwarnai.
Bagaimana cara mewarnai satu baris penuh kalau sebuah nilai duplikat?
Pilih seluruh tabel (misalnya A2:C8) lalu pakai aturan yang mengunci kolom kuncinya: =COUNTIF($B$2:$B$8,$B2)>1. Tanda $ sebelum B membuat setiap sel di baris itu tetap melihat ke kolom B.
Bagaimana cara menghapus warna penanda duplikat?
Pilih selnya lalu pilih Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Untuk mengedit aturannya, pakai Home > Conditional Formatting > Manage Rules.
