Stock control spreadsheet: free template and a two-minute escape test
A stock control spreadsheet tracks what you hold, where it is and when to reorder. It needs three tabs: products, stock movements and stock on hand, where a SUMIFS formula adds up every movement. Download our free Excel template, then take the test below to see if you've outgrown it.
How the Spreadsheet Escape Test works
Most warehouses start with a stock control spreadsheet. It's free, everyone knows how to use it, and for a while it works brilliantly. Then the orders grow, a second sales channel arrives, and somebody sorts one column and scrambles the lot.
The test asks ten quick questions about accuracy, visibility and growth. You get a score out of 20 straight away, plus a rough yearly cost of mistakes and spreadsheet admin, worked out from your own numbers. Leave your details for a free report on what to fix first.
If your spreadsheet is doing its job, the test will say so. Plenty of small warehouses don't need anything more yet.
Free inventory template for Excel
Our free stock control template is a ready-made inventory spreadsheet with the formulas already in. It's built for Excel and also opens in Google Sheets.
It has four tabs: Start here, Products, Stock movements and Stock on hand. You log products and movements, and Stock on hand works out what you've got, what it's worth and what needs reordering. It's protected, so nobody types over a formula.
These are the columns every stock sheet needs, and what goes in each one:
| Tab | Column | What goes in it | Example |
|---|---|---|---|
| Products | SKU | A unique code for each product | MUG-350-SAGE |
| Products | Product name | What it is, in plain words | Stoneware mug 350ml, sage |
| Products | Location | Where it lives: aisle, bay, shelf | A-01-01 |
| Products | Supplier | Who you buy it from | Stoke supplier |
| Products | Unit cost (£) | What you pay for one unit | £2.40 |
| Products | Lead time (days) | Days from placing an order to delivery | 7 |
| Products | Safety stock | Spare stock for busy weeks and late deliveries | 60 |
| Products | Reorder point | The stock level that tells you to order | 200 |
| Stock movements | Date | When the stock moved | 05/10/2026 |
| Stock movements | SKU | Which product moved | MUG-350-SAGE |
| Stock movements | Movement type | In, Out or Adjust, from a drop-down list | Out |
| Stock movements | Quantity | How many units | 220 |
| Stock movements | Reference | Order, purchase order or count it relates to | Web orders, week 1 |
| Stock movements | Who | Who did it | AK |
| Stock on hand | On hand | Worked out by SUMIFS: in, minus out, plus adjustments | 172 |
| Stock on hand | Stock value (£) | On hand x unit cost | £412.80 |
| Stock on hand | Status | REORDER when on hand is at or below the reorder point | REORDER |
The columns in our free stock control template. Examples are from the template's sample data.
Not sure what to put in the safety stock and reorder point columns? Work them out with our free safety stock calculator and reorder point calculator.
How to build a stock control spreadsheet in Excel
Prefer to build your own? Here's how, in five steps. These are the same formulas our template uses.
1. Set up a Products tab
One row per product, with SKU, product name, location, supplier, unit cost, lead time in days, safety stock and reorder point. Give every product a unique SKU, because every formula looks products up by it.
2. Log every movement on a Stock movements tab
One row per movement: date, SKU, movement type, quantity, reference and who did it. Use Data Validation to make the movement type a drop-down list of In, Out and Adjust, so nobody types "in" one day and "Delivery" the next.
Type In and Out quantities as positive numbers. For an Adjust, use a minus for stock lost and a plus for stock found.
3. Work out stock on hand with SUMIFS
On a Stock on hand tab, put each SKU in column A. In C2, this formula adds up everything in, takes away everything out, and adds any adjustments:
=SUMIFS('Stock movements'!$D:$D,'Stock movements'!$B:$B,A2,'Stock movements'!$C:$C,"In")-SUMIFS('Stock movements'!$D:$D,'Stock movements'!$B:$B,A2,'Stock movements'!$C:$C,"Out")+SUMIFS('Stock movements'!$D:$D,'Stock movements'!$B:$B,A2,'Stock movements'!$C:$C,"Adjust")
Stock on hand for the SKU in A2. Column D is the quantity, column B the SKU and column C the movement type on the Stock movements tab.
Copy it down for every product. In our template, each formula is wrapped in an IF that leaves empty rows blank.
4. Flag reorders with IF
Put the reorder point in D2. Then in F2:
=IF(C2<=D2,"REORDER","OK")
Shows REORDER when stock on hand (C2) is at or below the reorder point (D2).
Add conditional formatting so REORDER shows up in red. For stock value, multiply stock on hand by the unit cost.
5. Protect it
Lock the formula cells and protect the Stock on hand tab, so nobody types over a formula by mistake. Keep one master file in a shared place, not copies on people's desktops.
Make your spreadsheet work until you're ready
A stock control spreadsheet can serve you well for a long time if you look after it. These habits make the biggest difference:
- Keep one master file, and never email copies around
- Limit who can edit it, and give everyone else view-only access
- Lock formula cells so they can't be typed over
- Use drop-down lists for anything that must always be spelled the same way
- Log every movement on the day it happens, not at the end of the week
- Count a few locations every week, rather than relying on one big stocktake
- Count your most valuable products most often. An ABC analysis shows you which ones they are
- Save a dated backup every week
Ordering the same products again and again? Our EOQ calculator works out how much to order each time.
Seven signs you've outgrown your stock control spreadsheet
- Your team checks the spreadsheet, then walks to the shelf to make sure
- Stocktake turns up a long list of lines that don't match
- There's more than one version of the stock file and nobody's sure which is right
- Pick lists are printed, scribbled on and typed back in later
- Everything stops when the one person who understands the sheet is off
- You sell through more than one channel and keep separate stock figures for each
- You need to track batches, expiry dates or serial numbers, and the columns keep multiplying
Recognise three or more? Take the test above for an honest score.
What your spreadsheet might be costing you
A spreadsheet is free to start. The cost shows up later, in picking mistakes and hours spent checking and fixing the sheet. Here's a worked example for a warehouse sending 200 orders a day, using the same sums as the test:
| What | The working | A year |
|---|---|---|
| Orders a year | 200 orders a day x 5 days x 52 weeks | 52,000 |
| Orders with a mistake | 1 in every 100 orders | 520 |
| Cost of those mistakes | 520 x £25 (re-send, refund and time) | £13,000 |
| Time checking and fixing the sheet | 5 hours a week x £15 an hour x 52 weeks | £3,900 |
| Total cost a year | Mistakes plus spreadsheet admin | £16,900 |
Worked example using the same sums as the Spreadsheet Escape Test. Your own numbers will differ.
At the 99.98% picking accuracy Minster's workflows deliver, 52,000 orders a year would mean about 10 picking mistakes, roughly £260. Put your own numbers into the test above to see yours.
Excel inventory management vs a warehouse management system
Excel inventory management works well when you hold a couple of hundred products, ship a few dozen orders a day and one or two people look after stock.
The trouble starts with scale and with people. A spreadsheet only knows what someone typed into it, and only when they typed it. As orders grow, the gap between the sheet and the shelf grows too.
| Stock control spreadsheet | Warehouse management system | |
|---|---|---|
| Who can edit | Anyone with the file, often at the same time, with no record of who changed what | Everyone has their own login and permissions, and changes are recorded |
| Live stock | Only as up to date as the last person who typed into it | Updated as stock moves, from goods-in to dispatch |
| Batch and expiry tracing | Extra columns that grow with every delivery | Batches and lots held against the stock, so any product can be traced |
| More than one sales channel | A tab or file per channel, matched up by hand | One stock figure for every channel |
| Picking | Printed lists, ticked off and typed back in later | Picks guided on a device or by voice, and confirmed as they happen |
| Cost | Free to start. The cost shows up as checking time and mistakes | A subscription. Worth it once it costs less than the time and mistakes it saves |
How a stock control spreadsheet compares with a warehouse management system (WMS).
A warehouse management system (WMS) records every stock movement as it happens, from goods-in to dispatch. Instead of a file that's out of date the moment it's saved, the whole team works from one live view of stock and orders.
A WMS for small businesses
You don't need to be a giant to outgrow a spreadsheet. CORE is Minster's entry-level warehouse management system, favoured by small and medium-sized businesses. EDGE is Minster's enterprise system, for larger or more complex operations.
- Minster's workflows deliver 99.98% picking accuracy and cut dispatch times by up to 80%
- Voice picking, so your team works hands-free
- Products grouped by how often they should be counted, so you rely far less on a big shut-down stocktake
- Stock traced by batch or lot number
- Around-the-clock technical support from our UK-based team
With Minster, you could be up and running with a highly capable SaaS WMS within 24 hours, and feeling improvements within 2 weeks.
Frequently asked questions
How do I make a stock spreadsheet in Excel?
Set up three tabs: Products (one row per product), Stock movements (one row per delivery, order or adjustment) and Stock on hand. Use SUMIFS to add up every movement for each SKU, and IF to flag anything at or below its reorder point. Our free template has all of this built in.
What should a stock control spreadsheet include?
At least the SKU, product name, location, supplier, unit cost, lead time, safety stock and reorder point for each product, plus a log of every movement with the date, type, quantity, reference and who did it. Stock on hand, stock value and the reorder flag should be worked out by formulas, never typed in.
Can I run stock control in Excel?
Yes, and many small warehouses do. It works well with a couple of hundred products, one sales channel and a small team. It struggles when several people edit the same file, you sell across several channels, or you need to track batches and expiry dates.
When should I stop using Excel for stock control?
Usually when checking and correcting the spreadsheet costs more than it saves. Common triggers are rising order volumes, a second sales channel, stocktakes full of differences and customers receiving the wrong items. The Spreadsheet Escape Test gives you an honest score in two minutes.
What's the difference between an inventory spreadsheet and a WMS?
An inventory spreadsheet is a record someone updates by hand. A WMS is a live system that records stock as it moves, guides your team through picking and packing, and keeps one stock figure across every channel.
How quickly can I get started with Minster?
With Minster, you could be up and running with a highly capable SaaS WMS within 24 hours, with around-the-clock technical support from our UK-based team. Tidying your product list and stock figures first makes the move quicker.
Is the stock control template really free?
Yes. Download it, use it and share it. There's no sign-up.
Ready to see life after the spreadsheet?
See how Minster gives your team one live view of stock and orders, with UK-based experts on hand around the clock.
