How to Make a Stock Register: From Notebook to Google Sheets
A stock register (also called a stock book) is simply a record of every item you keep, how much came in, how much went out, and what is left. Shops have kept them on paper for generations, and they still work. The trouble starts when the book gets busy: pages fill up, totals are worked out in your head, and one wrong subtraction carries forward for weeks.
This guide shows you how to set up a clear stock register on paper first, then how to move the same layout into a free Google Sheet so the balance is calculated for you.
What goes in a stock register
Every stock register, paper or digital, records the same few things for each movement:
- Date of the movement.
- Item name (one name per item, always spelled the same).
- In: quantity received (delivery, return from customer, found during a count).
- Out: quantity issued (sale, used in the shop, damaged, lost).
- Balance: what should be on the shelf after this line.
- Reference or note: invoice number, supplier, or a short reason.
Some businesses also record unit price and value. That is useful for accounting, but it is a separate job. If your main problem is "how many do I have?", start with quantities only.
Option A: The paper stock book
Use one page (or a few pages) per item. Write the item name, unit (piece, pack, box) and a reorder level at the top. Then rule these columns:
| Date | Reference | In | Out | Balance |
|---|---|---|---|---|
| 01 Oct | Opening count | 40 | 40 | |
| 03 Oct | Invoice 1182 | 20 | 60 | |
| 04 Oct | Sales | 14 | 46 | |
| 06 Oct | Damaged | 2 | 44 |
Rules that keep a paper register honest:
- Start with a real count. Count the shelf and write it as the opening balance. Don't guess.
- One line per movement. Never change an old line. If you made a mistake, add a correcting line with a note.
- Work out the balance every line. Previous balance + In - Out.
- Circle the balance when it reaches the reorder level. That is your reminder to buy more.
- Count and compare once a month. If the shelf and the book differ, add an adjustment line and write why.
Damaged and lost stock goes in the Out column. Leaving it out is the most common reason a stock book says 44 when the shelf says 41.
Where paper starts to struggle
A paper stock register is fine for a handful of items. It gets hard when:
- you have dozens of items and need to flip pages to see what is low,
- a calculation error carries forward into every later balance,
- you rename or replace an item and its history is split across pages,
- you want a quick "what do I need to reorder?" list.
That is usually the moment to move it into a spreadsheet.
Option B: The same register in Google Sheets
A spreadsheet keeps the same columns but does the balance for you. Here is the simplest version:
- Tab 1: Items. Columns: Item, Unit, Opening qty, Reorder level.
- Tab 2: Register. Columns: Date, Item, In, Out, Reference. One row per movement, just like your book. Leave the balance out of this tab.
- Back on Items, add On hand: =C2 + SUMIF(Register!B:B, A2, Register!C:C) - SUMIF(Register!B:B, A2, Register!D:D)
- Add Status: =IF(E2<=0, "OUT OF STOCK", IF(E2<=D2, "REORDER", "OK"))
Now the register is your history and the Items tab is your summary. You never add up a balance by hand again, and a filter on the Status column gives you your shopping list.
Tips for the move:
- Use the latest counted balance from your paper book as the opening quantity. You don't need to type in years of history.
- Turn the Item column on the Register tab into a dropdown from your Items list (Data > Data validation), so names always match.
- Keep the old notebook as your record of the past.
Should you keep a paper backup?
Some owners like to jot movements on paper during a busy day and type them in at closing time. That is fine. What matters is that one place is the official register. Two half-complete records are worse than one.
Related guides
- How to record stock in and out: the daily habits behind a good register.
- Shop inventory book vs Google Sheet: when it is time to switch.
- Stock in/out Google Sheets template: a full step-by-step build.
Quick recap
- A stock register records date, item, in, out, balance and a note.
- Start from a real count, add one line per movement, never erase.
- Damaged and lost stock counts as Out.
- A Google Sheet keeps the same layout and does the balance and reorder warning for you.