Excel Inventory Management: Setup and Limits
Ivan de Ramon · NOV 7, 2026
Excel inventory management works if you have few SKUs, one location, one or two people who edit the file, and orders that arrive slowly. You build a list of items, a log of every stock movement, and a few formulas that add it up. It stops working when several people, channels or warehouses need the same numbers at the same time.
This guide shows how to set up an Excel inventory file that holds up, with the exact formulas. Then it shows the signs that your file has reached its limit.
Is Excel good enough for inventory?
Ask four questions:
| Question | Excel is fine if | Look at software if |
|---|---|---|
| How many SKUs? | Under about 500 | Thousands, with variants |
| How many people edit? | One or two | Sales, purchasing and warehouse all edit |
| How many places sell or store stock? | One | Several channels or warehouses |
| What does a wrong number cost? | A reorder a day late | Oversold orders, angry customers |
Size is not the real limit. Excel handles 1,048,576 rows per worksheet, which is more than a small distributor will ever log. The limit is people and timing. A file is a copy. When two people hold two copies, you have two versions of the truth.
The simple structure that works
Use four sheets. Do not put everything on one.
Sheet 1: Items
One row per SKU. Never type stock numbers here. Only things that rarely change.
| SKU | Description | Unit | Supplier | Unit cost | Lead time (days) | Min level |
|---|---|---|---|---|---|---|
| FS-112 | Fertilizer 50 lb bag | bag | Acme Ag | 18.50 | 21 | 40 |
| FS-118 | Fertilizer 25 lb bag | bag | Acme Ag | 10.20 | 21 | 25 |
Rules for this sheet:
- One SKU, one row, one spelling. Use a rule such as category code plus number.
- One unit of measure per SKU. If you buy by the case and sell by the each, put the conversion in a column.
- Format it as a Table (Ctrl+T) so formulas grow with it.
Sheet 2: Transactions
This is the heart of the file. Every change in stock is one new row. You never overwrite a stock number. You add a row.
| Date | SKU | Type | Qty | Reference |
|---|---|---|---|---|
| 2026-11-02 | FS-112 | Receipt | 120 | PO-1041 |
| 2026-11-03 | FS-112 | Shipment | -35 | SO-8812 |
| 2026-11-04 | FS-112 | Adjustment | -2 | Count, damaged |
Receipts are positive. Shipments and write-offs are negative. Use Data Validation (Data tab) so "Type" can only be Receipt, Shipment, Adjustment or Return. A typo in "Type" or "SKU" breaks every total after it.
Sheet 3: Open orders
One row per open customer order line and per open purchase order line.
| Ref | Kind | SKU | Qty open | Date due |
|---|---|---|---|---|
| SO-8820 | Sales | FS-112 | 60 | 2026-11-08 |
| PO-1042 | Purchase | FS-112 | 100 | 2026-11-12 |
When an order ships or arrives, delete the row (or mark it Closed) in the same minute you add the transaction.
Sheet 4: Stock
One row per SKU, all formulas, no typing.
| SKU | On hand | Reserved | Available | On order | Value |
|---|
The next section gives the formulas.
The formulas you need
Assume your tables are named Items, Transactions and Orders.
On hand (sum of all movements for the SKU):
=SUMIFS(Transactions[Qty], Transactions[SKU], [@SKU])
Reserved (open sales order quantity):
=SUMIFS(Orders[Qty open], Orders[SKU], [@SKU], Orders[Kind], "Sales")
Available:
=[@[On hand]] - [@Reserved]
On order (open purchase order quantity):
=SUMIFS(Orders[Qty open], Orders[SKU], [@SKU], Orders[Kind], "Purchase")
Inventory value:
=[@[On hand]] * XLOOKUP([@SKU], Items[SKU], Items[Unit cost])
Reorder flag:
=IF([@Available] + [@[On order]] <= XLOOKUP([@SKU], Items[SKU], Items[Min level]), "ORDER", "")
Add Conditional Formatting so "ORDER" turns red and any negative Available turns bold red. Negative available means you promised stock you do not have.
Here is the Stock sheet for FS-112 using the rows above, with a starting balance of 80 on 2026-11-01:
| Measure | Calculation | Result |
|---|---|---|
| On hand | 80 + 120 - 35 - 2 | 163 |
| Reserved | open sales order SO-8820 | 60 |
| Available | 163 - 60 | 103 |
| On order | open purchase order PO-1042 | 100 |
| Reorder flag | 103 + 100 = 203, above the min level of 40 | none |
The flag logic counts what is on order. Without that, you reorder an item twice because the first order has not arrived yet.
A simple dashboard
Add a fifth sheet with a PivotTable on the Stock sheet. Three views are enough:
- Inventory value by supplier or category, so you see where the cash sits.
- Items where Available is below zero or the reorder flag says ORDER.
- Items with stock but no sales rows in the last 90 days. These are your slow stock.
Refresh the PivotTable each Monday (Data, Refresh All) and look at it before you place any order.
A weekly routine that keeps the file honest
A spreadsheet has no guard rails. Habits are the guard rails.
- Same day, same minute. Log every receipt and shipment when it happens, not on Friday.
- One owner. One person is responsible for the file. Others send changes to that person, or use a form.
- Count a few items every week. Pick 10 SKUs, count them, and write the difference as an Adjustment with a reason. Start with the items that cost the most.
- Back up. Keep the file in OneDrive or SharePoint with version history, not on one laptop.
- Lock the formulas. Protect the Stock sheet so nobody types over a formula.
- Do a monthly check. Sort Available from low to high. Look at the negatives. Find out why.
For a deeper method for counting and for setting min levels, see the Inventory Playbook, and use the free reorder point calculator to check your numbers.
Five Excel mistakes that cost money
1. Typing over formulas. Someone fixes a stock number by typing 50 into a formula cell. From now on that cell is wrong, and nobody knows.
2. Two versions of the file. "Inventory_final_v3_Mike.xlsx". Whoever saved last wins, and the other changes are lost. Excel has a legacy shared-workbook mode for up to 256 users, but Microsoft notes that a workbook with Excel tables cannot use it. Tables are exactly what this guide recommends. Use co-authoring in OneDrive instead, and still keep one owner.
3. Mixed units. The supplier sells cases of 12. Sales sells eaches. The file mixes both. Add a conversion column and use one unit for stock.
4. No history. Overwriting the old number with the new number means you cannot find out what happened. Always add a transaction row.
5. Counting open orders wrong. If shipped orders stay in the Open orders sheet, Reserved is too high and you refuse sales you could fill.
Signs you have outgrown Excel
Be honest with this list. If three or more are true, the file costs you more than software would.
- Two or more people edit stock numbers every day.
- You sell on more than one channel (wholesale, Shopify, Amazon) and re-type orders.
- You oversold at least once this quarter.
- Nobody trusts the "on hand" number, so people walk to the shelf to check.
- It takes more than an hour a week to prepare a purchase order.
- You need lot numbers, bin locations or more than one warehouse.
- The file has macros that only one person understands.
The Playbook chapter Why spreadsheets break, and when they don't goes deeper on these failure modes.
Moving from Excel to software without a mess
Your spreadsheet is a good start for the move, because you already have clean SKUs and transactions.
- Fix the Items sheet first: one row per SKU, one unit, clear names.
- Count the shelf and write a clean opening balance.
- Export Items and your last 12 months of sales as CSV files.
- Ask the vendor to import a sample and show you the result on your real SKUs.
- Run both for two weeks. Compare numbers every day. Stop the spreadsheet only when they match.
How Convexity handles this
Convexity is inventory software for small and mid-size distributors. A spreadsheet or CSV export can be the starting point for mapping and importing your records, as long as SKU identifiers and field definitions are consistent. An import is not the same as ongoing sync. If you need live updates from sales channels, that is a separate integration, set up with you during onboarding.
In Convexity, stock is shown as on hand, reserved, available and on order, with the same meaning as the columns above. The inventory management page has a worked example. The Convexity team handles data preparation and imports, and your team tests real orders before go-live. Convexity keeps QuickBooks Online as your accounting system.
Frequently asked questions
Can I manage inventory in Excel?
Yes, for a small catalog, one location and one or two editors. Use four sheets: Items, Transactions, Open orders and Stock. Log every movement as a new row and let formulas calculate the totals.
What is the best Excel formula for inventory?
SUMIFS. It adds all transaction quantities for one SKU, so on-hand stock is always the sum of its history. Add XLOOKUP to pull cost and minimum levels from your Items sheet.
How many SKUs can Excel handle?
Technically many thousands, since a worksheet has 1,048,576 rows. The practical limit is how many people and systems touch the file. Many teams outgrow it at a few hundred active SKUs once orders come from several channels.
Is Excel or Google Sheets better for inventory?
For one editor, they work about the same. Google Sheets is easier for several people to open at once. Neither one stops two people from overwriting each other's logic or counts, so both need a single owner and a weekly routine.