Bikin Excel Stok Barang dari Nol: Rumus, Validasi, dan Proteksi Sheet

File stok barang Excel yang tahan lama butuh tiga hal: struktur sheet yang bener, saldo yang dihitung rumus bukan diketik manual, dan sel rumus yang dikunci biar nggak ketimpa.

Kebanyakan file stok bikinan sendiri rusak di bulan kedua. Bukan karena rumusnya salah, tapi karena ada yang nyisipin baris di tengah atau ngetik angka di sel yang harusnya rumus.

Panduan ini nyusun file-nya dari sheet kosong sampai siap dipakai kasir, termasuk bagian proteksi yang paling sering dilewatin.

Gimana struktur file stok barang yang bener?

Tiga sheet, masing-masing punya satu tugas. Nggak lebih, nggak kurang.

Sheet	Isi	Seberapa sering berubah
master	Daftar barang, stok awal, stok minimum	Jarang, cuma pas ada barang baru
mutasi	Catatan masuk dan keluar per baris	Tiap hari, nambah terus
dashboard	Ringkasan dan pivot table	Otomatis ngikut

Kesalahan paling umum: naruh semuanya di satu sheet. Akibatnya nama barang diketik ulang ratusan kali, dan tiap salah ketik jadi barang baru waktu direkap.

Langkah 1: bikin sheet master barang

Buka file baru, ganti nama Sheet1 jadi master. Isi baris pertama dengan enam judul kolom ini:

Kolom	Judul	Contoh
A	Kode	BRS-5
B	Nama	Beras Pandan Wangi 5 kg
C	Satuan	sak
D	Stok Awal	30
E	Stok Min	15
F	Harga Beli	62000

Isi datanya, terus blok seluruh tabel dan tekan Ctrl + T. Centang "My table has headers". Di kotak Table Name yang muncul di tab Table Design, ketik tblMaster.

Langkah Ctrl + T itu penting banget dan sering dilewatin. Tanpa tabel Excel, rumus yang kamu tulis nggak ikut kebawa waktu ada baris baru, dan rujukan rentangnya nggak nambah sendiri.

Langkah 2: bikin sheet mutasi

Sheet baru, kasih nama mutasi. Enam kolom:

A: Tanggal
B: Kode
C: Nama
D: Masuk
E: Keluar
F: Keterangan

Isi satu baris contoh biar tabelnya kebentuk, blok, Ctrl + T lagi, kasih nama tblMutasi.

Kolom C nggak diketik manual. Isi rumus ini di baris pertama data:

=IFERROR(XLOOKUP([@Kode], tblMaster[Kode], tblMaster[Nama]), "Kode nggak terdaftar")

Kalau Excel kamu versi 2019 ke bawah, XLOOKUP belum ada. Pakai ini:

=IFERROR(VLOOKUP([@Kode], tblMaster, 2, FALSE), "Kode nggak terdaftar")

Rumus di dalam tabel Excel otomatis nyalin sendiri ke baris baru. Sekali nulis, seterusnya ngikut. Detail sintaks XLOOKUP dan IFERROR bisa kamu cek kalau ada bagian yang masih asing.

Langkah 3: pasang dropdown di kolom kode

Ini satu langkah yang paling besar dampaknya. Dari catatan yang aku periksa di dataset toko_berkah, 46% selisih stok sumbernya salah ketik kode barang. Dropdown ngilangin sumber masalah itu sepenuhnya.

1. Blok kolom Kode di sheet mutasi, dari baris data pertama ke bawah.
2. Buka tab Data, klik Data Validation.
3. Di Allow, pilih List.
4. Di Source, ketik persis: =INDIRECT("tblMaster[Kode]")
5. Buka tab Error Alert, isi pesan yang jelas, misalnya "Kode ini belum ada di sheet master. Tambahin dulu di sana."
6. Klik OK.

Kenapa pakai INDIRECT? Karena kotak Source di Data Validation nggak nerima rujukan tabel secara langsung. INDIRECT yang nerjemahin teks itu jadi rujukan yang kebaca. Langkah rincinya ada juga di dokumentasi Microsoft.

Langkah 4: rumus saldo otomatis

Balik ke sheet master. Tambah kolom G dengan judul Saldo, isi rumus ini:

=[@[Stok Awal]]
  + SUMIFS(tblMutasi[Masuk], tblMutasi[Kode], [@Kode])
  - SUMIFS(tblMutasi[Keluar], tblMutasi[Kode], [@Kode])

