Cara Membuat Daftar Drop Down di Excel


Daftar drop-down Excel adalah cara mudah untuk mengontrol nilai yang dimasukkan dalam sel. Mereka sangat ramah pengguna dan merupakan cara yang bagus untuk mengurangi kesalahan input.

Anda dapat membuat daftar drop down di Excel dengan berbagai cara. Kami akan menjelajahi cara membuat daftar drop down di Excel menggunakan tiga metode.

Cara Membuat Daftar Drop Down di Excel Metode 1 – Secara manual

Jika Anda menginginkan opsi sederhana (misalnya, Putih hitam; Ya/Tidak/Tidak Tahudll.), maka metode tercepat mungkin melakukannya secara manual.

    1. Pilih sel atau rentang sel tempat Anda ingin membuat daftar turun bawah.
    1. Pergi ke Data tab. Di dalam grup perintah Alat Data, pilih ikon Validasi Data.

Daftar tarik-turun Excel

    1. Di kotak dialog Validasi Data, di dalam tab Pengaturan, pilih Daftar sebagai kriteria Validasi.
    1. Di bidang Sumber, masukkan opsi, dipisahkan dengan koma. Pastikan bahwa Tarik-turun dalam sel opsi dicentang.

Daftar tarik-turun Excel

    1. Klik Oke.

Ini akan membuat daftar drop-down di sel yang dipilih. Semua item yang terdaftar di bidang sumber terdaftar di baris yang berbeda di menu tarik-turun.

Daftar tarik-turun Excel


Daftar tarik-turun Excel

Metode 2 – Merujuk data dari sel lain

Ada juga opsi untuk membuat drop down di Excel menggunakan rentang sel sebagai data sumber untuk daftar validasi Anda.

Untuk melakukannya:

    1. Tentu saja, pertama-tama, Anda harus menyiapkan sumber, atau daftar di tempat lain. Kami telah memasukkan data sumber di sel A2 hingga A7 pada lembar kerja lain bernama Sumber dalam buku kerja yang sama ini.
    1. Selanjutnya, pilih sel atau rentang sel tempat Anda ingin membuat daftar turun bawah. Dalam contoh kami, kami telah memilih B2:C5.

Daftar tarik-turun Excel

    1. Buka tab Data. Di dalam grup perintah Alat Data, pilih tombol Validasi data ikon.

Daftar tarik-turun Excel

    1. Pilih ‘Daftar’ sebagai kriteria Validasi.
    1. Di bidang ‘Sumber’, masukkan rentang yang berisi daftar nilai yang akan digunakan sebagai daftar tarik-turun Anda, atau Anda cukup mengeklik di dalam bidang ‘Sumber’ dan pilih sel di Sumber lembar kerja.

Daftar tarik-turun Excel

    1. Klik Oke.

Ini akan membuat daftar drop-down di sel yang dipilih. Setiap item yang terdaftar ditampilkan dalam baris yang berbeda di menu drop-down.

Daftar tarik-turun Excel

Metode 3 – Rumus OFFSET (daftar tarik-turun dinamis)

Anda juga dapat menggunakan rumus OFFSET untuk membuat daftar tarik-turun dinamis, yang secara otomatis diperbarui saat item ditambahkan ke akhir daftar.

Sintaks dari fungsi OFFSET adalah:

=OFFSET(reference, rows, cols, [height], [width])

Tiga argumen pertama diperlukan dan dua yang terakhir adalah opsional.

    • Referensi – Ini adalah sel atau rentang sel (berdekatan) dari mana Anda dapat mendasarkan offset. Referensi adalah titik awal.
    • Baris – Ini adalah jumlah baris (bawah atau atas) untuk bergerak dari titik awal. Jika baris adalah angka positif, rumus bergerak ke bawah dari referensi awal. Dalam kasus angka negatif, itu naik dari referensi awal.
    • cols – Jumlah kolom yang ingin Anda pindahkan rumusnya dari titik awal. Begitu juga dengan baris, cols bisa positif (di sebelah kanan referensi awal) atau negatif (di sebelah kiri referensi awal).
    • Tinggi – Jumlah baris yang harus berisi hasil. Jika dihilangkan, tinggi referensi digunakan.
    • Lebar – Jumlah kolom yang harus berisi hasil. Jika dihilangkan, lebar referensi digunakan.
