Daftar Drop-down Excel: Penyiapan, Tips, dan Pemecahan Masalah

Pernahkah Anda mengeklik panah kecil di sebuah sel Excel lalu muncul daftar opsi? Ini bisa berupa daftar departemen, wilayah, status, atau kategori. Jika jawabannya ya, Anda sudah berinteraksi dengan daftar drop-down. 

Daftar drop-down Excel membimbing pengguna agar memasukkan data secara konsisten, sehingga mengurangi kemungkinan salah ketik atau entri yang tidak sesuai. Baik saat mengisi pelacak proyek, membuat lembar anggaran, atau merancang formulir untuk digunakan orang lain, drop-down membantu menjaga semuanya tetap rapi dan terprediksi.

Dalam panduan ini, saya akan menunjukkan cara membuat daftar tersebut dari nol, menyesuaikannya sesuai kebutuhan Anda, memperbaikinya saat ada masalah, dan bahkan membangun versi yang lebih dinamis dan interaktif untuk alur kerja lanjutan. Anda tidak perlu menjadi ahli Excel untuk memulainya; cukup dengan spreadsheet yang aktif dan beberapa titik data saja sudah cukup. 

Jika Anda baru mulai menggunakan Excel, kursus Introduction to Excel kami membahas keterampilan seperti menavigasi antarmuka, memahami format data, dan bekerja dengan fungsi dasar. Selain itu, menurut saya Excel Formulas Cheat Sheet yang dapat Anda unduh sangat membantu sebagai referensi karena memuat semua fungsi Excel yang paling umum.

Membuat Daftar Drop-Down Dasar di Excel

Sekarang, mari kita lihat cara membuat daftar drop-down di Excel

Langkah demi langkah pembuatan

Untuk membuat daftar drop-down di Excel, gunakan langkah-langkah berikut:

Langkah 1: Siapkan data sumber

Sebelum membuat drop-down, tentukan item apa saja yang akan dimasukkan ke dalam daftar. Anda dapat mengetik pilihan ini langsung saat menyiapkan drop-down, atau mencantumkannya di sel pada spreadsheet Anda.

Langkah 2: Menerapkan validasi data

Saat daftar Anda siap:

• Sorot sel atau rentang sel tempat Anda ingin menampilkan drop-down.

• Buka tab Data pada pita dan klik Data Validation.

• Di kotak dialog yang muncul, pada bagian Allow, pilih List.

• Di kotak Source, ketik nilai Anda secara langsung atau rujuk rentang sel tempat daftar Anda berada.

Langkah 3: Finalisasi dan pengujian

Setelah memastikan Anda telah memasukkan rentang yang benar:

• Tekan OK untuk menyelesaikan.
• Klik salah satu sel yang divalidasi. Anda akan melihat panah kecil di sebelah kanan, tempat Anda dapat memilih entri dari daftar.

Jika Anda memasukkan item secara manual yang sebelumnya tidak didefinisikan ke dalam sel, Anda akan mendapatkan error. Validasi ini membantu mencegah kesalahan saat entri data.

Daftar dinamis berbasis Tabel (Table Magic)

Jika Anda memerlukan kontrol lebih atas daftar Anda, Anda dapat menggunakan tabel Excel untuk membuat daftar dinamis. Ikuti langkah-langkah di bawah ini:

• Pilih daftar sumber Anda dan tekan Ctrl + T (atau buka tab Insert > Table).
• Pastikan untuk mencentang "My table has headers".

• Beri nama tabel Anda yang bermakna di bawah tab Table Design.

• Pilih rentang sel tempat daftar drop-down Anda akan muncul, lalu pilih tab Data > Data Validation > List.

• Di kolom "Source", ketik =INDIRECT("DepartmentList[Department]")

Saat Anda mengonversi daftar sumber menjadi tabel, Anda memungkinkan Excel untuk secara otomatis menyertakan item baru ke daftar drop-down saat ditambahkan. 

