Cara Membuat Data Validation di Excel

Seperti dalam pemrosesan data mana pun, GIGO atau Garbage In Garbage Out juga berlaku di Excel. Bila kita masukkan data tidak benar ke Excel, hasilnya juga pasti tidak benar.

Artikel kita kali ini membahas bagaimana cara membuat data validation di Excel, yaitu fitur yang bisa membantu menjaga keabsahan data yang kita masukkan ke worksheet.

Kami menggunakan data yang bisa teman-teman download pada workbook di bawah ini sebagai bahan demo.

File: data-validation-excel.xlsx.

Apakah Data Validation di Excel Itu?

Validasi data adalah kegiatan memeriksa akurasi dan kualitas data sebelum diproses.

Bersih-bersih data ini diperlukan agar data bisa diproses sesuai dengan ketentuan dan menghasilkan informasi yang berguna dan benar.

Validasi data dilakukan pada batasan atau objektif tertentu.

Sebagai contoh, syarat peserta pemilihan umum adalah warga negara Indonesia dengan minimal usia 17 tahun. Usia 16 atau di bawahnya tidak diperbolehkan. Bila dilanggar, maka nantinya ada kesalahan informasi jumlah pemilih.

Data validation Excel adalah aktifitas menerapkan batasan-batasan entri ke dalam worksheet agar data sesuai dengan objektif yang ingin dicapai.

Perintah Data Validation Excel Ada di Mana?

Perintah data validation Excel bisa ditemui di tab Data pada kelompok perintah Data Tools.

Mengklik perintah Data Validation akan membuka kotak dialog dengan nama sama, seperti terlihat pada Gambar 01.

Bila kita klik dropdown, Excel akan memberikan beberapa pilihan perintah, yaitu Data Validation, Circle Invalid Data, dan Clear Validation Circles.

Masing-masing perintah ini dapat diakses dengan shortcut keyboard ALT,A,V,V untuk Data Validation, ALT,A,V,I untuk Circle Invalid Data, dan ALT,A,V,R untuk Clear Validation Circles.

Cara Membuat Dropdown List dengan Data Validation Excel

Salah satu cara menjaga validitas entri adalah dengan membatasi pilihan pada nilai-nilai yang sudah ditentukan.

Demo ini bertujuan untuk:

• Membatasi entri pada pilihan-pilihan tertentu menggunakan dropdown list.
• Membuat agar pesan entri tampil saat cell diaktifkan.
• Membuat pesan kesalahan saat entri tidak menggunakan pilihan yang ada.

(Demo 01) Berikut cara membuat dropdown list dengan data validation Excel.

1. Pilih cell atau range yang akan divalidasi, yaitu B2:B9.
2. Buka kotak dialog Data Validation.
3. Pada tab Settings, di dropdown Allow, pilih List.
4. Klik Source, pilih range sumber data pilihan, yaitu J2:J9 (Bila mengetikkan langsung, jangan lupa tanda absolut $ untuk kolom dan baris).
5. Pastikan Ignore blank dan In-cell dropdown.

   

6. Pada tab Input Message, aktifkan Show input message when cell is selected.
7. Pada Title, ketikkan judul entri, yaitu Daftar Mata Pelajaran.
8. Pada Input message, ketikkan pesan Pilih mata pelajaran.

9. Pada tab Error Alert, aktifkan Show error alert after invalid data is entered.
10. Pada Style, pilih tipe notifikasi.
11. Pada Title, ketikkan judul pesan kesalahan Mata Pelajaran Tidak Ada.
12. Pada Error message, masukkan pesan kesalahan Mata pelajaran tidak ada dalam daftar.
13. Klik OK.

Bila teman-teman klik salah satu cell di range B2:B9, cell tersebut akan menampilkan tooltip. Tooltip tersebut berisi pesan yang kita masukkan pada Langkah 7 dan 8.

Selain tooltip, Excel juga akan menampilkan tombol dropdown di samping cell. Mengklik tombol tersebut akan menghadirkan pilihan-pilihan mata pelajaran.

Bila kita coba masukkan mata pelajaran selain yang ada di dropdown, pesan kesalahan sesuai dengan yang kita input pada Langkah 11 dan 12 akan tampil.

Catatan: Judul entri dan pesan kesalahan tidak harus dibuat. Untuk pesan kesalahan, bila tidak dibuat, Excel akan menampilkan pesan default saat terjadi kesalahan.

Fitur-fitur di Kotak Dialog Data Validation Excel

Pada Demo 01, kita sudah belajar bagaimana cara menggunakan data validation di Excel untuk menyaring informasi yang kita masukkan ke worksheet.

Pada dropdown Allow, teman-teman lihat ada beberapa pilihan selain List, yaitu Any value, Decimal, Whole number, Date, Time, Text length, dan Custom.

