Membuat Drop Down List dengan Validasi Data

Kalau kamu sering kerja bareng spreadsheet atau bikin form entri data, pasti pernah ngerasain sakit hati lihat data yang berantakan karena orang ngetik bebas.

Mau bikin laporan? Pivot error. Mau analisis? Nggak konsisten. Solusinya gampang: drop down list + data validation.

Artikel ini bakal bahas lengkap — dari dasar sampai trik pro — cara membuat drop down list di Microsoft Excel dan Google Sheets, plus validasi yang bener supaya data tetap konsisten.

Semua contoh nyata, tabel, rumus, script, dan langkah-langkah yang bisa langsung dicopy paste. Santai aja gayanya, cocok buat anak remaja Indonesia.

Ringkasan singkat (kalau mau skimming cepat)

• Dropdown = sel dengan pilihan; Data validation = aturan input.
• Bikin dropdown statis gampang; dinamis lebih keren (otomatis update saat list berubah).
• Dropdown bertingkat (dependent) bikin input lebih spesifik.
• Lindungi dari paste sembarangan dengan proteksi, script, dan pengecekan.
• Tools lanjut: Named Range, Table (Excel), UNIQUE/FILTER (Sheets), Apps Script & VBA.

Kenapa Dropdown & Validasi Data Penting? (bahasa santai)

Bayangin kamu punya data penjualan, kolom `Kategori` harusnya cuma: `Pakaian`, `Aksesoris`, `Elektronik`. Tapi orang iseng ngetik `PAKAIAN`, `pakaian`, `Pakaian ` atau `Pakaian.` — jadi ada banyak variasi yang bikin pivot report error. Dropdown + data validation mencegah hal itu.

Intinya:

• Konsistensi data terjaga.
• Lebih cepat input karena tinggal pilih.
• Kurangi typo dan data duplikat karena kesalahan human.
• Bikin form lebih ramah untuk orang yang nggak jago Excel.

Oke, langsung aja kita mulai praktik.

Contoh dataset latihan (copy-paste ke sheet)

Gunakan tabel ini sebagai contoh. Letakkan di sheet `master` atau `raw`:

Selain `master`, buat sheet `lists` untuk menyimpan pilihan dropdown:

Sheet `lists`

> Catatan: Struktur lists bisa diubah sesuai kebutuhan. Untuk dropdown bertingkat, lebih rapi pakai layout master table (Category/Subcategory/Product).

1. Cara membuat dropdown sederhana (statis)

A. Microsoft Excel — langkah cepat

1. Siapkan daftar pilihan di sheet `lists`, mis. `lists!A2:A4`.
2. Pilih sel tempat dropdown ingin muncul, mis. `Orders!D2` (kolom `Kategori`).
3. Menu → Data → Data Validation.
4. Di tab Settings, pilih Allow: List.
5. Di Source, ketik `=lists!$A$2:$A$4` atau gunakan mouse untuk pilih range.
6. Klik OK.

Sekarang `Orders!D2` punya panah kecil untuk pilih kategori.

B. Google Sheets — langkah cepat

1. Pilih sel target (mis. `Orders!D2`).
2. Menu → Data → Data validation.
3. Criteria: List from a range, isi `lists!A2:A4`.
4. Centang Show dropdown list in cell.
5. Pilih On invalid data: `Reject input` agar memasukkan nilai lain langsung ditolak.
6. Klik Save.

2. Dropdown dari nilai langsung (inline)

Kalau pilihannya cuma sedikit, kamu bisa langsung tulis di source.

Excel (source):

=Ya,Tidak

atau kadang pakai `;` tergantung regional:

=Ya;Tidak

Google Sheets (List of items):

Ketik `Ya,Tidak` di kotak List of items.

3. Menggunakan Named Range (praktis & rapi)

Named Range bikin rumus lebih mudah dibaca.

Cara bikin Named Range:

Excel / Google Sheets:

1. Pilih `lists!A2:A4`.

2. Excel: di Name Box (kiri atas), ketik `KategoriList` → Enter. Google Sheets: Data → Named ranges → Create a range → beri nama `KategoriList`.

