Panduan Cara Membuat Dropdown List Bertingkat di Excel untuk Pemula

Tahukah Anda apakah itu dropdown list bertingkat (nested dropdown list) atau list validasi bertingkat (nested list validation)? Nah, dropdown list bertingkat adalah dropdown list yang berubah secara otomatis dengan menyesuaikan isi data pada sel lainnya.

Dengan kata lain, dropdown list digunakan untuk membatasi isi cell di Excel dengan beberapa pilihan tertentu. Anda tentunya pernah melihat list validasi seperti pilihan nama provinsi kemudian terdapat form isian nama kabupaten/kota dimana dropdown listnya akan sesuai dengan data provinsi yang telah dipilih. Begitu pula dengan isian nama kecamatan yang secara otomatis dropdown listnya akan sesuai dengan data kabupaten yang dipilih.

Cara Membuat Dropdown List Bertingkat di Excel

Nah, bagaimana cara membuat dropdown list validasi data bertingkat pada Excel? Ada dua cara yang bisa kita lakukan untuk melakukan hal tersebut yaitu:

• Menggunakan fungsi If
• Menggunakan fungsi Indirect

A. Membuat Dropdown List Bertingkat Dengan Fungsi If

Adapun langkah-langkah yang diperlukan untuk cara membuat dropdown list bertingkat di Excel dengan fungsi IF di Excel yaitu :

Buatlah data untuk list validasi tingkat pertama dan kedua yang akan digunakan sebagai pilihan data untuk dropdown list pertama dan kedua. Misalnya seperti gambar dibawah ini:

Beri nama range untuk masing-masing list data yang telah dibuat dengan cara pilih sel-sel yang akan diberi nama lalu klik kanan pilih Define Name.

Setelah itu, buatlah satu name range untuk sel yang tidak ada isinya (sel kosong). Misalkan, dibuat nama range “List_Empty” yang mengarah pada sel G1. Buka Name Manager untuk melihat semua name range dengan mengklik Formulas > Name Manager seperti gambar dibawah ini:Maka hasilnya akan terlihat seperti pada gambar dibawah ini:
Pada gambar diatas terdapat List_Jenis untuk untuk tingkat pertama (A2:A3), List_Tumbuhan untuk tingkat kedua (E2:E5)dan List_Hewan untuk tingkat kedua lainnya (F2:F5). Sedangkan, List_Kosong untuk sel kosong yaitu G1.

Langkah selanjutnya adalah mengatur list validation untuk tingkat pertama. Caranya adalah klik Data > Data Validation. Pada contoh ini saya terapkan pada sel C7 seperti pada gambar dibawah ini:Cara membuat dropdown list untuk tingkat pertama ini sebenarnya sama seperti cara membuat dropdown list pada umumnya. Perbedaannya terletak pada penggunaan named range sebagai sumber datanya. Dengan begitu, cukup menggunakan named range untuk source data validation-nya seperti pada gambar diatas.

Kemudian, setting list validation tingkat kedua dengan menggunakan rumus excel dibawah ini pada source list data validationnya:

=IF($C$7="Makanan",List_Makanan,IF($C$7="Minuman",List_Minuman,List_Empty))Rumus diatas dapat diartikan bahwa jika sel B7 berisi teks "Tumbuhan" maka data validasi akan menggunakan named range "List_Tumbuhan" sebagai source datanya. Jika B7 berisi teks "Hewan" maka akan menggunakan nama range "List_Hewan" sebagai sumber data list validasinya. Dan apabila B7 tidak berisi teks "Tumbuhan" atau "Hewan" maka akan menggunakan nama range "List_Kosong".

Rumus diatas dapat diartikan bahwa jika sel C7 berisi teks “Tumbuhan” maka data validasi akan menggunakan named range “List_Tumbuhan” sebagai source datanya. Jika C7 berisi teks “Hewan” maka akan menggunakan nama range “List_Hewan” sebagai sumber data list validasinya. Dan apabila C7 tidak berisi teks “Tumbuhan” atau “Hewan” maka akan menggunakan nama range “List_Kosong“. Tampilannya akan tampak seperti pada gambar dibawah ini:

Nah, jika semua langkah diatas dilakukan dengan benar maka hasilnya akan tampak seperti gambar dibawah ini:

B. Membuat Dropdown List Bertingkat Dengan Fungsi Indirect

Penggunaan fungsi IF untuk hal ini umumnya hanya berlaku untuk jumlah pilihan yang sedikit. Namun, jika Anda ingin membuat pilihan yang lebih banyak maka  sebaiknya Anda mencoba menggunakan fungsi INDIRECT Excel untuk membuat dropdown list bertingkat.

Adapun, langkah-langkah untuk membuat dropdown list validasi bertingkat dengan fungsi Indirect yaitu:

Buat daftar data seperti cara pertama yang akan dijadikan sumber list validasi.

Gunakan fitur Create from Selection untuk menamai masing-masing kolom sumber data tersebut pada menu Defined Names dengan cara:

1. Lakukan seleksi range sumber pada kolom pertama.
2. Pilih Tab Formulas -> Create from Selection.
3. Pada kotak opsi Create Names from Selection, centanglah bagian Top row.
4. Klik OK.
5. Untuk kolom-kolom sumber data yang lainnya, ulangi langkah 1-4 di atas.

Setelah itu, Anda akan memiliki beberapa nama range yang dapat Anda lihat di kotak name manager seperti gambar dibawah ini:

Langkah selanjutnya adalah setting validasi data untuk tingkat pertama, untuk contoh ini pada sel B8. Gunakan rumus berikut pada source list validasinya.

Kemudian, gunakan fungsi Indirect Excel untuk source validasi data tingkat kedua. Masukkan rumus berikut untuk mengisi kolom source list validasinya.

=INDIRECT($C$8)

Atur validasi data tingkat ketiga untuk cell B10. Seperti pada tingkat ke-2 sebelumnya, pada Source list validasinya masukkan rumus excel berikut:

=INDIRECT($C$9)

Nah, jika semua langkah diatas dilakukan dengan benar maka hasilnya akan tampak seperti gambar dibawah ini:

Sekian pembahasan kali ini tentang cara membuat dropdown list bertingkat di Excel. Semoga bermanfaat dan selamat mencoba. Terima kasih!

Editor: Muchammad Zakaria