The short answer: how to make an inventory spreadsheet
To make an inventory spreadsheet that you can actually trust, build it in Excel or Google Sheets with four tabs and let formulas do the counting:
- Items: one row per product, with a unique SKU code, name, unit of measure, category, supplier, last purchase price and reorder level.
- Inward: one row every time stock arrives (date, SKU, quantity, reference).
- Outward: one row every time stock leaves (date, SKU, quantity, reference).
- Stock: current balance per SKU, calculated as opening stock plus inward minus outward, never typed by hand.
Then lock the formulas, add a low-stock highlight, and count the shelves on a schedule to check the sheet against reality. The rest of this page shows the exact columns, how to start a list from nothing, the 20-point checklist to run on an existing file, what to prepare before a stock count, and the signs that a warehouse has outgrown spreadsheets.
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. Whether you are building a new inventory list or checking one you already have, the sections below cover both.
The columns every inventory list needs
Items tab (the master list)
One row per product. This is the only place an item's details are typed.
- SKU code (unique)
- Item name (one standard spelling)
- Unit of measure
- Category (dropdown)
- Supplier
- Last purchase price
- Reorder level
- Opening stock and its date
- Last counted date
Inward tab (stock received)
One row per delivery or return to stock. Nothing is ever overwritten, only added.
- Date
- SKU code (dropdown from Items)
- Quantity
- Supplier invoice or GRN number
- Entered by
Outward tab (stock issued)
One row per sale, issue to production, transfer or write-off.
- Date
- SKU code (dropdown from Items)
- Quantity
- Order, job or reason
- Entered by
Stock tab (calculated balance)
Read-only. Formulas pull opening stock, add inward and subtract outward per SKU, then compare against the reorder level.
- Current balance per SKU
- Below reorder level flag
- Stock value at last purchase price
How to start an inventory list from scratch
Decide what counts as one item
Agree whether a box of 12 and a single unit are one item or two, and whether sizes and colours get their own codes. This decision shapes every row that follows.
Give every item a SKU code
Keep codes short and meaningful, for example a category prefix plus a number. Never reuse a code for a different product.
Build the Items tab first
Fill in the master list with the columns above before recording any movement. Movement rows should only ever point at a code that already exists here.
Do a full opening count
Physically count everything on one day, enter it as opening stock with that date, then lock those cells. This is the baseline every later number depends on.
Record every movement as a new row
Receipts go on Inward, issues and sales go on Outward, the same day they happen. If it is not on a tab, it did not happen as far as the sheet is concerned.
Let formulas calculate the balance
Use SUMIFS on the Inward and Outward tabs by SKU so the Stock tab updates itself. Nobody should type a balance.
Keep it accurate with regular counts
Count a few items every week on rotation (cycle counting) rather than relying only on one full count a year, and fix the cause of any difference, not just the number.
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. Google Sheets keeps version history by default; in Excel it is available for files saved to OneDrive or SharePoint.
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.
Inventory preparation checklist: before a stock count
Fix the date and cut-off time
Announce when the count happens and the exact moment receipts and dispatches stop being recorded, so nothing is counted twice or missed.
Post every pending movement
Enter all deliveries, issues, returns and write-offs up to the cut-off before anyone starts counting. A count compared against an out-of-date sheet produces false differences.
Tidy and label the locations
Group like items together, label shelves and bins, and move damaged or returned goods to a separate marked area so they are counted on their own.
Print or share blind count sheets
Give counters a list of SKU codes, names and locations with an empty quantity column. Hiding the system figure stops people counting to match it.
Assign zones and pairs
Give each person or pair a defined area, and recount any item where the difference is above a threshold you set in advance.
Freeze movement during the count
Hold dispatches and park incoming deliveries in a receiving area until the count is done, or record them separately with a time stamp.
Reconcile and record the reason
Enter the counted figures, review every difference, and post adjustments with a reason (damage, theft, data entry, unit mix-up). Then update the last counted date.
Spreadsheet inventory management problems, and when to replace spreadsheets in a warehouse
A well-built sheet works for a single store with a few hundred items and one or two people entering data. The common spreadsheet inventory management problems start when that changes:
- No real-time picture. Stock is only as current as the last person who updated the file, so sales can promise stock that left the building yesterday.
- Several people, several copies. Emailed versions and offline edits create competing numbers, and nobody is sure which one is right.
- Manual entry errors. Typed quantities and codes are where most count differences start, and a sheet cannot scan a barcode on its own.
- No multi-location view. Tracking stock across warehouses, vans or branches in one file quickly becomes a tangle of tabs.
- Weak control. Anyone with edit access can change a balance, and most sheets cannot enforce approvals for adjustments.
- Slow files. Years of movement rows and heavy formulas make large workbooks slow to open and recalculate.
If several of these sound familiar, the next step does not have to be a big ERP project. Many teams move to a simple mobile app on top of the same data, with barcode scanning at the dock, a form for every receipt and issue, and automatic low-stock alerts. We build these on platforms such as AppSheet; see our AppSheet developer and low-code development pages. For a wider check on whether the business has outgrown spreadsheets in general, use the outgrown Excel checklist. If stock problems start further upstream, purchase order follow up and order processing automation cover the supplier and customer sides.
Questions about inventory spreadsheets and stock checklists
How do I make an inventory spreadsheet in Excel or Google Sheets?
What columns should an inventory list have?
How do I keep an inventory list accurate?
What should be on an inventory preparation checklist before a stock count?
Who is this inventory spreadsheet checklist for?
When should I run through it?
Do I need special software?
When should a warehouse replace spreadsheets for inventory?
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.