Menerapkan validasi data ke sel

Anda dapat menggunakan validasi data untuk membatasi tipe data atau nilai yang dimasukkan pengguna ke sel. Salah satu penggunaan validasi data yang paling umum adalah untuk membuat daftar menurun. Lihat video berikut tentang gambaran umum validasi data cepat.

Mengunduh contoh kami

Anda dapat mengunduh buku kerja contoh dengan seluruh contoh validasi data yang ditemukan dalam artikel ini. Anda dapat mengikuti, atau membuat skenario validasi data sendiri.

Unduh contoh validasi data Excel

Menambahkan validasi data ke sel atau rentang

Catatan: Tiga langkah pertama dalam bagian ini adalah cara untuk menambahkan tipe validasi data apa pun. Langkah 4-8 adalah cara yang dikhususkan untuk membuat Daftar menurun.

  1. Pilih satu atau beberapa sel untuk divalidasi.

  2. Di tab Data, di grup Alat Data, klik Validasi Data.

    Validasi Data terletak di tab Data, grup Peralatan Data
  3. Di tab Pengaturan, dalam kotak Perbolehkan, pilih Daftar.

    Tab pengaturan dalam kotak dialog Validasi Data
  4. Di kotak Sumber, ketikkan nilai daftar Anda, yang dipisahkan dengan koma. Sebagai contoh:

    1. Untuk membatasi jawaban menjadi dua pilihan ("Apakah Anda memiliki anak?" sebagai contoh), ketikkan Ya,Tidak.

    2. Untuk membatasi reputasi kualitas vendor menjadi tiga peringkat, ketik Rendah,Rata-Rata,Tinggi.

      Catatan: Langkah-langkah ini pada umumnya hanya disarankan untuk item daftar yang mungkin tidak akan berubah. Jika memiliki daftar yang dapat berubah, atau jika perlu menambahkan atau menghapus item dari waktu ke waktu, lebih baik Anda mengikuti langkah Praktik Terbaik berikut.

      Praktik Terbaik: Anda juga dapat membuat entri daftar dengan mengacu ke rentang sel di mana pun dalam buku kerja. Cara paling efisien adalah dengan membuat daftar, kemudian memformatnya sebagai Tabel Excel (dari tab Beranda pilih Gaya > Format sebagai Tabel > pilih Gaya Tabel yang paling sesuai dengan Anda. Berikutnya, pilih Rentang Isi Data tabel, yang merupakan bagian dari tabel yang hanya berisi daftar Anda, bukan Header tabel (dalam hal ini adalah Departemen), lalu berikan nama yang bermakna dalam Kotak Nama di atas kolom A.

      Masukkan nama yang bermakna untuk daftar tersebut dalam kotak nama

    Kini, sebagai ganti dari mengetikkan nilai daftar di kotak Sumber validasi data, tambahkan nama yang baru saja Anda tentukan, didahului dengan tanda Sama dengan (=).

    Awali nama tabel dengan tanda =

    Hal terbaik dalam menggunakan tabel adalah ketika Anda menambahkan atau menghapus item dari daftar, daftar validasi data akan diperbarui secara otomatis.

    Catatan: Lebih baik jika meletakkan daftar di lembar kerja terpisah (tersembunyi jika perlu) sehingga tidak ada orang yang dapat mengeditnya.

  5. Pastikan kotak Menu turun bawah dalam sel tercentang. Jika tidak, Anda tidak akan dapat melihat panah turun bawah di dekat sel.

    Daftar menurun dalam sel muncul di samping sel
  6. Untuk menentukan cara yang diinginkan guna menangani nilai kosong (null), pilih atau hapus kotak centang Abaikan kosong.

    Catatan: Jika nilai yang Anda perbolehkan didasarkan pada rentang sel yang memiliki nama yang ditetapkan dan ada sel kosong di suatu sel di rentang tersebut, mencentang kotak Abaikan kosong memungkinkan nilai apa pun dimasukkan di sel yang divalidasi. Ini juga berlaku untuk sel apa pun yang direferensi oleh rumus validasi: jika sel yang direferensi kosong, mencentang kotak Abaikan kosong memungkinkan nilai apa pun dimasukkan di sel yang divalidasi.

  7. Uji validasi data untuk memastikan validasi bekerja dengan benar. Coba masukkan data yang valid dan tidak valid di sel untuk memastikan pengaturan Anda bekerja sesuai keinginan dan pesan muncul saat Anda mengharapkannya.

Catatan: 

  • Setelah membuat daftar menurun, pastikan daftar bekerja seperti yang diinginkan. Sebagai contoh, Anda mungkin ingin memeriksa jika sel sudah cukup lebar untuk memperlihatkan semua entri.

  • Jika daftar entri untuk daftar turun bawah Anda ada pada lembar kerja lain dan Anda ingin mencegah pengguna melihatnya atau mengubahnya, pertimbangkan menyembunyikan dan memproteksi lembar kerja itu. Untuk informasi lebih lanjut tentang cara memproteksi lembar kerja, baca Mengunci sel untuk memproteksinya.

  • Menghapus validasi data - Pilih satu atau beberapa sel yang berisi validasi yang ingin Anda hapus, kemudian masuk ke Data > Validasi Data lalu dalam dialog validasi data tekan tombol HapusSemua, kemudian OK.

Tabel berikut ini mencantumkan tipe validasi data lain dan memperlihatkan cara menambahkannya ke lembar kerja.

Untuk melakukan ini:

Ikuti langkah-langkah ini:

Batasi entri data ke bilangan bulat dalam batasan.

  1. Ikuti langkah 1-3 dalam Menambahkan validasi data ke sel atau rentang di atas.

  2. Dari daftar Perbolehkan, pilih Angka bulat.

  3. Di kotak Data, pilih tipe batasan yang Anda inginkan. Misalnya, untuk menetapkan batas atas dan bawah, pilih between.

  4. Masukkan nilai minimum, maksimum, atau tertentu untuk memperbolehkan.

    Kotak dialog kriteria validasi

    Anda juga dapat memasukkan rumus yang mengembalikan nilai angka.

    Misalnya, Anda memvalidasi data di sel F1. Untuk menetapkan batas minimum pengurangan hingga dua kali jumlah anak di sel tersebut, pilih lebih besar atau sama dengan di kotak Data dan masukkan rumusnya, =2*F1, di kotak Minimum.

Batasi entri data ke angka desimal dalam batasan.

  1. Ikuti langkah 1-3 dalam Menambahkan validasi data ke sel atau rentang di atas.

  2. Di kotak Perbolehkan, pilih Desimal.

  3. Di kotak Data, pilih tipe batasan yang Anda inginkan. Misalnya, untuk menetapkan batas atas dan bawah, pilih between.

  4. Masukkan nilai minimum, maksimum, atau tertentu untuk memperbolehkan.

    Anda juga dapat memasukkan rumus yang mengembalikan nilai angka. Sebagai contoh, untuk mengatur batasan maksimum untuk komisi dan bonus 6% dari gaji staf penjualan dalam sel E1, pilih kurang dari atau sama dengan dalam kotak Data dan masukkan rumus, =E1*6%, di kotak Maksimum.

    Catatan: Agar pengguna dapat memasukkan persentase, misalnya 20%, pilih Desimal di kotak Perbolehkan, pilih tipe pembatasan yang Anda inginkan di kotak Data, masukkan nilai minimum, maksimum, atau spesifik sebagai desimal, misalnya 0,2, lalu tampilkan sel validasi data sebagai persentase dengan memilih sel tersebut dan mengklik Gaya Persen Gambar tombol di grup Angka pada tab Beranda.

Batasi entri data ke tanggal dalam rentang tanggal.

  1. Ikuti langkah 1-3 dalam Menambahkan validasi data ke sel atau rentang di atas.

  2. Di kotak Perbolehkan, pilih Tanggal.

  3. Di kotak Data, pilih tipe batasan yang Anda inginkan. Misalnya, untuk memungkinkan tanggal setelah hari tertentu, pilih lebih dari.

  4. Masukkan tanggal mulai, akhir, atau tertentu untuk memperbolehkan.

    Anda juga dapat memasukkan rumus yang mengembalikan tanggal. Misalnya, untuk mengatur jangka waktu antara tanggal hari ini dan 3 hari dari tanggal hari ini, pilih antara dalam kotak Data, masukkan =HARI INI() dalam kotak Tanggal mulai, dan masukkan =HARI INI ()+3 dalam kotak Tanggal selesai.

    Pengaturan kriteria validasi untuk membatasi entri tanggal ke jangka waktu tertentu

Batasi entri data ke waktu dalam jangka waktu.

  1. Ikuti langkah 1-3 dalam Menambahkan validasi data ke sel atau rentang di atas.

  2. Di kotak Perbolehkan, pilih Waktu.

  3. Di kotak Data, pilih tipe batasan yang Anda inginkan. Misalnya, untuk memungkinkan waktu sebelum waktu tertentu, pilih kurang dari.

  4. Masukkan waktu awal, akhir, atau waktu tertentu yang akan diperbolehkan. Jika Anda ingin memasukkan waktu spesifik, gunakan format waktu hh:mm.

    Sebagai contoh, Anda memiliki sel E2 yang disiapkan dengan waktu mulai (08.00), dan sel F2 dengan waktu selesai (17.00), dan ingin membatasi waktu rapat antara waktu tersebut, lalu pilih antara di kotak Data, masukkan =E2 di kotak Waktu mulai, kemudian masukkan =F2 di kotak Waktu selesai.

    Pengaturan validasi untuk membatasi entri waktu dalam jangka waktu

Batasi entri data ke teks dengan panjang yang ditentukan.

  1. Ikuti langkah 1-3 dalam Menambahkan validasi data ke sel atau rentang di atas.

  2. Di kotak Perbolehkan, pilih Panjang Teks.

  3. Di kotak Data, pilih tipe batasan yang Anda inginkan. Misalnya, untuk memungkinkan hingga jumlah karakter tertentu, pilih kurang dari atau sama dengan.

  4. Dalam hal ini, kami ingin membatasi entri menjadi 25 karakter, jadi pilih kurang dari atau sama dengan dalam kotak Data lalu masukkan 25 di kotak Maksimum.

    Contoh Validasi Data dengan panjang teks terbatas

Hitung hal yang diperbolehkan berdasarkan konten sel lain.

  1. Ikuti langkah 1-3 dalam Menambahkan validasi data ke sel atau rentang di atas. Dalam kotak Perbolehkan pilih tipe data yang Anda inginkan.

  2. Di kotak Data, pilih tipe batasan yang Anda inginkan.

  3. Di kotak atau beberapa kotak di bawah kotak Data, klik sel yang ingin Anda gunakan untuk menetapkan apa saja yang diperbolehkan.

    Sebagai contoh, untuk memperbolehkan entri bagi akun hanya jika hasil tidak melebihi anggaran di sel E1, pilih Perbolehkan >Bilangan bulat, Data, kurang dari atau sama dengan, dan Maksimum >= E1.

    Pengaturan validasi untuk menghitung berdasarkan konten sel yang lain

Catatan: Contoh berikut ini menggunakan opsi Kustom untuk menulis rumus guna mengatur kondisi Anda. Tidak perlu mengkhawatirkan apa yang diperlihatkan kotak Data, karena hal tersebut dinonaktifkan dengan opsi Kustom.

Untuk memastikan bahwa

Masukkan rumus ini

Sel yang berisi ID produk (C2) selalu dimulai dengan prefiks standar dari "ID-" dan setidaknya memiliki panjang karakter 10 (lebih besar dari 9).

=AND(KIRI(C2, 3) ="ID-",LEN(C2) > 9)

Contoh 6: Rumus dalam validasi data

Sel yang berisi nama produk (D2) hanya berisi teks.

=ISTEXT(D2)

Contoh 2: Rumus dalam validasi data

Sel yang berisi ulang tahun seseorang (B6) harus lebih besar daripada jumlah tahun yang ditetapkan di sel B4.

=IF(B6<=(HARI INI()-(365*B4)),BENAR,SALAH)

Contoh validasi data untuk membatasi entri ke umur minimum

Semua data dalam rentang sel A2:A10 berisi nilai yang unik.

=COUNTIF($A$2:$A$10,A2)=1

Contoh 4: Rumus dalam validasi data

Catatan: Anda harus memasukkan rumus validasi data untuk sel A2 terlebih dahulu, kemudian menyalin A2 ke A3:A10 sehingga argumen kedua untuk COUNTIF akan cocok dengan sel saat ini. Maka bagian A2)=1 akan berubah menjadi A3)=1, A4)=1 dan seterusnya.

