Cara Membuat Dropdown List Bertingkat Pada Excel

Dropdown List Bertingkat - Saat membuat Dropdown List dengan fitur data validation, ada kalanya kita ingin dropdown list untuk pilihan sel tersebut berubah secara otomatis menyesuaikan dengan isi data pada sel lainnya.

Kasus semacam ini sering disebut dengan dropdown list bertingkat (nested dropdown list) atau list validasi bertingkat (nested list validation).

Kasus ini berbeda dengan dropdown list dinamis yg sdh kita pelajari sebelumnya.

Misalnya kita tlah membuat list validasi berupa pilihan nama-nama provinsi, kemudian kita menginginkan untuk isian nama kabupaten/kota, dropdown listnya akan menyesuikan dengan isian data provinsi yg tlah dipilih. Begitu juga dengan isian data nama kecamatan yg secara otomatis pilihan dropdown listnya akan menyesuaikan dengan data kabupaten yg dipilih.

Dropdown list atau list validasi sendiri digunakan untuk membatasi isi cell di excel dengan beberapa pilihan tertentu. Misalnya membuat pilihan jenis kelamin Laki-laki/Perempuan, pilihan Ya/Tidak & lain sbgainya.

Untuk membuat pilihan sel semacam ini, kita menggunakan fitur data validation di excel. Silahkan dibaca pada tautan brkut: Cara Membuat Dropdown List Dengan Data Validation Excel.

Kembali kpda pokok permasalahan tentang dropdown list bertingkat di excel, bagaimana cara membuatnya? Silahkan simak penjelasan brkut ini.

• Dropdown List Validasi Bertingkat Dengan Rumus IF
• Dropdown List Validasi Bertingkat Dengan Rumus INDIRECT
• Download Contoh File List Validasi bertingkat

Pada tutorial "Cara membuat dropdown list bertingkat pada excel" kali ini, saya anggap anda sdh bisa membuat list data validasi pada excel. Sehingga dimungkinkan akan ada beberapa langkah yg saya lewati.

Jika diperlukan slahkan baca 2 tutorial belajar excel brkut:

1. Data Validation Excel - Cara Membatasi Isi Cell di Excel
2. Cara Membuat Dropdown List Dengan Data Validation Excel

Ada dua cara yg bisa kita lakukan untuk membuat dropdown list validasi data bertingkat pada excel. Yang pertama dengan menggunakan Fungsi Excel IF & yg kedua dengan menggunakan Fungsi Excel INDIRECT.

Cara Membuat Dropsdown List Bertingkat Dengan Rumus IF Excel

Untuk cara pertama ini, kita akan menggunakan fungsi IF excel.

Langkah-langkah yg diperlukan untuk membuat dropdown list bertingkat dengan rumus IF excel adalah sbgai brkut:

1. Buatlah sumber data untuk list validasi tingkat pertama yg akan kita gunakan sbgai pilihan data untuk dropdown list.
2. Siapkan juga sumber data untuk list validasi tingkat kedua. Daftar list ini yg nantinya akan digunakan untuk dropdown list kedua. Sebagai contoh saya akan menggunakan sumber data sprti pada gambar brkut:
   
   
3. Beri nama range untuk masing-masing list data yg tlah kita buat. Gunakan named range yg mudah diingat. Cara membuat nama range bisa anda pelajari pada link ini: 3 Cara Memberi Nama Range Pada Excel
4. Selain nama range untuk masing-masing list, buat lagi satu (1) named range yg mengarah pada referensi sel yg tidak ada isinya (Sel kosong). Misal saya membuat nama range "Kosong" yg mengarah pada sel E1 sprti digambar brkut:
   
   
   
   Pada contoh ini range untuk tingkat pertama (A2:A3) Saya beri nama List_Jenis. Se&gkan untuk Range tingkat kedua pertama (C2:C4) saya beri nama List_Tumbuhan & Range tingkat kedua selanjutnya (D2:D5) saya beri nama List_Hewan. Se&gkan untuk nama range List_Kosong saya gunakan untuk menamai sel E1.
5. Setlah selesai membuat nama range kita lanjutkan dengan mengatur list validation untuk tingkat pertama. Pada contoh ini saya terapkan pada sel B7.
   
   
   
   Cara membuat dropdown list untuk tingkat pertama ini sama sprti cara membuat dropdown list pada umumnya. Hanya saja kali ini kita menggunakan named range sbgai source datanya. Sehingga kita cukup menggunakan nama range untuk source data validation-nya sprti dlm gambar diatas.
   
   

   =List_Jenis

6. Selanjutnya kita setting untuk list validation tingkat kedua dengan menggunakan rumus excel brkut pada source list data validationnya:
   
   

   =IF($B$7="Tumbuhan";List_Tumbuhan;IF($B$7="Hewan";List_Hewan;List_Kosong))

   
   
   
   Perhatikan bahwa nama range pada rumus diatas, tidak perlu ditulis diantara tanda petik ganda.
   
   Rumus diatas dapat diartikan bahwa jika sel B7 berisi teks "Tumbuhan" maka data validasi akan menggunakan named range "List_Tumbuhan" sbgai source datanya. Jika B7 berisi teks "Hewan" maka akan menggunakan nama range "List_Hewan" sbgai sumber data list validasinya. Dan apabila B7 tidak berisi teks "Tumbuhan" atau "Hewan" maka akan menggunakan nama range "List_Kosong".
   
   Untuk penjelasan yg lebih detail tentang Fungsi IF bertingkat bisa anda temukan pada tautan brkut: Rumus IF Bertingkat pada Excel.
7. Selesai. Jika langkah-langkah yg anda lakukan benar maka hasilnya akan nampak sprti brkut:
   
   

Dengan menggunakan cara ini anda harus paham tentang cara menggunakan rumus IF bertingkat.

Bagi yg baru belajar excel menuliskan rumus secara langsung di bagian source Data Validation biasanya akan sedikit kesulitan.

Tips dari saya, tulis dulu rumusnya di lembar kerja atau sel tertentu. Kalau perlu hilangkan dulu tanda sama dengan diawal(=).

Jika dirasa sdh benar lakukan copy paste rumus excel tersebut ke bagian source Data Validation

Cara diatas adalah alternatif pertama untuk membuat dropdown list bertingkat atau list validasi bertingkat di excel.

Langkah-langkah diatas relatif lebih mudah dilakukan & difahami jika jumlah pilihan selnya hanya sedikit.

Untuk kasus dengan jenjang atau tingkat dropdown list yg banyak ada cara lain yg relatif lebih mudah.

Mari kita pelajari cara kedua brkut ini.

Cara Membuat Dropdown List Bertingkat Dengan Fungsi INDIRECT Excel

Selain menggunakan fungsi IF sprti cara pertama, kita juga bisa menggunakan fungsi INDIRECT Excel untuk membuat dropdown list bertingkat.

Langkah-langkah untuk membuat dropdown list validasi bertingkat dengan rumus Indirect adalah sbgai brkut:

1. Buat daftar data yg akan kita jadikan sumber list validasi sprti pada cara pertama.
   
   
   
   Untuk cara ke-2 ini judul kolom harus "sama persis" dengan list sumber untuk masing-masing pilihan validasi data.
2. Gunakan fitur Create from Selection pada menu Defined Names untuk menamai masing-masing kolom sumber data tersebut. Caranya:
   
   
   
   • Seleksi range sumber pada kolom pertama.
   • Pilih Tab Formulas--Create from Selection.
   • Pada kotak opsi Create Names from Selection, centang hanya pada bagian Top row.
   • Klik OK.
   • Ulangi langkah 1-4 di atas untuk kolom-kolom sumber data yg lainnya.
   
   
   Selain membuat nama range satu persatu anda bisa juga menyeleksi seluruh list data yg dibuat lalu menggunakan Create form selection untuk menamai masing-masing kolom pada range tersebut.
   
   Hanya saja jika memakai cara ini anda perlu menyesuiakan lagi range data pada masing-masing nama sebab banyak data untuk masing-masing nama kemungkinan tidak sama.
   
   Setlah selesai membuat nama range untuk masing-masing kolom sumber list validasi maka anda akan memiliki beberapa nama range sprti yg nampak pada kotak name manager brkut:
   
   
   
3. Setlah selesai membuat nama range, langkah brkutnya adalah men-setting validasi data untuk tingkat pertama, untuk contoh ini pada sel B8. Gunakan rumus brkut pada source list validasinya.
   
   

   =Kabupaten

   
   
   
   Untuk list validasi tingkat pertama ini masih sama sprti cara sebelumnya.
   
4. Gunakan fungsi Indirect Excel untuk source validasi data tingkat kedua. Tuliskan rumus brkut untuk mengisi kolom source list validasinya.
   
   

   =INDIRECT($B$8)

   
   
   
   $B$8 Merupakan sel acuan yg akan menentukan source range mana yg akan digunakan untuk dropdown list tingkat ke-2 ini.
   
5. Atur validasi data tingkat ketiga untuk cell B10. Seperti pada tingkat ke-2 sebelumnya, pada Source list validasinya masukkan rumus excel brkut:
   
   

   =INDIRECT($B$9)

   
   
6. Selesai. Jika langkah yg anda lakukan benar maka hasilnya akan sprti pada gambar dibawah ini.
   
   

Rumu excel "=INDIRECT(Alamat_Sel_Rujukan)" sprti yg kita gunakan di atas berlaku jika list pilihan yg kita gunakan tidak mengandung spasi atau hanya satu kata saja.

Jika mengandung spasi atau lebih dari satu kata maka cara diatas tidak berlaku. Sebab nama range tidak boleh mengandung spasi.

Apabila mengandung spasi maka saat membuat nama range dengan menggunakan cara "Create form Selection" di atas, secara otomatis spasi tersebut akan diganti dengan garis bawah/ underscore (_).

Lalu bagaimana solusinya?

Solusinya adalah dengan menggunakan rumus yg merubah spasi dengan garis bawah tersebut.

Salah satu cara yg bisa kita lakukan adalah dengan menggunakan fungsi SUBSTITUTE. Sehingga rumus excel untuk source validasinya akan sprti dibawah ini:

INDIRECT(SUBSTITUTE(Referensi_Sel_Acuan;" ";"_"))

Perhatikan contoh brkut:



Dengan data diatas masing-masing source list validasi yg di gunakan adalah:

- Sel B8 :

=List_Kabupaten

- Sel B9 :

=INDIRECT(SUBSTITUTE($B$8;" ";"_"))

Pada saat menggunakan rumus data diatas mungkin anda akan mendapatkan pesan error brkut.



Hal ini wajar sebab untuk saat ini sel B8 masih kosong sehingga excel menganggap nama range yg dicari tidak ditemukan. Jadi abaikan saja pesan error ini & lanjut klik YES.

- Sel B10 :

=INDIRECT(SUBSTITUTE($B$9;" ";"_"))

Download File Contoh List Validasi Betingkat di Excel

Masih bingung juga? File untuk latihan saya dankan pada tautan dibawah ini:

Dropbox
GDrive

*Jika link mati / tidak dapat diakses slahkan lapor via kontak yg tersedia

FacebookTwitterWhatsapp

LINK DOWNLOAD TERKUNCI.
Silahkan SHARE untuk membuka kunci!

Apabila masih belum bisa juga coba baca ulang dari awal. setidaknya 10x.

Kalau masih gagal juga slahkan tinggalkan pesan di bagian bawah halaman ini.

Untuk tutorial membuat dropdown list dinamis yg otomatis menyesuaikan sumber data yg kita buat bisa anda pelajari pada link brkut: Cara Membuat Dropdown List Dinamis di Excel.

Jika dirasa bermanfaat jangan sungkan-sungkan untuk share artikel tutorial belajar excel ini ke media sosial yg anda gunakan.

Semoga bermanfaat. Salam .
Sumber https://www.kelasexcel.id

Bagikan Artikel ini

Berlangganan update artikel terbaru via email: