Cara menggunakan data validation list excel

Drop down list merupakan fitur excel yang fungsinya untuk mempermudah input data. Umumnya berisi beberapa pilihan data dan pengguna hanya boleh memilih salah satu dari list tersebut. Cara membuat drop down list di excel juga sangat mudah, hanya perlu beberapa langkah seperti dijabarkan berikut ini.

Menggunakan drop down list atau disebut juga combo box ini akan sangat memudahkan untuk input data yang jumlahnya sedikit. Misalnya data dalam bentuk pilihan “Ya” atau “Tidak”. Cara ini juga akan meminimalisir kesalahan ketik, karena itu disebut data validation. Selain itu drop down list juga akan mempercepat proses input data.

Bagaimana cara membuat drop down list di excel

Ada dua cara membuat drop down list di excel. Kedua cara ini dibedakan berdasarkan cara input list data yang akan dibuatkan drop down nya. Bisa input langsung atau mengambil dari baris data yang sudah ada, berikut penjelasannya.

1. Membuat drop down list dengan input langsung

Cara ini umumnya juga disebut comma-sparated value karena list datanya akan dimasukkan dengan dipisahkan oleh tanda koma atau titik koma. Langkah-langkah membuatnya seperti berikut:

• Pertama pilih atau blok sel yang akan dibuatkan drop down list. Pada contoh ini akan dibuat drop down list kota asal tamu dari sebuah hotel.

• Selanjutnya buka Data Validation pada tab Data grup Data Tools kemudian klik Data Validation. Atau bisa juga menggunakan shortcut : ALT + A + V + V

• Pada bagian box Data Validation Setting lakukan langkah-langkah berikut:
  1. Di bagian drop down Allow pilih List, ini menandakan drop down nya akan dibuat urutan list.
  2. Di bagian Source ketikkan daftar data yang ingin digunakan. Dalam contoh ini akan digunakan nama beberapa kota. Pisahkan masing-masing kota dengan tanda koma atau titik koma [biasanya tergantung setting di excel masing-masing].
  3. Centang bagian Ignore_blank jika Anda membolehkan sel drop down kosong.
  4. Pastikan untuk mencentang bagian In-cell dropdown, bagian ini yang membuat listnya berbentuk drop down.
• Setelah selesai klik OK. Maka drop down list sudah jadi dan siap digunakan.

2. Membuat drop down list dari data di sheet lain

Cara kedua ini sebenarnya mirip dengan cara pertama hanya saja untuk data yang dijadikan sumber drop down listnya diambil dari data sel yang sudah ada. Misalnya untuk contoh ini sudah tersedia data kota asal tamu hotel di sheet2. Data itu bisa digunakan pada kolom Source di bagian Data Validation Setting.

Input pada bagian Source akan tampak seperti ini [=Sheet2!$A$1:$A$4] jadi bukan daftar kota seperti yang sudah dibuat di cara ke satu. Untuk selanjutnya langkahnya sama dengan cara nomer 1.

Bagaimana menghapus drop down list di excel?

Nah, setelah drop down list tadi jadi dan bisa digunkan, terkadang kita dibuat bingung bagaimana menghapusnya. Karena drop down ini akan terus ada pada sel yang ditentukan jika belum di hapus.

Langkah untuk menghapus drop down list seperti berikut:

• Blok sel yang ingin dihapus drop down nya.
• Pilih tab Data > Data Validation.
• Kemudian pada box Data Validation Setting klik Clear All.

Sekarang seluruh drop down list sudah terhapus.

Tips cara cepat input data di drop down list

Untuk mempercepat input data di drop down list, daripada memilih dengan menggunakan mouse Anda juga bisa menggunakan shortcut: Alt + panah atas/panah bawah.

Cara ini bisa mempercepat pemilihan data yang biasanya jumlahnya hanya sedikit sehingga menghemat waktu dan tidak perlu menggeser-geser mouse lagi.

