TL;DR
File stok barang Excel yang tahan lama disusun dari tiga sheet: master barang, mutasi harian, dan dashboard. Saldo dihitung otomatis pakai SUMIFS yang narik dari sheet mutasi, jadi nggak ada penjumlahan manual yang bisa rusak waktu ada baris disisipin. Kolom kode barang dikunci pakai dropdown Data Validation, dan semua sel rumus dilindungi lewat Protect Sheet supaya nggak ketimpa orang lain.
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.
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.
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.
Sheet baru, kasih nama mutasi. Enam kolom:
A: Tanggal
B: Kode
C: Nama
D: Masuk
E: Keluar
F: KeteranganIsi 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.
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.
=INDIRECT("tblMaster[Kode]")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.
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)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:
Pesan ulang, pilih warna kuning.Habis dengan warna merah.Sekarang tiap kali kamu buka file, barang yang perlu dipesan langsung kelihatan tanpa nyortir apapun.
Sheet ketiga, kasih nama dashboard. Klik satu sel di sheet mutasi, terus Insert lalu PivotTable, taruh hasilnya di sheet dashboard.
Susunannya:
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.
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.
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.
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.
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".
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.
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.
Simpan di OneDrive atau Google Drive supaya riwayat versinya kesimpen. Kalau ada yang ngerusak file hari Rabu, kamu masih bisa balik ke versi Selasa.
Ini kelihatan sepele sampai kamu butuh nambah kolom enam bulan kemudian. Catat di tempat lain sejak awal.
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.
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.
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.
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.
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.
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.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Punya ratusan angka dan mau tau sebarannya masuk ke rentang mana aja? FREQUENCY ngitung distribusi frekuensi sekali jalan, tanpa nulis COUNTIFS berkali-kali.
SUMIFS ribet kalau kriterianya banyak dan sering ganti. Rumus database Excel kayak DSUM baca kriteria dari sel terpisah, jadi kamu tinggal ubah tabel kecil tanpa nyentuh rumus.
DATEDIF ngasih umur bulat, tapi kadang kamu butuh angka desimal kayak 27,6 tahun buat hitung masa kerja atau bunga. YEARFRAC ngasih pecahan tahun dengan presisi.