5. Cara Membuat Dropdown List Bertingkat di Excel Otomatis Sesuai Kategori ✓
Cara Membuat Dropdown List Bertingkat di Excel Otomatis Sesuai Kategori: Panduan Lengkap
Blog: miablog.web.id | kecamatandankota.blogspot.com | Channel: PAPAHQIQI CHANNEL
- Kenapa Input Data Masih Manual? Kenalan dengan Dropdown Bertingkat
- Kunci Rahasia: Kombinasi Name Manager dan Rumus INDIRECT
- 5 Langkah Praktis Membuat Dropdown Bertingkat
- Trik Khusus Menangani Kategori yang Mengandung Spasi
- Pertolongan Pertama Mengatasi Error dan Data Tidak Muncul
- FAQ Netizen Excel dan Administrasi Perkantoran
- Panduan Teknis SEO dan Rendering Blogger
1. Kenapa Input Data Masih Manual?
Mengisi data laporan kantor, data kependudukan, atau rekap anggaran secara manual sering memicu kesalahan ketik (human error). Contohnya memilih Kategori "Kecamatan Cipedes" tetapi di kolom sebelah terketik nama Kelurahan yang berada di "Kecamatan Cihideung".
Dengan fitur Dropdown List Bertingkat (Dependent Dropdown List), pilihan pada dropdown kedua akan otomatis menyesuaikan pilihan yang Anda klik pada dropdown pertama.
2. Kunci Rahasia: Kombinasi Name Manager dan Rumus INDIRECT
Untuk membuat dropdown bertingkat tanpa VBA atau Macro (100% rumus standar Excel), kita hanya butuh dua fitur utama:
- Name Manager (Create from Selection): Untuk memberi nama identitas pada sekelompok sel atau data.
- Data Validation + Rumus INDIRECT: Rumus =INDIRECT() mengubah teks nama kategori menjadi referensi rentang sel yang aktif.
3. 5 Langkah Praktis Membuat Dropdown Bertingkat
Ikuti simulasi data wilayah pemerintahan sederhana berikut.
Langkah 1: Siapkan Master Data
Buat tabel master di Sheet baru (misal Sheet_Master). Susun header kolom sebagai Kategori Utama, isi di bawahnya sebagai Sub-Kategori.
| Kecamatan_Cihideung | Kecamatan_Cipedes | Kecamatan_Tawang |
|---|---|---|
| Nagarawangi | Panglayungan | Kahuripan |
| Cilembang | Nagarasari | Tawangsari |
| Tugujaya | Sukamanah | Lengkongsari |
| Argasari | Cipedes | Empangsari |
Langkah 2: Buat Named Range untuk Kategori Utama
- Blok judul kolom utama (Kecamatan_Cihideung, Kecamatan_Cipedes, Kecamatan_Tawang).
- Klik menu Formulas pada Ribbon Excel.
- Ketik nama rentang di Name Box (kotak kiri Formula Bar), misal: Daftar_Kecamatan.
- Tekan Enter.
Langkah 3: Buat Named Range Otomatis untuk Sub-Kategori
- Blok seluruh tabel master (dari judul hingga baris paling bawah).
- Pilih menu Formulas - Create from Selection (atau Ctrl + Shift + F3).
- Pada pop-up, centang hanya opsi Top row.
- Klik OK. Excel otomatis jadikan judul kolom sebagai nama rentang untuk data di bawahnya.
Langkah 4: Buat Dropdown Induk (Kategori Pertama)
- Pindah ke Sheet Input (misal Sheet_Input).
- Klik sel dropdown induk (misal A2).
- Klik Data - Data Validation.
- Allow: List. Source: =Daftar_Kecamatan
- Klik OK. Sel A2 sudah berisi pilihan nama Kecamatan.
Langkah 5: Buat Dropdown Anak (Sub-Kategori Bertingkat)
- Klik sel dropdown sub-kategori (misal B2).
- Klik Data - Data Validation. Allow: List.
- Source masukkan rumus INDIRECT yang mengarah ke A2:
Klik OK.
4. Trik Khusus: Menangani Kategori yang Mengandung Spasi
Excel tidak mengizinkan spasi dalam penamaan Name Manager. Jika kategori mengandung spasi (Kota Tasikmalaya), Excel menolak atau otomatis ganti spasi jadi underscore (Kota_Tasikmalaya). Jika sel induk berisi Kota Tasikmalaya, =INDIRECT(A2) akan error karena tidak menemukan nama range berspasi.
Solusi Rumus SUBSTITUTE:
Rumus SUBSTITUTE(A2," ","_") akan ubah spasi di sel A2 jadi garis bawah secara otomatis sebelum dibaca INDIRECT.
5. Pertolongan Pertama: Mengatasi Error dan Data Tidak Muncul
| Gejala Masalah | Penyebab Utama | Solusi Praktis |
|---|---|---|
| Error #REF! pada Dropdown | Teks di sel Induk tidak cocok dengan nama di Name Manager | Pastikan ejaan, huruf besar kecil, dan tanda baca sama persis |
| Dropdown Kedua Kosong | Sel Induk belum dipilih saat pembuatan rumus | Pilih dulu nilai pada dropdown pertama, baru setting Data Validation kedua |
| Pilihan Data Tidak Update | Ada penambahan baris baru di Master | Gunakan Format as Table (Ctrl + T) agar Name Manager dinamis |
6. FAQ Netizen Excel dan Administrasi Perkantoran
Sangat bisa. Logikanya sama. Dropdown Level 3 cukup diarahkan pada sel Dropdown Level 2 dengan rumus =INDIRECT(SUBSTITUTE(B2," ","_")).
Bisa, tetapi Google Sheets menggunakan pendekatan berbeda pada fitur Data Validation (menggunakan opsi Dropdown from a range dengan rumus dinamis).
Ubah rentang data master menjadi Excel Table dengan menekan Ctrl + T. Setiap baris baru yang ditambahkan otomatis masuk ke Name Manager.
7. Panduan Teknis SEO dan Rendering Blogger
Agar artikel disukai Google dan nyaman di HP via Blogger miablog.web.id:
- Custom Permalink: dropdown-bertingkat-excel-otomatis
- Search Description: Tutorial lengkap cara membuat dropdown list bertingkat di Excel otomatis menggunakan rumus INDIRECT dan Name Manager tanpa VBA. Mudah dan akurat.
- Labels: Tutorial Excel, Administrasi Perkantoran, Produktivitas Kerja, Aplikasi Kantor