Ilmu HRD: Tips Excel

Mencegah Data Duplikat pada Microsoft Excel

Ketika kita membuat atau mengolah suatu data master seperti data barang, data pemasok atau data costumer biasanya ada ketentuan yang mana salah satu dari atributnya tidak boleh ada yang sama dan harus unik. Pada database kita mengenalnya dengan istilah Primary Key, yaitu suatu nilai dalam basis data yang digunakan untuk mengidentifikasi setiap record secara unik. Primary key harus merupakan field yang benar-benar unik dan tidak boleh ada nilai NULL. Namun bagaimana jika kita hanya ingin membuat sebuah data sederhana saja di Excel? Tips kali ini menggunakan fitur Data Validation, Conditional Formatting danRemove Duplicates untuk menyelesaikannya. Yuk disimak Smile

Untuk Data yang Baru Akan Dibuat

Jika Anda baru akan membuat datanya, tentukan kolom mana yang akan dijadikan primary key. Pada contoh kasus di bawah ini saya menggunakan kolom Kode Barang. Berikut langkah-langkah pengerjaannya:

Sorot area kolom Kode Barang yaitu dari cell B4:B13.
Pada tab Data kategori Data Tools pilih Data Validation, sehingga muncul kotak dialog Data Validation.
Pada tab Settings kotak pilihan Allow, pilih Custom. Pada kotak isian Formulamasukkan formula berikut: =COUNTIF($B$4:$B$13;B13)=1.

Pada tab Error Alert kotak isian Title, isikan: “Kode Barang Ditolak!” dan pada kotak isianError message isikan: “Kode Barang sudah ada, periksa kembali.” lalu klik OK.

Sampai tahap ini Anda sudah selesai membuat validasi data duplikat. Untuk mengetahui validasi ini bekerja, mari kita lakukan testing. Pada cell B13 isikan: ACC003 dan lihatlah apa yang terjadi. Muncul kotak dialog dengan isi yang telah kita buat tadi.

Klik tombol Retry untuk mengisikan kode lain atau klik tombol Cancel untuk membatalkan.

Untuk Data yang Sudah Ada

Tips di atas digunakan jika belum ada data yang dimasukkan atau baru akan dibuat, namun bagaimana jika kita sudah punya datanya dan ingin mengetahui record mana saja yang duplikat?

Untuk kasus ini, kita bisa menggunakan fitur Conditional Formatting. Berikut langkah-langkah pengerjaannya:

Sorot kolom yang akan dicari data duplikat. Seperti contoh sebelumnya, area yang disorot adalah kolom Kode Barang.
Pada tab Home kategori Styles klik Conditional Formatting > Highlight Cells Rules lalu pilih Duplicates Value. Muncul kotak dialog Duplicates Value lalu klik tombol OK.

Sampai tahap ini Anda sudah bisa melihat data mana saja yang duplikat, namun jika data yang digunakan cukup banyak Anda harus mengeceknya dari data paling awal sampai paling akhir. Untuk menyingkatnya, mari kita filter data tersebut sehingga hanya menampilkan data duplikat.
Tempatkan penunjuk cell pada area data lalu aktifkan fitur Filter yang dapat diakses di tab Data kategori Sort & Filter.
Pada kolom Kode Barang, klik icon panah bawah > klik Filter by Color lalu pilih warna cell yang duplikat tadi.

Sekarang Excel hanya menampilkan data yang duplikat, setelah ini Anda bisa menentukan apakah salah satu data tersebut diganti atau dihapus. Untuk menghapusnya secara otomatis, Anda dapat menggunakan fitur Remove Duplicates, caranya:

Tempatkan penunjuk cell di area data, pada tab Data kategori Data Tools pilihRemove Duplicates sehingga mucul kotak dialog Remove Duplicates.
Ceklis pada checkbox Kode Barang lalu klik OK. Excel akan melakukan penghapusan data duplikat. Jika sudah selesai, Excel akan memberikan report nya dengan menampilkan kotak informasi yang berisi berapa jumlah record yang dihapus dan jumlah record yang tersisa.