3. Di Data Validation → Source: `=KategoriList`.

Keuntungannya: Kalau range pindah, kamu cukup ubah definisi Named Range, bukan ubah semua validation.

4. Dropdown DINAMIS — list update otomatis

Sering kita mau dropdown auto-update saat nambah item di lists. Ada beberapa metode.

A. Excel: Gunakan Table (paling mudah)

1. Pilih `lists` range → Insert → Table (Ctrl+T).
2. Table akan otomatis memperluas saat baris baru ditambahkan.
3. Nama Table bisa diubah di *Table Design*, mis. `tblKategori`.
4. Data Validation Source:

=tblKategori[Kategori]

Atau gunakan `INDIRECT` jika perlu.

B. Excel: Named Dynamic Range pakai OFFSET

Define Name → `ItemsList` → Refers to:

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

Kelemahan: `OFFSET` volatile (lemot kalau data besar).

C. Google Sheets: UNIQUE + SORT helper

1. Buat helper di `helpers!A2`:

=SORT(UNIQUE(lists!A2:A))

2. Data Validation di `Orders!D2` → List from a range: `helpers!A2:A`.

Setiap ada tambah kategori di `lists`, `helpers` update otomatis.

5. Dropdown bertingkat (dependent dropdown)

Ini yang sering ditanya: pilih `Kategori` → `Subkategori` cuma yang cocok muncul. Ada beberapa cara.

A. Cara klasik Excel — Named Range + INDIRECT

• Buat named ranges untuk tiap subkategori, nama range harus sama persis dengan value kategori (tanpa spasi, atau gunakan underscore).
• `lists!B2:B4` → name = `Pakaian`
• `lists!C2:C3` → name = `Elektronik`

1. Buat dropdown Kategori: `=lists!A2:A4`.

2. Untuk Subkategori, Data Validation Source:

=INDIRECT(D2)

Dimana D2 = cell yang berisi kategori terpilih. Contoh: Jika D2 = `Pakaian`, `INDIRECT("Pakaian")` mengembalikan range bernama `Pakaian`.

Kekurangan: nama range harus valid (tidak ada spasi). Bisa gunakan `SUBSTITUTE` untuk mengatasi spasi, tapi lebih ribet.

B. Google Sheets — FILTER + UNIQUE (lebih modern)

Ini lebih fleksibel tanpa named range.

Master table (`master` sheet) kolom A=Kategori, B=Subkategori:

Langkah:

1. Buat dropdown Kategori `Orders!D2` → dari `UNIQUE(master!A2:A)`.

2. Untuk Subkategori helper (`helpers!D2`) pakai formula (tergantung value di `Orders!D2`):

=UNIQUE(FILTER(master!B2:B, master!A2:A = Orders!D2))

3. Data validation Subkategori → list from range → `helpers!D2:D`.

Kalau kamu mau banyak baris order, gunakan array formula dengan `INDEX`/`OFFSET` agar helper berubah per baris.

C. Excel 365 — dynamic arrays (FILTER)

Excel 365 punya `FILTER`:

Helper:

=UNIQUE(FILTER(master!B2:B100, master!A2:A100 = D2))

Kemudian validation source: refer helper spill range, mis. `SheetHelpers!E2#`.

6. Dropdown yang menampilkan label tapi menyimpan kode (display vs value)

Kadang mau tampilin nama panjang tapi simpan kode singkat untuk analisis. Spreadsheet dasar nggak pisahkan display dan value, tapi ada trik.

Master helper:

1. Dropdown menunjuk ke kolom `Label`.

2. Setelah user pilih label di `Orders!F2`, di kolom `Kode` pakai `VLOOKUP`:

text

=VLOOKUP(F2, helper!A2:B100, 2, FALSE)

Sekarang user lihat nama produk tapi sistem simpan `K001` untuk perhitungan, laporan, atau integrasi.

7. Validasi kustom (bukan cuma list)

Data validation juga bisa bikin aturan lain, misalnya:

A. Batas angka

=AND(ISNUMBER(A2), A2>=1, A2<=100)

Di Google Sheets gunakan Custom formula is.

