To keep track of stock in Excel, use two sheets. The first is an item list with one row for each product. The second is a movement log with one row for each receipt, sale, transfer or adjustment. A formula adds up the log for each item to give the stock on hand, and a second formula flags the items that have reached their reorder level. This setup works for a few hundred products and a few thousand movements a year, and it needs no add-in or database.
The weak point of most stock spreadsheets is not the formulas. It is the habit of typing a new quantity over the old one, so that nobody can say where a difference came from. A movement log avoids that problem because each change is kept as a row with a date and a reason. This guide shows how to build the sheets, which formulas to use and how to check the results. When you need to set the reorder level itself, our free reorder point calculator works it out from demand and lead time.
Why a movement log beats typing over the count
A single column called "stock" looks simpler, but it hides the history. When the figure is wrong, you cannot tell whether a delivery was missed, a sale was entered twice or a damaged item was never removed. A log keeps every event, so you can trace a difference back to the row that caused it.
The log also gives you more useful reports. You can see how many units left the warehouse last month, which items moved fastest and which had no movement at all. That information is the starting point for ordering decisions and for finding dead stock.
Step 1: Set up the item list
Create a sheet called Items. Give every product one row and one unique code. The codes are the link between the two sheets, so keep them short, fixed and never reused for a different product.
| Column | What it holds | Example |
|---|---|---|
| A: Item code | Unique code that does not change | CAB-200 |
| B: Item name | The name your team uses on the shelf | Cable set, 2 m |
| C: Unit | The unit you count in | piece |
| D: Location | Where the stock is kept | Bay 4 |
| E: Reorder level | Stock on hand at which you should order | 80 |
| F: Stock on hand | Formula, described in step 3 | 166 |
| G: Status | Formula, described in step 4 | OK |
Step 2: Log every movement
Create a second sheet called Movements. Each row records one event, and you never edit a row once it is entered. If you made a mistake, add a correcting row with a reason in the notes column. The columns below are enough for most small operations.
- Column A, Date: the day the goods moved, not the day you typed the row.
- Column B, Item code: a code from the Items sheet. Use a drop-down list with data validation so that a typo cannot create a new item.
- Column C, Type: IN for receipts and OUT for dispatches, damages and write-offs.
- Column D, Quantity: always a positive number, so the type tells the formula which way it moves.
- Column E, Signed quantity: a formula, =IF(C2="OUT",-D2,D2), that turns each row into a plus or a minus.
- Column F, Reference and notes: the delivery note, order number or reason, so that any row can be checked.
Record movements on the day they happen, not in a batch at the end of the week. A week of unrecorded dispatches is the most common reason a sheet and a shelf disagree.
Step 3: Calculate stock on hand
In the Items sheet, cell F2 adds up every signed quantity for the item in the first row. Copy the formula down the column. The formula is:
=SUMIFS(Movements!$E:$E, Movements!$B:$B, A2)
This adds the signed quantities of all movements for the item in A2, so receipts add and dispatches subtract. If you start with an opening count, enter it as one IN row dated on the first day of the period, so that every figure comes from the log.
Step 4: Flag items that need ordering
In cell G2, use a formula that compares stock on hand with the reorder level:
=IF(F2<=E2,"Order now","OK")
Apply conditional formatting to the Status column so that "Order now" rows appear in a colour. Then sort or filter the list each morning. The reorder level in column E should come from your demand and lead time, not from a guess. The reorder point calculator gives you a figure with the steps written out, and you can paste the result into column E.
Step 5: Check the sheet against the shelf
A formula can only add what was entered. Count the stock on a regular cycle to find where the sheet and the shelf differ. Count a few items each week rather than the whole warehouse once a year. When the counts differ, record an adjustment row with the counted quantity and a reason, then find out why the difference arose.
Our guide to how to do a stock count step by step sets out a method that you can use with the sheet. If you want to understand which items deserve the most attention, the article on inventory management methods explains ABC analysis and related approaches.
| Common problem | Likely cause | Fix |
|---|---|---|
| Stock on hand is negative | A dispatch was logged before its receipt, or a code was mistyped | Check the date order and the item code, then correct with a new row |
| Sheet is higher than the shelf | A damaged or returned item was never logged as OUT | Log the write-off or return with a reason and recount the item |
| Items disappear from the list | A code was changed or a row was deleted | Never change or delete codes; deactivate items instead |
| Totals look wrong after a sort | Formulas referred to rows that moved | Use whole-column references in SUMIFS and keep the item list as one table |
A worked example
Take item CAB-200 with an opening count of 120 units on 1 September. The movement log shows a receipt of 200 units on 3 September, a dispatch of 150 units on 12 September and an OUT of 4 damaged units on 20 September. The signed quantities are +120, +200, -150 and -4. The SUMIFS formula returns 166 units on hand. If the reorder level is 80, the status stays OK. If a further dispatch of 90 units were logged, the stock would fall to 76, and the status would change to "Order now".
When Excel stops being enough
A well-kept workbook serves a small team well. It starts to struggle when several people update it at once, when stock is in more than one building, or when the sheet has to be connected to sales orders, invoices and purchasing. At that point, duplicate entries and delayed updates become a routine cost. Stock also needs to link to the finance records, so that the value of the stock and the cost of what you sold agree.
Dika Ops keeps stock, sales, purchasing and finance on one record, so a dispatch updates the stock and the accounts together. Barcode scanning in inventory and warehouse work replaces typing with a scan. If you are reaching the limits of a spreadsheet, we can review the process with you and show how a system would handle the same movements. You can talk to us about your operation, and our team can connect it to the data you already hold through our integration and automation work.
Frequently asked questions
Can I track stock in Excel for free?
Yes. Excel and the free spreadsheet apps can handle an item list and a movement log with the formulas shown here. The cost is the time to enter data and to keep the sheet accurate, not the software itself.
How many items can an Excel stock sheet handle?
There is no small fixed limit for a sheet of this kind, but performance drops as the movement log grows into tens of thousands of rows and many formulas recalculate at once. Archive old years to a separate file and keep the current period in the working file.
Should I use a table or a range?
Use an Excel table for the item list and the movement log, so that new rows extend the formulas automatically. Whole-column references in SUMIFS also work well, but avoid placing totals or notes inside the columns that the formulas read.
How do I handle stock that is held in two locations?
Add a location column to the movement log and filter the SUMIFS formula by location as well as by item code. Keep the item list as one row per product, and report stock by location through a filter or a pivot table.
How often should I update the sheet?
Record each movement on the day it happens. Count a sample of items every week and the whole stock at least once a year, or more often if the value is high or the items are easy to lose. Compare the counts with the sheet and correct the differences with adjustment rows.
Next step
Start with the ten items that cause the most trouble. Create the Items sheet for them, log one week of movements, and check the result against a count on Friday. Set reorder levels with the reorder point calculator, and when the list grows, compare the sheet with the features in warehouse management systems to see what a larger setup needs. You can read more about our team on the Dika Design home page.



