Apa Itu Validasi Data di Excel? Panduan Komprehensif untuk Menambahkan, Mengedit, dan Menghapus Aturan Validasi Data di Excel

Memahami dan menerapkan prinsip validasi data sangat penting bagi analis data pemula maupun spesialis berpengalaman. Dalam tutorial ini, kita akan mempelajari cara melakukan validasi data di Excel. Kita akan membahas berbagai teknik validasi data yang tersedia, serta mendemonstrasikan cara menambahkan, mengedit, dan menghapus aturan validasi data.

Jika Anda ingin meninjau dasar-dasar Excel sebelum memulai, termasuk rumus utama, operator, fungsi matematika, dan lainnya, jelajahi Cheat Sheet Rumus Excel kami.

Apa Itu Validasi Data di Excel?

Validasi data di Excel merujuk pada fitur khusus yang mengendalikan nilai yang dapat dimasukkan ke dalam sebuah sel. Fitur ini menawarkan beragam teknik yang dapat digunakan untuk memeriksa nilai yang sudah ada atau membuat aturan yang akan diterapkan pada nilai-nilai baru.

Untuk menemukan fitur Data Validation di Excel, ikuti langkah-langkah berikut:

1. Buka tab Data.
2. Masuk ke grup Data Tools.
3. Klik tombol Data Validation.

Membuka fitur Data Validation

Jendela pop-up berikut akan muncul di layar.

Jendela pop-up Data Validation

Nanti dalam tutorial ini, kita akan mempelajari lebih detail cara menyiapkan jendela ini, termasuk cara memodifikasi dan menghapus aturan validasi data. Pertama, mari kita pelajari lebih lanjut tentang validasi data.

Mengapa Validasi Data Penting?

Validasi data adalah proses memeriksa keakuratan dan konsistensi data yang telah atau akan dimasukkan ke dalam basis data untuk penyimpanan dan pemrosesan lebih lanjut. Dengan validasi data, kita memastikan bahwa data sesuai dengan kriteria tertentu yang telah ditetapkan. Kriteria ini dapat mencakup jenis data yang benar, nilai dalam rentang tertentu, dan elemen data dengan panjang yang sesuai.

Validasi data yang kuat wajib dimiliki karena banyak alasan. Ini melindungi dari kesalahan entri data dan memastikan bahwa informasi yang digunakan dalam analisis dapat diandalkan dan valid. Mari kita lihat lebih dekat alasan utama dilakukannya validasi data.

Kualitas data

Validasi data mencegah nilai yang tidak akurat atau tidak konsisten disimpan di basis data. Ini mengurangi kesalahan di kemudian hari, meningkatkan kualitas data secara keseluruhan, dan meningkatkan kebermaknaan analisis data.

Pentingnya memiliki data berkualitas tinggi tersarikan dengan baik dalam konsep GIGO, yang populer dalam ilmu komputer dan merupakan singkatan dari garbage-in dan garbage-out. Intinya, menganalisis data yang cacat akan menghasilkan temuan yang keliru dan menyesatkan.

Mari kita lihat beberapa contoh ketika aturan validasi data tidak diterapkan dan data kita berisi nilai-nilai yang janggal.

• Catatan Universitas: Sebuah universitas tidak bisa memiliki jumlah mahasiswa dalam bentuk desimal; hanya bilangan bulat yang valid.
• Rekam Medis: Suhu tubuh maksimum seseorang tidak mungkin lebih tinggi dari 50°C.
• Entri Usia: Kolom usia seharusnya hanya menerima nilai yang realistis, umumnya antara 0 dan 120.
• Alamat Email: Kolom email harus mengikuti format tertentu yang mencakup simbol '@'.
• Jumlah Persediaan: Kuantitas produk dalam sistem inventaris tidak boleh bernilai negatif.

Ingat bahwa meskipun validasi data memastikan integritas data, hal ini tidak menjamin kebenaran absolut. Misalnya, dalam tabel untuk kucing dan anjing, aturan validasi dapat memastikan hanya entri "cat" atau "dog" yang diizinkan, tetapi tidak bisa mencegah kucing dilabeli sebagai anjing karena kesalahan.

Integritas data dan kepatuhan

Validasi data sangat penting untuk menjaga integritas data dan memastikan kepatuhan terhadap regulasi serta standar industri.

Di bidang kesehatan, kepatuhan terhadap HIPAA wajib untuk melindungi informasi pasien. Demikian pula, bisnis yang beroperasi di Uni Eropa harus mematuhi GDPR guna menjamin privasi dan keamanan data individu.

