You count the shelves on Friday, type the numbers into a spreadsheet, and realize on Monday that you’re out of something you thought you had plenty of. A customer waits, a rush order gets delayed, and you spend an hour figuring out where the numbers went wrong.
Manual inventory tracking eats hours every week, and one mistyped digit can throw off your reorder decisions for days. The fix doesn’t require macros, VBA, or a paid add-on. A handful of built-in Excel features (Tables, SUMIFS, IF, and Conditional Formatting) can update your stock levels and flag low items on their own.
This tutorial walks you through building that tracker from scratch. It works in Excel for Microsoft 365, Excel 2021, and Excel 2019.
The Scenario and Dummy Data
Meet Lone Star Sweets, a small bakery in Austin, TX. The owner buys ingredients from a few local suppliers and needs to know, at a glance, what to reorder before the weekend rush.
She’ll use two sheets: one called Items (her master list of ingredients) and one called Log (every purchase and every use, one row per event).
Here’s the data for the Items sheet:
| Item ID | Item Name | Unit | Unit Cost | Reorder Level | Supplier |
|---|---|---|---|---|---|
| FLR-01 | All-Purpose Flour | 50 lb bag | $24.50 | 4 | Hill Country Supply |
| SUG-01 | Granulated Sugar | 25 lb bag | $18.75 | 3 | Hill Country Supply |
| BTR-01 | Unsalted Butter | 1 lb block | $4.20 | 20 | Barton Dairy |
| EGG-01 | Large Eggs | 15 dozen case | $38.00 | 2 | Barton Dairy |
| VAN-01 | Vanilla Extract | 32 oz bottle | $46.00 | 1 | Capital City Spices |
And here’s a sample of the Log sheet:
| Date | Item ID | Type | Quantity |
|---|---|---|---|
| 09/01/2026 | FLR-01 | In | 10 |
| 09/01/2026 | BTR-01 | In | 60 |
| 09/02/2026 | FLR-01 | Out | 3 |
| 09/02/2026 | EGG-01 | In | 4 |
| 09/03/2026 | BTR-01 | Out | 25 |
| 09/03/2026 | SUG-01 | In | 6 |
| 09/04/2026 | FLR-01 | Out | 4 |
| 09/04/2026 | EGG-01 | Out | 3 |
| 09/05/2026 | VAN-01 | In | 2 |
The idea is simple. Nobody edits stock numbers directly. You only log what came in and what went out, and Excel calculates the rest.
Step 1: Set Up Your Data as Excel Tables
Start by creating the Items sheet. Type the column headers from the first table above into row 1, then enter your ingredients underneath.
Click any cell inside your data, then press Ctrl + T (or go to Insert > Table). Make sure My table has headers is checked, then click OK. In the Table Design tab, rename the table to tblItems using the Table Name box.
Repeat the process on a second sheet named Log, and name that table tblLog.
[INSERT SCREENSHOT: Excel ribbon on the Table Design tab with the Table Name box highlighted and showing “tblItems,” with the Items data formatted as a table behind it]
Why Tables Matter
A regular range of cells doesn’t grow on its own. An Excel Table does.
When you type a new row directly beneath a Table, Excel expands the Table automatically, and every formula that references it picks up the new row. That’s what makes this tracker “automated.” You never have to edit a formula range again.
Step 2: Add Drop-Down Lists to Prevent Typos
A single typo in the Item ID (say, FLR-1 instead of FLR-01) makes that entry invisible to your formulas. Drop-down lists eliminate that risk.
Go to the Log sheet and select the entire Item ID column data (not the header). Click Data > Data Validation. Under Allow, choose List. In the Source box, type:
=INDIRECT("tblItems[Item ID]")
Click OK. Now repeat the process for the Type column, but this time choose List and type In,Out directly into the Source box.
[INSERT SCREENSHOT: Data Validation dialog box on the Settings tab, with “List” selected under Allow and the INDIRECT formula typed in the Source field]
How This Works
Data Validation checks what someone types against an approved list. If the entry doesn’t match, Excel rejects it.
The INDIRECT function is there because Excel doesn’t accept a direct Table reference inside the Source box. Wrapping the Table column name in INDIRECT turns that text into a live reference, so new items you add to tblItems show up in the drop-down automatically.
Step 3: Build the Automatic Stock Calculation
Now for the part that saves you the most time. Return to the Items sheet and add a new column to the right of Supplier. Name it Current Stock.
In the first data cell of that column, enter this formula:
=SUMIFS(tblLog[Quantity],tblLog[Item ID],[@[Item ID]],tblLog[Type],"In")-SUMIFS(tblLog[Quantity],tblLog[Item ID],[@[Item ID]],tblLog[Type],"Out")
Press Enter. Excel fills the formula down the entire column automatically.
[INSERT SCREENSHOT: Items sheet showing the Current Stock column with the SUMIFS formula visible in the formula bar and calculated results in each row]
Breaking Down the Formula
Think of SUMIFS as a filtered adding machine. It adds up numbers, but only from rows that meet your conditions.
The first half of the formula says: “Add every quantity in the Log where the Item ID matches this row’s item and the Type is In.” The second half does the same for Out. Subtract one from the other, and you have your current stock.
The [@[Item ID]] piece simply means “the Item ID in this same row.” That’s why the formula works for every ingredient without changes.
Using the sample data, All-Purpose Flour shows 10 in and 7 out, so Current Stock reads 3.
Step 4: Add Automatic Low-Stock Alerts
Knowing your stock is helpful. Being warned before you run out is better.
Add another column to tblItems and name it Status. Enter this formula in the first cell:
=IF([@[Current Stock]]<=[@[Reorder Level]],"REORDER","OK")
Next, add a column named Reorder Cost with this formula:
=IF([@Status]="REORDER",[@[Unit Cost]]*[@[Reorder Level]],0)
Highlight Low Items in Red
Select the Status column data. Click Home > Conditional Formatting > Highlight Cells Rules > Equal To. Type REORDER in the left box, then choose Light Red Fill with Dark Red Text from the drop-down and click OK.
[INSERT SCREENSHOT: Conditional Formatting “Equal To” dialog box with REORDER typed in and Light Red Fill with Dark Red Text selected, next to a Status column showing highlighted cells]
The Logic Behind It
The IF function asks a yes-or-no question. Is current stock at or below the reorder level? If yes, it displays “REORDER.” If no, it displays “OK.”
The Reorder Cost formula builds on that answer. It only calculates a dollar amount when the item is flagged, so you can add up that column and see exactly how much to budget for your next supplier order. With the sample data, flour (3 on hand, level 4) and eggs (1 on hand, level 2) get flagged.
Step 5: Create a Simple Summary Dashboard
Create a new sheet named Dashboard. In cell A1, type Items to Reorder. In cell B1, enter:
=COUNTIF(tblItems[Status],"REORDER")
In cell A2, type Estimated Reorder Cost. In cell B2, enter:
=SUM(tblItems[Reorder Cost])
Format B2 as currency by selecting it and clicking Home > Number Format > Currency.
[INSERT SCREENSHOT: Dashboard sheet showing “Items to Reorder” with a count and “Estimated Reorder Cost” formatted as a dollar amount]
COUNTIF counts how many cells in the Status column say “REORDER,” and SUM totals the dollar column. Open this sheet each morning and you’ll know where you stand in about five seconds.
Pro-Tip and Troubleshooting
Fix: Current Stock Shows 0 When It Shouldn’t
If a stock number looks wrong, check the Item ID first. A trailing space or a slightly different spelling in the Log means SUMIFS can’t match it. Using the drop-down from Step 2 prevents this, but older entries you pasted in may still have the problem.
To clean them up, add a helper column in the Log with =TRIM([@[Item ID]]) and compare the results against the original.
Fix: The Table Didn’t Expand
If you type below your Table and it doesn’t grow, go to File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type. Make sure Include new rows and columns in table is checked.
Pro-Tip: Show the Most Recent Date for Each Item
Want to know when you last restocked something? Add a Last Restock column to tblItems with this formula:
=MAXIFS(tblLog[Date],tblLog[Item ID],[@[Item ID]],tblLog[Type],"In")
MAXIFS finds the latest date that meets your conditions. Format the column as a date (MM/DD/YYYY) and you can spot ingredients that haven’t been replenished in weeks. This function is available in Excel 2019 and later.
Conclusion
You now have a working inventory tracker that updates itself. Log what comes in and what goes out, and Excel handles the counting, the warnings, and the reorder budget.
Start with your five most important items and expand from there. Once the habit sticks, you’ll wonder how you ever managed with a Friday-afternoon shelf count and a hopeful guess.