Baca Juga:  6 Teknik Tabel Pivot Tingkat Lanjut Yang Harus Anda Ketahui di 2022

Untuk membuatnya lebih mudah diikuti, kita akan menggunakan fungsi OFFSET untuk membuat daftar drop-down dinamis dalam contoh berikut.

Kami ingin kolom B dan C pada permainan lembar kerja untuk menampilkan daftar drop-down semua klub dalam liga. Data sumber nama klub akan berasal dari lembar lain (Sumber) dalam buku kerja ini.

Daftar tarik-turun ExcelDaftar tarik-turun Excel

    1. pada permainan lembar kerja, pilih sel B2 hingga C5, karena kami ingin daftar tarik-turun dibuat untuk semua sel dalam rentang itu.

Daftar tarik-turun Excel

    1. Dari Data pada pita, dalam grup Alat Data, klik Validasi Data. Kotak dialog ‘Validasi Data’ muncul.
    1. Di kotak Izinkan, klik Daftar.
    1. Klik di kotak Sumber dan masukkan rumus:
=OFFSET(Source!$A$2,,,COUNTA(Source!$A:$A),1)

Rumus di atas memberi tahu Excel untuk menggunakan sel A2 di lembar kerja Sumber sebagai titik awal (perhatikan referensi absolut ke sel A2). Karena hasil yang kita inginkan harus dimulai dengan sel referensi, offset harus tetap pada baris nol, dan kolom nol (jauh dari titik awal).

Ketinggian hasil kami harus berapa pun tinggi daftarnya, jadi kami meminta Excel untuk menghitung jumlah nilai dalam daftar dengan menggunakan rumus COUNTA, dengan merujuk ke seluruh kolom A.

Hasilnya hanya akan lebar satu kolom, jadi 1 adalah argumen terakhir dalam rumus kita.

Daftar tarik-turun Excel

    1. Klik Oke.

Daftar drop-down berfungsi seperti yang terlihat pada dua contoh sebelumnya, tetapi menawarkan keuntungan tambahan karena diperbarui setiap kali pilihan ditambahkan ke akhir daftar pada Sumber lembar kerja.

Cukup tambahkan item baru ke akhir daftar sumber, dan pilihan daftar drop-down akan segera diperbarui.

Daftar tarik-turun Excel

Izinkan entri lain

Sejauh ini, kami belum menyesuaikan Peringatan Kesalahan default untuk daftar drop-down yang telah kita buat. Ini berarti bahwa setelah daftar diatur, Excel hanya akan menerima nilai yang merupakan bagian dari daftar. Jika kami mencoba mengetikkan nilai yang bukan bagian dari daftar Sumber, kami akan mendapatkan pesan kesalahan.

Baca Juga:  Cara Menguasai Excel dalam 11 Langkah, Lakukan Sekarang!

Daftar tarik-turun ExcelJika diinginkan, Anda dapat membuat daftar drop-down di Excel yang memungkinkan entri lain yang tidak disertakan dalam daftar sumber.

Untuk melakukan ini:

    1. Pilih sel atau sel dengan daftar drop-down di mana Anda ingin mengizinkan entri ‘on-the-fly’.
    1. Buka tab Data, lalu dari grup perintah Alat Data, klik Validasi Data.
    1. Kotak dialog Validasi Data akan muncul. Buka tab Peringatan Kesalahan dan hapus centang pada ‘Tampilkan peringatan kesalahan setelah data yang dimasukkan tidak valid’ kotak. Klik Oke.

Daftar tarik-turun ExcelAnda sekarang dapat memasukkan nilai yang tidak ada dalam daftar drop-down.

Salin aturan validasi data dari sel lain

Jika ada sel yang membawa aturan validasi data yang ingin Anda salin ke sel lain:

    1. Pergi ke sel yang berisi aturan dan salin.
    1. Buka sel target dan dari tab Beranda, klik panah drop-down Tempel.
    1. Memilih Tempel Spesial.
    1. Dari kotak dialog Tempel Spesial, pilih Validasi tombol radio dan klik OK.

Daftar tarik-turun Excel

Tambahkan item ke daftar drop-down Excel

Bahkan jika Anda tidak menggunakan daftar drop-down dinamis, berikut adalah trik kecil yang berguna untuk menambahkan item ke daftar drop-down dengan cepat:

    1. Buka daftar dan klik kanan salah satu nilai.
    1. Dari menu konteks, klik Sisipkan.
    1. Pilih “Shift cell down” dan klik OK.
    1. Ketik item baru.