• Any value adalah pilihan default dan akan menghapus validasi dari suatu cell. Setelah data validation terhapus, kita bisa memasukkan nilai apa pun.
• Whole number membatasi entri pada bilangan bulat.
• Decimal membatasi entri pada bilangan desimal.
• List akan memberikan daftar pilihan (dropdown list) sebagai bentuk entri. Lihat Demo 01.
• Date adalah tipe validasi untuk entri tanggal.
• Time adalah tipe validasi untuk entri waktu.
• Text length adalah bentuk validasi untuk panjang teks.
• Custom adalah bentuk validasi dengan menggunakan rumus sebagai batasan.

Walau pun masing-masing tipe validasi terlihat berbeda, secara prinsip penggunaannya adalah sama. Penerapan setiap tipe validasi, kecuali Custom, List, dan Any value, didasarkan pada rentang yang bisa kita tetapkan seperti pada Demo 03 Langkah 5 dan 6.

Selain tipe-tipe validasi pada dropdown Allow, kotak dialog Data Validation juga memiliki pengaturan-pengaturan lain.

Pada Gambar 06, baris-baris B2:B9 tidak memiliki nilai. Bila Ignore Blank tidak aktif, baris-baris tersebut akan dianggap sebagai tidak valid. Sebaliknya, bila aktif, sistem validasi akan mengabaikan cell-cell tanpa nilai tersebut.

In-cell dropdown, bila aktif, akan menampilkan dropdown list dengan alternatif isian yang bisa kita pilih (Lihat Gambar 07). Bila tidak aktif, kita perlu mengetikkan langsung alternatif pilihan tersebut.

Apply these changes to all other cells with the same settings akan menerapkan perubahan terhadap semua cell yang menggunakan pengaturan validasi yang sama, walau pun cell-cell tersebut tidak terpilih.

Show input message when cell is selected, saat aktif, akan menampilkan pesan petunjuk entri saat cell yang memiliki validasi dipilih.

Show error alert after invalid data is entered, saat aktif, akan menampilkan pesan kesalahan setelah kita memasukkan nilai yang tidak sesuai dengan ketentuan.

Cara Merubah Data Validation Excel Pada Satu Cell ke Cell Lain

Pada Demo 01, kita menerapkan cara membuat data validation di Excel pada range B2:B9.

Ada kemungkinan data tersebut berkembang melebihi delapan baris cell. Bila hal itu terjadi, kita mungkin akan sedikit repot memilih cell yang menggunakan pengaturan validasi yang sama dalam jumlah besar.

Excel punya cara menanganinya, tanpa mengharuskan kita memilih semua cell tersebut.

Pada demo berikut, kami ingin menambahkan mata pelajaran baru di dropdown list. Salah satu cell akan direvisi pengaturan validasi datanya. Revisi ini akan berpengaruh terhadap semua cell yang menggunakan validasi data yang sama, walau pun tidak terpilih.

(Demo 02) Berikut cara merubah data validation Excel pada satu cell ke cell lagi.

1. Tambahkan mata pelajaran Fisika pada baris di bawah Kimia.
2. Pilih cell yang memiliki validasi yang diinginkan, yaitu B2.
3. Buka kotak dialog Data Validation.
4. Klik Source, pilih range sumber data baru, yaitu J2:J7 (bila mengetikkan langsung, jangan lupa tanda absolut $).
5. Pada tab Settings, aktifkan Apply these changes to all other cells with the same settings. Setelah mengaktifkan pilihan ini, seleksi cell akan diperluas menjadi B2:B9.
6. Klik OK.

Pada Gambar 10, kami membuka dropdown list cell B3. Perhatikan Fisika sudah menjadi salah satu pilihan di dalamnya.

Cara Membuat Data Validation di Excel untuk Range Angka

Pada dasarnya, spreadsheet seperti Excel adalah media pencatatan angka. Karena itu, wajar bila Excel memiliki validasi data untuk angka.

Demo kali ini akan membuat validasi di mana entri hanya diperbolehkan 1 sampai 100.

(Demo 03) Berikut cara membuat data validation di Excel untuk range angka.

1. Pilih cell atau range di mana validasi akan dilakukan, yaitu C2:C9.
2. Buka kotak dialog Data Validation.
3. Pada tab Settings, pada dropbox Allow, pilih Whole number.
4. Pada Data, pilih between.
5. Pada Minimum, masukkan referensi =$F$1 sebagai batas bawah.
6. Pada Maximum, masukkan referensi =$F$2 sebagai batas atas.
7. Klik OK.

Pada Gambar 12, validasi bekerja sesuai pengaturan yang kita buat.

Catatan: Seperti pada Demo 01, kita bisa menambahkan pesan entri dan kesalahan. Kami sengaja melewatkan keduanya di demo ini. Saat tidak ada pesan custom, Excel akan menampilkan pesan kesalahan default, kecuali bila kita non-aktifkan Show error alert after invalid data is entered.

