Stock register in Google Sheets: format, formulas and setup

The three tabs

1. Items: one row per item. Code, name, unit (bag, kg, quintal, piece), opening stock per godown, minimum level.
2. Entries: every movement, one row each, never edited after the fact. This is your register.
3. Stock: calculated, never typed. Current quantity per item and godown.

Columns for the entries log

Formulas for live stock

In the Stock tab, with item in column A and godown in column B:

=Items!C2 + SUMIFS(Entries!D:D, Entries!B:B, "In", Entries!C:C, A2, Entries!E:E, B2) - SUMIFS(Entries!D:D, Entries!B:B, "Out", Entries!C:C, A2, Entries!E:E, B2)

That is opening stock, plus everything that came in, minus everything that went out, for that item in that godown. For a total across godowns, drop the godown condition.

Low-stock alerts

• Visual: conditional formatting on the Stock tab that turns a row red when quantity is below the minimum from Items.
• Message: a small Apps Script that runs every morning, finds red rows and sends one Telegram or WhatsApp message to the purchase person and the owner.
• Reorder suggestion: average daily Out over the last 30 days times your supplier lead time, plus the minimum.

Making entry fast for staff

Staff should never open the sheet. Give them a Google Form with dropdowns for Type, Item and Godown, a number field and a photo upload. Save it to their phone home screen. Entry takes about 30 seconds and the sheet updates instantly.

Mistakes to avoid

• Typing item names freely. “Bag 50kg” and “50 kg bag” become two items. Always use dropdowns.
• Editing old entries. Correct with a new adjustment entry so the history stays honest.
• Mixing units. Fix one unit per item and convert at entry.
• Never checking against physical stock. Do a monthly count and record the difference as an adjustment with a reason.