Bacanya: stok awal, ditambah semua yang masuk buat kode ini, dikurangi semua yang keluar buat kode ini. SUMIFS yang ngerjain penjumlahan bersyaratnya.

Perhatiin rumusnya nunjuk ke nama kolom tabel, bukan ke D:D. Rujukan sekolom penuh maksa Excel ngecek sejuta baris tiap kali hitung ulang, dan itu yang bikin file stok jadi lemot di bulan keenam.

Mau lihat posisi stok di tanggal tertentu? Taruh tanggal di sel H1 sheet master, terus tambah kolom baru:

=[@[Stok Awal]]
  + SUMIFS(tblMutasi[Masuk], tblMutasi[Kode], [@Kode], tblMutasi[Tanggal], "<=" & $H$1)
  - SUMIFS(tblMutasi[Keluar], tblMutasi[Kode], [@Kode], tblMutasi[Tanggal], "<=" & $H$1)

Langkah 5: kolom status stok

Tambah kolom H dengan judul Status:

=IF([@Saldo]<=0, "Habis",
   IF([@Saldo]<=[@[Stok Min]], "Pesan ulang", "Aman"))

Tiga kondisi, tiga jawaban. Cara kerja bertingkat kayak gini dijelasin lebih pelan di halaman fungsi IF.

Biar kelihatan dari jauh, kasih warna otomatis:

1. Blok kolom Status.
2. Home lalu Conditional Formatting lalu Highlight Cells Rules lalu Text that Contains.
3. Ketik Pesan ulang, pilih warna kuning.
4. Ulangi buat Habis dengan warna merah.

Sekarang tiap kali kamu buka file, barang yang perlu dipesan langsung kelihatan tanpa nyortir apapun.

Langkah 6: dashboard barang terlaris

Sheet ketiga, kasih nama dashboard. Klik satu sel di sheet mutasi, terus Insert lalu PivotTable, taruh hasilnya di sheet dashboard.

Susunannya:

• Rows: Nama
• Values: Sum of Keluar
• Filters: Tanggal

Klik kanan di salah satu angka, pilih Sort, urutin dari terbesar. Sekarang kamu punya daftar barang terlaris yang bisa disaring per periode.

Satu hal yang wajib diinget: pivot table nggak nyegerin sendiri. Klik kanan lalu Refresh tiap kali ada data baru, atau atur lewat PivotTable Options supaya nyegerin tiap file dibuka.

Langkah 7: proteksi sheet biar rumusnya aman

Bagian ini yang paling sering dilewatin, padahal ini yang nentuin file kamu masih hidup enam bulan lagi atau nggak.

Logikanya kebalik dari yang orang kira. Di Excel, semua sel statusnya terkunci secara bawaan, tapi kuncinya baru aktif setelah sheet-nya diproteksi. Jadi urutannya: buka kunci sel yang boleh diisi dulu, baru proteksi sheet-nya.

1. Di sheet mutasi, blok kolom Tanggal, Kode, Masuk, Keluar, dan Keterangan. Kolom Nama jangan ikut, itu isinya rumus.
2. Tekan Ctrl + 1, buka tab Protection, hilangin centang Locked, klik OK.
3. Buka tab Review, klik Protect Sheet.
4. Centang "Select unlocked cells" doang, hilangin centang "Select locked cells" biar kursor nggak bisa nyasar ke sel rumus.
5. Isi password, catat di tempat lain, klik OK.
6. Ulangi buat sheet master, tapi yang dibuka kuncinya cuma kolom Kode sampai Harga Beli.

Terakhir, kunci struktur file-nya lewat Review lalu Protect Workbook. Ini yang nyegah orang ngapus atau ganti nama sheet, dua hal yang bikin semua rumus antar sheet langsung error. Penjelasan lengkap opsinya ada di dokumentasi Microsoft soal proteksi worksheet.

Satu catatan jujur: proteksi sheet itu pagar buat nyegah kesalahan, bukan brankas. Password-nya bisa dibuka pakai alat gratisan. Kalau datanya sensitif, andelin izin akses folder, bukan password file.

Contoh kasus: file toko_berkah sebelum dan sesudah

File stok toko_berkah sebelumnya satu sheet dengan 1.204 baris mutasi buat 58 barang, saldo diketik manual di kolom paling kanan.

Hasil pemeriksaannya:

Masalah	Jumlah baris
Nama barang ditulis beda buat barang yang sama	63
Saldo diketik manual, nggak cocok sama hitungan	27
Baris kosong nyempil di tengah data	11
Angka disimpan sebagai teks	9