Lihat Excel Shortcuts Cheat Sheet untuk mempelajari cara meningkatkan produktivitas dengan mempelajari pintasan untuk berbagai fitur Excel.

Cara Menambahkan atau Menghapus Item dari Daftar Drop-Down

Pada titik tertentu, Anda mungkin perlu memperbarui daftar drop-down. Saya akan menunjukkan cara menghapus atau menambahkan item ke daftar drop-down.

Jika Anda membuat daftar drop-down menggunakan input manual, cukup tambahkan item baru di akhir daftar.

Anda juga dapat menambahkan item baru ke rentang sel jika Anda memilih "Source" sebagai rentang sel.

Jika Anda mereferensikan daftar dari tabel Excel (seperti yang saya tunjukkan sebelumnya), ketik nilai baru di bawah baris terakhir. Excel akan otomatis memperluas dan memperbarui tabel, yang juga akan memperbarui daftar Anda.

Cara Menghapus Daftar Drop-Down

Anda dapat menghapus daftar drop-down dari lembar Excel tanpa menghapus data yang sudah Anda masukkan.

Untuk menghapus daftar drop-down yang dibuat menggunakan Data Validation:

• Pilih sel atau rentang yang berisi drop-down.
• Buka Data > Data Validation.
• Di kotak dialog, klik Clear All > OK.

Metode ini menghapus aturan validasi dan panah drop-down. Nilai sel yang ada tetap utuh tetapi tidak lagi dibatasi pada opsi drop-down sebelumnya.

Jika Anda menggunakan combo box atau kontrol ActiveX:

• Buka Developer > Design Mode.
• Pilih kontrolnya, lalu tekan Delete pada keyboard Anda.

Perlu dicatat bahwa data Anda yang ada akan tetap utuh meskipun setelah menghapus aturan validasi untuk daftar drop-down.

Teknik Lanjutan: Daftar Dinamis dan Dependen

Sekarang setelah Anda mempelajari dasar-dasarnya, mari kita lihat cara membuat daftar yang fleksibel untuk penggunaan lanjutan.

Daftar drop-down dinamis

Daftar drop-down dinamis akan diperbarui secara otomatis setiap kali Anda mengubah data sumber. Jika daftar Anda memiliki duplikat, sebaiknya gunakan fungsi UNIQUE() terlebih dahulu untuk mengambil nilai unik. Misalnya, jika data Anda ada di "A2:A21", Anda dapat menggunakan rumus di bawah ini di tempat lain untuk membuat daftar yang lebih bersih bagi drop-down Anda.

=UNIQUE(A2:A21)

Kemudian Anda dapat menggunakan rentang keluaran ini sebagai sumber untuk drop-down Anda.

Anda juga dapat menggunakan fungsi OFFSET() jika daftar Anda bertambah, tetapi Anda tidak ingin mengonversinya menjadi tabel formal. 

=OFFSET(ListData!$A$2, 0, 0, COUNTA(ListData!$A:$A) -1)

Daftar drop-down dinamis digunakan pada formulir langsung, lembar pelacakan, atau spreadsheet kolaboratif. Fitur ini memastikan pembaruan otomatis saat pengguna memasukkan data atau ketika data berubah.

Drop-down dependen (bertingkat)

Daftar drop-down dependen (cascading drop-down) adalah serangkaian drop-down di mana pilihan pada satu daftar bergantung pada seleksi yang dibuat di daftar lain. Ini ideal untuk data hierarkis seperti kategori dan subkategori.

Saat membuat drop-down dependen, Anda terlebih dahulu membuat rentang bernama untuk setiap grup sub-item. Drop-down kedua menggunakan fungsi INDIRECT() untuk mereferensikan rentang bernama yang sesuai dengan pilihan pertama.

Langkah 1: Siapkan data sumber Anda

Buat daftar kategori dan sub-kategori di kolom terpisah mengikuti urutan tertentu. Pastikan setiap rentang sub-kategori dinamai menggunakan nama "Category" yang sesuai seperti yang muncul di daftar.

