MarCat

Stock register format for a shop, in Excel: columns, formulas and a worked example

A stock register format for a supermarket or grocery shop in Excel: the columns, the closing stock formula, a worked example, and what GST rules ask for.

By Swarbhanu Gupta, who has run Banjara supermarket in Ahmedabad since January 2025 (billing on MarCat since April 2026) · 12 November 2026

Every shop has a stock register, even if it is only in the owner's head. The trouble starts when the head and the shelf disagree. A good stock register on paper or in Excel tells you, for every item, what you started with, what came in, what went out and what should be on the shelf now.

An Excel register is a good start. When it grows too big to keep up, stock and inventory management software moves stock with every bill instead.

This guide gives you a stock register format that works for a supermarket or grocery shop, the Excel formulas to run it, a worked example with made-up items, and the point where Excel stops being enough.

The one formula behind every stock register

Whatever format you use, it comes down to this:

Closing stock = Opening stock + Received - Sold - Other goods out

In a real store, "received" and "other goods out" each have more than one part. Keep them in separate columns, or you will never find where a difference came from:

Goes in Goes out
Purchases from the supplier bill Sales on customer bills
Free goods from a scheme Returns to the supplier
Customer returns put back on the shelf Damaged, expired or broken
Used in the shop or given away as samples

What GST rules ask a store to record

If your store is registered under GST, this is not optional. Section 35 of the CGST Act asks every registered person to keep a true and correct account of the stock of goods. Rule 56(2) of the CGST Rules says the stock account should show:

  1. The opening balance
  2. Receipts (goods that came in)
  3. Supplies (goods that went out on sale)
  4. Goods lost, stolen, destroyed, written off, or given away as gifts or free samples
  5. The balance of stock

Rule 56(2) does not apply to a person paying tax under the composition scheme (section 10). The Act also allows these accounts to be kept in electronic form, so an Excel sheet or software is fine. Your accountant can tell you exactly how it applies to your store.

The good news: the columns GST asks for are the same columns that help you run the shop.

The stock register format

For one item, one month, the register looks like this. All numbers are made up.

Date Details Opening Received Free Sold Cust. return To supplier Damaged Closing
1 Nov Opening stock 20 20
3 Nov Supplier bill 1045 24 2 46
1-10 Nov Counter sales 30 16
8 Nov Customer return, bill 2231 1 17
12 Nov Torn packs 2 15

Item: Toor dal 1 kg, barcode 8901234567890, unit: pack.

Two things this format gets right that many registers miss:

  • Free goods have their own column. A "buy 12, get 1 free" scheme puts real packs on your shelf. If they are not entered, the shelf always has more than the register, and nobody knows why.
  • Damaged and expired goods are written off on the day. Packs quietly thrown away are the most common reason a register does not match the shelf at month end.

Building it in Excel: one log, one summary

A separate sheet for every item becomes unmanageable after 50 items. A better way is one log of every movement, and a summary that adds it up.

Sheet 1: Moves. One row per movement:

A: Date B: Barcode C: Item D: Type E: Qty F: Bill / note
01-11 8901234567890 Toor dal 1 kg Opening 20 Count on 31 Oct
03-11 8901234567890 Toor dal 1 kg Purchase 24 Bill 1045
03-11 8901234567890 Toor dal 1 kg Free 2 Bill 1045 scheme
10-11 8901234567890 Toor dal 1 kg Sale 30 Counter, 1-10 Nov
03-11 8901234567892 Glucose biscuit 200 g Purchase 48 Bill 1046

Use a fixed list of words in column D (Opening, Purchase, Free, Sale, Cust return, To supplier, Damaged). In Excel, select column D, go to Data > Data Validation > List, and type the words. This stops "sale", "Sales" and "sold" from being three different things.

Always enter quantity as a positive number. The type decides whether it adds or subtracts.

Sheet 2: Summary. One row per item, with the barcode in A2. For each column, add up that type with SUMIFS:

  • Opening: =SUMIFS(Moves!E:E,Moves!B:B,A2,Moves!D:D,"Opening")
  • Purchase: the same formula with "Purchase"
  • Do the same for Free, Sale, Cust return, To supplier and Damaged.

Then closing stock, if those seven columns are C to I in this order (C Opening, D Purchase, E Free, F Sale, G Cust return, H To supplier, I Damaged):

=C2+D2+E2+G2-F2-H2-I2

(opening + purchase + free + customer return - sale - to supplier - damaged; adjust the letters to your own column order).

Add a column for physical count and one for difference (=count - closing). Count one section a week, fill in the count, and look at the items with a difference first.

Units: the mistake that breaks the register on day one

Decide the unit for each item once, and stick to it.

  • If you buy toor dal in bags of 30 packs, a purchase of 2 bags is 60 packs in the register, not 2.
  • If you sell loose sugar by weight, keep it in kg with decimals (0.5, 0.25), and enter purchases in kg too.
  • A sealed 1 kg atta bag and loose atta from a sack are two different items, with two rows.

Many "the register is wrong" problems start with a unit mismatch, not with theft.

Where an Excel stock register stops working

Excel is a good start for a small shop. In a supermarket it starts to break in predictable places:

  1. Typing every sale. The counter makes the bill, and someone has to copy sales into the register again. Every day it is late, the register is wrong for that day.
  2. One product, two MRPs. When a brand changes the MRP, old and new packs share a barcode. One row in Excel cannot tell you how many of each you have.
  3. More than one person editing. Two people working on one file overwrite each other.
  4. Batch and cost. Knowing what each pack cost you, purchase by purchase, needs more columns than anyone keeps up with.

Where software helps

The register is only as good as the day it was last updated. Software helps when sales and purchases update stock the moment they happen, so the register is a report you read, not a sheet you type. Excel is still useful for a quick check or a count sheet.

How MarCat handles it

We have run Banjara, our own supermarket in Ahmedabad, since January 2025, and it has billed on MarCat since April 2026. Keeping the register and the shelf in step is a daily job in any store. This is what MarCat does:

  • Stock moves on its own. Every sale and every purchase moves stock automatically, with batch, MRP and cost tracking.
  • Purchase entry the way bills arrive. Purchase entry follows how a supplier bill actually comes in, and vendor records and outstanding balances stay in the same system.
  • Two MRPs kept apart. Every MRP change asks: keep both prices or replace. Keep both, and old and new stock each keep their own MRP and stock.
  • Loose items in decimals. Loose items and decimal quantities like 0.5 kg are billed straight from the counter.
  • Reports. Stock movement reports, including lines that stopped moving, plus purchase and GST-ready reports for your accountant.
  • Your Excel list comes with you. Items can be brought in from Excel, and your data stays yours to export whenever you want.

If you want your stock register to update itself from the counter, message us on WhatsApp at +91 96787 65518.

Questions

What columns should a stock register have?
Date, item code, item name, unit, opening stock, received, free goods received, sold, customer returns, returned to supplier, damaged or expired, and closing stock. Closing stock = opening + received + free + customer returns - sold - returned to supplier - damaged.
Does a GST registered shop have to keep a stock register?
Yes. Section 35 of the CGST Act asks every registered person to keep a true account of stock of goods, and rule 56(2) of the CGST Rules lists what the stock account should show: opening balance, receipts, supplies, goods lost, stolen, destroyed, written off or given as gifts or free samples, and the balance. Rule 56(2) does not apply to a person paying tax under the composition scheme (section 10). Ask your accountant how it applies to your store.
Can I keep the stock register in Excel?
Yes. The CGST Act allows accounts to be kept in electronic form. Excel works well for a small item list. It gets hard when you have hundreds of items, many bills a day, loose items and MRP changes.
Can MarCat bring in my stock list from Excel?
Yes. Items can be brought in from Excel, and from the billing software you use today. We help you move, so you do not have to type your stock in again.