Membuat Dropdown List Dinamis dengan Data Validation di Excel

Membuat dropdown list dinamis adalah fitur yang memungkinkan pengguna Excel untuk memilih nilai dari daftar yang dapat berubah secara otomatis sesuai dengan kondisi atau kriteria tertentu. Fitur ini sangat berguna untuk menyederhanakan input data dan meningkatkan keakuratan dalam pekerjaan sehari-hari. Dalam artikel ini, kita akan membahas cara membuat dropdown list dinamis menggunakan Data Validation di Excel.

Langkah 1: Persiapkan Data

Sebelum kita membuat dropdown list, pertama-tama kita perlu menyiapkan data yang akan digunakan. Misalnya, kita akan membuat dropdown list untuk memilih nama desa berdasarkan kategori kecamatan. Buatlah dua kolom di lembar kerja Excel, satu untuk nama-nama desanya dan satu lagi untuk kategori kecamatan. Isi data nama-nama desa dan kategori kecamatan sesuai kebutuhan.

Sebagai contoh, berikut adalah tabel yang akan kita buat:

Langkah 2: Membuat Dropdown List untuk Kategori

Kita akan membuat dropdown list dinamis untuk kategori kecamatan terlebih dahulu. Dan karena kategori yang kita punya cuma dua maka kita buat dropdown list tanpa memakai rumus lagi. Caranya klik Data – Data Validation:

Langkah 3: Membuat Dropdown List Dinamis

1. Pilih sel di mana Anda ingin menampilkan dropdown list (sebagai contoh, di sel C13).
2. Pergi ke tab “Data” dan pilih “Data Validation.”
3. Dalam jendela Data Validation, pilih “List” sebagai kriteria.
4. Masukkan formula berikut sebagai sumber data: =OFFSET($B$2;MATCH(B13;$C$3:$C$9;0)-1;0;COUNTIF($C$3:$C$9;B13);1).

Penjelasan Rumus

Formula di Data Validation ini memiliki beberapa komponen yang perlu dijelaskan. Mari kita bahas satu per satu:

Formula Data Validation:

=OFFSET($B$2;MATCH(B13;$C$3:$C$9;0)-1;0;COUNTIF($C$3:$C$9;B13);1)

Komponen Formula:

1. OFFSET:

• OFFSET(reference; rows; cols; height; width) Fungsi OFFSET digunakan untuk menggeser atau menentukan suatu rentang sel berdasarkan sel referensi. Dalam konteks ini, kita menggunakan $B$2 sebagai sel referensi.

2. MATCH:

• MATCH(lookup_value; lookup_array; match_type) Fungsi MATCH digunakan untuk mencari nilai dalam rentang sel tertentu dan mengembalikan posisinya. Dalam konteks ini, kita mencari nilai yang terdapat di sel B13 dalam rentang $C$3:$C$9.

3. COUNTIF:

• COUNTIF(range, criteria) Fungsi COUNTIF digunakan untuk menghitung berapa kali suatu kriteria muncul dalam rentang sel. Dalam konteks ini, kita menghitung berapa kali nilai yang terdapat di sel B13 muncul dalam rentang $C$3:$C$9.

Detail Formula:

• MATCH(B13;$C$3:$C$9;0)-1:
• MATCH digunakan untuk mencari posisi nilai di sel B13 dalam rentang $C$3:$C$9 dengan pencarian eksak (match_type = 0).
• -1 digunakan untuk mengkompensasi fakta bahwa kita ingin merujuk pada sel di atas sel yang ditemukan.
• OFFSET($B$2;...:
• $B$2 adalah sel referensi awal yang akan digunakan sebagai titik awal dari pergeseran.
• COUNTIF($C$3:$C$9;B13):
• COUNTIF digunakan untuk menghitung berapa kali nilai di sel B13 muncul dalam rentang $C$3:$C$9.
• Hasil ini digunakan sebagai tinggi (height) dalam fungsi OFFSET.
• 1):
• Angka 1 ini digunakan sebagai lebar (width) dalam fungsi OFFSET. Karena kita hanya ingin merujuk pada satu kolom.

Langkah 4: Uji Dropdown List

Sekarang, cobalah pilih kategori pada sel B13. Daftar dropdown list pada sel C13 akan otomatis berubah sesuai dengan kategori yang Anda pilih.

Kesimpulan

Dengan mengikuti langkah-langkah di atas, Anda telah berhasil membuat dropdown list dinamis menggunakan Data Validation di Excel. Fitur ini akan membantu mempercepat dan menyederhanakan proses penginputan data, meningkatkan akurasi, dan memudahkan analisis data dalam pekerjaan sehari-hari Anda. Selamat mencoba!

Bagi yang membutuhkan file Membuat Dropdown List Dinamis dengan Data Validation di Excel ini untuk latihan, file Excel bisa didownload melalui tautan di bawah ini: