For basic inventory tracking in Excel, make an Inventory sheet with one row per item and a Transactions sheet that records every receipt and issue. Calculate each item’s current quantity from its opening balance and transaction history, then compare that quantity with a reorder point to see what needs attention.
What a basic inventory tracker should do
An inventory tracker is a current, organized record of what you have, where it is, and whether you need to replenish it. A useful starter workbook does more than list product names: it gives each item a unique identifier, records stock movements, calculates the balance, and makes low-stock items easy to spot.
Excel can handle this for a small shop, storeroom, office, workshop, or personal collection. It is especially useful when a few people can follow the same process and the number of items is manageable. It is not automatically a live connection to sales, purchasing, or warehouse systems; someone must enter or import transactions accurately.
- Item identity: A SKU or other unique code helps distinguish similar products.
- Stock balance: A formula calculates quantity on hand from recorded movements.
- Reorder signal: A threshold highlights when stock is at or below a chosen level.
- History: Dated transactions help explain how the balance changed.
Plan the workbook before entering data
Create two worksheets and name them Inventory and Transactions. Keep the item list separate from the movement history. If you put only a manually updated current quantity in the item list, you may know the balance but not why it changed. A transaction log preserves that explanation and makes it easier to check mistakes.
On Inventory, use these headers in row 1: A, SKU; B, Item; C, Location; D, Opening Qty; E, Current Qty; F, Reorder Point; G, Status; H, Unit Cost; and I, Stock Value. Enter one item per row, starting in row 2. Use a unique, stable SKU in column A; avoid using the item description as the key because names can change or be duplicated.
On Transactions, use A, Date; B, SKU; C, Type; D, Qty; and E, Note. Each receipt, issue, or adjustment gets its own row. Enter quantities as positive numbers and use a consistent transaction type: IN for stock received and OUT for stock issued. The note can explain a purchase, sale, damaged item, or count correction.
Build the tracker step by step
- Enter the item list on Inventory, including a starting balance in Opening Qty. Treat this as the quantity on hand at the moment you begin using the workbook.
- Record movements on Transactions as they happen. For example, receiving 12 units of SKU P-104 is one row with that SKU, type IN, and quantity 12. Issuing 9 units is another row with type OUT and quantity 9.
- Put the balance formula in Current Qty for each item, then fill it down. Keep SKUs consistent across both worksheets; a trailing space or different spelling can prevent a match.
- Add a reorder point for each item. This is a planning threshold, not a quantity Excel can determine on its own. Set it based on how quickly the item is used and how long replenishment usually takes.
- Use Excel’s table feature to make lists easier to filter and extend. Select the range, choose Insert > Table, and confirm that the table has headers. On Windows, Ctrl+T also opens the Create Table dialog; on Mac, use Insert > Table if the shortcut differs in your Excel setup.
For a clear example, suppose SKU P-104 starts with 20 units, has 12 units received, and has 9 issued. Its current balance should be 23. If its reorder point is 5, it is not yet at the threshold.
Calculate quantity, reorder status, and value
With Inventory!A2 containing the SKU and Inventory!D2 containing its opening quantity, and with Transactions!B2:B1000 holding SKUs, C2:C1000 holding IN or OUT, and D2:D1000 holding positive quantities, enter this in Inventory!E2:
=D2+SUMIFS(Transactions!$D$2:$D$1000,Transactions!$B$2:$B$1000,A2,Transactions!$C$2:$C$1000,"IN")-SUMIFS(Transactions!$D$2:$D$1000,Transactions!$B$2:$B$1000,A2,Transactions!$C$2:$C$1000,"OUT")
With the example above, the result is 23: 20 + 12 − 9. The formula adds all matching IN quantities and subtracts all matching OUT quantities. The ranges currently cover rows 2 through 1000, so extend them if the log grows beyond that. If you convert the transaction range to an Excel table, you can also use table references, which expand as you add rows.
With current quantity in E2, reorder point in F2, and status in G2, enter this in Inventory!G2:
=IF(E2<=F2,"REORDER","OK")
With a current quantity of 23 and a reorder point of 5, the result is OK. The less-than-or-equal comparison means an item marked at exactly its threshold will also show REORDER. Copy the formula down for the rest of the inventory.
With current quantity in E2 and unit cost in H2, enter this in Inventory!I2 to calculate the value represented by the on-hand quantity:
=E2*H2
If the example item has a unit cost of 4.50, the result is 103.50 (23 × 4.50). This is a simple quantity-times-cost estimate, not a complete accounting valuation method. If costs change between purchases, decide which cost basis your organization needs rather than assuming this calculation answers that accounting question.
Make low stock and errors easier to see
Apply conditional formatting to the Status column so REORDER stands out visually. Select the relevant status cells and use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter REORDER and choose a format. The wording remains useful if someone prints the sheet without its colors.
Prevent avoidable entry differences with dropdowns. Select the transaction-type cells, choose Data > Data Validation, set Allow to List, and provide IN,OUT as the source. For transaction SKUs, a list based on the inventory SKU range can help, though a long or frequently changing list needs maintenance unless it is set up to expand with a table or a named range. Data validation reduces inconsistent entries; it does not prove that a transaction is correct.
Use consistent units. Do not record one item in individual pieces and another in cases without making the unit clear. If a case contains 12 pieces, decide whether the tracker’s base unit is cases or pieces and convert movements consistently. Display dates as dates, quantities as numbers, and unit costs as currency. Freeze the header row with View > Freeze Panes > Freeze Top Row if the list is long.
Use a routine for receipts, issues, and counts
A spreadsheet is only as current as its entries. Choose a routine that reflects how the stock is handled: enter a receipt when it is accepted, and enter an issue when stock leaves the location or is assigned for use. If entries are delayed until the end of the week, the sheet may show more stock than is actually available in the meantime.
- Receipt: Record the date, SKU, IN, quantity received, and a useful reference or note.
- Issue: Record the date, SKU, OUT, quantity removed, and reason or destination where helpful.
- Correction: Record a clearly explained adjustment rather than silently overwriting the opening balance or old transactions.
- Physical count: Compare the shelf quantity with the calculated balance, investigate differences, and document the correction.
For example, if the sheet shows 23 units but a count finds 21, do not simply change the formula result. Check for a missed issue, a duplicate receipt, a damaged item, or a counting error. Then enter an adjustment using the method your process defines. One option is a distinct adjustment type and a revised formula; another is to record the correction as an IN or OUT with a note. Keep the chosen convention consistent and explain it to everyone entering data.
Common problems and practical limits
A frequent mistake is treating the calculated balance as proof that the physical stock is correct. The formula only totals what has been entered. Missing transactions, incorrect quantities, duplicate entries, or an incorrect starting balance can all produce a tidy but wrong number. Regular cycle counts—checking a portion of items on a schedule—can reveal discrepancies before they accumulate.
Another issue is reusing SKUs or changing them without updating related records. A transaction log depends on identifiers matching exactly. Protect the meaning of existing codes, and if a code must change, plan how to update or map historical records. Also decide how to handle returns, transfers between locations, kits, and damaged goods. A single location column describes where an item is stored, but it does not calculate transfers; those usually need a paired OUT and IN transaction or a more detailed movement model.
Excel files can also create coordination problems when several people edit separate copies. Agree on one shared workbook and a defined process, and check that concurrent editing is supported by the storage location and Excel setup in use. Keep a backup and periodically review formulas, since pasting over a formula can turn a calculated cell into a fixed value. For sensitive business records, follow the organization’s access and retention rules.
When Excel is enough—and when to move on
A basic workbook can be a reasonable fit when one person or a small team records a manageable catalog, transactions are straightforward, and periodic counts are practical. It is inexpensive to start if Excel is already available, and the columns and checks can be adjusted without deploying a specialist inventory system. The main costs are staff time, training, checking entries, and maintaining the workbook; Excel does not remove those responsibilities.
Consider dedicated inventory software when the process needs barcode scanning, multiple warehouses, purchase-order workflows, lot or serial tracking, integrations with sales channels, role-based controls, or reliable simultaneous updates. A spreadsheet can be expanded, but complexity increases the risk of broken formulas and inconsistent procedures. Before switching, list the workflows and reports you actually need, then compare software features, subscription or setup costs, data migration, and training requirements. Exporting a clean SKU list and transaction history makes any future change easier.
Frequently Asked Questions
Can Excel automatically update inventory when I make a sale?
Not by itself. A sale affects the calculated balance only after its issue is entered in the Transactions sheet or imported from a connected system. A formula can total recorded movements, but it cannot know that a sale happened unless the data reaches the workbook.
Should I track inventory in one sheet or two?
For a simple list with no history requirement, one sheet can work, but a separate transaction log is easier to audit and correct. Keeping item details on Inventory and dated movements on Transactions avoids repeatedly overwriting the balance.
How do I make a low-stock alert in Excel?
Store a reorder point for each SKU, compare it with the calculated current quantity using an IF formula, and optionally apply conditional formatting to the resulting REORDER status. Choose the threshold based on your replenishment needs; Excel does not select it automatically.
Can I use a barcode scanner with this workbook?
Many scanners enter a barcode as keyboard input, so they may be usable if the barcode matches a SKU and the active cell is correct. Test the scanner and your workflow before relying on it. Scanning alone does not create a complete transaction or prevent someone from recording the wrong quantity.
How often should I count the physical stock?
There is no single schedule that suits every operation. Count more often when items move quickly, are costly, or have a history of discrepancies; slower-moving, low-risk items may need less frequent checks. Use the results to investigate process problems, not only to adjust the spreadsheet.
Related
- Excel Statistical Analysis Tools and How to Use Them
- Excel Z-Test: How to Calculate and Interpret Results
- Excel Site Analysis: Methods, Limits, and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Not Responding When Deleting Rows? Causes and FixesIf Excel stops responding while deleting rows, first give it a little time: removing rows can trigger lengthy…
- Out of Office in Outlook on the Web: How to Set ItTo set an out-of-office reply in Outlook on the web, open Settings, find Automatic replies, turn them on,…
- How to Set Your Out of Office in New Outlook: Step by StepIn New Outlook, open Settings (the gear icon), go to Accounts > Automatic replies, turn on automatic replies,…
- Embed Video in PowerPoint Using Embed Code: Steps & LimitsYes. In PowerPoint for Microsoft 365 on Windows, go to Insert > Video > Online Videos, paste the…
- Excel Set Print Area: Steps, Tips, and FixesTo set a print area in Excel for Microsoft 365 on Windows, select the cells you want to…
- How to Delete a Page With Content in Microsoft WordTo delete a page that contains text, pictures, or other content in Microsoft Word for Microsoft 365 on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.