Pembersihan dan pra-pemrosesan data

Prinsip yang terkenal menyatakan bahwa analis data menghabiskan sekitar 80% waktunya untuk menyiapkan data agar siap digunakan dan hanya 20% untuk analisis itu sendiri. Karena analis data sering menghabiskan porsi waktu yang tidak seimbang untuk membersihkan dan memroses data, aturan validasi data yang diterapkan lebih awal dalam alur kerja dapat menghemat banyak waktu.

Pengambilan keputusan

Menganalisis data yang telah divalidasi menghasilkan temuan yang andal dan bermakna yang pada gilirannya membantu membuat keputusan berbasis data yang lebih tepat. Dalam jangka panjang, validasi data menghemat waktu dan biaya perusahaan serta menghasilkan bisnis yang lebih sehat, efisien, dan menguntungkan.

Berbagai Teknik Validasi Data di Excel

Excel memiliki berbagai pendekatan validasi data untuk memenuhi beragam kebutuhan. Untuk kemudahan, mari kita bagi ke dalam beberapa kelompok dan mengkarakterisasi masing-masing.

Membatasi jenis data dan rentang/panjang

Di Excel, Anda dapat memilih dari jenis data Excel berikut: Whole number, Decimal, Date, Time, dan Text.

Sekarang mari kita buat sebuah tabel untuk melihat penerapan kondisi logis tertentu pada berbagai jenis data kita. Kondisi logis tersebut mencakup: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, dan less than or equal to.

• Between dan Not Between: Kondisi ini umumnya digunakan pada nilai numerik, tanggal, dan waktu. Saat diterapkan pada text, between dan not between merujuk pada panjang, atau jumlah karakter, dalam sebuah sel.

• Equal to dan Not Equal to: Dapat diterapkan pada semua jenis data. Untuk text, kondisi ini memeriksa kecocokan persis.

• Greater Than, Less Than, Greater Than or Equal To, dan Less Than or Equal To: Kondisi ini cocok untuk jenis data whole number, date, dan time, tetapi tidak langsung berlaku untuk text kecuali jika mempertimbangkan panjang teks atau, dalam kasus tertentu, urutan alfabet.

Membuat daftar drop-down

Terkadang, perlu membatasi opsi di sel Excel tertentu ke sekumpulan nilai yang telah ditentukan. Mari kita pertimbangkan contoh sebelumnya di mana hanya kucing dan anjing yang merupakan entri valid dalam sebuah tabel. Cara terbaik untuk membuat aturan validasi data guna mencegah buaya dan hewan lain masuk ke tabel kita adalah dengan membuat daftar drop-down untuk setiap sel yang relevan. Mari kita telusuri langkah-langkah detailnya.

1. Masukkan item yang ingin Anda tampilkan dalam daftar drop-down Anda. Anda juga dapat mengurutkan item dalam daftar dengan memilih seluruh daftar, klik kanan, pilih Sort, dan pilih metodenya.

Memasukkan item untuk daftar drop-down

2. Konversikan daftar Anda menjadi tabel Excel. Pilih sel mana pun dalam rentangnya dan tekan Ctrl+T. Langkah ini penting karena jika Anda menambah atau menghapus item dari daftar, semua daftar drop-down yang bergantung akan diperbarui secara otomatis.

Mengonversi daftar untuk drop-down menjadi tabel

3. Pilih sel atau beberapa sel Excel di lembar kerja tempat Anda ingin menambahkan daftar drop-down.
4. Buka jendela pop-up Data Validation. Buka tab Data. Masuk ke grup Data Tools dan klik tombol Data Validation.
5. Pada tab Settings, di kolom Allow, pilih List. Biarkan kotak In-cell dropdown tetap dicentang. Biarkan kotak Ignore blank tetap dicentang jika sel Excel tempat Anda ingin menambahkan daftar drop-down boleh dibiarkan kosong; jika tidak, hapus centangnya:

Menetapkan daftar drop-down sebagai metode validasi data

6. Letakkan kursor di kotak Source, pilih rentang daftar Anda, lalu tekan OK. Anda perlu menghilangkan baris header agar tidak muncul sebagai opsi pilihan.

Memilih sumber untuk daftar drop-down