Langkah 2: Buat daftar drop-down pertama

Pada drop-down pertama, pilih rentang kategori utama. Gunakan Data Validation > List dan atur sumber dari rentang kolom 'Category'. Langkah ini mirip dengan yang telah kita gunakan sebelumnya.

Langkah 3: Buat daftar drop-down dependen

Selanjutnya, siapkan drop-down sub-kategori. Buka Data Validation > List. Untuk sumber, arahkan agar mereferensikan sel pada daftar drop-down pertama.

=INDIRECT(A2)

Langkah 4: Uji drop-down

Periksa apakah item sudah ditempatkan dengan benar di kolom 'Category' dan 'Sub-Category'.

Berikut adalah masalah umum yang perlu diperhatikan saat menggunakan drop-down dependen:

• Pastikan rentang subkategori bernama cocok dengan teks di drop-down kategori utama. Tidak boleh ada spasi ekstra dan cocokkan huruf besar-kecilnya.

• Jika INDIRECT() mengembalikan error #REF!, periksa kembali bahwa rentang bernama ada dan sesuai dengan nilai daftar utama.

Kustomisasi dan Pengalaman Pengguna

Anda dapat membuat daftar drop-down menjadi lebih fleksibel untuk meningkatkan pengalaman pengguna dan kegunaan. Pada bagian ini, saya akan menunjukkan cara menyesuaikan daftar drop-down untuk berbagai kasus penggunaan.

Pesan input dan peringatan kesalahan

Excel memungkinkan Anda melampirkan pesan singkat ke sel drop-down untuk membantu pengguna membuat pilihan yang tepat. Untuk menyiapkan pesan input:

• Pilih sel drop-down.
• Buka Data > Data Validation.
• Beralih ke tab Input Message.
• Masukkan judul dan pesan seperti "Pilih departemen dari daftar."

Panah drop-down akan muncul dengan pesan di samping sel yang dipilih. Ini akan membantu pengguna memahami data yang diperlukan untuk kolom tersebut.

Anda dapat membuat peringatan "Error" muncul jika seseorang mencoba memasukkan data yang tidak sesuai dengan opsi drop-down. Untuk menyesuaikan fitur ini:

• Buka Data Validation > tab Error Alert.
• Pilih gaya peringatan: Stop (mencegah entri data tidak valid), Warning (memperingatkan tetapi mengizinkan pengabaian), atau Information (menampilkan info tetapi membiarkan entri dilanjutkan)
• Berikan pesan yang menjelaskan pembatasan, seperti "Harap pilih departemen yang valid dari daftar."

Selalu gunakan bahasa yang jelas dan ramah pengguna dalam pesan input dan peringatan kesalahan untuk meningkatkan kejelasan pesan.

Drop-down yang dapat dicari

Di versi Excel modern, seperti Microsoft 365 dan Excel untuk web, Anda dapat menggunakan fungsionalitas drop-down yang dapat dicari, terutama saat menavigasi daftar item yang panjang. Saat Anda mengeklik panah drop-down, Anda dapat mulai mengetik, dan Excel akan memfilter daftar agar sesuai dengan input Anda. Fitur ini penting saat bekerja dengan daftar panjang, seperti nama pelanggan, SKU produk, atau nama negara.

Namun, drop-down yang dapat dicari hanya tersedia di versi Excel terbaru dan tidak di versi desktop lama seperti Excel 2016 atau 2019. Jika Anda menggunakan versi Excel yang lebih lama, gunakan combo box atau kontrol formulir dengan kemampuan pencarian bawaan melalui VBA untuk membuat daftar drop-down yang dapat dicari.

Mengizinkan entri lain atau input manual

Terkadang Anda mungkin ingin menambahkan item ke daftar sambil melewati validasi awal. Jika Anda ingin menambahkan data kustom:

