Lompat ke konten
Anda disini: M Jurnal » Excel Dasar » Cara Membuat Drop Down List Pada Excel (dari A – Z)

Cara Membuat Drop Down List Pada Excel (dari A – Z)

Cara membuat drop down list excel dengan mudah - Drop Down List Bertingkat ( Saling Terhubung )

Drop Down List pada Excel adalah list tertentu (bisa angka / teks) yang berguna untuk validasi data pada cell Microsoft Excel. List akan tampil dalam bentuk pilihan drop down.

Misalnya, Coba Anda lihat Tab Home pada Excel. Kemudian pada bagian Font, klik icon panah kebawah. Akan muncul List jenis Font yang tersedia pada Excel.

Nah, cara membuat drop down list ini lah yang akan Anda pelajari pada panduan ini. Saya yakin Anda pasti menemukan banyak sekali manfaat Drop Down List Excel ini.

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

Buat Drop Down List Manual

Drop Down List manual sangat berguna jika Anda hanya memiliki sedikit list data. Sebagai contoh, silahkan buka Sheet Drop Down 1 pada Excel Online.

Pada Cell A1 terdapat form data Jenis Kelamin. Sedangkan pengguna akan mengisi data Jenis Kelamin pada Cell B1. Saya ingin pengguna hanya mengetik Laki-Laki atau Perempuan agar tidak terjadi kesalahan input data.

Sementara, persepsi setiap orang terhadap jenis kelamin bisa berbeda-beda. Ada yang menganggap Pria dan Wanita, Laki-Laki dan Perempuan, Cowok dan Cewek. Terlebih lagi ada juga resiko kesalahan Typo seperi lki-laki atau prempuan.

Agar data yang diinput seragam dan tidak terjadi kesalahan, silahkan Anda ikuti tahap – tahap membuat drop down list manual berikut:

Cara membuat drop down list excel
  1. Klik Cell B1 sebagai tempat data drop down Anda.
  2. Klik Tab Data, kemudian klik Icon Data Validations.
  3. Muncul Window Data Validation. Klik menu Setting kemudian Ubah Allow menjadi List
  4. Ketik Laki-Laki, Perempuan pada source sebagai list.
  5. Centang In-Cell Dropdown jika belum. Kemudian klik Ok untuk melanjutkan. Berikut hasilnya:
Cara membuat drop down list excel dengan mudah - Drop Down List Bertingkat ( Saling Terhubung )

Silahkan klik icon panah kebawah tepat disamping kanan cell B1, Anda akan melihat list drop down yang telah dibuat.

Jika Anda ingin menghapus atau menambah list data pada drop down, Anda harus membuka kembali window data validation. Kemudian hapus atau tambahkan list baru ke dalam form source.

Sedikit merepotkan sih karena harus membuka kembali window data validation. Tapi Saya punya cara yang lebih cepat tanpa membuka Window data validation seperti berikut:

Buat Drop Down List dari Range Data

Sebelum membuat Drop Down List dari range data, Anda perlu membuat list sumber. List sumber ini bisa Anda buat dalam Sheet yang sama maupun sheet berbeda.

Saya berikan contoh list pada Sheet yang berbeda. Silahkan gunakan Sheet Drop Down 2 dan Sheet List pada Excel Online sebagai bahan latihan Anda.

Drop Down List pada Range sangat berguna jika Anda memiliki List yang banyak. Bukan seperti contoh sebelumnya, yaitu list jenis kelamin Laki-Laki atau Perempuan.

sponsored-jd-sport

Dalam contoh ini, Saya memiliki List Nama Provinsi. Pertama-tama, susun list Anda pada Sheet List seperti gambar berikut:

Selanjutnya buat Drop Down List pada Sheet Drop Down 2 dengan cara berikut:

Cara Membuat Drop down list menggunakan range data lengkap
  1. Klik cell B1 pada Sheet Drop Down 2 sebagai output kita. Kemudian klik Tab Data dan Data Validation seperti cara sebelumnya.
  2. Pada Window Data Validation, ubah Allow menjadi List.
  3. Klik form Source. Kemudian klik Sheet List dan Blok semua list data yaitu Range C2:C10. Jika Anda belum tahu apa itu Range, Silahkan kunjungi Panduan Range, Cell, Column, dan Row M Jurnal
  4. Jangan lupa centang In-Cell DropDown untuk menampilkan icon dropdown pada cell. Kemudian klik Ok untuk melanjutkan. Berikut hasilnya:
Cara membuat drop down list excel dengan mudah - Drop Down List Bertingkat ( Saling Terhubung )

Dengan drop down list otomatis ini, Anda bisa menghapus, menambahkan , atau mengurutkan list tanpa membuka window data validation. Gimana caranya ? silahkan ikuti contoh-contoh berikut:

Edit Drop Down List

Silahkan buka Sheet List pada Excel online diatas. Untuk menghapus, menambahkan, atau mengurutkan list Anda atapun untuk mengubah pesan error, silahkan ikuti tahap – tahap berikut:

#1 Menghapus atau Menambahkan List

Sebagai contoh, Saya ingin menambahkan nama provinsi baru (Papua Barat) diantara Jakarta dan Jambi. Untuk itu, ikuti tahap-tahap berikut:

Menghapus atau menambahkan
  1. Klik List Jambi.
  2. Klik Tab Home, kemudian klik Icon Delete lalu delete sheet rows (untuk menghapus) atau Insert lalu klik insert sheet rows (untuk menambahkan).
  3. Jika anda ingin menambahkan, silahkan isi list baru pada cell yang baru saja ditambahkan. Berikut hasilnya:
Menambahkan daftar baru.

Silahkan lihat Drop Down List Anda, Excel secara otomatis menyesuaikan source list.

Penting! Cara ini tidak bisa Anda gunakan untuk menambahkan list pada baris paling atas (list nomor 1) atau list paling bawah. Untuk kendala tersebut, silahkan lihat Dynamic Drop Down List

#2 Trik Mengurutkan List

Dalam contoh lain, mungkin Anda ingin mengurutkan list dari A-Z atau Kecil-Besar atau sebaliknya. Untuk itu, silahkan buka sheet List, kemudian ikuti tahap-tahap berikut:

Mengurutkan Daftar
  1. Ketik Header List. Dalam contoh ini, Saya isi nama list menjadi “Provinsi“.
  2. Klik Tab Home dan Klik Sort & Filter kemudian klik Sort A to Z. Berikut hasilnya:
Belajar Excel

Secara otomatis, Drop Down List pada Sheet Drop Down 2 akan terurut sesuai source (sheet List).

#3 Hapus Drop Down List

Untuk menghapus drop down list, silahkan ikuti tahap-tahap berikut:

Cara menghapus drop down list excel
  1. Klik cell yang terdapat drop down list. Dalam contoh ini Cell B1
  2. Pada Data Tools Group, klik Tab Data dan Klik Data Validation.
  3. Klik Clear All kemudian klik Ok untuk melanjutkan.

Silahkan lihat Cell B1, Fitur Drop Down List sudah tidak tersedia.

#4 Edit Instruksi Pengisian

Anda bisa memberikan instruksi pengisian data pada cell yang terdapat Drop Down Listnya. Jadi ketika pengguna klik Cell yang terdapat drop down list, maka otomatis instruksi pengisian akan muncul seperti gambar berikut:

Instruksi Pengisian Drop Down List Excel

Secara default, Excel tidak menampilkan instruksi pengisian. Anda bisa mengubahnya dengan cara berikut:

Edit Instruksi Pengisian
  1. Klik Cell B1, klik Tab Data, dan Klik Data Validation seperti cara sebelumnya.
  2. Klik menu Input Message.
  3. Centang Show Input Message whe cell is selected.
  4. Silahkan isi Judul pada bagian Title dan Instruksi pada bagian Input Message. Kemudian klik Ok untuk melanjutkan.

#5 Edit Pesan Error

Ketika pengguna mengisi nilai yang tidak terdapat pada list, maka Excel akan memberikan pesar Error seperti gambar berikut:

Pesan Error

Anda bisa mengubah pesan Error ini dengan cara berikut:

Edit Pesan Error Drop Down List Excel
  1. Buka Window Data Validation seperti cara sebelumnya (klik cell B1, klik Tab Home, klik Data Validations)
  2. Klik menu Error Alert dan centang Show Error Alert after invalid data is entered.
  3. Silahkan ubah Style, Title, dan Error Message. Kemudian klik Ok untuk melanjutkan.

Pada bagian Style terdapat 3 pilihan sebagai berikut:

Stop: jika Anda memilih Stop, maka pengguna tidak bisa memasukkan nilai lain kecuali nilai yang terdapat pada list.

Warning dan Information: jika Anda memilih warning atau information, maka pengguna bisa memasukkan nilai lain. Namun Excel memberikan pilihan untuk melanjutkan atau tidak.

Silahkan sesuaikan dengan kebutuhan Anda.

Trik: Dynamic Drop Down List [Auto-Update]

Seperti yang Saya sebutkan sebelumnya, Anda tidak bisa menambahkan list baru di awal atau pun di akhir list.

Untuk mengatasi hal ini, Saya berikan Trik termudah yaitu menggunakan gabungan Fungsi OFFSET dan COUNTA untuk membuat Dynamic Drop Down List Auto-Update.

Source: Gabungan Fungsi OFFSET dan COUNTA

Saya menggunakan contoh yang berbeda, silahkan buka Sheet Dynamic dan Sheet Dynamic List pada Excel Online.

Buat list Anda pada kolom A tanpa header (judul list) seperti cara sebelumnya. Dalam contoh ini Saya menggunakan list nama buah.

Kemudian, buat Drop Down List pada Sheet Dynamic seperti cara sebelumnya. Perbedaannya hanya pada bagian Source. Kita akan menggunakan gabungan Fungsi OFFSET dan COUNTA. Perhatikan tahap berikut:

Cara membuat drop down list excel auto update dengan gabungan fungsi OFFSET dan COUNTA
  1. Masukkan Fungsi OFFSET seperti berikut: =OFFSET(
  2. Klik Sheet Dynamic List dan klik Cell A1 pada sheet Dynamic List. Jika sudah benar, Fungsi pada Form Source akan seperti berikut: =OFFSET('Dynamic List'!$A$1
  3. Ketik ,0,0, sehingga rumus menjadi =OFFSET('Dynamic List'!$A$1,0,0, (Akan Saya jelaskan setelah gambar hasil).
  4. Masukkan Fungsi COUNTA seperti berikut COUNTA( sehingga fungsi menjadi =OFFSET('Dynamic List'!$A$1,0,0,COUNTA(
  5. Klik header kolom A pada Sheet Dynamic List. Caranya arahkan kursor ke header kolom A (seperti gambar) sampai kursor berubah menjadi tanda panah kebawah. Kemudian klik header tersebut. Pastikan setelah klik, Fungsi menjadi =OFFSET('Dynamic List'!$A$1,0,0,COUNTA('Dynamic List'!$A:$A
  6. Ketik ),1) sehingga fungsi menjadi =OFFSET('Dynamic List'!$A$1,0,0,COUNTA('Dynamic List'!$A:$A),1)
    Ingat!: Sesuaikan operator pemisah rumus Anda ( , atau ; ). Berikut hasilnya:
Auto Update dengan gabungan Fungsi OFFSET dan COUNTA

Penting! Tanda koma (,) merupakan Operator Pemisah Rumus. Sesuaikan dengan pengaturan komputer Anda. Hanya ada dua pilihan 1. Tanda koma (untuk format English) dan 2. tanda titik koma (;) (untuk format Indonesia)

Dengan menggunakan gabungan Fungsi OFFSET dan COUNTA, Drop Down List akan otomatis ter-update jika Anda menambahkan List baru di bagian bawah.

Makna Fungsi OFFSET dan COUNTA untuk Drop Down List

Secara default, Fungsi OFFSET menggunakan 5 Argument

Tulis Rumus / Fungsi OFFSET:
=OFFSET(reference,rows,cols,[height],[width])

Berhubung list tersusun secara vertikal (dalam 1 kolom) serta berupa huruf (bukan Angka), maka trik nya gunakan Fungsi COUNTA pada argument [height]. Sehingga gabungan fungsi OFFSET dan COUNTA seperti berikut:

Tulis Rumus / Fungsi:
=OFFSET(‘Dynamic List’!$A$1,0,0,COUNTA(‘Dynamic List’!$A:$A),1)

Artinya:
Ambil Nilai dari Sheet Dynamic List Cell A1 dengan Baris ke Tidak ditentukan dan Kolom ke Tidak ditentukan Tinggi Baris Hitung Cell yang tidak kosong pada Sheet Dynamic List Semua Kolom A Jumlah Kolom = 1

Pertama argument references: 'Dynamic List'!$A$1, yaitu Excel akan mengambil data yang dimulai dari Cell A1 pada Sheet Dynamic List.

Kedua argument rows: 0, yaitu baris data yang diambil tidak ditentukan sehingga Excel akan mengambil semua baris pada kolom yang sama.

Ketiga argument cols: 0, yaitu kolom data yang di ambil tidak ditentukan sehingga Excel akan mengambil semua kolom pada baris yang sama.

Keempat argument [height]: COUNTA('Dynamic List'!$A:$A). Fungsi COUNTA akan memfilter data yang akan di ambil oleh Fungsi OFFSET. Argument fungsi COUNTA tertulis 'Dynamic List'!$A:$A, artinya hanya ambil data pada Kolom A yang berisi saja dan Abaikan Cell Kosong.

Kelima argument [width]: 1, yaitu data yang di ambil hanya pada 1 kolom saja yaitu kolom A. Karena, argument reference memulai pengambilan data dari Cell A1.

Cara Auto Update Drop Down List

Sekarang coba Anda tambahkan list baru pada kolom A di Sheet Dynamic List. Sebagai contoh, Saya tambahkan Belimbing pada baris paling bawah dari list yaitu Cell A11. Kemudian lihat Drop Down List sudah terupdate secara otomatis seperti gambar berikut:

Auto Update

Trik: 2 Drop Down List Saling Terhubung

2 atau lebih Drop Down list saling terhubung sering juga disebut dengan drop down list bertingkat. Misalnya, perhatikan gambar berikut:

Cara membuat 2 atau lebih drop down list Bertingkat ( Saling Terhubung / tersinkron ) pada microsoft excel

Penjelasan: Jika form Provinsi berisi Jambi, maka Form Kabupaten hanya bisa diisi Kota Jambi atau Kota Sungai Penuh.

Selanjutnya, Jika form Kabupaten berisi Kota Jambi, maka pengguna hanya bisa mengisi form Kecamatan dengan Jambi Timur / Jambi Selatan / Kota Baru / Eka Jaya.

Namun jika form Kabupaten berisi Kota Sungai Penuh, maka pengguna hanya bisa mengisi form kecamatan dengan Koto Baru / Kumun Debai / Pesisir Bukit / Pondok Tinggi / Sungai Penuh.

Nah kondisi seperti ini dapat Anda selesaikan dengan mudah. Selengkapnya akan saya bahas pada SUB-BAB Dependent Drop Down List. Silahkan gunakan pintasan Panduan Berikut:

Pintasan Panduan Data Validation Excel

Bab sebelumnya: Template Excel.

  1. Data Validations: Untuk mengisi Cell dengan pilihan khusus (Contoh: Cell B2 hanya bisa diisi dengan angka 1, 2, 3, atau 4).
  2. Function + Data Validation: 4 Contoh cara menggunakan Rumus / Fungsi pada Data Validation Excel
  3. Drop-Down List: (Anda Disini).
  4. Dependent Drop-Down Lists: Contoh: jika Cell B1 di isi Rabu, maka Cell B2 hanya bisa di isi Siang atau Sore. Jika Cell B1 di isi Kamis, maka Cell B2 hanya bisa di isi Pagi atau Sore, dsb..

Bab berikutnya: Keyboard Shortcut.

8 tanggapan pada “Cara Membuat Drop Down List Pada Excel (dari A – Z)”

  1. Tanya bang.
    1. Bagaimana caranya supaya dropdown listnya bisa urut abjad tanpa harus sortir di kolom referensinya. Contoh kasus diatas adalah penambahan buah Belimbing pada baris terakhir, bagaimana supaya di dropdown list nya, belimbing bisa muncul di urutan setelah nama buah berawalan huruf A?
    2. Untuk kasus list yg terlalu banyak, terkadang untuk scroll list ke bawah butuh waktu. Bisakah ketika kita mengetik beberapa huruf pertama, maka list otomatis mempersingkat daftarnya sesuai urutan abjad yg kita ketik?
    Trima kasih atas pencerahannya

  2. mau tanya donk bg,

    bisa kah kita membuat drop down list dengan file yang dipisah bukan beda sheet ya, tapi beda file?
    Untuk kasus list yg terlalu banyak, terkadang untuk scroll list ke bawah butuh waktu. Bisakah ketika kita mengetik beberapa huruf pertama, maka list otomatis mempersingkat daftarnya sesuai urutan abjad yg kita ketik?
    Trima kasih atas pencerahannya

  3. mau tanya bang,
    coba fitur yg dynamic, di excel(microsoft 365) saya nggak bisa kak, kira-kira apa penyebabnya ya? terimakasih

  4. Salam
    Min, izin tanya, untuk bikin dropdown, tapi agar data di kolom berikutnya otomatis terisi di tabel yang dibuat dropdown. Mirip dg Dropdown bertingkat tapi data selanjutnya itu pasti otomatis terinput tanpa dropdown lagi.

    Terima kasih

Komentar Anda:

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *