Inventory management in Excel: a step-by-step setup, and when to move on
Set up inventory management in Excel for free: two sheets to copy, the SUMIFS formula for stock on hand, a reorder flag, and when to move to an app.
For inventory management in Excel, keep two tables. An Items sheet lists each item’s part number, cost, reorder point and supplier, and a Stock moves sheet gets one row for every delivery, pick or correction. A SUMIFS formula adds up the moves into stock on hand, and an IF formula flags anything at or below its reorder point.
It’s for owners and office managers at shops, trades, rental yards and small distributors who want a stock management system in Excel before paying for inventory software. You get the two sheets to copy, the exact formulas, a worked example, and the signs your stock has outgrown the workbook.
How we researched this: in October 2026 we checked each menu path, formula and limit below against Microsoft’s support pages. The steps follow Excel for Microsoft 365 on Windows; Excel for the web and Excel for Mac may label a few things differently.
Is there free inventory management software in Excel?
Not as a feature. Excel has no inventory screen, but it has everything you need to build simple inventory control yourself: tables, drop-down lists, SUMIFS and conditional formatting. Microsoft also has a gallery of free inventory templates for Excel, including an inventory list with reorder highlighting and a warehouse inventory, which are a quick way to start a stock list.
Whichever you start from, one habit keeps a stock sheet right: record every move, and let Excel work out the count. Set up that way, the workbook gives you:
- Stock on hand that adds up. Every delivery and pick is a row, so the count can always be explained.
- A reorder list. Items at or below their reorder point are flagged and highlighted.
- A history. Who took what, when, and for which job.
- Stock value. On hand times cost, item by item and in total.
In effect, you’re building a small inventory database: one table of items and one table of moves. Our guide to how to use Excel as a database covers tables, IDs and lookups in more depth. This setup suits one stockroom, a few hundred items and a handful of people.
Excel inventory template: an Items sheet and a Stock moves sheet
The example is a small plumbing and heating supplier with a counter and two service vans. Your items will differ, whether they’re hitches and straps or filters and belts, but the two sheets don’t. Moves feed the Items table, where formulas work out what’s on hand and what to reorder:
Items sheet, one row per item you stock:
| Column | What it holds |
|---|---|
| SKU | Your part number or stock code for the item (SKU is short for stock keeping unit), such as VLV-BB-050. One per item. |
| Item | What it is, such as 1/2” brass ball valve |
| Unit | each, box, roll or ft, picked from a drop-down |
| Cost | What you pay for one unit |
| Reorder point | Order more when stock falls to this |
| Supplier | Who you buy it from |
| On hand | Worked out by SUMIFS (step 4) |
| Status | Reorder or OK, worked out by IF (step 5) |
| Value | On hand times cost (step 4) |
Stock moves sheet, one row per delivery, pick, return or count correction:
| Column | What it holds |
|---|---|
| Date | The day it happened |
| SKU | The item that moved, picked from a drop-down |
| Item | Its name, filled in by XLOOKUP so you can check the SKU (step 3) |
| Type | In or Out, picked from a drop-down |
| Quantity | How many, always more than zero |
| Who | Who moved it, picked from a list of names |
| Note | PO number, job, rental number or Starting count |
To set it up without typing, paste each block below into cell A1 of its sheet. Then select column A, choose Data > Text to Columns, pick Delimited, select Next, tick Comma and select Finish (Text to Columns). The valve’s test rows let you check your formulas: once they’re in, it should show 14 on hand.
Items sheet:
SKU,Item,Unit,Cost,Reorder point,Supplier,On hand,Status,Value
VLV-BB-050,Brass ball valve 1/2 in,each,7.25,15,Metro Pipe SupplyStock moves sheet:
Date,SKU,Item,Type,Quantity,Who,Note
10/1/2026,VLV-BB-050,,In,40,Maria,Starting count
10/6/2026,VLV-BB-050,,Out,12,Mike,Job 2214
10/9/2026,VLV-BB-050,,Out,8,Dana,Job 2231
10/14/2026,VLV-BB-050,,In,24,Maria,PO 1043
10/20/2026,VLV-BB-050,,Out,30,Jake,Counter saleTwo habits keep the numbers honest. Nobody types a stock count, because On hand is always a formula. And nobody edits an old move; a mistake gets a new move that corrects it. Rentals fit the same pattern: 2 ratchet straps going out on rental R-1061 are an Out move, and an In move when they come back, so On hand counts only what’s in the yard.
How to create an inventory system in Excel in 7 steps
1. Turn each sheet into a named table
Select any cell in the Items headings and go to Home > Format as Table. Choose any style, and leave My table has headers ticked (create and format tables). With a cell of the table selected, find the Table Name box on the Table Design tab and call the table Items. Repeat on Stock moves and name that table Moves, since table names can’t include spaces (rename a table).
Tables do two jobs here. A move typed in the row below the Moves table joins it, so SUMIFS counts it straight away (resize a table). And a formula typed once in Items fills the whole column (calculated columns). In the formulas, Moves[Quantity] means the Quantity column of the Moves table, and [@SKU] means the SKU in the same row (structured references).
2. Enter your items and a starting count
Fill in a row on Items for everything you stock, and delete the valve’s test rows first if you pasted them. For the reorder point, take what you use in a normal week, multiply by the weeks your supplier takes to deliver, and add a few spare. Ten ball valves a week with one-week delivery gives 15, with five to spare.
Next, count what’s on the shelves. Each item gets one In move with the counted quantity, your name and the note Starting count, dated today (Ctrl+; enters the date, insert the date). From then on, stock only changes through new rows on Stock moves.
3. Set up drop-downs and entry checks
Drop-downs keep entries consistent, so “Rec’d” or “Delivered” never turns up where SUMIFS expects In, and a mistyped SKU can’t slip past. Select a column’s cells, open Data > Data Validation and choose List under Allow (drop-down lists). Then:
- Type: enter In,Out as the Source. Under Error Alert, pick the Stop style, which refuses anything that isn’t on the list.
- SKU: click into Source and drag over the SKU cells of the Items table, leaving out the heading. Because that list sits in a table, items you add later show up in the drop-down too.
- Who and Unit: enter the choices themselves, such as Maria,Mike,Dana,Jake and each,box,roll,ft.
For Quantity, set Allow to Whole number, Data to greater than and Minimum to 0 (Decimal if you sell by the foot). For Date, set Allow to Date, Data to greater than and Start date to a day before your first count, such as 1/1/2026 (data validation rules).
The Item column on Stock moves shows the name of the SKU picked, so a wrong pick stands out:
=XLOOKUP([@SKU], Items[SKU], Items[Item], "Not found")Excel 2016 and 2019 don’t have XLOOKUP (XLOOKUP function), so use VLOOKUP there. Keep FALSE at the end, or it won’t look for an exact match (VLOOKUP function):
=IFERROR(VLOOKUP([@SKU], Items, 2, FALSE), "Not found")4. Calculate stock on hand from stock in and stock out
In the first row of On hand on the Items sheet, enter:
=SUMIFS(Moves[Quantity], Moves[SKU], [@SKU], Moves[Type], "In") - SUMIFS(Moves[Quantity], Moves[SKU], [@SKU], Moves[Type], "Out")In plain words: the first SUMIFS adds up every In move for this item’s SKU, and the second adds up every Out move. On hand is the first minus the second (SUMIFS function).
For stock value, put =[@[On hand]]*[@Cost] in the Value column. In a cell beside the table, =SUM(Items[Value]) gives the total value of your stock at cost.
5. Flag what to reorder
In the Status column, enter:
=IF([@[On hand]]<=[@[Reorder point]], "Reorder", "OK")Any item at or below its reorder point shows Reorder, and the rest show OK (IF function).
6. Highlight reorders and see what you use
With the Status cells selected, go to Home > Conditional Formatting > Highlight Cells Rules > Equal To, type Reorder and pick a fill, such as light red (conditional formatting). For an order list, filter Status to Reorder with its heading button, then sort by Supplier so each supplier’s items sit together.
To see which items you go through fastest, click in the Moves table and choose Insert > PivotTable (create a PivotTable). In the field list, put SKU in Rows, Quantity in Values, where Excel sums it, and Type in Filters, set to Out (arrange PivotTable fields). The PivotTable doesn’t update itself: right-click it and choose Refresh after new moves.
7. Count regularly, and correct with a move
Shelves drift away from the sheet: a pick nobody logged, a damaged box, a part that rode off in the wrong van. Count a few items every week. Where the shelf and On hand disagree, add a move for the difference, Out if stock is missing and In if there’s extra, with the note Count correction.
Never type a number into On hand. It replaces that cell’s formula, so later deliveries and picks stop counting for that item.
When does inventory management in Excel stop working?
Excel is a fair place to start: you probably have it already, your team can read it, and every number in this workbook can be checked. It holds up for one stockroom and a few people who sit near it. These are the signs it’s costing more time than it saves:
- Several people log stock all day. Store the workbook in OneDrive or SharePoint in Microsoft 365 and several people can edit it at once, in Excel for the web or Excel for Microsoft 365 (co-authoring). They still share one grid, though. When two people change the same cell, the last saved change usually wins, and a value pasted into a cell isn’t checked against its drop-down (more on data validation). And Who shows whichever name someone picked, not who typed the row.
- You keep stock in more than one place. For a warehouse and two vans, add a Location column to Stock moves and a third condition to each SUMIFS. Then you need On hand, Status and a reorder point for every location. Each transfer is two rows, an Out and an In, and a missed half leaves one location’s count wrong, and the total with it.
- You order on purchase orders. Stock on order isn’t on hand, so the Reorder flag keeps showing items you’ve already ordered. Fixing that means an orders sheet, with each delivery, including partial ones, matched to its order line by hand. See how to track purchase orders from request to receipt.
- Moves happen away from the desk. Excel’s phone apps can open a shared workbook, but logging a pick from a van means finding the next empty row of a wide table on a small screen. So picks get written on paper and typed in later, or not at all.
Two or more of these? Then your stock has outgrown the workbook. Our guide to keeping track of inventory as it grows covers the step from a spreadsheet to an app. If you scan barcodes at a till or sell online, look at inventory software built into your point of sale or online store instead.
Move your Excel inventory to a GoodTaco app
The workbook you just built already keeps items and moves apart, which is the hard part of setting up an inventory app. GoodTaco turns it into one your crew can use from a phone. There are two ways in.
Upload the workbook. Drop the .xlsx file on the spreadsheet to app upload, with no account needed to try it, and say what the app is for, such as “Log stock in and out from a phone and show what to reorder.” GoodTaco brings Items and Stock moves into a built-in database, makes Type and Unit into pick lists, and keeps every formula it can recreate exactly; any it can’t come across as values. Then ask for what Excel couldn’t do: “Add a Take out form the vans can use” or “Make each move pick its item from the Items list.”
Or start from the inventory management template. It’s a ready-made stock app. Your copy comes with sample records from a plumbing and heating shop with a warehouse and two vans; rename the items and locations to yours, or clear the sample and start empty. It has:
- Items with part number, unit, cost, price, reorder point, most to keep and supplier, and stock at each location.
- Take out, Receive, Move and Count buttons made for a phone in one hand, with an Undo after each one. A move from the warehouse to a van is one step. Nobody can take out more than the app shows at that location; if the shelf holds more, switch to Count in the same box, save the real number, then take it out.
- A stock history: every take-out, delivery, move and count, with who made it, from their sign-in, and when, on each item and for the whole stockroom.
- An overview of low stock, out of stock and stock value at cost, and a Needs reordering list grouped by supplier, with a Start an order button.
- Purchase orders you receive in full or in part. What arrives goes into stock at the location you pick, and until then the item shows it as on the way.
Make the app private and add your office staff and yard or van crew. Each person signs in with their Google account or a link sent by email, then uses the app in their phone’s browser. GoodTaco is free to start; paid plans begin at $9 a month, and Pro, at $49 a month, includes 10 users. Bigger teams talk to us about Enterprise.
To change the app, ask in plain words, like “Show what each van used this week,” and roll back to an earlier version if you don’t like the result. Prefer a spreadsheet that stays live? Our Google Sheets app builder works on a connected Google Sheet, reading and writing the same rows.
Know what’s on the shelf, and in every van, without opening a workbook. Start free, no credit card needed.
Frequently asked questions
Can you use Excel for inventory management?
Yes, for one stockroom and a few people. Keep an Items sheet with each item's part number (SKU), cost, reorder point and supplier, log every delivery and pick on a Stock moves sheet, and let SUMIFS work out stock on hand. It gets hard once several people, several locations or purchase orders are involved.
How do I create a simple inventory system in Excel?
Make two Excel tables: Items (SKU, item, unit, cost, reorder point, supplier) and Moves (date, SKU, In or Out, quantity, who, note). Add drop-down lists for SKU and Type, calculate On hand with SUMIFS, flag Reorder with IF, and highlight it with conditional formatting.
What is the formula for stock on hand in Excel?
With an Items table and a Moves table, use =SUMIFS(Moves[Quantity], Moves[SKU], [@SKU], Moves[Type], "In") - SUMIFS(Moves[Quantity], Moves[SKU], [@SKU], Moves[Type], "Out") in the Items table. It adds up every In move for the item and takes away every Out move.
Is there free inventory management software in Excel?
Excel has no inventory feature of its own, but Microsoft offers free inventory templates for Excel, including an inventory list with reorder highlighting and a warehouse inventory. You can also build a free system yourself from two tables and a SUMIFS formula, as this guide shows.
What is the best Excel template for inventory management?
One that logs every stock in and stock out and works out what's on hand with a formula, so nobody types over a count. Microsoft's free inventory templates are a quick start for a simple stock list. For stock that moves every day, use two tables like this guide's: Items and Stock moves, with SUMIFS for on hand and IF to flag reorders.
Can several people update an Excel inventory at the same time?
Yes, if the workbook is saved in OneDrive or SharePoint in Microsoft 365 and people open it in Excel for the web or a current Microsoft 365 version of Excel. If two people change the same cell, the last saved change usually wins, and pasted values skip drop-down checks.
When should I stop using Excel for inventory?
When several people log stock all day, you keep stock in more than one place, you order on purchase orders with partial deliveries, or most moves happen away from a desk. That's when an inventory app, such as GoodTaco's inventory management template, saves time and mistakes.
Start from the inventory management template
Get your own copy of a working inventory management app with sample data, ready to make yours. Free to start.
Use the inventory management template, free