• Pilih sel atau rentang sel tempat Anda ingin menampilkan drop-down.
• Buka Data > Data Validation.
• Pada tab Error Alert, hapus centang pada kotak "Show error alert after invalid data is entered".

Saat Anda menghapus centang opsi ini, Anda dapat memasukkan nilai apa pun yang tidak ada di daftar opsi drop-down.

Meskipun mengaktifkan entri manual meningkatkan fleksibilitas, hal ini dapat menyebabkan data tidak konsisten atau tidak valid jika pengguna salah ketik atau memasukkan nilai yang tidak terduga. Ini juga mengurangi manfaat memiliki daftar yang terkontrol.

Untuk menangani entri yang tidak ada dalam daftar tersebut:

• Gunakan pemformatan bersyarat untuk menandai sel dengan nilai yang tidak ada di daftar validasi untuk ditinjau.

• Buat kolom pembantu yang memeriksa validitas. Misalnya, gunakan COUNTIF() untuk memverifikasi apakah entri ada di daftar sumber.

• Secara berkala audit dan perbarui daftar sumber Anda untuk mengakomodasi entri baru yang valid.

Lihat kursus Conditional Formatting in Google Sheets kami untuk mempelajari cara menerapkan pemformatan bersyarat guna memvalidasi data untuk mendapatkan wawasan cepat.

Form Controls dan ActiveX untuk Fungsionalitas yang Lebih Tinggi

Jika Anda memerlukan fleksibilitas dan fungsionalitas yang lebih tinggi pada daftar drop-down, Excel menyediakan kontrol lanjutan seperti Form Control dan combo box ActiveX.

Combo box Form Control

Form Control combo box bekerja seperti daftar drop-down standar tetapi memungkinkan pengguna menautkannya dengan sel lain. Metode ini berguna saat mengintegrasikan daftar drop-down dengan formulir atau dasbor.

Untuk menggunakan combo box:

• Buka tab Developer.
• Klik Insert > Form Controls, pilih Combo Box.

• Gambar combo box di lembar kerja Anda.

• Klik kanan kontrol dan pilih Format Control.

• Di tab Control, atur "Input Range" dan "Cell Link" tempat indeks item yang dipilih akan muncul.

Anda akan melihat bahwa combo box mengembalikan angka yang sesuai dengan posisi item dalam daftar. Anda dapat mengambil nilai sebenarnya dari posisi item menggunakan fungsi INDEX().

Form control lebih disukai saat membangun dasbor atau laporan interaktif. Kontrol ini juga dapat digunakan pada skenario saat VBA tidak diperlukan, namun Anda membutuhkan pemformatan yang lebih fleksibel dibandingkan yang diizinkan oleh validasi data standar.

Kotak ActiveX Control

ActiveX Control menawarkan daya dan kustomisasi lebih, termasuk kontrol font, pelengkapan otomatis, dan kemampuan memicu makro berdasarkan interaksi pengguna.

Untuk menambahkan kotak ActiveX Control

• Buka tab Developer > Insert > ActiveX Controls > Combo Box.

• Gambar kotaknya di lembar.
• Klik kanan dan pilih Properties untuk mengonfigurasi seperti "ListFillRange", "LinkedCell", lalu sesuaikan font, warna latar, dan gaya batas.

Keunggulan menggunakan ActiveX Combo Box adalah fleksibilitas pemformatan yang lebih besar untuk font, warna, dan tata letak. Ini juga memungkinkan pemrograman berbasis event untuk formulir dan aplikasi yang sangat interaktif. Fitur ini terintegrasi dengan makro dan automasi.

Namun, kontrol ActiveX hanya berfungsi di Windows dan tidak didukung di Excel untuk Mac atau Excel Online. Kontrol ini juga lebih berat dibanding form control dan dapat memperlambat kinerja di workbook besar. Untuk penggunaan lanjutan, Anda mungkin memerlukan pengetahuan VBA.

Pemecahan Masalah Umum