7. Pilih sel atau beberapa sel Excel yang telah Anda buatkan validasi data ini. Sekarang Anda akan melihat segitiga drop-down di sebelah kanan sel tersebut. Setelah mengklik segitiga itu, Anda dapat memilih nilai yang diperlukan untuk sel tersebut.

Membuka daftar drop-down di sebuah sel

Menerapkan rumus kustom

• range: Di sini kita merujuk pada rentang sel Excel tempat kita ingin menerapkan validasi data berbasis rumus kustom.
• uppermost_cell: Di sini kita merujuk pada sel pertama, atau yang paling atas, dalam rentang kita.

Hanya angka atau hanya teks

Tujuan dari dua rumus berikut adalah untuk memverifikasi apakah konten sel tertentu berupa angka atau teks. Ini berguna saat Anda perlu membedakan antara nilai numerik dan non-numerik, atau membedakan antara teks dan non-teks.

=ISNUMBER(uppermost_cell)Didukung oleh

=ISTEXT(uppermost_cell)Didukung oleh

Hanya teks huruf besar atau huruf kecil

=AND(EXACT(uppermost_cell, UPPER(uppermost_cell)), ISTEXT(uppermost_cell))Didukung oleh

=AND(EXACT(uppermost_cell, LOWER(uppermost_cell)), ISTEXT(uppermost_cell))Didukung oleh

Tanpa duplikasi

=COUNTIF(range, uppermost_cell)<=1Didukung oleh

Hanya nilai yang diawali substring tertentu

Rumus berikut memeriksa apakah n karakter pertama dari sebuah string sesuai secara persis. Rumus ini akan mengembalikan nilai TRUE atau FALSE.

=EXACT(LEFT(uppermost_cell, n), substring)Didukung oleh

Hanya hari kerja atau akhir pekan

=WEEKDAY(uppermost_cell, 2)<=5Didukung oleh

=WEEKDAY(uppermost_cell, 2)>5Didukung oleh

Hanya tanggal yang sudah lewat

=uppermost_cell<TODAY()Didukung oleh

Tanpa spasi awal atau akhir

=uppermost_cell=TRIM(uppermost_cell)Didukung oleh

Tanpa spasi

=uppermost_cell=SUBSTITUTE(uppermost_cell, " ", "")Didukung oleh

Mencegah melebihi total

=SUM(range)<=total_cellDidukung oleh

Untuk cepat meningkatkan keahlian Anda dengan rumus di Excel, pertimbangkan untuk mengikuti kursus Data Analysis in Excel yang komprehensif dan berorientasi praktik.

Cara Menggunakan Validasi Data di Excel

Saya membagi bagian ini menjadi dua subbagian untuk kejelasan. Subbagian pertama akan menunjukkan cara menyiapkan workbook Anda untuk memasukkan aturan validasi data. Bagian kedua akan menjelaskan cara menerapkan aturan ini pada sel yang sudah berisi data. Mari kita lihat.

Cara mengonfigurasi workbook Excel untuk aturan validasi data

1. Pilih sel atau beberapa sel Excel tempat Anda menginginkan validasi data.
2. Buka jendela pop-up Data Validation. Buka tab Data. Masuk ke grup Data Tools dan klik tombol Data Validation.
3. Pada tab Settings, di kolom Allow, pilih metode yang diperlukan, yang secara default disetel ke Any value (berarti tanpa validasi data).

Memilih metode yang diperlukan

4. Biarkan kotak Ignore blank tetap dicentang jika sel Excel tempat Anda ingin melakukan validasi data boleh dibiarkan kosong; jika tidak, hapus centangnya.

Menangani sel kosong

5. Jika Anda memilih metode List, pilih aturan yang sudah ditentukan dan relevan dengan jenis data Anda. Jika Anda memilih metode Custom, masukkan rumus kustom.

Memilih kondisi logis untuk membatasi rentang/panjang data

Memasukkan batas bawah dan atas untuk membatasi rentang/panjang data

Menerapkan rumus kustom

6. Pada tab Input Message, Anda dapat menetapkan Title dan Input message. Secara default, tidak ada pesan masukan.

Mengatur pesan masukan untuk memandu pengguna nilai yang tepat

Memeriksa tampilan pesan masukan

7. Pada tab Error Alert, Anda dapat menetapkan Style (Stop, Warning, atau Information), Title, dan Error message untuk pesan kesalahan. Secara default, pesan kesalahan berjenis Stop.

Mengatur pesan kesalahan

Setelah mengklik OK, Anda dapat menguji apakah ini berfungsi dengan memilih sel Excel kosong mana pun yang menerapkan validasi data ini dan memasukkan nilai yang tidak valid. Sebagai perbandingan, berikut contoh pesan kesalahan di masing-masing gaya.

Mendapatkan pesan kesalahan gaya Stop saat memasukkan nilai tidak valid

Mendapatkan pesan kesalahan gaya Warning saat memasukkan nilai tidak valid

Mendapatkan pesan kesalahan gaya Information saat memasukkan nilai tidak valid

Pesan kesalahan default saat memasukkan nilai tidak valid

Catatan: Pesan kesalahan dengan gaya Warning atau Information tidak mencegah orang memasukkan data yang tidak valid, tetapi pesan kesalahan dengan gaya Stop akan mencegahnya.

Cara menggunakan validasi data di Excel pada sel yang sudah terisi

Bagaimana jika beberapa sel Excel tempat Anda ingin melakukan validasi data sudah diisi dengan nilai? Fitur Data Validation memungkinkan validasi data saat entri maupun setelah entri.

1. Pilih sel Excel tempat Anda ingin melakukan validasi data. Beberapa sel tersebut sudah berisi nilai.

Memilih sel untuk validasi data, termasuk yang sudah berisi nilai

2. Buka jendela pop-up Data Validation. Buka tab Data. Masuk ke grup Data Tools dan buka tombol Data Validation.
3. Pada tab Settings, di kolom Allow, pilih metode yang diperlukan, yang secara default disetel ke Any value.

Menetapkan validasi data untuk sel yang dipilih

3. Pilih salah satu sel Excel dari sel yang telah Anda terapkan validasi data ini, buka tab Data, klik panah drop-down di sebelah kanan tombol Data Validation pada grup Data Tools, lalu pilih Circle Invalid Data.

Melingkari data tidak valid di Excel

Data tidak valid yang dilingkari di Excel

4. Sekarang Anda dapat memodifikasi setiap nilai yang tidak valid agar memenuhi kriteria yang ditetapkan. Setelah melakukan ini, lingkaran di sekitar nilai tersebut akan hilang secara otomatis. Penting untuk diingat bahwa jika Anda hanya menghapus nilai yang tidak valid, lingkaran akan tetap ada.
5. Untuk menghapus lingkaran validasi, pilih salah satu sel Excel dari sel yang telah Anda terapkan validasi data ini, buka tab Data, masuk ke grup Data Tools, buka tombol Data Validation, lalu pilih Clear Validation Circles.

Menghapus lingkaran validasi dari data tidak valid

Cara Mengedit Validasi Data di Excel

1. Pilih salah satu sel Excel yang sudah diterapkan validasi data.
2. Buka jendela pop-up Data Validation. Buka tab Data. Masuk ke grup Data Tools dan klik tombol Data Validation.
3. Lakukan modifikasi yang Anda perlukan pada tab mana pun di jendela Data Validation. Jika perlu, tinjau kembali instruksi dari bagian Cara Menggunakan Validasi Data di Excel.
4. Buka tab Settings dan centang Apply these changes to all other cells with the same settings.
5. Tekan OK.

Menerapkan modifikasi validasi data ke semua sel lain dengan pengaturan yang sama

Cara Menghapus Validasi Data di Excel

1. Pilih salah satu sel Excel yang sudah diterapkan validasi data.
2. Buka jendela pop-up Data Validation. Buka tab Data. Masuk ke grup Data Tools dan klik tombol Data Validation.
3. Buka tab Settings dan centang Apply these changes to all other cells with the same settings.
4. Klik tombol Clear All.
5. Tekan OK.

Menghapus validasi data

Kesimpulan

Tutorial ini membahas definisi validasi data, manfaatnya, implementasinya di Excel, berbagai teknik yang tersedia, serta cara menggunakan, mengedit, dan menghapus validasi data di Excel.

Jika Anda ingin mengoptimalkan kemampuan Excel dan mempelajari cara menyiapkan, memvisualisasikan, dan menganalisis data melalui latihan praktik, sekaligus menemukan beberapa fitur lanjutan, cobalah jalur keterampilan komprehensif dan ramah pemula Excel Fundamentals. Pertimbangkan juga kursus khusus seperti Data Preparation in Excel atau Financial Modeling in Excel yang menawarkan pelatihan terfokus untuk memperdalam keahlian Anda. Sumber daya ini akan membantu Anda berkembang dari validasi data dasar hingga menguasai fitur-fitur Excel tingkat lanjut.