B. Panjang teks

=LEN(A2) <= 60

C. Format kode tertentu (regex)

=REGEXMATCH(A2,"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$")

Excel tidak punya `REGEXMATCH` built-in (kecuali Excel 365 terbaru dengan `TEXTSPLIT`/`FILTER`), jadi butuh VBA atau formula panjang.

8. Menampilkan pesan input & pesan error (UX)

Biar user nggak bingung, tambahkan petunjuk.

• Data → Data Validation → tab Input Message: centang Show input message when cell is selected. Isi Title & Message.
• Tab Error Alert: atur style (Stop/Warning/Information) dan pesan error.

• Saat create data validation ada opsi Show validation help text. Masukin instruksi singkat.
• Pilih On invalid data → Reject input untuk mem-blok input yang salah.

Contoh pesan:

9. Mencegah bypass validasi (paste over)

Masalah nyata: user copy-paste ke sel dropdown sehingga bypass validation. Cara mitigasi:

A. Proteksi sheet / range

• Excel: Review → Protect Sheet → atur permissions.
• Google Sheets: Data → Protected sheets and ranges → set permissions.

Proteksi mencegah user ubah struktur, tapi biasanya masih bisa paste values jika punya akses edit.

B. Gunakan form untuk input (best for public)

• Google Forms → responses masuk ke Sheet. Forms punya validasi field sendiri sehingga input terkontrol.

C. Cek otomatis via script (Apps Script / VBA)

Buat script yang memeriksa setiap perubahan dan mengembalikan atau menandai baris invalid.

Apps Script (Google Sheets) contoh:

javascript
function onEdit(e) {
  var sh = e.range.getSheet();
  if (sh.getName() !== 'Orders') return;
  var col = e.range.getColumn();
  if (col == 4) { // kolom kategori
    var val = e.value;
    var master = SpreadsheetApp.getActive().getRangeByName('KategoriList').getValues().flat();
    if (master.indexOf(val) == -1) {
      e.range.setBackground('#ffcccc');
      SpreadsheetApp.getUi().alert('Nilai tidak valid! Pilih dari daftar.');
    } else {
      e.range.setBackground(null);
    }
  }
}

vb
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range
  Set rng = Intersect(Target, Range("D2:D100"))
  If rng Is Nothing Then Exit Sub
  Application.EnableEvents = False
  For Each cell In rng
    If Application.WorksheetFunction.CountIf(Sheets("lists").Range("A2:A10"), cell.Value) = 0 Then
      MsgBox "Nilai tidak valid: " & cell.Value
      cell.ClearContents
    End If
  Next
  Application.EnableEvents = True
End Sub

D. Gunakan conditional formatting untuk highlight invalid entries

Biar gampang ketahuan tanpa script:

Atur rule: format cells if `COUNTIF(lists_range, cell)=0` → warna merah.

Ini memberi visual warning.

10. Dropdown dari query / rumus (Sheets) — contoh nyata

Kadang daftar berasal dari data master yang terus berubah. Misal: daftar produk aktif.

Master sheet `products`:

Helper untuk dropdown (Sheets):

=FILTER(products!B2:B, products!C2:C="Active")

Kemudian data validation → List from a range → helper range.

Atau pakai `QUERY`:

=QUERY(products!A:C, "select B where C = 'Active' order by B", 0)

11. Dropdown dari array dinamis (Excel 365)

Excel 365 mendukung spill ranges. Contoh ambil daftar unik terurut:

=SORT(UNIQUE(products!B2:B100))

Lalu di Data Validation source pakai referensi `SheetHelpers!$A$2#` (tanda `#` berarti spill).

12. Dropdown untuk banyak baris (apply validation ke kolom)

• Pilih seluruh kolom tempat entri akan dilakukan, mis. `Orders!D2:D1000`.
• Apply Data Validation sekali. Semua sel akan punya dropdown.
• Jika pakai dynamic named range/ table, semua baris baru ikut validasi jika bentuknya rapi.

13. Styling & UX tips (biar user nyaman)

