Tutorial Excel : Validasi Data Menambah Menggunakan & Menghapus

Berikut ini tutorial excel tentang cara validasi data dan bagaimana validasi untuk angka, tanggal atau nilai teks. Membuat daftar validasi data, menyalin validasi data ke sel lain, menemukan entri yang tidak valid, memperbaiki dan menghapus validasi data.

Saat anda menggunakan excel. Anda mungkin sering ingin mengontrol input informasi ke dalam sel tertentu untuk memastikan semua entri data akurat dan konsisten. Antara lain, Anda mungkin ingin mengizinkan hanya tipe data tertentu seperti angka atau tanggal dalam sel.

Atau membatasi angka ke rentang tertentu dan teks dengan panjang tertentu. Anda bahkan mungkin ingin memberikan daftar entri yang dapat diterima yang telah ditentukan sebelumnya untuk menghilangkan kemungkinan kesalahan. Validasi Data Excel memungkinkan Anda melakukan semua hal ini di semua versi Microsoft Excel 365, 2019, 2016, 20013, 2010 dan yang lebih rendah.

Pengertian validasi data

Validasi Data Excel adalah fitur yang membatasi (memvalidasi) input pengguna ke lembar kerja. Secara teknis, Anda membuat aturan validasi yang mengontrol jenis data apa yang dapat dimasukkan ke dalam sel tertentu.

Berikut adalah beberapa contoh validasi data Excel yang dapat dilakukan:

• Izinkan hanya nilai numerik atau teks dalam sel.
• Batasi entri ke pilihan dari daftar drop-down.
• Izinkan hanya angka dalam rentang yang ditentukan.
• Izinkan entri data dengan panjang tertentu.
• Batasi tanggal dan waktu di luar rentang tertentu .
• Validasi entri berdasarkan sel lain.
• Menampilkan pesan input saat pengguna memilih sel.
• Menampilkan pesan peringatan bila data yang dimasukkan salah.
• Temukan entri yang salah di sel yang divalidasi.

Misalnya, Anda dapat mengatur aturan yang membatasi entri data ke angka 4 digit antara 1000 dan 9999. Jika pengguna mengetik sesuatu yang berbeda, Excel akan menampilkan peringatan kesalahan yang menjelaskan kesalahan yang mereka lakukan:

Cara validasi data di excel

Untuk menambahkan validasi data di Excel, lakukan langkah-langkah berikut.

1. Buka kotak dialog Validasi Data

Pilih satu atau lebih sel untuk memvalidasi, pergi ke data tab> Alat Data kelompok, dan klik Validasi Data tombol.

Anda juga dapat membuka kotak dialog Validasi Data dengan menekan Alt > D > L, dengan setiap tombol ditekan secara terpisah.

2. Buat aturan validasi Excel

Pada tab Pengaturan , tentukan kriteria validasi sesuai dengan kebutuhan Anda. Dalam kriteria, Anda dapat memberikan salah satu dari berikut ini:

• Nilai – ketik angka di kotak kriteria seperti yang ditunjukkan pada gambar di bawah.
• Referensi sel – buat aturan berdasarkan nilai.
• Rumus – memungkinkan untuk mengekspresikan kondisi yang lebih komplek.

Sebagai contoh, mari kita buat aturan yang membatasi pengguna untuk memasukkan bilangan bulat antara 1000 dan 9999:

Dengan aturan validasi yang dikonfigurasi, klik OK untuk menutup jendela Validasi Data atau beralih ke tab lain untuk menambahkan pesan input atau/dan peringatan kesalahan.

3. Tambahkan pesan masukan (opsional)

Jika Anda ingin menampilkan pesan yang menjelaskan kepada pengguna data apa yang diizinkan dalam sel tertentu, buka tab Pesan Masukan dan lakukan hal berikut:

• Pastikan kotak Perlihatkan pesan input saat sel dipilih dicentang.
• Masukkan judul dan teks pesan Anda ke dalam bidang yang sesuai.
• Klik OK untuk menutup jendela dialog.

Segera setelah pengguna memilih sel yang divalidasi, pesan berikut akan muncul:

4. Menampilkan peringatan kesalahan (opsional)