Hasilnya adalah Excel secara otomatis memperluas rentang sumber untuk memasukkan nilai pertama dan terakhir dalam daftar, dan semua yang ada di antaranya.

Hapus item dari daftar drop-down

Untuk menghapus item dengan cepat dari daftar drop-down:

    1. Buka daftar dan klik kanan nilai yang akan dihapus.
    1. Dari menu konteks, klik Hapus.
    1. Pilih “Shift cell up” dan klik OK.

Hasilnya adalah Excel menyesuaikan rentang sumber sehingga daftar sumber validasi data dimulai dengan referensi ke nilai pertama dan diakhiri dengan referensi ke nilai terakhir.

Hapus daftar tarik-turun

Jika Anda ingin menghapus semua nilai dari sel, termasuk daftar drop-down, pemformatan, dll., cukup buka tab Beranda, klik drop-down Hapus, dan pilih Hapus Semua.

Namun, jika Anda hanya ingin menghapus daftar drop-down tetapi tetap mempertahankan nilai yang dipilih, lakukan hal berikut:

    1. Pilih sel dengan daftar drop-down.
    1. Pada tab Data, di grup Alat Data, klik Validasi data.
    1. Dibawah Mengizinkan, mengubah Daftar ke Nilai apa pun.
Baca Juga:  6 Teknik Tabel Pivot Tingkat Lanjut Yang Harus Anda Ketahui di 2022

Jika Anda juga ingin menghapus semua daftar drop-down lainnya dengan pengaturan yang sama, centang “Terapkan perubahan ini ke semua sel lain dengan pengaturan yang sama.”

    1. Klik Oke.

Daftar tarik-turun Excel

Buat daftar drop-down dependen

Berikut twistnya. Anda mungkin ingin tahu cara membuat daftar drop-down di Excel di mana opsi berubah tergantung pada apa yang dipilih dalam daftar drop-down sebelumnya.

Misalnya, di kolom B kami akan memilih nama negara yang liganya akan ditampilkan.

Kemudian kolom C dan D hanya akan menawarkan klub-klub milik negara yang dipilih di kolom B dari baris itu.

Pengaturan ini adalah sebagai berikut:

Langkah 1: Buat daftar negara yang akan menjadi daftar validasi data “induk” Anda. Kami telah mendaftarkan Inggris, Spanyol, dan Italia sebagai daftar induk kami.

Langkah 2: Daftar nama klub berdasarkan lokasi di kolom terpisah.

Langkah 3: Buat rentang bernama untuk setiap daftar negara, dengan nama yang identik dengan daftar validasi data induk Anda.

Daftar tarik-turun ExcelUntuk menampilkan daftar orang tua opsi dalam menu tarik-turun di Excel:

    1. Pilih sel di mana kita ingin daftar induk muncul sebagai opsi tarik-turun. Dalam contoh kami, kami telah memilih sel B2:B5.

Daftar tarik-turun Excel

    1. Buka tab Data > klik ikon Validasi Data > klik Daftar dari Mengizinkan tarik turun > pergi ke Sumber dan pilih kisaran $A$2:$A$4 pada lembar kerja Sumber.

Daftar tarik-turun ExcelUntuk menampilkan daftar tanggungan pilihan:

    1. Pilih sel di mana kita ingin nilai dependen muncul sebagai opsi drop-down. Kami telah memilih C2:D5.
    1. Buka tab Data > klik ikon Validasi Data > klik Daftar dari Mengizinkan drop down > pergi ke bidang Sumber dan ketik =INDIRECT(B2).

Dalam kasus kami, kami juga ingin menggunakan liga negara yang sama dengan daftar induk untuk kolom D. Oleh karena itu, kami harus menyorot rentang C2 hingga D6 dan menggunakan referensi campuran saat memasukkan formula untuk validasi.

Karena kita akan selalu ingin mencari di kolom B untuk nama negara, referensi ke kolom B adalah tetap tetapi nomor baris akan relatif terhadap baris yang kita pilih.

Oleh karena itu kami akan mengetik =INDIRECT($B2)

Daftar tarik-turun ExcelSekarang daftar dependen kami di kolom C dan D berfungsi dengan baik, dengan opsi berubah tergantung pada apa yang dipilih di kolom B.

Daftar tarik-turun Excel

Tinggalkan komentar