Cara Menggunakan Rumus Data Validation Excel

Bagaimana bila validasi yang kita inginkan tidak tersedia di pengaturan validasi data? Sebagai contoh, kolom Kode Barang hanya memiliki nilai dengan kode diawali oleh SC.

Cara ini bisa dilakukan dengan menggunakan rumus sebagai metode validasi data.

Demo berikut ini akan membuat validasi data agar nama siswa menggunakan teks.

(Demo 04) Berikut cara menggunakan rumus data validation Excel.

1. Pilih cell atau range yang akan divalidasi, yaitu A2:A9.
2. Buka kotak dialog Data Validation.
3. Pada tab Settings, pilih Custom untuk Allow.
4. Pada Formula, masukkan =ISTEXT(A2).
5. Klik OK.

Catatan: Walaupun cell A2 yang dimasukkan sebagai parameter rumus, Excel akan menyesuaikan sendiri parameter tersebut untuk setiap baris.

Bila kita coba masukkan nama siswa bukan teks, Excel akan menampilkan pesan kesalahan, seperti yang kita lihat pada Gambar 14.

Tapi, bagaimana dengan nama siswa yang sudah berupa angka seperti pada cell A4 dan A7? Jawabannya bisa teman-teman temui sebentar lagi.

Silakan baca artikel berikut ini untuk bahasan lebih lengkap mengenai menggunakan rumus di data validation Excel.

Baca: Menggunakan Rumus Data Validation Excel.

Cara Melingkari Data Tidak Valid di Excel

Pada Demo 04, kita membuat validasi agar nama siswa hanya berupa teks. Artinya, cell A4 dan A7 tidak valid. Tapi tidak ada efek apa-apa atas kedua cell tersebut.

Excel tidak akan melakukan apa-apa bila validasi dibuat setelah entri dilakukan.

Bila ukuran data kecil seperti pada contoh demo-demo di atas tentu tidak masalah. Mudah melihat dan menggantinya. Tapi bagaimana bila data besar sampai puluhan ribu baris? Bagaimana kita tahu mana yang tidak valid?

Excel memiliki fitur untuk melingkari cell sebagai penanda bahwa cell tersebut memiliki nilai tidak valid.

(Demo 05) Berikut cara melingkari data tidak valid di Excel.

1. Pilih range yang akan ditandai.
2. Jalankan perintah Circle Invalid Data atau gunakan shortcut ALT,A,V,I.

Hasilnya akan tampak seperti pada gambar di bawah.

Catatan: Lingkaran akan hilang bila kita mengganti nilai terlingkar dengan nilai valid. Cara lain untuk menghilangkan lingkaran adalah menggunakan perintah Clear Validation Circles (shortcut: ALT,A,V,R)

Cara Menghapus Data Validation di Excel

(Demo 06) Berikut bagaimana cara menghapus data validation di Excel:

1. Pilih cell atau range yang ingin dihilangkan validasinya.
2. Buka kotak dialog Data Validation.
3. Pada dropdown Allow, pilih Any Value.
4. Klik OK.

Any value, sesuai namanya, mengijinkan kita untuk memasukkan nilai apa pun ke dalam cell bersangkutan.

Cara Copy Data Validation Excel ke Cell Lain

Bila karena suatu sebab kita harus menyalin atau copy validasi dari satu cell ke cell lain, cukup lakukan dengan metode copas biasa, yaitu CTRL+C (Copy) dan CTRL+V (Paste).

Namun, CTRL+C dan CTRL+V akan copy dan semua isi dan format cell sumber ke cell tujuan.

Paste Special punya perintah yang mengijinkan kita hanya menyalin validasi data.

(Demo 06) Berikut cara copy data validation Excel dari satu cell ke cell lain menggunakan Paste Special.

1. Pilih cell atau range yang akan disalin aturan validasinya, yaitu C2.
2. Tekan CTRL+C.
3. Klik cell C10.
4. Tekan CTRL+ALT+V untuk menampilkan kotak dialog Paste Special.

5. Pada kelompok Paste, pilih Validation.
6. Klik OK.

Dengan cell C10 masih terpilih, buka kotak dialog Data Validation. Perhatikan cell tersebut kini telah memiliki validasi yang sama dengan C2, tanpa mengikutkan format dan isi cell tersebut.

Lebih lanjut mengenai Paste Special Excel, monggo baca artikel di bawah ini.

Baca: Paste Special Excel.

Penutup

Demikian bahasan kami mengenai cara membuat data validation di Excel.

Validasi data adalah penting untuk menjaga kebersihan data dan memberikan hasil yang lebih akurat. Pada akhirnya, akan menentukan pengambilan keputusan yang mungkin penting.

Bila teman-teman ada pertanyaan atau masukan, silakan layangkan pada kolom komentar di bawah.