Bahkan dengan fitur lanjutan, Anda mungkin mengalami beberapa masalah saat bekerja dengan daftar drop-down di Excel. Mari kita bahas jebakan umum dan cara men-debug masalah tersebut.

Error daftar dan perbaikannya

Berikut adalah masalah umum dan cara memperbaikinya:

• Opsi kosong dalam drop-down: Daftar drop-down Anda mungkin menyertakan baris kosong. Untuk mengatasinya, pastikan rentang sumber tidak menyertakan sel kosong.
• Item hilang dalam drop-down: Beberapa item mungkin tidak muncul di daftar drop-down Anda. Untuk memperbaikinya, verifikasi bahwa rentang sumber atau tabel mencakup semua item yang diharapkan. Konfirmasikan bahwa sumber Data Validation mencakup daftar lengkap untuk daftar berbasis rentang dan sesuaikan seperlunya.
• Referensi salah atau usang: Jika ada referensi yang salah, pastikan formula atau daftar sumber Data Validation mengarah ke lembar dan rentang sel yang benar. Perhatikan juga salah ketik atau perubahan tidak sengaja pada rentang bernama.
• Daftar drop-down tidak muncul atau panah drop-down hilang: Jika Anda menghapus validasi data, daftar drop-down mungkin tidak muncul. Selalu pastikan Anda memilih "List" sebagai jenis validasi dan terapkan kembali Data Validation setiap kali Anda menghapus pemformatan.

Debugging Lanjutan

Beberapa masalah ini mungkin melibatkan kesalahan rumus atau perilaku spill yang terkait dengan daftar dinamis dan rentang bernama:

• Error #REF!: Error ini terjadi saat rumus atau rentang bernama merujuk ke sel, lembar, atau tabel yang dihapus. Tinjau dan perbarui rentang bernama atau rumus yang menggunakan OFFSET(), INDIRECT(), atau fungsi array dinamis untuk memperbaiki error ini.

• Error #SPILL!: Error ini terjadi saat rumus array dinamis seperti UNIQUE() mencoba mengeluarkan nilai, tetapi area spill terhalang data lain. Selalu periksa bahwa tidak ada sel gabungan yang kosong agar rumus dapat mengisi sel yang berdekatan.

• Ketidaksesuaian array dinamis: Jika Anda membuat daftar menggunakan fungsi seperti UNIQUE(), SORT(), atau FILTER(), ukuran keluarannya dapat berubah. Oleh karena itu, gunakan rentang bernama dinamis atau rujuk seluruh kolom dari keluaran rumus.

Saya merekomendasikan mengikuti kursus Advanced Excel Functions untuk mempelajari lebih lanjut tentang offset dan rentang dinamis di Excel.

Kesimpulan

Daftar drop-down di Excel berguna untuk membimbing entri data, memastikan konsistensi, dan meningkatkan kegunaan spreadsheet Anda secara keseluruhan. Dari pembuatan daftar dasar dan tautan tabel dinamis hingga pemilihan bertingkat dan kontrol formulir kustom, alat ini membantu membuat spreadsheet lebih interaktif, akurat, dan ramah pengguna. 

Menguasai teknik drop-down mengurangi kesalahan, meningkatkan konsistensi data, dan meletakkan dasar bagi solusi spreadsheet yang lebih profesional dan dapat diskalakan. Saya mendorong Anda untuk meningkatkan keterampilan dengan mempelajari integrasi fungsionalitas drop-down dengan Power Query atau menggunakan VBA untuk membuka automasi dan kecerdasan yang lebih besar dalam alur kerja Excel Anda.

Jika Anda ingin meningkatkan keterampilan Excel, saya merekomendasikan kursus Data Analysis in Excel kami. Kursus ini akan membantu Anda menguasai analitik lanjutan dan mendorong karier Anda. Saya juga merekomendasikan kursus Intermediate Power Query in Excel kami untuk mempelajari transformasi data dan penggunaan bahasa M untuk membuat fungsi dinamis.