Selain pesan input, Anda dapat menampilkan salah satu peringatan kesalahan berikut saat data yang tidak valid dimasukkan ke dalam sel.

Stop (default) :

Jenis peringatan paling ketat yang mencegah pengguna memasukkan data yang tidak valid. Anda klik Retry untuk mengetik nilai yang berbeda atau Batal untuk menghapus entri.

Warning

Memperingatkan pengguna bahwa data tidak valid, tetapi tidak mencegah memasukkannya.

Anda mengklik Yes untuk memasukkan entri yang tidak valid, No untuk mengeditnya, atau Cencel untuk menghapus entri.

Information

Jenis peringatan paling permisif yang hanya memberi tahu pengguna tentang entri data yang tidak valid.

Anda mengklik OK untuk memasukkan nilai yang tidak valid atau Cencel untuk menghapusnya dari sel.

Untuk mengonfigurasi pesan kesalahan khusus, buka tab Peringatan Kesalahan dan tentukan parameter berikut:

• Centang kotak Tampilkan peringatan kesalahan setelah data tidak valid dimasukkan (biasanya dipilih secara default).
• Di kotak Style , pilih jenis peringatan yang diinginkan.
• Masukkan judul dan teks pesan kesalahan ke dalam kotak yang sesuai.
• Klik Oke.

Dan sekarang, jika pengguna memasukkan data yang tidak valid, Excel akan menampilkan peringatan khusus yang menjelaskan kesalahan tersebut (seperti yang ditunjukkan di awal tutorial ini).

• Catatan. Jika Anda tidak mengetik pesan Anda sendiri, peringatan Berhenti default dengan teks berikut akan muncul: This value does not match the data validation restrictions defined for this cell.

Contoh validasi data excel

Saat menambahkan aturan validasi data di Excel, Anda bisa memilih salah satu pengaturan yang telah ditentukan sebelumnya atau menentukan kriteria kustom berdasarkan rumus validasi Anda sendiri. Di bawah ini kita akan membahas masing-masing opsi bawaan, dan minggu depan kita akan melihat lebih dekat rumus validasi data Excel dalam tutorial terpisah.

Seperti yang sudah Anda ketahui, kriteria validasi ditentukan pada tab Pengaturan pada kotak dialog Validasi Data ( tab Data > Validasi Data ).

Bilangan bulat dan desimal

Untuk membatasi entri data ke bilangan bulat atau desimal , pilih item yang sesuai di kotak Perbolehkan. Dan kemudian, pilih salah satu kriteria berikut di kotak Data :

• Sama dengan atau tidak sama dengan angka yang ditentukan
• Lebih besar dari atau kurang dari angka yang ditentukan
• Antara dua angka atau tidak di antara untuk mengecualikan rentang angka itu

Misalnya, ini adalah cara Anda membuat aturan validasi Excel yang memungkinkan bilangan bulat apa pun yang lebih besar dari 0:

Validasi tanggal dan waktu di Excel

Untuk memvalidasi tanggal, pilih Date dalam kotak Perbolehkan , lalu pilih kriteria yang sesuai dalam kotak Data. Ada cukup banyak opsi yang telah ditentukan untuk dipilih: hanya izinkan tanggal di antara dua tanggal, sama dengan, lebih besar dari atau kurang dari tanggal tertentu, dan banyak lagi.

Demikian pula, untuk memvalidasi waktu, pilih Time di kotak Perbolehkan , lalu tentukan kriteria yang diperlukan.

Misalnya, untuk mengizinkan hanya tanggal antara tanggal mulai di B1 dan tanggal akhir di B2, terapkan aturan validasi tanggal Excel ini:

Text length

Untuk mengizinkan entri data dengan panjang tertentu, pilih Panjang teks dalam kotak Allow, dan pilih kriteria validasi sesuai dengan logika bisnis Anda.

Misalnya, untuk membatasi input hingga 10 karakter, buat aturan ini:

Catatan. Teks Length pilihan membatasi jumlah karakter tapi bukan tipe data, yang berarti aturan di atas akan memungkinkan kedua teks dan angka di bawah 10 karakter atau 10 digit, masing-masing.

Excel data validation list (drop-down)

Untuk menambahkan daftar drop-down item ke sel atau grup sel, pilih sel target dan lakukan hal berikut:

1. Buka Data Validation kotak dialog ( data tab> Data Validation ).
2. Pada tab Setting, pilih List di kotak Allow.
3. Dalam kotak Source, ketik item daftar validasi Excel Anda, dipisahkan dengan koma. Misalnya, untuk membatasi input pengguna ke tiga pilihan, ketik Yes, No, N/A .
4. Pastikan kotak In-cell dropdown dipilih agar panah drop-down muncul di sebelah sel.
5. Klik Oke .

Daftar validasi data Excel yang dihasilkan akan terlihat seperti ini:

• Catatan. Harap berhati-hati dengan opsi Ignore blank, yang dipilih secara default. Jika Anda membuat daftar turun bawah berdasarkan rentang bernama yang memiliki setidaknya satu sel kosong, memilih kotak centang ini memungkinkan memasukkan nilai apa pun di sel yang divalidasi. Dalam banyak situasi, ini juga berlaku untuk rumus validasi: jika sel yang direferensikan dalam rumus kosong, nilai apa pun akan diizinkan di sel yang divalidasi.

Cara lain untuk membuat daftar validasi data di Excel

Menyediakan daftar yang dipisahkan koma secara langsung di kotak Sumber adalah cara tercepat yang bekerja dengan baik untuk dropdown kecil yang kemungkinan tidak akan pernah berubah. Dalam skenario lain, Anda dapat melanjutkan dengan salah satu cara berikut:

• Buat daftar validasi data dari rentang sel.
• Membuat daftar validasi data dinamis berdasarkan rentang bernama.
• Buat daftar validasi data Excel dari tabel. Yang terbaik adalah bahwa dropdown berbasis tabel bersifat dinamis dan diperbarui secara otomatis saat Anda menambahkan atau menghapus item dari tabel.

Cara mengedit validasi data di Excel

Untuk mengubah aturan validasi Excel, lakukan langkah-langkah berikut:

1. Pilih salah satu sel yang divalidasi.
2. Buka Data Validation kotak dialog ( data tab> Data Validation ).
3. Lakukan perubahan yang diperlukan.
4. Pilih kotak centang Apply these changes to all other cells with the same settings untuk menyalin perubahan yang Anda buat ke semua sel lain dengan kriteria validasi asli.
5. Klik OK untuk menyimpan perubahan.

Misalnya, Anda dapat mengedit daftar validasi data Excel dengan menambahkan atau menghapus item dari kotak Source, dan menerapkan perubahan ini ke semua sel lain yang berisi daftar turun bawah yang sama:

Cara menyalin aturan validasi data Excel ke sel lain

Jika Anda telah mengonfigurasi validasi data untuk satu sel dan ingin memvalidasi sel lain dengan kriteria yang sama, Anda tidak perlu membuat ulang aturan dari awal.

Untuk menyalin aturan validasi di Excel, lakukan 4 langkah cepat ini:

1. Pilih sel tempat aturan validasi berlaku dan tekan Ctrl + C untuk menyalinnya.
2. Pilih sel lain yang ingin Anda validasi. Untuk memilih sel yang tidak berdekatan, tekan dan tahan tombolCtrl kunci saat memilih sel.
3. Klik kanan pilihan, klik Paste Special, lalu pilih opsi Validation. Atau, tekan pintasan Tempel Spesial > Validasi : Ctrl + Alt + V, kemudian N.
4. Klik Oke .

• Tip. Alih-alih menyalin validasi data ke sel lain, Anda bisa mengonversi kumpulan data Anda ke tabel Excel . Saat Anda menambahkan lebih banyak baris ke tabel, Excel akan menerapkan aturan validasi Anda ke baris baru secara otomatis.

Cara menemukan sel dengan validasi data di Excel

Untuk menemukan semua sel yang divalidasi dengan cepat di lembar kerja saat ini, buka tab Home > Editing group, dan klik Find & Select > Data Validation:

Ini akan memilih semua sel yang memiliki aturan validasi data yang diterapkan padanya:

Cara menghapus validasi data di Excel

Secara keseluruhan, ada dua cara untuk menghapus validasi di Excel: pendekatan standar yang dirancang oleh Microsoft dan teknik bebas mouse yang dirancang oleh Geeks Excel yang tidak akan pernah melepaskan tangan mereka dari keyboard kecuali benar-benar diperlukan (misalnya untuk minum kopi 🙂

Metode 1: Cara biasa untuk menghapus validasi data

Biasanya, untuk menghapus validasi data di lembar kerja Excel, Anda melanjutkan dengan langkah-langkah berikut:

1. Pilih sel dengan validasi data.
2. Pada tab Data , klik tombol  Data Validation.
3. Pada tab Settings, klik tombol Clear All, lalu klik OK.

Kiat:

• Untuk menghapus validasi data dari semua sel pada lembar saat ini, gunakan fitur Temukan & Pilih untuk memilih semua sel yang divalidasi.
• Untuk menghapus aturan validasi data tertentu, pilih sel mana pun dengan aturan itu, buka jendela dialog  Data Validation, centang kotak Apply these changes to all other cells with the same settings, lalu klik tombol Clear All.

Seperti yang Anda lihat, metode standarnya cukup cepat tetapi membutuhkan beberapa klik mouse, sejauh yang saya ketahui bukan masalah besar. Tetapi jika Anda lebih suka bekerja dengan keyboard daripada mouse, Anda mungkin menemukan pendekatan berikut ini menarik.

Metode 2: Tempel Spesial untuk menghapus aturan validasi data

Secara de jure, Excel Paste Special dirancang untuk menempelkan elemen tertentu dari sel yang disalin. Secara de facto, ia dapat melakukan lebih banyak hal bermanfaat. Antara lain, dapat dengan cepat menghapus aturan validasi data di lembar kerja. Berikut caranya:

• Pilih sel kosong tanpa validasi data, dan tekan Ctrl + C untuk menyalinnya.
• Pilih sel yang ingin Anda hapus validasi datanya.
• tekan Ctrl + Alt + V, kemudian n, yang merupakan pintasan untuk Paste Special > Data Validation.
• tekan Enter. Selesai!

Kiat validasi data Excel

Sekarang setelah Anda mengetahui dasar-dasar validasi data di Excel, izinkan saya membagikan beberapa tips yang dapat membuat aturan Anda jauh lebih efektif.

Validasi data Excel berdasarkan sel lain

Alih-alih mengetik nilai secara langsung di kotak kriteria, Anda bisa memasukkannya ke dalam beberapa sel, lalu merujuk ke sel tersebut. Jika Anda memutuskan untuk mengubah kondisi validasi nanti, Anda cukup mengetikkan angka baru di lembar, tanpa harus mengedit aturan.

Untuk memasukkan referensi sel , ketikkan di kotak yang diawali dengan tanda sama dengan, atau klik panah di samping kotak, lalu pilih sel menggunakan mouse. Anda juga dapat mengklik di mana saja di dalam kotak, lalu memilih sel pada lembar.

Misalnya, untuk mengizinkan bilangan bulat apa pun selain nomor di A1, pilih yang tidak sama dengan kriteria di kotak Data dan ketik =$A$1 di kotak Value :

Untuk melangkah lebih jauh, Anda bisa memasukkan rumus di sel yang direferensikan, dan meminta Excel memvalidasi input berdasarkan rumus itu.

Misalnya, untuk membatasi pengguna memasukkan tanggal setelah tanggal hari ini, masukkan =TODAY() rumus di beberapa sel, misalnya B1, lalu siapkan aturan validasi tanggal berdasarkan sel tersebut:

Atau, Anda dapat memasukkan =TODAY() rumus secara langsung di kotak Start Date, yang akan memiliki efek yang sama.

Aturan validasi berbasis rumus

Dalam situasi ketika tidak mungkin untuk menentukan kriteria validasi yang diinginkan berdasarkan nilai atau referensi sel, Anda bisa mengekspresikannya menggunakan rumus.

Misalnya, untuk membatasi entri ke nilai minimum dan maksimum dalam daftar angka yang ada, katakanlah A1:A10, gunakan rumus berikut:

=MIN($A$1:$A$10)

=MAX($A$1:$A$10)

Harap perhatikan bahwa kami mengunci rentang dengan menggunakan tanda $ ( referensi sel absolut ) sehingga aturan validasi Excel kami berfungsi dengan benar untuk semua sel yang dipilih.

Bagaimana menemukan data yang tidak valid pada lembar?

Meskipun Microsoft Excel mengizinkan penerapan validasi data ke sel yang sudah memiliki data di dalamnya, itu tidak akan memberi tahu Anda jika beberapa nilai yang ada tidak memenuhi kriteria validasi.

Untuk menemukan data tidak valid yang masuk ke lembar kerja Anda sebelum Anda menambahkan validasi data, buka tab Data, dan click Data Validation > Circle Invalid Data.

Segera setelah Anda memperbaiki entri yang tidak valid, lingkaran akan hilang secara otomatis. Untuk menghapus semua lingkaran, buka tab Data , dan klik Data Validation > Clear Validation Circles.

Bagaimana melindungi lembar kerja dengan validasi data

Jika Anda ingin melindungi lembar kerja atau buku kerja dengan kata sandi, konfigurasikan pengaturan validasi data yang diinginkan terlebih dahulu, lalu lindungi lembar tersebut. Penting bagi Anda untuk membuka kunci sel yang divalidasi sebelum melindungi lembar kerja, jika tidak, pengguna Anda tidak akan dapat memasukkan data apa pun ke dalam sel tersebut. Untuk panduan terperinci, silakan lihat Cara membuka kunci sel tertentu pada lembar yang dilindungi.

Artikel lainnya yang mungkin anda suka : Cara print di excel dan settingnya.

Cara berbagi buku kerja dengan validasi data

Untuk mengizinkan beberapa pengguna berkolaborasi di buku kerja, pastikan untuk membagikan buku kerja setelah Anda melakukan validasi data. Setelah membagikan buku kerja, aturan validasi data Anda akan tetap berfungsi, tetapi Anda tidak akan dapat mengubahnya, atau menambahkan aturan baru.

Validasi Data Excel tidak berfungsi

Jika validasi data tidak berfungsi dengan benar di lembar kerja Anda, kemungkinan besar itu karena salah satu alasan berikut.

Validasi data tidak berfungsi untuk data yang disalin

Validasi data di Excel dirancang untuk melarang pengetikan data yang tidak valid secara langsung di dalam sel, tetapi tidak dapat menghentikan pengguna menyalin data yang tidak valid. Meskipun tidak ada cara untuk disable pintasan copy/paste (selain dengan menggunakan VBA), Anda setidaknya dapat mencegah penyalinan data dengan menyeret dan menjatuhkan sel. Untuk melakukannya, masuk ke File > Options > Advanced > Editing options, dan kosongkan kotak centang Enable fill handle and cell drag-and-drop .

Validasi data Excel tidak tersedia saat dalam mode edit sel

Perintah Validasi Data tidak tersedia (berwarna abu-abu) jika Anda memasukkan atau mengubah data dalam sel. Setelah Anda selesai mengedit sel, tekan Enter atau Esc untuk keluar dari mode edit, lalu lakukan validasi data.

Validasi data tidak dapat diterapkan ke buku kerja yang diproteksi atau dibagikan

Meskipun aturan validasi yang ada tetap berfungsi di buku kerja yang diproteksi dan dibagikan, tidak mungkin mengubah pengaturan validasi data atau menyiapkan aturan baru. Untuk melakukannya, batalkan berbagi dan/atau buka proteksi buku kerja Anda terlebih dahulu.

Rumus validasi data salah

Saat melakukan validasi data berbasis rumus di Excel, ada tiga hal penting yang harus diperiksa:

• Rumus validasi tidak mengembalikan kesalahan.
• Rumus tidak mereferensikan sel kosong.
• Referensi sel yang sesuai digunakan.

Perhitungan ulang manual diaktifkan

Jika mode Perhitungan Manual diaktifkan di Excel Anda, rumus yang tidak dihitung dapat mencegah data divalidasi dengan benar. Untuk mengubah opsi penghitungan Excel kembali ke otomatis, buka tab Formulas tab > Calculation, klik tombol Calculation Options, lalu klik Automatic.

Nah, itu adalah tutorial excel tentang bagaimana mem validasi data yang benar. Terimakasih sudah berkunjung ke artikel saya yang panjang ini.