Cukup mudah bukan membuat drop down list di excel. Silahkan dicoba di komputer Anda agar semakin mahir.

Panduan ini akan mengajarkan Anda contoh cara menggunakan Rumus pada Data Validation Excel. Sebelum Anda memulai, setidaknya Anda harus memahami dasar-dasar Data Validations terlebih dahulu. Lihat SUB-BAB Data Validations pada

Daftar Isi Konten:

Data Validation + Fungsi TODAY dan WEEKDAY

Dengan menggabungkan Data Validation dan Fungsi TODAY atau WEEKDAY, Anda bisa membatasi pengisian cell dengan tanggal tertentu. Dengan cara ini, pengguna hanya bisa mengisi data kedalam cell sesuai tanggal yang memenuhi kriteria. Untuk menggunakan Data Validation + Rumus Excel pada data berupa tanggal, Saya memiliki 2 contoh berbeda. Mari kita pelajari satu per satu.

#1 Data Validation + Fungsi TODAY

Misalnya, Saya ingin Range A2:A15 hanya bisa diisi dengan data berupa tanggal mulai dari tanggal 1 Januari 2020 sampai 5 Hari setelah tanggal hari ini. Untuk tujuan tersebut, Anda bisa menggunakan Fungsi TODAY pada data validations. Silahkan ikuti tahap-tahap berikut:

1. Pertama, pilih [blok] semua Cell yang akan menggunakan Data Validation. Dalam contoh ini, Range A2:A15.
2. Kedua, Klik Tab Data pada Ribbon
3. Ketiga, Klik Icon Data Validation
4. Keempat, Bagian Allow, pilih Date, kemudian bagian Data pilih Between
5. Kelima, Start Date isi dengan tanggal pertama yang menjadi kriteria. Dalam contoh ini tanggal 01/01/2020
6. Keenam, pada bagian End Date, masukkan Rumus TODAY seperti berikut: =TODAY[]+5 kemudian klik Ok.

Sekarang coba kita buktikan. Silahkan ketik tanggal berapapun antara tanggal 01/01/2020 sampai 5 hari setelah tanggal hari ini pada cell A2 dan A3. Misalnya tanggal 15/02/2020 dan 30/11/2020. Lihat hasilnya.

Penting! Cara membuat Notice [Pesan Petunjuk Pengisian] sudah Saya jelaskan pada SUB-BAB Data Validation [].

Sejauh ini, Excel tidak menunjukkan pesan error karena data yang kita isi kedalam cell sesuai kriteria. Sekarang, coba masukkan tanggal berapapun sebelum tanggal 01/01/2020 pada Cell A4, misalnya tanggal 31/12/2019 dan lihat hasilnya.

Excel secara otomatis menolak dan menunjukkan pesan kesalahan, karena tanggal tersebut tidak sesuai dengan kriteria.

Kemudian coba kita buktikan Fungsi TODAY + 5. Fungsi TODAY adalah fungsi dinamis yang secara otomatis menggunakan “Tanggal Hari Ini” saat Anda membuka File Excel. Jadi jika fungsi =TODAY[]+5, maka 5 hari setelah tanggal hari ini bisa dimasukkan kedalam cell.

Note: Panduan ini Saya buat pada Tanggal 12/03/2021. 5 hari setelah tanggal 12 maret adalah 17 Maret 2021. Jadi saat ini, Saya tidak bisa memasukkan tanggal setelah 17/03/2021 ke dalam cell. Misalnya, Saya coba masukkan tanggal 18/03/2021, ternyata Excel menolak dan memberikan pesan kesalahan.

Namun, jika Anda membuka file Excel pada tanggal 15/03/2021, maka Anda bisa memasukkan data hingga tanggal 20/05/2021. Disinilah pentingnya Anda memahami penggunaan Fungsi TODAY.

#2 Data Validation + Fungsi WEEKDAY