Untuk informasi selengkapnya

Pastikan entri alamat email di sel B4 berisi simbol @.

=ISUMBER(FIND("@",B4)

Contoh validasi data yang memastikan alamat email berisi simbol @

  • Mengapa perintah Validasi Data tidak diaktifkan di pita? Perintah mungkin tidak tersedia karena:

    • Tabel Microsoft Excel mungkin ditautkan ke situs SharePoint Anda tidak dapat menambahkan validasi data ke tabel Excel yang ditautkan ke situs SharePoint. Untuk menambahkan validasi data, tautan tabel Excel harus dilepaskan atau konversikan tabel Excel menjadi rentang.

    • Anda mungkin sedang memasukkan data Perintah Validasi Data tidak diaktifkan di tab Data ketika memasukkan data dalam sel. Untuk mengakhiri pemasukan data, tekan ENTER atau ESC.

    • Lembar kerja mungkin diproteksi atau dibagikan Anda tidak dapat mengubah pengaturan validasi data jika buku kerja dibagikan atau diproteksi. Untuk mempelajari cara berhenti berbagi atau memproteksi buku kerja, lihat Memproteksi buku kerja.

  • Dapatkah saya mengubah ukuran font?</legacyBold> Tidak, ukuran font telah ditentukan. Satu-satunya cara untuk mengubah ukuran tampilan adalah dengan menyesuaikan zoom layar Anda di sudut kanan bawah jendela Excel. Namun, Anda dapat menggunakan Kotak Kombo Activex. Lihat Menambahkan kotak daftar atau kotak kombo ke lembar kerja.

  • Apakah ada cara agar validasi data saya menjadi isi otomatis atau pilih otomatis ketika saya mengetik? Tidak, namun jika menggunakan Kotak Kombo ActiveX Anda memiliki fungsi tersebut.

  • Dapatkah saya membuat beberapa pilihan di daftar validasi data? Tidak kecuali Anda menggunakan Kombo ActiveX atau Kotak Daftar.

  • Dapatkah saya memilih item di daftar validasi data dan mengaturnya untuk mengumpulkan daftar lain? Ya! Hal ini disebut Validasi Data Dependen. Untuk informasi selengkapnya, lihat Membuat Daftar Menurun Dependen.

  • Bagaimana cara menghapus semua validasi data di lembar kerja? Anda dapat menggunakan dialog Masuk Ke > Khusus. Di tab Beranda > Pengeditan > Temukan & Pilih (atau tekan F5 atau Ctrl+G di keyboard), kemudian Khusus > Validasi data lalu pilih baik Semua (untuk menemukan semua sel dengan validasi data) maupun Sama (untuk menemukan sel yang cocok dengan pengaturan validasi data tertentu).

    Kotak dialog Masuk Ke Khusus

    Selanjutnya munculkan dialog validasi data (Tab Data > Validasi Data), tekan tombol Hapus Semua, kemudian OK.

  • Dapatkah saya memaksa seseorang untuk membuat entri di sel dengan validasi data? Tidak, namun Anda dapat menggunakan VBA (Visual Basic for Applications) guna memeriksa jika seseorang telah membuat entri dalam kondisi tertentu, seperti sebelumnya mereka Menyimpan atau Menutup Buku Kerja. Jika mereka belum menentukan pilihan, Anda dapat membatalkan acara tersebut dan tidak mengizinkan mereka untuk melanjutkan hingga pilihan telah ditentukan.

  • Bagaimana saya dapat memberi warna sel berdasarkan pilihan daftar validasi data? Anda dapat menggunakan Pemformatan Bersyarat. Dalam hal ini Anda mungkin ingin menggunakan Hanya format sel yang berisi opsi.

    Hanya memformat sel yang berisi opsi
  • Bagaimana cara memvalidasi alamat email? Anda dapat menggunakan metode Kustom > Rumus lalu memeriksa apakah simbol @ ada di entri. Dalam hal ini, rumus yang digunakan adalah =ISNUMBER(FIND("@",D2)). Fungsi FIND mencari simbol @, dan jika ditemukan, posisi numerik dalam string teks akan dikembalikan dan entri akan diperbolehkan. Jika tidak ditemukan, FIND akan mengembalikan kesalahan dan mencegah entri.

Apakah Anda memiliki pertanyaan tentang fungsi tertentu?

Posting pertanyaan dalam forum komunitas Excel

Bantu kami meningkatkan Excel

Memiliki saran tentang bagaimana kami dapat meningkatkan Excel versi berikutnya? Jika demikian, silakan lihat topik-topik di Suara Pengguna Excel.

Lihat juga

Selengkapnya tentang validasi data

Video: Membuat dan mengelola daftar menurun

Menambahkan atau menghapus item dari daftar menurun

Menghapus daftar menurun

Kembangkan keterampilan Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×