• Beri label jelas dengan contoh di header: `Kategori (pilih dari daftar)`.
• Gunakan Input Message untuk instruksi.
• Urutkan list alfabet atau logika populer (High→Medium→Low).
• Jangan terlalu panjang label. Kalau panjang, gunakan `Label` di dropdown + `VLOOKUP` untuk kode.
• Slicer kalau mau tampilan filter interaktif di dashboard.
• Gunakan warna netral untuk sel dropdown; hanya warnai invalid entries dengan warna merah muda.

14. Studi kasus lengkap (langkah-demi-langkah): 4 contoh nyata

Studi Kasus 1 — Sekolah: Pendaftaran Siswa

• Dropdown `Kelas`: 10A, 10B, 11A, 11B.
• Dropdown `Jenis Kelamin`: Laki-laki, Perempuan.
• Validasi `Email`, `Phone`.

1. `lists` sheet:

• A2:A5: `10A,10B,11A,11B`
• B2:B3: `Laki-laki,Perempuan`

2. `Registrasi!C2` (Kelas) → Data Validation Source: `lists!$A$2:$A$5`.

3. `Registrasi!D2` (Gender) → Source: `lists!$B$2:$B$3`.

4. `Phone` validasi kustom (Sheets):

=AND(REGEXMATCH(E2,"^\+?62[0-9]{9,12}$") , LEN(REGEXREPLACE(E2,"[^0-9]",""))>=10)

atau di Excel gunakan kombinasi `ISNUMBER` dan `LEN` setelah `SUBSTITUTE`.

5. Input message: "Pilih kelas dari daftar. Untuk pendaftaran online, isi nomor dengan format 62xxxxxxxx."

Extra: jika user paste nomor salah, Apps Script akan mengecek dan menandai.

Studi Kasus 2 — Toko Online: Order Entry

Kebutuhan:

• Dropdown `Kategori`, `Subkategori`, `Produk` bertingkat.
• Setelah pilih produk, otomatis fill `Harga` memakai VLOOKUP.
• Validasi qty sebagai integer > 0.

Implementasi (Sheets):

1. `master` sheet: semua produk dengan kolom Category/Subcategory/Product/Price.
2. `Orders!C2` Kategori → `=UNIQUE(master!A2:A)` sebagai helper.
3. `Orders!D2` Subkategori helper:

=UNIQUE(FILTER(master!B2:B, master!A2:A = Orders!C2))

4. `Orders!E2` Produk helper:

=UNIQUE(FILTER(master!C2:C, master!A2:A = Orders!C2, master!B2:B = Orders!D2))

5. `Orders!F2` Harga otomatis:

text
=IFERROR(VLOOKUP(E2, master!C2:D100, 2, FALSE), "")

6. Validasi qty (G2) custom:

=AND(ISNUMBER(G2), G2>=1)

Excel 365 mirip, gunakan `FILTER` & `XLOOKUP`:

=XLOOKUP(E2, master!C:C, master!D:D, "")

Studi Kasus 3 — HR: Absensi & Status

• Dropdown Status: `Hadir`, `Izin`, `Sakit`, `Alpha`.
• Jika `Izin`, tampilkan list alasan: `Sakit`,`Urusan Keluarga`, `Keperluan Sekolah`.
• Validasi tanggal harus bukan weekend.

1. Status dropdown dari `lists`.
2. Sub alasan dependent jika `Izin`.
3. Validasi tanggal (Sheets):

=NOT(OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7))

Jika weekend → tolak input, atau tampilkan warning.

Studi Kasus 4 — Keuangan Pribadi: Kategori Pengeluaran

• Dropdown kategori pengeluaran (Makanan, Transport, Tagihan, Hiburan).
• Jika kategori = Tagihan, subkategori: (Listrik, Air, Internet).
• Auto-sum total per kategori via pivot.

Implementasi:

1. Buat `lists` kategori & sub.
2. Dropdown di kolom `Kategori`.
3. Subcategory dependent via FILTER/UNIQUE.
4. Lakukan pivot: Rows = Kategori, Values = SUM(Total).

Hasil: laporan pengeluaran rapi tanpa typo kategori.

15. Troubleshooting & kesalahan umum

• Dropdown tak muncul / panah hilang: pastikan cell tidak berada dalam filtered hidden rows, dan validation diterapkan dengan benar.
• Pasted values bypass validation: gunakan proteksi, script, atau form input.
• INDIRECT error: cek nama range, hindari spasi. Gunakan `SUBSTITUTE(A2," ","_")` atau buat mapping helper.
• Helper formula tidak spill: pastikan tempat untuk spill kosong.
• List terlalu panjang (ribuan): dropdown default nggak nyaman; buat searchable UI via Apps Script sidebar atau gunakan autocomplete di form.

16. Script & macro lengkap contoh (copy-paste ready)

A. Apps Script: enforce validation for category (Google Sheets)

javascript
function onEdit(e) {
  var ss = e.source;
  var sh = e.range.getSheet();
  if (sh.getName() !== 'Orders') return;
  var col = e.range.getColumn();
  var row = e.range.getRow();
  if (col === 4 && row >= 2) { // Category in col D
    var val = e.value;
    var listRange = ss.getRangeByName('KategoriList');
    var list = listRange.getValues().flat();
    if (list.indexOf(val) === -1) {
      e.range.setBackground('#ffd6d6');
      SpreadsheetApp.getUi().alert('Nilai tidak valid! Pilih dari daftar kategori.');
      // Optional: clear the invalid value
      // e.range.clearContent();
    } else {
      e.range.setBackground(null);
    }
  }
}

B. VBA: clear invalid entries in range D2:D100 (Excel)

vb
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, cell As Range
  Set rng = Intersect(Target, Me.Range("D2:D100"))
  If rng Is Nothing Then Exit Sub
  Application.EnableEvents = False
  For Each cell In rng
    If WorksheetFunction.CountIf(Sheets("lists").Range("A2:A100"), cell.Value) = 0 Then
      MsgBox "Nilai tidak valid: " & cell.Value, vbExclamation
      cell.ClearContents
    End If
  Next cell
  Application.EnableEvents = True
End Sub

17. Best practices & checklist sebelum deploy

• [ ] Simpan semua daftar di sheet `lists` atau `master`.
• [ ] Pakai Named Range atau Table untuk kemudahan maintenance.
• [ ] Gunakan helper `UNIQUE`/`SORT` supaya dropdown rapi.
• [ ] Beri input message dan error message yang jelas.
• [ ] Proteksi sheet penting / gunakan form untuk publik.
• [ ] Jalankan pengecekan invalid entries (script atau conditional formatting).
• [ ] Dokumentasikan daftar pilihan dan aturan input di sheet README.

18. Latihan praktis (buat jago)

1. Buat form order: bikin sheet `master`, `helpers`, `orders`. Implementasikan dependent dropdown Kategori → Subkategori → Produk.
2. Tambah fitur: setelah pilih produk, isi harga otomatis, dan hitung total = qty * price.
3. Validasi: pastikan qty adalah integer > 0; jika tidak, tampilkan pesan.
4. Proteksi: lindungi formula harga agar user nggak bisa ganti.
5. Report: buat pivot untuk total penjualan per kategori.

19. Kesimpulan (gaya santai)

Oke bro/sis, sekarang kamu mesti paham banget tentang gimana cara membuat drop down list dengan validasi data yang keren, rapi, dan tahan salah input.

Mulai dari dropdown sederhana sampai dependent dropdown, validasi kustom, helper rumus, sampai script buat enforce rules. Ingat prinsip dasarnya:

• Jaga sumber list (master/list sheet) rapi.
• Pakai Named Range atau Table supaya gampang maintain.
• Pilih metode dependent dropdown yang cocok (INDIRECT vs FILTER).
• Jangan lupa proteksi dan pengecekan otomatis supaya data tetap bersih.

Coba praktek langsung pake contoh yang ada di artikel ini.

Kalau masih bingung, buka sheet kamu, copy contoh tabel, dan ikuti langkah demi langkah.

Semakin sering praktek, kamu bakal cepet jago dan spreadsheet-mu bakal bebas typo, rapi, dan enak dipakai buat analisis. Selamat ngoprek!