Cara Membuat Dependent Dropdown List (Bertingkat) Excel

Dependent Dropdown List Excel sering juga disebut dengan Dropdown List Bertingkat. Pada Panduan sebelumnya, Saya sudah menunjukkan contoh hasil dropdown list bertingkat, yaitu 3 dropdown list.

Sebetulnya seperti apa sih DropDown list bertingkat itu ? Intinya begini, katakanlah Dropdown list pertama berisi A dan B. Sedangkan Dropdown List kedua berisi 1, 2, 3, dan 4.

Ketika pengguna memilih A pada Dropdown list pertama, maka pengguna hanya bisa memilih 1 atau 2 pada Dropdown list kedua.

Sementara ketika pengguna memilih B pada Dropdown List Pertama, maka pengguna hanya bisa memilih 3 atau 4 pada dropdown list kedua.

Untuk tujuan tersebut, Anda bisa mengikuti panduan ini.

Contoh Dropdown List Bertingkat Excel Online

Sebagai bahan latihan kali ini, Silahkan download atau gunakan file Excel Online berikut:

Note:
1. Silahkan klik Icon Full Screen Pada Sudut Kanan Bawah File Excel Online berikut untuk menggunakan fitur Excel Online.
2. Klik Icon Download (gambar selembar kertas dengan tanda panah kebawah) pada baris sudut kanan bawah File Excel Online berikut untuk download file.
3. Jika Link Download atau Excel Online Error, silahkan beritahu Saya melalui kolom komentar (Terima kasih 🙂 ).

Cara Membuat 3 Dropdown List Bertingkat

Dalam contoh ini, Saya akan membuat 3 buah dropdown list bertingkat (saling terhubung) dengan kriteria sebagai berikut:

Dropdown ListIsi
Tingkat PertamaProvinsi
Tingkat KeduaKab. / Kota
Tingkat KetigaKecamatan

Kemudian, untuk membuat dropdown list bertingkat, ikuti 5 tahap berikut:

#1 Persiapkan Struktur List Data

Yang kita ketahui, dalam contoh ini ada 3 buah dropdown list. Oleh karena itu, kita akan mempersiapkan 3 Struktur data untuk masing-masing dropdown yaitu Provinsi, Kab/Kota dan Kecamatan.

Buatlah struktur list data Anda seperti gambar berikut:

Struktur List Dropdown Bertingkat Excel

Terdapat 8 list yang akan kita gunakan untuk filter masing-masing Dropdown list. Perhatikan tujuan kedelapan dropdown list tersebut:

NoListDropdown List KeKegunaan
1ProvinsiPertamaUntuk Pilihan dropdown list pertama
2JambiKeduaBerguna jika pada Dropdown list pertama terpilih Jambi
3JakartaKeduaBerguna jika pada Dropdown list pertama terpilih Jakarta
4Kota_JambiKetigaBerguna jika pada Dropdown list kedua terpilih Kota Jambi
5Kota_Sungai_PenuhKetigaBerguna jika pada Dropdown list kedua terpilih Kota Sungai Penuh
6Jakarta_UtaraKetigaBerguna jika pada Dropdown list kedua terpilih Jakarta Utara
7Jakarta_PusatKetigaBerguna jika pada Dropdown list kedua terpilih Jakarta Pusat
8Jakarta_SelatanKetigaBerguna jika pada Dropdown list kedua terpilih Jakarta Selatan

#2 Gunakan Named Range / Tabel Bernama

Ubahlah ke delapan list tersebut menjadi Named Range atau Tabel Bernama untuk mengidentifikasi list. Dalam contoh ini Saya menggunakan Named Range.

Saya memberi nama masing-masing list seperti gambar berikut:

Buat Named Range / Tabel Bernama

Penting!
Judul setiap list pada gambar (seperti Provinsi, Jambi, Jakarta, Kota_Jambi, Kota_Sungai_Penuh) bukan Cara membuat Named Range / Tabel Bernama. Namun hanya untuk memudahkan Anda memahami Panduan ini.

