Saat Anda bekerja dengan data dalam jumlah besar, Anda akan mudah kehilangan jejak data dan melihatnya muncul beberapa kali dalam tabel. Beberapa duplikat ditempatkan dengan sengaja sementara yang lain adalah kesalahan. Apa pun masalahnya, Anda mungkin ingin duplikat ini disorot secara otomatis kepada Anda.
Menemukan sel duplikat dalam spreadsheet kecil itu mudah tetapi ketika berhadapan dengan kumpulan data yang besar dan kompleks, bisa sangat sulit untuk melakukannya secara manual. Untungnya, ada alat bawaan di Excel yang memungkinkan Anda menyorot nilai duplikat. Dalam tutorial ini, kami akan menunjukkan cara menyorot data duplikat dengan fitur pemformatan bersyarat di Excel.
Sorot Duplikat dengan Pemformatan Bersyarat di Excel. Umumnya, Anda mungkin ingin menemukan duplikat di Excel, karena lebih sering duplikat ada karena kesalahan dan harus dihapus atau duplikat penting untuk analisis dan harus disorot di Excel.
Ada dua metode untuk menemukan nilai duplikat dengan pemformatan bersyarat di Excel. Mereka:
- Sorot duplikat menggunakan aturan Nilai Duplikat
- Sorot duplikat menggunakan rumus khusus Excel (COUNTIF dan COUNTIFS)
Sorot Duplikat Menggunakan Aturan Nilai Duplikat
Mari kita asumsikan kita memiliki kumpulan data ini:
Pertama, pilih rentang sel yang berisi nilai duplikat. Lalu buka tab 'Beranda', klik 'Pemformatan Bersyarat' di bagian Gaya pada pita. Di drop-down, pindahkan kursor Anda ke opsi pertama untuk 'Sorot Aturan Sel' dan itu akan menampilkan lagi daftar aturan di kotak pop-out. Pilih opsi 'Nilai Duplikat' di sini.
Setelah Anda mengklik Nilai Duplikat, kotak dialog Nilai Duplikat akan muncul. Di sini Anda dapat memilih jenis pemformatan untuk nilai duplikat. Anda dapat memilih dari warna untuk hanya mengisi sel, hanya untuk font, sebagai batas, atau format kustom jika diinginkan. Kemudian klik 'OK' untuk menutup kotak dialog.
Di sini, kami memilih 'Isi Hijau dengan Teks Hijau Tua' untuk contoh kami.
Setelah Anda memilih jenis pemformatan, itu akan menyorot semua nilai duplikat dalam rentang yang dipilih seperti yang ditunjukkan di bawah ini.
Menyorot Duplikat Unyanyikan Rumus COUNTIF
Metode lain untuk menyorot nilai duplikat adalah dengan menggunakan pemformatan bersyarat dengan rumus COUNTIF sederhana dalam satu kolom atau di beberapa kolom.
Pilih rentang data tempat Anda ingin menyorot duplikat. Kemudian pada tab 'Beranda' dan klik opsi 'Pemformatan Bersyarat'. Di tarik-turun, klik opsi 'Aturan Baru'.
Ini akan membuka kotak dialog Aturan Pemformatan Baru.
Di kotak dialog Aturan Pemformatan Baru, pilih opsi 'Gunakan rumus untuk menentukan sel mana yang akan diformat' di bawah kotak daftar Pilih Jenis Aturan, lalu masukkan rumus COUNTIF berikut untuk menghitung duplikat.
=COUNTIF($A$1:$C$11,A1)>1
Kemudian, klik tombol 'Format' untuk membuka kotak dialog Format Sel. Di kotak dialog Format Cells, Anda dapat memilih warna Fill dari palet warna untuk menyorot sel dan kemudian klik 'OK'. Di sini kami memilih warna isian biru untuk memformat duplikat.
Kemudian, klik 'OK' lagi untuk menutup kotak dialog. Rumus akan menyorot semua nilai sel yang muncul lebih dari sekali.
Selalu masukkan rumus untuk sel kiri atas dalam rentang yang dipilih (A1:C11). Excel secara otomatis menyalin rumus ke sel lain.
Anda juga dapat menentukan aturan sesuka Anda. Misalnya, Jika Anda ingin menemukan nilai yang hanya muncul dua kali dalam tabel, masukkan rumus ini sebagai gantinya (Dalam kotak dialog Aturan Pemformatan Baru):
=COUNTIF($A$1:$C$11,A1)=2Hasil:
Terkadang Anda mungkin hanya ingin melihat duplikat dan memfilter nilai unik. Untuk melakukan ini, pilih rentang, buka tab Beranda, klik opsi 'Urutkan & Filter' di sudut kanan atas excel dan pilih opsi 'Filter'.
Sel pertama dari setiap kolom kemudian akan menampilkan menu tarik-turun tempat Anda dapat menentukan kriteria filter. Klik pada drop-down di sel pertama kolom dan pilih 'Filter by Color'. Kemudian pilih warna biru.
Sekarang, Anda hanya akan melihat sel yang disorot dan Anda dapat melakukan apa pun yang Anda inginkan dengannya.
Temukan dan Sorot Baris Duplikat di Excel menggunakan Rumus COUNTIFS
Jika Anda ingin menemukan dan menyorot baris duplikat di Excel, gunakan COUNTIFS alih-alih COUNTIF.
Pilih rentang, buka tab 'Beranda' dan klik 'Pemformatan Bersyarat di grup Gaya. Di tarik-turun, klik opsi 'Aturan Baru'.
Di kotak dialog Aturan Pemformatan Baru, pilih opsi 'Gunakan rumus untuk menentukan sel mana yang akan diformat' di bawah kotak daftar Pilih Jenis Aturan, lalu masukkan rumus COUNTIFS di bawah ini:
=COUNTIFS($A$1:$A$20,$A1,$B$1:$B$20,$B1,$C$1:$C$20,$C1)>1Dalam rumus di atas, rentang A1:A20 mengacu pada kolom A, B1:B20 mengacu pada kolom B dan C1:C20 mengacu pada kolom C. Rumus menghitung jumlah baris berdasarkan beberapa kriteria (A1, B2, dan C1) .
Kemudian, klik tombol 'Format' untuk memilih gaya pemformatan dan klik 'OK'.
Sekarang, Excel hanya menyoroti baris duplikat seperti yang ditunjukkan di bawah ini.
Itu dia.