A spreadsheet is a perfectly reasonable way to manage inventory, right up until it is not.
The transition is rarely noticed at the time. What usually happens is that a spreadsheet works well for a year, the business grows, and the file quietly stops being reliable — but because it has always been there and everyone knows how it works, it keeps being used long after the errors have started costing more than software would.
This guide covers how to build one that works, and just as importantly, how to recognise the point at which you have outgrown it.
When a spreadsheet is the right tool
A spreadsheet suits you if most of these are true:
- One person maintains it. The moment two people edit the same file, versions diverge.
- You sell through one channel. Multi-channel selling means one stock figure serving several places, and a spreadsheet cannot update a marketplace listing when something sells on your website.
- Under roughly a few hundred SKUs. Beyond that, finding and updating rows becomes the job.
- Stock movements are countable by hand — tens per day rather than hundreds.
- You do not need batch, serial or expiry tracking. These are close to impossible to maintain manually with any confidence.
- The consequence of an error is manageable. If a stock error means an apology, fine. If it means a compliance breach, no.
It is also the right tool when you are working out what you actually need. Building a spreadsheet forces you to decide what to track, and that clarity makes a later software selection far better informed.
The columns your spreadsheet needs
Start with these. Resist adding more until you have used it for a month.
Identification
- SKU — your unique code. One per sellable variant. This is the column everything else hangs off, so decide the format before you start rather than letting it evolve.
- Product name — human-readable description.
- Category — for grouping and filtering.
- Barcode — if your products have one, so you can search by scan.
- Location — where it lives. Even in a small stockroom, “Shelf C3” beats “somewhere in the back”.
Quantities
- Quantity on hand — current physical stock.
- Quantity allocated — committed to orders not yet shipped.
- Quantity available — on hand minus allocated. This is the number that matters for selling, and the one most spreadsheets omit.
- Quantity on order — placed with suppliers, not yet received.
Reordering
- Reorder point — the level that triggers a new order.
- Reorder quantity — how much to order when it does.
- Lead time in days — measured, not quoted.
- Supplier — who you buy it from.
Money
- Unit cost — landed cost including freight and duty, not just the invoice price.
- Selling price.
- Stock value — quantity on hand multiplied by unit cost.
Housekeeping
- Last counted date — so you can see what has not been verified recently.
- Last movement date — for identifying dead stock.
- Notes — for anything that does not fit elsewhere.
Two formulas that make it useful
A spreadsheet that only records numbers is a list. These two turn it into a tool.
Available stock:
= [Quantity on hand] – [Quantity allocated]
Simple, and the single most useful calculated column. Selling against on-hand rather than available is how you promise stock that is already committed to somebody else.
Low stock flag:
= IF([Quantity available] + [Quantity on order] <= [Reorder point], "ORDER", "")
The addition of quantity on order is what stops it telling you to reorder something you ordered last week. Without that term, the flag stays on until the delivery arrives and people learn to ignore it.
Two more worth adding once the basics are working.
Stock value:
= [Quantity on hand] * [Unit cost]
Summed across the sheet, this is your inventory investment — a figure many small businesses have never actually calculated.
Days of cover:
= [Quantity available] / [Average daily usage]
More informative than a raw quantity, because 40 units means something different for a product selling two a week than for one selling twenty a day.
Add conditional formatting so low-stock rows colour themselves, and sort or filter the sheet so flagged rows appear at the top. A flag nobody sees is not a flag.
How to keep it accurate
This is the part that determines whether the spreadsheet is useful or actively misleading.
Update at the time of movement. Not at the end of the day, not from a notepad. Every hour of delay adds error, and end-of-day updating from memory is the single largest source of inaccuracy in spreadsheet-based inventory.
One person owns it. If several people need to update it, use a cloud-based sheet with genuine multi-user editing rather than emailing a file around. Versions of an inventory file living in different inboxes is the classic failure.
Count regularly. Cycle count a portion each week — a shelf or a category — rather than attempting everything at once. Record the date in the last-counted column so gaps are visible.
Log adjustments separately. Keep a second tab recording every correction with a date and a reason. The pattern in those reasons is where your process faults are, and overwriting a cell destroys that information permanently.
Protect the formula cells. The most common way a working spreadsheet breaks is somebody typing a number over a formula. Lock them.
Back it up automatically. Cloud storage with version history, so an accidental deletion or a bad paste can be reversed. Local files with no history is how a year’s work disappears.
Never sort without selecting everything. Sorting one column and leaving the others in place scrambles every row, and it is frighteningly easy to do without noticing.
Structuring the file properly
Most spreadsheets fail structurally rather than through bad formulas. Four tabs, each with one job, avoids nearly all of it.
Tab 1 — Products. One row per SKU, holding the things that rarely change: name, category, barcode, supplier, unit cost, selling price, reorder point, reorder quantity, lead time. This is your reference data, and nothing here should be edited during day-to-day use.
Tab 2 — Stock. Current position per SKU: quantity on hand, allocated, available, on order, location, last counted. This is the tab people look at.
Tab 3 — Movements. Every receipt, sale, adjustment and transfer, one row each, with date, SKU, quantity, direction and reason. Append-only — never edit or delete a row.
Tab 4 — Suppliers. Contact details, lead times, minimum order quantities, payment terms.
The movements tab is the one people leave out, and it is the most valuable. Without it you have a current position and no history, so when a figure is wrong there is nothing to investigate. With it, you can trace how a number became what it is, and you can calculate average usage for reorder points from actual data rather than from someone’s impression.
It also changes how the stock tab works. Rather than typing over the quantity each time something moves, the quantity on hand becomes a calculation:
= SUMIF(Movements[SKU], [@SKU], Movements[Quantity])
with receipts positive and sales negative. Nobody overwrites anything, every change is recorded, and the current position is derived rather than maintained. That single structural decision eliminates the most common cause of spreadsheet inventory becoming untrustworthy.
Two supporting habits: use proper named tables rather than raw cell ranges, so formulas keep working when rows are added; and use data validation on the SKU column in the movements tab, so a typo cannot create a phantom product.
A worked example
Say you stock a product with this position:
- Quantity on hand: 240
- Allocated to unshipped orders: 55
- On order from supplier: 0
- Average daily usage: 12
- Supplier lead time, measured: 9 days
- Safety stock: 45
Available stock is 240 − 55 = 185 units. That is what you can actually promise, not 240.
Reorder point is (12 × 9) + 45 = 153 units.
Days of cover is 185 ÷ 12 = roughly 15 days.
So you are above your reorder point with about two weeks of cover, and the low-stock flag correctly stays off. Now sell 40 units: available drops to 145, below the reorder point of 153, and the flag fires. You raise a purchase order for your reorder quantity, enter it in the on-order column, and the flag clears — because the formula includes on-order stock, it will not keep nagging whilst the delivery is in transit.
The point of working through it is that the four columns interact. Operations that track only quantity on hand see 240 units and no problem, whilst 55 of those are already sold and the real position is far tighter than it appears.
A simple stocktake and counting workflow
A spreadsheet only stays accurate if something regularly checks it against the shelf. Here is a workflow that takes under an hour a week and catches almost everything.
Add three columns to your sheet: Counted qty, Date counted, and Variance (a formula: counted quantity minus the quantity your sheet says you hold).
Pick what to count this week. Not everything. Take your top 20 lines by value or by sales, and count those every week. Take the rest and count a rotating slice — a tenth of them a week means everything is checked roughly quarterly, which is enough for slow-moving stock.
Print a blind count sheet. Filter to the rows you are counting, and print or export only the product, the description and the location — never the expected quantity. People confirm what they are shown, so a blind count is the single biggest accuracy gain available and it costs nothing.
Count by location, not by product. Walk the shelves in order and count what is in each place. If a product is held in two locations, record two lines. Counting by product invites whoever is counting to stop looking once the number matches.
Enter the counts, then look at the variance column rather than adjusting as you go.
Recount anything with a significant variance before changing a figure. The second count disagrees with the first often enough that adjusting on one count writes errors in rather than out.
Investigate before adjusting. Check the next bay along, the returns pile, the packing bench and the last few transactions. A large share of apparent losses are things in the wrong place.
Then update the quantity, and record why in a notes column — mis-pick, receiving error, damage, unrecorded sale, or unknown. A fortnight of reason codes usually points straight at one process that needs fixing.
Stamp the date. A Date counted column tells you at a glance what has not been checked for months, which is where the next error is hiding.
A full stocktake of everything, once or twice a year, still has its place for the accounts. The weekly routine is what keeps the figures usable in between, and it makes the annual count far less painful because the big surprises have already been found.
Pros and cons of managing inventory in a spreadsheet
An honest assessment, because spreadsheets get both oversold and unfairly dismissed.
The pros.
- It costs nothing, and you already know how to use it.
- It is completely flexible. Any column you want, any calculation, no waiting for a supplier to add a feature.
- It is immediate. You can have a working stock sheet this afternoon, where software takes weeks.
- It forces you to understand your own numbers. Building the reorder formula yourself teaches you more about your stock than any dashboard will.
- It is portable. Your data is yours, in a format anything can read, with no contract and no migration if you leave.
- It is genuinely adequate at small scale. A few hundred lines, one location, one or two people — a well-kept sheet will not hold you back.
The cons.
- One person at a time, realistically. Shared cloud sheets help, and two people editing the same rows during a busy period still causes losses.
- No real audit trail. You can see that a number changed. You usually cannot see who changed it, when, or what it was before.
- Silent errors. A formula dragged one row short, a column sorted without its neighbours, a value typed over a formula. Nothing warns you, and the sheet keeps producing confident, wrong answers.
- No connection to where you sell. Every sale has to be entered by hand, which means the sheet is always slightly out of date and overselling is a matter of time.
- It does not scale. Performance degrades, and more importantly the manual entry burden grows in direct proportion to your order volume, which is exactly the wrong shape as you grow.
- No barcode scanning at the point of movement, which is where most inventory errors are actually prevented.
- It relies entirely on discipline. The sheet is accurate only for as long as every person updates it every time, and busy weeks are precisely when that stops happening.
The fair summary: a spreadsheet is the right answer at the start and a liability past a certain point, and the crossover comes sooner than most people expect — usually the first time you add a sales channel or a second person who needs the same figures.
The limits of a spreadsheet
Worth knowing before you hit them:
No real-time multi-channel sync. If you sell on your own site and a marketplace, nothing updates one when the other sells. Overselling follows.
No transaction history. A cell shows what the number is now, not how it became that. When a figure is wrong, you cannot see when or why.
No barcode scanning. Every movement is typed, and typing is where errors originate.
No user permissions or audit trail. Anyone with the file can change anything, and nothing records who did.
No automated alerts. The low-stock flag only works if someone opens the file and looks.
Fragile at scale. Large sheets with many formulas become slow, and the risk of a mis-sort or an overwritten formula rises with size and with the number of people involved.
No batch, serial or expiry tracking in any form you can rely on.
When to move to software
The signals, roughly in the order they appear:
- You are selling on more than one channel. This is usually the decisive one, because manual synchronisation does not scale past a handful of listings.
- More than one person needs to update stock.
- You have oversold, and particularly if it has happened more than once.
- You are spending more than an hour a day on the spreadsheet. At that point the subscription is cheaper than your time.
- You cannot answer “what did we have last Tuesday?” because there is no history.
- You need batch or expiry tracking for regulatory reasons.
- Counting has become a major exercise because the range has grown.
Moving does not waste the spreadsheet work. A clean, well-structured sheet is exactly what a migration needs, and the discipline of having maintained one makes the new system easier to configure because you already know what you track and why.
Making the move to software without losing anything
When the spreadsheet has run its course, the transition goes better if the sheet has been maintained properly — and worse if it has not, because whatever is wrong gets imported.
Clean before you migrate. Duplicate SKUs, products discontinued two years ago, rows with no cost or no supplier, inconsistent category names. Migrating that reproduces it inside a system you are now paying for. This is the step that takes the time, and it is unavoidable.
Standardise the SKU format first. If codes have grown organically — some with hyphens, some with spaces, some with trailing characters nobody can explain — fix it in the spreadsheet where a find-and-replace is trivial, rather than after import where it is not.
Fill in the dimensions and weights. Most systems want them, most spreadsheets do not have them, and gathering them is slow. Starting before you select a system removes the usual cause of a delayed go-live.
Count immediately before cutover. Importing quantities you do not trust guarantees the new system is wrong on day one, and the team will conclude the software is at fault.
Keep the spreadsheet read-only for a month. Not as a parallel system — that produces double entry and divergence — but as a reference for checking anything that looks wrong in the new system.
Import the movements history if you can. Many systems accept a transaction import, and having twelve months of usage history from day one means reorder points and forecasts work immediately rather than after a year of accumulating data.
A well-kept spreadsheet is a good migration source. The columns in this guide map almost directly onto the fields any inventory system will ask for, which is not a coincidence — the same information is needed either way, and the difference is who maintains the arithmetic.
Where to go deeper
- How to manage inventory — the process the spreadsheet supports.
- Inventory management for small business — the wider picture at this scale.
- Cloud inventory management — the usual next step.
- What is a reorder point — the calculation behind the flag.
- How to track inventory — methods beyond a sheet.
- What is a SKU — getting the identifier right before you build.
The one-line takeaway
A spreadsheet works for a single-channel operation with a few hundred SKUs and one person maintaining it — build it around available stock rather than on-hand, update at the moment of movement, and move to software when you start selling through a second channel.
Frequently asked questions
Can I manage inventory with a spreadsheet? Yes, for a single-channel business with a modest range and one person maintaining the file. It stops being viable when multiple channels, multiple editors or regulatory tracking requirements appear.
What should an inventory spreadsheet include? SKU, product name, category, location, quantity on hand, quantity allocated, quantity available, quantity on order, reorder point, reorder quantity, lead time, supplier, unit cost, selling price and stock value, plus last-counted and last-movement dates.
What is the formula for a low stock alert? =IF([Available] + [On order] <= [Reorder point], "ORDER", ""). Including quantity on order is what prevents it flagging items you have already reordered, which is what causes people to stop trusting the alert.
How do I stop my inventory spreadsheet becoming inaccurate? Update at the moment of movement rather than later, have one owner, cycle count a portion each week, log adjustments with reasons on a separate tab, protect formula cells, and back up with version history.
When should I move from a spreadsheet to inventory software? When you sell through more than one channel, when more than one person needs to update stock, when you have oversold, or when maintaining the sheet is taking more than about an hour a day.
Is Excel or Google Sheets better for inventory? Google Sheets handles genuine multi-user editing and automatic version history better, which addresses two of the main failure modes. Excel is faster with large datasets and has stronger formula and macro capability. For most small inventory uses the collaboration advantage matters more.
How many SKUs can a spreadsheet handle? Technically thousands, practically a few hundred before maintaining it becomes the job rather than a task within it. The constraint is rarely the software — it is the time spent finding rows, the rising chance of a mis-sort, and the difficulty of counting a range that large by hand.
Can a spreadsheet handle multiple warehouse locations? Technically yes, with a location column or a tab per site, but it becomes unwieldy quickly and there is no way to transfer stock between locations with an audit trail. Multiple locations is usually a sign you have outgrown the approach.
Warehouse Wise is a free, independent library of guides on inventory management, warehousing and fulfilment, written for the people actually doing the work — new warehouse and inventory staff, small and growing retail and ecommerce businesses, and anyone who holds stock and wants to understand it properly. Every guide explains the term, shows a worked example with real numbers where it helps, and stops. We are independent: we do not sell software or equipment, we take no commission on referrals, and our guides are not ranked by who pays us.