Excel Inventory Tracking: Movements, Reorder Levels and Count Differences
The rule for inventory tracking in Excel fits in one sentence: don’t type current stock into a cell; calculate it from movements. The layout starts with two tables: a product card that lists each item once, and a movements table where every receipt, issue (stock taken out), transfer and count adjustment is its own row. SUMIFS gives the balance, conditional formatting flags reorder levels, and two helper tables handle balances and counts. With several people writing to one file, errors surface afterwards, not at entry. Below, a fictional two-location spare parts operation walks through it.
Download the Excel inventory tracking template (.xlsx, 55 KB). It’s a single-location, simplified version; its limits are covered below.
Example: spare parts in two locations
The locations, product codes and all figures below are fictional; I generated every row of the period in a Python script and checked each figure there.
A plant’s Main Warehouse holds all 240 maintenance spare parts; the Line Storeroom beside the production line holds 45 of them: 285 location-item rows. Six people enter movements: two storekeepers, three shift technicians and one buyer. Over 20 working days, 1,119 movement rows were entered before the count: 186 receipts, 812 issues and 121 rows for 62 transfers. Each transfer is two rows; for three transfers only the outgoing row was entered. At the period-end count, the shelf and the sheet disagreed on 23 of the 285 rows (8.1%).
How to set up the product card and the movements table
Both the product card and the movements table should be Excel tables: select a cell, choose Home > Format as Table, tick the headers box, and name them Products and Movements. According to Microsoft, a formula typed in a table column fills the whole column, and structured references adjust as rows are added (Microsoft: structured references). A fixed $E$2:$E$1001 range quietly stops at the 1,000th data row; a table doesn’t.
Products: Product Code · Product Name · Unit · Pack Size (units per box; 1 if the item doesn’t come boxed). Use codes, not names: “Bearing 6205” and “bearing 6205 ” are two different products to Excel.
Movements: Date · Location · Product Code · Type · Quantity · Unit · Document No · Entered By · Effect.
Type is picked from a list: Opening, Receipt, Issue, Return, Transfer Out, Transfer In, Count Adjustment. When you switch over, each location-item’s counted quantity becomes an Opening row. A part coming back is a Return, with the original issue’s document in Document No. Unit is also picked from a list (unit, box); the Effect formula looks for the text “box”. Quantities are positive; only Count Adjustment carries a sign (a shortfall is −4). A transfer is two rows with the same Document No, and each transfer gets its own Document No; never record a second transfer of the same product under one document. The Effect column applies the sign and the box conversion:
=IF(OR([@Type]="Issue",[@Type]="Transfer Out"),-1,1)*[@Quantity]*IF([@Unit]="box",INDEX(Products[Pack Size],MATCH([@[Product Code]],Products[Product Code],0)),1)
Why the balance is never updated by hand
A hand-edited balance loses the trail (nobody sees the Line Storeroom’s 6 changed to 2) and the breakdown (a count difference, a wrong unit and a forgotten transfer merge into one number).
Formulas for balance, reorder level and count difference
The balance comes from SUMIFS, the reorder alert from conditional formatting, the count difference from counted minus a frozen system balance. Formulas use English function names and , separators (Microsoft: SUMIFS); if your Excel uses semicolons, swap them. I tested the logic of the balance, count and check columns in the script on the same 1,119 rows; I haven’t run the formulas in Excel.
Balance per location
The third table is StockLevels: Location · Product Code · Balance · Minimum · Status. Copy Location and Product Code from Movements filtered to Type = Opening (285 rows here); a new location-item gets a 0 Opening row and a StockLevels row. Balance:
=SUMIFS(Movements[Effect],Movements[Product Code],[@[Product Code]],Movements[Location],[@Location])
Bearing RUL-6205: in the Main Warehouse, 120 opening + 100 received − 60 transferred − 38 issued = 122. In the Line Storeroom, 12 opening + 60 transferred − 66 issued = 6.
Reorder level alert
Keep a separate minimum per location-item; the same part might need 10 at the line and 40 in the main warehouse:
=IF([@Balance]<=[@Minimum],IF([@Location]="Line Storeroom","Request transfer","Reorder"),"OK")
To colour the row, select StockLevels’ data range, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and with Balance in C and Minimum in D enter =$C2<=$D2 (Microsoft: formulas in conditional formatting). After the count adjustments, 6 rows were flagged in the example, 3 per location; RUL-6205 in the Line Storeroom showed 2 ≤ 10, “Request transfer”.
Count difference and freezing the count
Count on the last day of the period after movements stop, and enter it in a Count table: Location · Product Code · Count Date · Counted · System · Difference · Reason · Close-out gap. Each count gets its own rows and date; never delete an old count. System adds up movements to that count’s date:
=SUMIFS(Movements[Effect],Movements[Product Code],[@[Product Code]],Movements[Location],[@Location],Movements[Date],"<="&[@[Count Date]])
Keep times out of Date cells; I haven’t tested the "<="& criterion with dates (Microsoft: COUNTIF). Once the count is in, paste the System column back as values. Count Adjustment rows are dated on the count day so the period closes at the counted quantity; a live System formula would pick them up and zero all 23 differences. Difference is =[@Counted]-[@System]; for RUL-6205 the Line Storeroom counted 2 against a system 6: −4.
How to find the cause of a count difference
Work through every difference in this order; a difference with a cause is corrected through its own row, and only one with no row becomes a Count Adjustment. Start with direction: a negative difference (less on the shelf) points to an unrecorded issue or a duplicate receipt, a positive one to a unit error or a missing Transfer In.
- Filter the check columns (below): a flagged row on the same location-item is usually the cause.
- Look at the size. A multiple of Pack Size − 1 is a sign of a unit error (2 boxes entered as 2 units: 2 × 49 = +98). A transfer quantity with no other leg is a one-leg transfer.
- Compare documents: delivery note quantities and line counts against Movements.
- Check work orders. A part used on a work order with no issue row is an unrecorded issue; the evidence is the work order or the person admitting it.
- No evidence: write “unexplained”. Never leave Reason blank; these feed next period’s process discussion.
RUL-6205 has no flags, −4 isn’t a box multiple, and the Line Storeroom has a maintenance work order replacing this bearing with no issue row: an unrecorded issue. The 23 differences:
| Cause | Line | Main | How it was corrected |
|---|---|---|---|
| Unrecorded issue | 7 | 2 | Count Adjustment |
| Unit (boxes entered as units) | 0 | 4 | Row’s Unit changed to “box” |
| Same delivery note entered twice | 0 | 3 | Second row’s quantity 0, Document No + “-VOID” |
| Only the outgoing transfer leg entered | 3 | 0 | Missing Transfer In, dated on the transfer day |
| Unexplained | 2 | 2 | Count Adjustment |
Seven rows were corrected in place and 16 added (3 Transfer In, 13 Count Adjustment); the period closed at 1,135 rows. Before correcting in place, write the old value in Reason (“Unit was unit, changed to box”). Once the close-out gap is 0, old rows stay untouched; later errors get new rows.
When someone writes into a counted period
Check closed periods in the Count table, not row by row. The close-out gap is the live twin of System minus Counted:
=SUMIFS(Movements[Effect],Movements[Product Code],[@[Product Code]],Movements[Location],[@Location],Movements[Date],"<="&[@[Count Date]])-[@Counted]
Once corrections are done it’s 0 on every row. A row later dated into the period, or an edited old row, moves it off 0: an issue of 3 dated day 15 entered after the count shows −3. When a gap appears, find that location-item’s row and reverse it; a genuine movement is re-entered dated in the next period. Don’t write a row-level =[@Date]<=LastCount; it flags every row of the period, Opening included.
Which errors can a formula catch?
A formula catches errors that have a row and misses errors that don’t. Add four check columns to Movements; each returns TRUE or FALSE, and you filter for TRUE daily.
Negative stock (end of day):
=SUMIFS(Movements[Effect],Movements[Product Code],[@[Product Code]],Movements[Location],[@Location],Movements[Date],"<="&[@Date])<0
Box or unit: a receipt of a boxed item entered as “unit” with a quantity that isn’t a multiple of Pack Size?
=IFERROR(AND([@Type]="Receipt",[@Unit]="unit",INDEX(Products[Pack Size],MATCH([@[Product Code]],Products[Product Code],0))>1,MOD([@Quantity],INDEX(Products[Pack Size],MATCH([@[Product Code]],Products[Product Code],0)))<>0),TRUE)
An unknown code makes MATCH return #N/A, which a TRUE filter hides; IFERROR(...,TRUE) flags it. It’s only a sign: 10 boxes of 10 entered as “10 units” slips through; a genuine part-box receipt warns. Microsoft’s page is contradictory about XLOOKUP in Excel 2016 and 2019 (Microsoft: XLOOKUP); INDEX/MATCH works in every version.
Duplicate: a second row with the same document, location, product, type and quantity? Both rows of a pair are flagged (Microsoft: COUNTIFS).
=COUNTIFS(Movements[Document No],[@[Document No]],Movements[Location],[@Location],Movements[Product Code],[@[Product Code]],Movements[Type],[@Type],Movements[Quantity],[@Quantity])>1
Transfer nets to zero: flags when a document’s transfer rows don’t add up to 0.
=AND(LEFT([@Type],8)="Transfer",SUMIFS(Movements[Effect],Movements[Document No],[@[Document No]],Movements[Product Code],[@[Product Code]],Movements[Type],"Transfer*")<>0)
It catches a missing leg, the missing leg of a second transfer on one document, a double outgoing row and a 60-out, 6-in mismatch; it misses a balanced second transfer and differences that cancel, hence one document per transfer.
Don’t use TODAY() for backdating: a TODAY()-3 check flags every correctly entered older row tomorrow. Writing into a counted period is the real damage; the close-out gap catches it. Microsoft says data validation shows no message when data is copied or filled, and only Stop blocks entry (Microsoft: data validation).
What daily checks would have shown in the example
The file had no check columns. Checked each evening against rows entered so far, they would have flagged 20 rows: 5 negative stock, 4 box/unit, 8 duplicates (4 pairs: 3 real, 1 legitimate: two batches on one delivery note) and 3 transfers. Four negatives were delivery notes entered days late with their real dates. Filter FLT-0310 showed −4 on day 7 and 16 once the day-6 receipt of 20 went in on day 9; columns added after the count never show these. The fifth followed the O-rings’ 2 boxes entered as “2 units”. Daily checks could have caught 10 of the 23 count differences (unit 4, duplicate 3, transfer 3); in this example the other 13 have no row.
What breaks when several people use the same file?
On a network folder one person writes at a time and others open it read-only; with co-authoring on OneDrive or SharePoint everyone writes, but nothing checks an entry against actual stock when it’s saved. Locks and co-authoring are covered in my guide to sharing an Excel file with multiple users. For inventory:
- Simultaneous issues push stock negative. With 4 of belt KYS-A42 in the Line Storeroom, two technicians each issue 4 within minutes and the balance hits −4 (not among the 23 differences). I infer, untested, that data validation wouldn’t stop it. Checking stock at save time is covered on my rules-based data entry form page.
- Anyone can change any row. Sheet protection guards StockLevels, not old Movements rows.
For a shared file: StockLevels stays protected, everyone only adds rows, and items below minimum get a shelf check before an issue.
How to put a value on a count difference
A difference’s value is the difference × the period’s average purchase price, kept on movement rows, not the card. Add a unit price and Amount (entered quantity × that unit’s price; box price on box rows) to Movements, and name two cells PeriodStart and PeriodEnd. The divisor sums Effect, not Quantity, so it’s in units. In the Count table:
=IFERROR(SUMIFS(Movements[Amount],Movements[Product Code],[@[Product Code]],Movements[Type],"Receipt",Movements[Date],">="&PeriodStart,Movements[Date],"<="&PeriodEnd)/SUMIFS(Movements[Effect],Movements[Product Code],[@[Product Code]],Movements[Type],"Receipt",Movements[Date],">="&PeriodStart,Movements[Date],"<="&PeriodEnd),0)
This is a management indicator, not an official valuation; agree the formal method with your accountant.
What’s in the downloadable template?
The template is single-location with fixed ranges: up to 199 products and 1,000 movement rows. Products is an Excel table; Movements fills in Product Name from the code by formula and shows ? for a wrong code; types are Receipt, Issue, Return and Count Adjustment; opening stock sits on the product card. Stock Levels is all formulas, protected without a password, and opens with Review > Unprotect Sheet. This page’s two-location layout isn’t in it: Location, Opening and Transfer types, the Count table and the check columns are yours to add. Beyond one location or 199 products the fixed-range formulas need rewriting; StockLevels above is their table-based form.
Where does a web app or inventory system start?
Split your count differences into errors tied to a row (wrong unit, duplicate receipt, missing transfer leg) and errors with none (unrecorded issue, unexplained loss). Excel finds the first group afterwards; a web app can stop it at entry. Software alone can’t see the second; process (shelf access, a required issue document) reduces it.
In the example, 10 of the 23 differences were in the first group and 13 in the second, and 9 of those 13 were in the Line Storeroom: differences with no row hit 20% of its 45 items against 1.7% of the Main Warehouse’s 240. Here I’d start with check columns and the Line Storeroom’s issuing routine; an app would touch fewer than half the differences. If row-tied errors are most of yours and several people and locations enter data, weigh an app seriously.
| Route | When | Limit |
|---|---|---|
| Excel, one person entering | One person records movements on a network folder or one computer | Read-only while someone else has it open; errors found later through check columns |
| Excel, co-authoring | Several people write at once and everyone may see everything | Nothing stops an entry against stock at save time; old rows can change |
| Off-the-shelf inventory software | Sales, invoicing and barcode checkout run with stock | Your location, transfer and approval rules go only as far as its settings |
| Custom web app | Row-tied errors dominate; several people and locations | It can’t see unrecorded issues either; first-version scope agreed in writing |
Two functions specific to this example could be built: both transfer legs as one record, and boxes converted to units on entry with the box-or-unit question asked there. Shared functions (negative stock block, count screen, audit trail) are on the inventory tracking system page.
The app runs on a virtual private server (VPS) that can be opened in your company’s name. The data is yours, and you can take it as Excel or CSV whenever you want. For small and mid-sized teams there’s no per-user licence; the cost is the server plus maintenance. The source code sits in a repository you can access, with run instructions, a dependency list and known limitations.
If you’d like to work out how your own count differences split, get in touch.
I read the sources on 2026-10-31.
Frequently Asked Questions
Do the template and these formulas work in Google Sheets?
I haven’t tested it. According to Google, an Excel file is brought into Sheets with File > Import; with Drive’s setting to convert uploaded files turned on, Excel tables come in as Sheets tables (Google: Excel and Sheets). Afterwards, compare a few hand-totalled balances and check protection, validation and colouring.
Can I enter movements in Excel with a barcode scanner?
A keyboard-style scanner types the code into the selected cell; scan into Product Code and type the quantity. If card codes don’t match barcode values, add a Barcode column to the card. Flag codes missing from the card with =COUNTIF(Products[Product Code],[@[Product Code]])=0. I haven’t tested a scanner with Excel.
What should I do when the movements table grows over the years?
According to Microsoft, a worksheet holds at most 1,048,576 rows (Microsoft: Excel specifications, read on 2026-10-31); I haven’t measured when a file slows down. At the year-end count, I suggest a new file with each location-item’s counted balance as an Opening row, the old one archived read-only.