Before you trust the stock numbers
This inventory spreadsheet checklist is for owners and ops heads who still run stock on Excel or Google Sheets, and who have watched the count drift away from what is actually on the shelf. The sheet looks fine. Then one day the number on screen and the number in the store are two different things, and nobody can say where it went wrong.
Most of the trouble comes from small structural gaps: a duplicate row, a merged cell, a date in the wrong format, a total someone typed by hand. Fix those before you rely on the sheet for ordering, valuation or a stock-take, and the numbers hold up. Run through the 20 points below, in order, and tick off each one on your own file.
The 20-point inventory spreadsheet checklist
Frozen header row
Freeze the top row so column labels stay on screen as you scroll. Without it, you lose track of which column is stock and which is the reorder level.
One row per SKU
Every product gets exactly one row. Duplicate rows for the same item are how one count says 14 and another says 20.
Unique SKU code column
Give every item a short unique code. Names change and repeat, but a code is what your formulas and your POS can reliably match on.
Item names standardised
Pick one spelling and format for each item. 'Oil 15L', 'oil 15 l' and 'Oil-15L' will never add up as the same product.
Unit of measure column
State whether a number means pieces, kilos, litres or boxes. A value of 6 means nothing until the sheet says 6 of what.
Opening stock locked
Record and lock the opening balance for the period. If someone edits it later, every running total after it is quietly wrong.
Dropdown for category
Use a dropdown for category instead of free text, so filters and totals group items the same way every time.
Reorder level per item
Set the minimum stock for each item in its own column, so low stock becomes a formula instead of a memory test.
Protected stock formula
Lock the cells that calculate current stock, so a stray edit cannot overwrite the formula with a typed number.
Dates in one format
Pick one date format and apply it everywhere. Mixed formats break sorting and any as-of calculation you build later.
Supplier name per item
Keep the supplier against each item, so when stock runs low you know who to reorder from without a second lookup.
Last purchase price field
Store the most recent cost per item. It is what you need to value stock and to spot when a supplier quietly raised the rate.
Conditional format for low stock
Highlight any row below its reorder level automatically, so gaps jump out instead of hiding in a long list.
No merged cells
Never merge cells in the data area. Merged cells break sorting, filtering and almost every formula that reads the column.
Separate inward and outward tabs
Keep stock received and stock issued on their own tabs, so the current balance is a calculation rather than a guess.
Running balance formula
Let a formula carry the balance forward as opening plus inward minus outward, instead of typing a fresh number each day.
Stock-take date column
Note when each item was last physically counted, so you know which figures are verified and which are overdue for a check.
Audit log of edits
Turn on version history or a simple edit log, so when a number changes you can see who changed it and when.
Daily backup copy
Keep an automatic daily copy. One accidental overwrite should never cost you the whole stock register.
Dashboard from raw data
Build any summary view from the raw rows, never by typing totals by hand, so the dashboard always matches the data.
Questions about the inventory spreadsheet checklist
Who is this inventory spreadsheet checklist for?
When should I run through it?
Do I need special software?
Want your stock tracked without the spreadsheet drift?
Tell us how you track inventory today. We will come back with what automating the stock register and alerts would look like for your business.
Part of our Supply Chain and Procurement hub. Start with our supply chain automation.