Untuk membuat Named Range / Tabel bernama silahkan kunjungi Panduan Named Range atau Tabel Bernama.

Selanjutnya kita akan menggunakan named range ini untuk mengidentifikasi dan filter list ke ketiga dropdown list. Perhatikan langkah-langkah berikut:

#3 Buat Dropdown List Tingkat 1 (Provinsi)

Untuk membuat Dropdown list tingkat pertama, silahkan ikuti cara berikut:

Cara Membuat Dropdown List Tingkat 1 Excel
  1. Pertama, klik Cell tempat Anda menerapkan Dropdown list tingkat pertama. Dalam contoh ini Cell B1
  2. Kedua, Buka Window Data Validation. Caranya, klik Tab Data kemudian klik Data Validation
  3. Ketiga, ubah Validation Criteria – Allow menjadi List
  4. Keempat, pada Kolom Source, masukkan =Nama_List. Gunakan Named Range Data Anda. Dalam contoh ini menjadi =Provinsi
  5. Terakhir, klik Ok untuk melanjutkan. Berikut hasilnya:
Contoh Dropdown list bertingkat. Tingkat 1.

Dengan menggunakan rumus =Nama_List atau =Provinsi, maka Dropdown list pertama hanya akan mengambil data dari list yang bernama Provinsi.

#4 Buat Dropdown List Tingkat 2 (Kabupaten)

Kali ini kita akan menggunakan bantuan Fungsi IF untuk memfilter 2 buah list kabupaten (Jambi dan Jakarta).

Penting!
Jika Anda belum tahu cara kerja Rumus IF, silahkan kunjungi Panduan Fungsi IF Excel M Jurnal

Untuk membuat dropdown list kedua, silahkan ikuti tahap-tahap berikut:

Buat Dropdown List Tingkat 2 Excel (Dependent)
  1. Pertama, ulangi tahap 1 – 3 pada Cara membuat dropdown list pertama di atas. Note: sesuaikan cell tempat Anda menerapkan dropdown list kedua. Dalam contoh ini, cell B2
  2. Kedua, pada kolom Source, gunakan fungsi IF berikut: =IF(B1=”Jambi”,Jambi,IF(B1=”Jakarta”,Jakarta,”Pilih Provinsi”)) Note: Ketik manual sesuai data Anda.
  3. Ketiga, klik Ok untuk melanjutkan. Berikut hasilnya:
Contoh Dropdown List bertingkat excel dengan fungsi / Rumus IF

Penting!
Aplikasi Excel Saya menggunakan tanda koma (,) sebagai operator pemisah rumus. Aplikasi Excel Anda belum tentu demikian. Silahkan cek di Panduan Mengubah Koma menjadi Titik Excel

Pada gambar di atas, Saya pilih Jambi pada Dropdown list pertama (Cell B1). Sehingga pada Dropdown list kedua (Cell B2), hanya tersedia list Kota Jambi dan Kota Sungai Penuh.

Begitu juga jika pada Dropdown list pertama Anda memilih Jakarta, maka sudah pasti Dropdown list kedua hanya tersedia list Jakarta Utara, Jakarta Pusat, dan Jakarta Selatan seperti gambar berikut:

Contoh Dependent Dropdown list bertingkat excel dengan fungsi / rumus IF

Kenapa bisa demikian ? sedikit Saya jelaskan makna dari Rumus IF yang kita gunakan:

Tulis Rumus / Fungsi IF:
=IF(B1=”Jambi”,Jambi,IF(B1=”Jakarta”,Jakarta,“Pilih Provinsi”)

Artinya:
Jika Cell B1 berisi Jambi maka Gunakan List Jambi namun Jika Cell B1 berisi Jakarta maka Gunakan List Jakarta Selain dari itu Pilih Provinsi

Setelah Anda membuat Named Range atau Tabel Bernama, maka ketika Anda memasukkan Nama Range / Tabel tersebut ke dalam Rumus / Fungsi, Excel akan menggunakan Range / Tabel tersebut sebagai referensi.

Misalnya seperti argument =IF(B1=”Jambi”,Jambi. Jika cell B1 berisi Jambi, maka Excel akan menggunakan data pada List Jambi.

Begitu juga dengan argument =IF(B1=”Jakarta”,Jakarta. Jika cell B1 berisi Jakarta, maka Excel akan menggunakan data pada list Jakarta.

#5 Buat Dropdown List Tingkat 3 (Kecamatan)

Caranya hampir sama dengan membuat Dropdown list tingkat kedua. Perbedaannya Anda akan menggunakan Rumus IF bertumpuk (bertingkat) yang lebih banyak.

Seberapa banyak ? Coba lihat list tingkat dua yaitu Jambi dan Jakarta. Ada berapa banyak isi nya ? 5 bukan ? Yiatu Kota Jambi, Kota Sungai Penuh, Jakarta Utara, Jakarta Pusat, dan Jakarta Selatan.

Oleh karena itu, Anda akan menggunakan 5 Fungsi IF bertumpuk agar Excel bisa memfilter list sesuai dengan List yang terpilih pada Dropdown list kedua.

Saya menggunakan Fungsi IF berikut:

=IF(B2=”Kota Jambi”,Kota_Jambi,IF(B2=”Kota Sungai Penuh”,Kota_Sungai_Penuh,IF(B2=”Jakarta Utara”,Jakarta_Utara,IF(B2=”Jakarta Pusat”,Jakarta_Pusat,IF(B2=”Jakarta Selatan”,Jakarta_Selatan,”Pilih Kab/Kota”)))))

Hasilnya Excel akan memfilter List jika Cell B2 memenuhi kriteria seperti gambar berikut:

Contoh Dropdown List bertingkat menggunakan rumus IF

Namun, menggunakan Rumus IF untuk memfilter dropdown list bertingkat hanya akan Efektif jika jumlah data untuk setiap list tidak banyak.

Seperti contoh ini, Saya hanya menggunakan 2 list Provinsi serta list Kabupaten dan Kecamatan masing-masing Provinsi yang tidak lebih dari 5.

Bayangkan jika Anda memiliki list data yang sangat banyak, misalnya List Seluruh Provinsi se-Indonesia serta Seluruh kabupaten/Kota hingga Kecamatan di Indonesia. Apakah Fungsi IF masih efektif ?

Tentu tidak Efektif. Namun jika ini yang Anda alami, Saya punya Solusinya. Silahkan gunakan Fungsi INDIRECT.

Bonus: Dropdown List Bertingkat Dengan Fungsi INDIRECT

Penting!
Sebelum Anda menggunakan Fungsi INDIRECT untuk Dropdown List bertingkat (dependent), silahkan pahami syarat utama berikut:

Named Range / Tabel Bernama untuk Dropdown list tingkat 2 harus sama dengan list data yang terdapat pada dropdown tingkat 1.

Sedangkan Named Range / Tabel Bernama untuk Dropdown List tingkat 3 harus sama dengan list data yang terdapat pada dropdown tingkat 2.

Jadi, Perhatikan Named Range Anda

Ilustrasinya begini… Perhatikan gambar berikut:

Perhatikan Named Range data

Range A2:A3 untuk Dropdown list tingkat 1 Saya beri Named Range: Provinsi. List ini berisi: Jambi dan Jakarta. Ingat isinya.

Selanjutnya untuk list tingkat 2. Dalam contoh ini, Range A7:A8 akan digunakan jika Dropdown list tingkat 1 terpilih: Jambi. Jadi, berikan Named Range Jambi untuk list ini.

Sedangkan Range C7:C9 akan digunakan jika Dropdown list tingkat 1 terpilih: Jakarta. Jadi berikan Named Range Jakarta untuk list ini.

Pahami baik-baik ya… Named Range tingkat 2 harus sama dengan list data yang terdapat pada tingkat 1.

Selanjutnya list tingkat 3 akan melanjutkan data list tingkat 2. Konsepnya sama saja, namun ada perbedaan penting, Perhatikan gambar:

Named Range untuk list tingkat 3

Lihat isi pada list tingkat 2. Misalnya untuk List Jambi berisi Kota Jambi dan Kota Sungai Penuh. Untuk List Jakarta berisi Jakarta Utara, Jakarta Pusat, dan Jakarta Selatan

Semua list terdiri lebih dari 1 kata, bahkan ada yang 3 kata yaitu Kota Sungai Penuh.

Apa masalahnya ? Anda tidak bisa membuat Named Range lebih dari 1 kata yang menggunakan spasi. Jika Named Range lebih dari 1 kata, maka Anda harus mengubah spasi menjadi underscore (_).

Sementara itu, apa Anda ingat Syarat Utama Fungsi INDIRECT untuk Dropdown List bertingkat yang Saya sebutkan sebelumnya ?

Named Range List tingkat 2 = data list tingkat 1.
Named Range List tingkat 3 = data list tingkat 2 dan seterusnya.

Nah disinilah kekeliruan sering terjadi sebagai penyebab kenapa Dropdown List Excel tidak muncul.

Sebagai Solusinya…

Upayakan ubah semua list data menjadi 1 kata saja agar semua Named Range bisa menggunakan 1 kata.

Namun, jika tidak memungkinkan. Silahkan gunakan list lebih dari 1 kata. Kemudian buat Named Range yang lebih dari 1 kata menggunakan underscore (ubah spasi antar kata menjadi underscore).

Kita akan menggunakan bantuan Fungsi SUBSTITUTE pada Rumus INDIRECT list tingkat ke 3.

Dalam contoh ini, Saya menggunakan Named Range list tingkat ke 3 yang sama dengan sebelumnya seperti gambar berikut:

Contoh Dependent Dropdown list bertingkat dengan fungsi INDIRECT dan SUBSTITUTE

Kemudian, silahkan buat Dropdown List Tingkat 1 seperti cara sebelumnya. Gunakan rumus =Provinsi pada kolom Source.

Dropdown List Tingkat 2 (Fungsi INDIRECT)

Perhatikan Gambar berikut untuk membuat Dropdown List tingkat kedua menggunakan Fungsi INDIRECT:

Cara membuat dependent dropdown list bertingkat dengan Fungsi / Rumus INDIRECT dan SUBSTITUTE
  1. Pertama, buka Window Data Validation seperti cara sebelumnya.
  2. Kedua, Ubah Validation Criteria – Allow menjadi List
  3. Ketiga, pada kolom Source, masukkan rumus =INDIRECT(
  4. Keempat, ketik nama Cell tempat Dropdown tingkat 1. (Cell B1)
  5. Kelima, ketik tanda tutup kurung
  6. Terakhir, klik Ok. berikut hasilnya:
Contoh Dependent Dropdown list bertingkat dengan fungsi INDIRECT dan SUBSTITUTE

Biar semakin paham, berikut makna Fungsi INDIRECT tersebut:

Tulis Rumus / Fungsi INDIRECT:
=INDIRECT(B1)

Artinya:
Ambil Referece Data Dari: Cell B1

Dengan kata lain, rumus INDIRECT akan mengambil reference data dari cell B1. Reference data tersebut bisa berupa cell, range, named range, ataupun table bernama.

Karena kita sudah membuat Named Range Semua List, maka jika Cell B1 berisi Jambi, maka Excel akan menggunakan Named Range Jambi untuk List tingkat 2. Begitu juga dengan Jakarta.

Dropdown List Tingkat 3 (Fungsi INDIRECT + SUBSTITUTE)

Karena Named Range list tingkat 2 (Kabupaten) terdiri dari 2 kata yang dipisah dengan underscore, maka kita akan menggunakan Fungsi INDIRECT dan SUBSTITUTE untuk Dropdown List tingkat 3. Perhatikan langkah-langkah berikut:

Cara Membuat dependent Dropdown list bertingkat dengan fungsi / rumus INDIRECT dan SUBSTITUTE
  1. Pertama, ulangi tahap 1 – 3 pada dropdown list tingkat 2 INDIRECT diatas
  2. Kedua, masukkan Fungsi SUBSTITUTE berikut: SUBSTITUTE(
  3. Ketiga, ketik nama Cell tempat Dropdown tingkat 2. (Cell B2)
  4. Keempat, ketik Operator pemisah rumus ( , atau ; )
  5. Kelima, ketik ” “,”_”)) atau ” “;”_”)) untuk mengganti underscore menjadi spasi.
  6. Terakhir, klik Ok. berikut hasilnya:
Contoh Dependent Dropdown list bertingkat dengan fungsi INDIRECT dan SUBSTITUTE

Benar bukan ? Excel menggunakan Named Range yang tepat sebagai acuan Dropdown list tingkat 3.

Biar semakin paham, berikut Saya jelaskan makna Fungsi INDIRECT + SUBSTITUTE tersebut:

Tulis Rumus / Fungsi INDIRECT + SUBSTITUTE:
=INDIRECT(SUBSTITUTE(B2,” “,“_”))

Artinya:
Ambil Reference Data Dari: Terlebih Dahulu Ubah Isi Cell B2 Menjadi Spasi Untuk Setiap Underscore (_)

Dengan kata lain, Fungsi SUBSTITUTE akan mengubah setiap Underscore (_) menjadi Spasi. Kemudian, Fungsi INDIRECT akan mengambil Reference Data berdasarkan isi Pada Cell B2.

Dalam contoh ini (lihat gambar), Cell B2 berisi Kota Jambi. Sementara Named Range List tersebut= Kota_Jambi. Kemudian, Fungsi SUBSTITUTE akan mengubah Underscore (_) menjadi spasi.

Sehingga, Fungsi INDIRECT akan menganggap Named Range Kota_Jambi = Kota Jambi.

Pembuktian:
Jika Anda masih penasaran apakah fungsi INDIRECT hanya menggunakan Named Range yang sama dengan list data tingkat diatas nya, coba ubah source untuk Dropdown list tingkat 3 menjadi =INDIRECT(B2)

Pasti Dropdown list tingkat 3 (Kecamatan) pada cell B3 tidak akan muncul.

Saya yakin, banyak keadaan lain yang tidak serupa dengan contoh pada panduan ini. Jika Anda tidak menemukan solusi dari Panduan ini, silahkan diskusikan pada Forum Excel M Jurnal. Saya akan dengan senang hati membantu Anda.

Pintasan Panduan Data Validations Excel

  1. Data Validations: Untuk mengisi Cell dengan pilihan khusus (Contoh: Cell B2 hanya bisa diisi dengan angka 1, 2, 3, atau 4).
  2. Reject Invalid Dates: Mengatur Pesan Error jika cell di isi dengan tanggal yang salah
  3. Budget Limit: Membuat Data Validations untuk Batas Anggaran pada cell tertentu.
  4. Prevent Duplicate Entries: Data Validations untuk mencegah peng-inputan data duplikat.
  5. Product Codes: Contoh Data Validations untuk mencegah peng-inputan kode produk yang salah.
  6. Drop-Down List: Membuat Data Validations menggunakan Drop-Down List
  7. Dependent Drop-Down Lists: (Anda Disini)
  8. Kg to lbs: Contoh Validasi untuk mengkonversi satuan Kg ke pon

Pusat Panduan Excel

List Artikel Terbaru

Tinggalkan Balasan