Setelah dipecah jadi tiga sheet dengan struktur di atas, tiga masalah pertama hilang sendiri. Nama barang cuma ada satu tempat, saldo nggak bisa diketik karena kolomnya terkunci, dan tabel Excel nggak nerima baris kosong di tengah.

Waktu bikin rekap mingguan juga turun. Sebelumnya sekitar 45 menit karena harus nyortir dan jumlahin manual. Sesudahnya cukup klik Refresh di pivot table.

Kesalahan umum waktu bikin file stok sendiri

Lupa ngubah data jadi tabel Excel

Tanpa Ctrl + T, rumus nggak nyalin ke baris baru dan rentang rujukannya nggak nambah. Ini akar dari hampir semua file stok yang "tiba-tiba salah".

Nulis tanggal pakai format bebas

Ngetik 25 Juli 2025 bikin Excel nyimpen itu sebagai teks, dan semua rumus yang nyaring per tanggal langsung meleset. Pakai format tanggal beneran, cek dengan ngelihat perataannya. Tanggal asli rata kanan, teks rata kiri.

Gabung masuk dan keluar dalam satu kolom

Nulis angka negatif buat barang keluar kelihatan hemat kolom. Tapi begitu ada yang lupa kasih tanda minus, saldonya kacau tanpa jejak. Dua kolom terpisah lebih tahan salah.

Nyimpen cuma di satu laptop

Simpan di OneDrive atau Google Drive supaya riwayat versinya kesimpen. Kalau ada yang ngerusak file hari Rabu, kamu masih bisa balik ke versi Selasa.

Nyimpen password proteksi cuma di kepala

Ini kelihatan sepele sampai kamu butuh nambah kolom enam bulan kemudian. Catat di tempat lain sejak awal.

FAQ

Kenapa harus pakai dua sheet, nggak satu aja?

Karena master dan mutasi punya sifat berbeda. Master isinya daftar barang yang jarang berubah, mutasi isinya baris baru tiap hari. Kalau digabung, kamu bakal ngulang nulis nama barang ratusan kali dan tiap salah ketik jadi barang baru di laporan. Pisah dua sheet bikin nama barang cuma ditulis sekali.

Rumus SUMIFS-nya lambat waktu barisnya udah ribuan, gimana?

Cek dulu apakah rumusmu nunjuk ke seluruh kolom kayak D:D. Rujukan sekolom penuh maksa Excel ngecek sejuta baris tiap kali hitung ulang. Ganti pakai nama tabel kayak tblMutasi[Masuk], yang cuma ngecek baris terisi. Kalau masih berat di atas 50 ribu baris, pindahin rekapnya ke pivot table.

Apa bedanya XLOOKUP sama VLOOKUP di file stok?

XLOOKUP nggak butuh hitung kolom ke berapa, dan bisa nyari ke kiri. Kalau kolom disisipin di tengah tabel master, rumus VLOOKUP yang pakai angka indeks bakal nunjuk kolom yang salah tanpa ngasih error. XLOOKUP aman dari masalah itu. Kekurangannya, XLOOKUP nggak jalan di Excel 2019 ke bawah.

Password proteksi sheet Excel aman nggak?

Nggak, dan memang bukan itu tujuannya. Proteksi sheet dirancang buat nyegah kesalahan, bukan nyegah orang jahat. Password-nya bisa dibuka dengan alat gratisan dalam hitungan menit. Buat data yang beneran perlu dijaga, andelin izin akses di folder penyimpanan, bukan password di file-nya.

File-nya mau dipakai bareng dua kasir, harus gimana?

Pindahin ke Google Sheets. Semua rumus di panduan ini jalan dengan sintaks yang sama, dan dua orang bisa ngisi barengan tanpa saling nimpa. Kalau harus tetap di Excel, simpan di OneDrive dan buka lewat Excel untuk web. File Excel yang dikirim bolak-balik lewat WhatsApp hampir pasti bikin dua versi yang beda isinya.

Langkah berikutnya

Kerjain tiga langkah pertama dulu hari ini: bikin sheet master, sheet mutasi, dan pasang dropdown kode. Tiga itu aja udah ngilangin sumber selisih terbesar.

Rumus saldo dan proteksi sheet bisa nyusul besok, waktu kamu udah punya beberapa baris data buat diuji.

Kalau kamu mau lihat dulu bentuk jadinya sebelum bikin sendiri, ada di template stok barang Excel. Buat variasi kolom yang cocok sama jenis usaha kamu, cek lima contoh kartu stok barang.