Fungsi WEEKDAY secara default mengelompokkan hari menjadi angka 1 sampai 7, yang mana angka 1 adalah untuk Minggu, 2 untuk hari Senin, hingga 7 untuk hari Sabtu.

Nah dengan fungsi WEEKDAY ini, Anda bisa mengecualikan tanggal pada hari-hari tertentu agar tidak bisa dimasukkan ke dalam Range Data. Misalnya, Saya hanya ingin Range A2:A15 hanya bisa diisi dengan tanggal hari kerja [Senin – Jum’at]. Untuk tujuan tersebut, Anda hanya perlu mengecualikan hari Sabtu dan Minggu pada data validation menggunakan Fungsi AND + WEEKDAY Excel seperti berikut:

1. Pertama, pilih [blok] Range A2:A15.
2. Kedua, Buka Window Data Validation [Note: ulangi ]
3. Ketiga, pada bagian Allow, pilih Custom
4. Keempat, masukkan gabungan Rumus AND dan WEEKDAY pada bagian Formula: =AND[WEEKDAY[A2]1,WEEKDAY[A2]7] kemudian klik Ok untuk melanjutkan.

Sedikit Saya jelaskan tentang Fungsi AND dan WEEKDAY yang digunakan.

Tulis Rumus / Fungsi AND + WEEKDAY:
=AND[WEEKDAY[A2]1,WEEKDAY[A2]7]

Artinya:
ISI TRUE jika Hari pada Cell A2 tidak sama dengan Minggu dan Hari pada Cell A2 tidak sama dengan Sabtu

Penting! Ingat, pada Fungsi WEEKDAY, 1 = Minggu, 2 = Senin, 3 = Selasa, 4 = Rabu, 5 = Kamis, 6 = Jum’at, dan 7 = Sabtu. Selain itu, karena kita menggunakan 2 Fungsi WEEKDAY, maka diperlukan bantuan Fungsi AND. Selengkapnya tentang penggunaan Fungsi AND, bisa Anda pelajari di Panduan Fungsi Logika Excel.

Dengan demikian, ketika Anda mengisi tanggal [haris Sabtu / Minggu], maka Excel secara otomatis menolak dan memberikan pesan kesalahan. Coba kita buktikan, silahkan isi tanggal 14/03/2021 [ini tanggal hari minggu], sudah pasti Excel menolak dan memberikan pesan kesalahan.

Data Validation + Fungsi SUM

Contoh ini lebih dikhususkan jika data Anda menggunakan Format Angka Valid. Perhatikan, setidaknya Anda harus memahami Format Cell khususnya Format Number untuk memasukkan angka Valid. Jika belum tahu, silahkan kunjungi Panduan Format Cell Excel

Selanjutnya, Saya memiliki contoh cara menggunakan data validation Excel untuk “Anggaran Pesta Ulang Tahun” agar tidak terjadi kesalahan input data melebihi batas anggaran [terjadi selisih].

Perhatikan, Cell C9 adalah Total dari semua list [Range C2:C8]. Sedangkan Cell C11 = Budget – Total. Saat ini, Cell C3 [Hiburan] masih kosong sehingga selisih Budget – Total = Rp 200,000.00. Kemudian, ketika pengguna memasukkan 400,000 pada cell C3, tentu akan terjadi selisih negatif sebesar -Rp 200,000.00.

Saya tidak ingin kesalahan ini terjadi dan hanya ingin pengguna hanya bisa meng-input data dengan total maksimal sesuai Bugdet [anggaran] yaitu Rp 1,000,000.00. Untuk tujuan tersebut, Anda bisa menggabungkan Data Validation dengan Fungsi SUM. Silahkan ikuti tahap-tahap berikut:

1. Pertama, pilih [blok] list data yaitu range C2:C8
2. Kedua, buka Window Data Validation seperti
3. Ketiga, pada bagian Allow, pilih Custom
4. Keempat, pada bagian Formula, masukkan Fungsi SUM berikut: =SUM[$C$2:$C$8]