Untuk membuat drop down list di Excel, pilih selnya, buka Data > Data Validation, atur Allow menjadi List, ketik item-itemnya di Source dengan pemisah koma (North,South,East,West) atau pilih rentang yang berisi item-item itu, lalu tekan OK. Setiap sel sekarang menampilkan panah berisi pilihan-pilihan tersebut, dan isian lain akan ditolak.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Region | Sales | |
| 2 | Ana | North | 120 | North | 360 | |
| 3 | Ben | South | 85 | |||
| 4 | Cara | North | 240 | |||
| 5 | Dan | East | 60 | |||
| 6 | Eve | South | 150 |
F2 menampilkan 360, total untuk North. Pilih North di B3 dan F2 bertambah sebesar 85 milik Ben. E2 juga punya drop down: pilih South di sana dan F2 menampilkan total South. Daftar untuk input ditambah rumus yang membacanya adalah cara pakai drop down yang paling umum.
Cara membuat drop down list, langkah demi langkah
- Pilih sel yang akan diberi daftar, misalnya B2:B6.
- Buka Data > Data Validation (grup Data Tools). Di Windows, urutan tombolnya Alt, A, V, V.
- Di tab Settings, atur Allow menjadi List.
- Di Source, ketik item-itemnya dengan pemisah koma,
North,South,East,West, atau klik di kotaknya lalu pilih rentang berisi item-item itu di lembar kerja, yang akan menuliskan=$F$2:$F$5. - Biarkan In-cell dropdown tetap dicentang (tanpa itu tidak ada panah, hanya pengecekannya).
- Tekan OK.
Untuk membuka daftarnya dengan keyboard, pilih selnya lalu tekan Alt+Down Arrow (Windows) atau Option+Down Arrow (Mac). Di Excel untuk Microsoft 365, mengetik huruf-huruf pertama di sel akan mempersempit daftar ke item yang cocok.
Ada dua tab opsional di dialog yang sama: Input Message menampilkan tips saat sel dipilih, dan Error Alert mengatur apa yang terjadi saat seseorang mengetik nilai yang tidak ada di daftar. Dengan gaya Stop (bawaan), isiannya ditolak; dengan Warning atau Information, isiannya diizinkan setelah muncul peringatan. Hilangkan centang Show error alert after invalid data is entered agar orang bisa mengetik apa saja sambil tetap ditawari daftarnya.
Item yang diketik dipisahkan dengan pemisah daftar dari pengaturan regional komputer. Di sebagian besar wilayah yang memakai koma sebagai pemisah desimal (Prancis, Jerman, Spanyol, Italia), pemisahnya adalah titik koma: Nord;Sud;Est;Ouest.
Drop down list dari rentang sel
Daftar yang diketik di dialog tersembunyi dari pandangan dan harus diedit di sana. Daftar di dalam sel lebih mudah dirawat: ubah sebuah sel dan setiap drop down yang memakainya ikut berubah. Di sini wilayahnya ada di E2:E5, dan drop down di B2:B6 memakai rentang itu sebagai sumbernya.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Regions | |
| 2 | Ana | North | 120 | North | |
| 3 | Ben | South | 85 | South | |
| 4 | Cara | North | 240 | East | |
| 5 | Dan | East | 60 | West | |
| 6 | Eve | South | 150 |
Ubah E5 dari West menjadi Central, lalu buka panah mana pun di kolom B: daftarnya menawarkan Central sebagai ganti West. Nilai yang sudah dipilih di kolom B tidak berubah.
Untuk memakai rentang di sheet lain, yang merupakan cara biasa untuk menyembunyikan daftar, ketik nama sheet di Source: =Lists!$A$2:$A$5. Agar daftarnya bertambah saat kamu menambah item di bagian bawah, ubah dulu item-itemnya menjadi tabel (pilih item-itemnya, Insert > Table) lalu pilih kolom tabel itu sebagai sumbernya: referensinya ikut melebar bersama tabel.
Drop down list dinamis dengan UNIQUE
Kalau item-itemnya harus diambil dari datanya sendiri (setiap wilayah yang muncul di sebuah kolom, masing-masing sekali), buat daftarnya dengan rumus lalu arahkan drop down ke hasilnya. =SORT(UNIQUE(B2:B8)) di G2 menampilkan wilayah-wilayah yang berbeda sebagai spill, dalam urutan abjad.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Pick | Sales | Regions | |
| 2 | Ana | North | 120 | South | 235 | East | |
| 3 | Ben | South | 85 | North | |||
| 4 | Cara | North | 240 | South | |||
| 5 | Dan | East | 60 | West | |||
| 6 | Eve | South | 150 | ||||
| 7 | Fay | West | 95 | ||||
| 8 | Gus | East | 110 |
G2 menampilkan East, North, South, West sebagai spill, dan drop down di E2 menawarkan keempatnya. Ubah B7 menjadi Central dan Central muncul di hasil spill maupun di daftar.
Di Excel, atur Source drop down-nya menjadi =$G$2#. Tanda # setelah sebuah sel berarti "seluruh hasil spill dari rumus ini", jadi panjang daftarnya selalu persis sama dengan hasilnya, tanpa baris kosong di akhir. Referensi spill butuh Excel 365 atau 2021; sel sumbernya boleh berada di sheet lain (=Lists!$A$2#). Kalau kolom datanya punya sel kosong, UNIQUE menghasilkan 0 untuk sel-sel itu; buang dengan =SORT(UNIQUE(FILTER(B2:B100,B2:B100<>""))). Halaman UNIQUE membahas fungsi ini secara lengkap.
Drop down list bertingkat
Daftar bertingkat berubah mengikuti pilihan di sel lain: pilih Fruit di A2, dan B2 hanya menawarkan buah. Di Excel 365 dan 2021, rumus FILTER membuat daftar keduanya: =FILTER(E2:E8,D2:D8=A2) menghasilkan item yang kategorinya cocok dengan A2, dan drop down di B2 memakai hasil spill itu sebagai sumbernya.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Category | Item | Category | Item | Items | ||
| 2 | Fruit | Pear | Fruit | Apple | Apple | ||
| 3 | Fruit | Pear | Pear | ||||
| 4 | Vegetable | Carrot | Kiwi | ||||
| 5 | Vegetable | Leek | |||||
| 6 | Bakery | Bread | |||||
| 7 | Fruit | Kiwi | |||||
| 8 | Bakery | Bagel |
Dengan Fruit di A2, G2 menampilkan Apple, Pear, dan Kiwi sebagai spill, dan itulah pilihan di B2. Pilih Bakery di A2: G2 berubah menjadi Bread dan Bagel. B2 tetap berisi Pear sampai kamu memilih lagi, karena drop down tidak pernah mengubah nilai yang sudah ada di sel. Di Excel, sumber untuk B2 adalah =$G$2#.
Di Excel versi lama, cara klasiknya memakai INDIRECT dan rentang bernama:
- Taruh item-item setiap kategori di kolomnya sendiri, dengan nama kategori sebagai header-nya: Fruit di satu kolom, Vegetable di kolom berikutnya.
- Pilih setiap kolom item lalu beri nama sesuai kategorinya di Name Box (di kiri formula bar):
Fruit,Vegetable,Bakery. - Beri A2 drop down dengan sumber
Fruit,Vegetable,Bakery. - Beri B2 drop down dengan sumber
=INDIRECT(A2). INDIRECT mengubah teks di A2 menjadi referensi ke rentang dengan nama tersebut.
Namanya harus sama persis dengan teks kategorinya dan tidak boleh mengandung spasi (pakai Dairy_Products, atau =INDIRECT(SUBSTITUTE(A2," ","_")) di sumbernya). Penjelasan lebih lanjut tentang INDIRECT ada di halaman INDIRECT.
Mencari nilai dari item yang dipilih
Drop down sering menjadi input di formulir pesanan atau penawaran harga: pengguna memilih produk, lalu sebuah rumus pencarian mengisi harganya.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Product | Price | ||
| 2 | Order | Pear | Apple | $1.20 | ||
| 3 | Pear | $1.50 | ||||
| 4 | Carrot | $0.80 | ||||
| 5 | Bread | $2.40 | ||||
| 6 | Milk | $1.10 |
Giliranmu: Di C2, hasilkan harga produk yang dipilih di B2 dari tabel di E:F.
Dengan Pear terpilih, jawabannya $1,50. Pilih produk lain di B2 dan harganya ikut berubah. =XLOOKUP(B2,E2:E6,F2:F6) juga bisa; lihat VLOOKUP untuk argumennya.
Mewarnai sel berdasarkan item yang dipilih
Untuk mewarnai sel berdasarkan apa yang dipilih (hijau untuk Done, merah untuk Late), tambahkan aturan conditional formatting ke sel yang sama: pilih B2:B6, buka Home > Conditional Formatting > Highlight Cells Rules > Equal To, ketik Late, lalu pilih formatnya. Untuk mewarnai satu baris penuh, pilih A2:B6 lalu pakai New Rule > Use a formula to determine which cells to format dengan =$B2="Late".
| A | B | |
|---|---|---|
| 1 | Task | Status |
| 2 | Quote | Done |
| 3 | Invoice | Late |
| 4 | Order | Open |
| 5 | Report | Late |
| 6 | Survey | Done |
B3 dan B5 disorot. Pilih Late di B4 dan sel itu ikut disorot; pilih Done di B3 dan sorotannya hilang. Halaman conditional formatting membahas aturannya secara lengkap.
Kenapa drop down list tidak berfungsi
- In-cell dropdown tidak dicentang di Data > Data Validation. Daftarnya tetap membatasi isian, tetapi tidak ada panah.
- Panahnya hanya muncul di sel yang dipilih. Tidak ada tanda apa pun di kisi untuk sel lain yang punya daftar; untuk menemukannya, pakai Home > Find & Select > Data Validation.
- Rentang Source punya sel kosong, jadi daftarnya menampilkan baris kosong. Pilih hanya sel yang terisi, atau pakai sumber berupa spill (
=$G$2#), yang tidak punya sel kosong. - Item diketik dengan pemisah yang salah:
North;Southdi Excel yang memakai koma akan menjadi satu item bernamaNorth;South. - Drop down hanya menyimpan satu nilai. Memilih item kedua akan menggantikan yang pertama; memilih beberapa item di satu sel butuh makro VBA.
Untuk menyalin drop down ke sel lain tanpa menyalin nilainya, salin selnya, lalu pakai Home > Paste > Paste Special > Validation. Untuk menghapusnya, pilih selnya lalu pilih Data > Data Validation > Clear All.
Pertanyaan yang sering diajukan
Bagaimana cara membuat drop down list di Excel?
Pilih selnya, buka Data > Data Validation, atur Allow menjadi List, ketik item-itemnya di Source dengan pemisah koma (North,South,East) atau pilih rentang yang berisi item-item itu (=$F$2:$F$5), lalu tekan OK.
Bagaimana cara mengedit drop down list di Excel?
Pilih sel yang punya daftar itu, buka Data > Data Validation, lalu ubah kotak Source. Centang Apply these changes to all other cells with the same settings untuk memperbarui semua salinannya. Kalau sumbernya berupa rentang, mengedit sel di rentang itu sudah mengubah daftarnya tanpa perlu membuka dialog.
Bagaimana cara menghapus drop down list di Excel?
Pilih selnya, buka Data > Data Validation, klik Clear All, lalu OK. Nilai yang sudah dipilih tetap ada di sel; hanya panah dan batasannya yang hilang.
Bagaimana cara membuat drop down list dari sheet lain?
Ketik referensinya beserta nama sheet di Source: =Lists!$A$2:$A$6, atau klik sheet lain itu lalu pilih rentangnya selagi kotak Source aktif. Rentang bernama (Formulas > Define Name) juga bisa: =Regions.
Bagaimana cara membuat drop down list yang otomatis diperbarui?
Arahkan ke rumus yang menghasilkan spill: taruh =SORT(UNIQUE(FILTER(B2:B100,B2:B100<>""))) di sel bantu seperti H2 lalu pakai =$H$2# sebagai Source. Nilai baru di kolom B langsung muncul di daftar, dan FILTER menjaga agar baris kosong tidak ikut masuk. Cara ini butuh Excel 365 atau 2021.
