How to Make an Inventory Tracker in Excel: Stock Levels, Reorder Alerts and Value

shape
shape
shape
shape
shape
shape
shape
shape
How to Make an Inventory Tracker in Excel: Stock Levels, Reorder Alerts and Value - featured image

An inventory tracker tells you what you have in stock, what is running low and what the stock is worth. Small shops, online sellers and workshops can run a reliable system in Excel without buying special software. This guide shows how to build an inventory tracker step by step, using a small product list that you can check by hand. It covers the columns to include, a separate sheet for stock movements, formulas for current stock, stock value, reorder alerts and the quantity to order, conditional formatting for low stock, lookups and a summary block. It also explains how to keep the data clean, how to avoid common errors, and how to prepare the file as a template for a client.

Updated November 2026

In this guide

  1. What the tracker needs
  2. Step 1: the product list
  3. Step 2: stock value
  4. Step 3: reorder alerts
  5. Step 4: how much to order
  6. Step 5: the stock movements log
  7. Step 6: calculate current stock from the log
  8. Step 7: look up item details
  9. Step 8: summary block
  10. Stock turnover and slow movers
  11. Keep the data clean
  12. Worked example: a week of stock movements
  13. Quick recap
  14. Practice problems
  15. Common mistakes

What the tracker needs

A good inventory tracker has two parts. The first is a product list with one row per item, holding the item code, name, unit cost, reorder level and maximum stock. The second is a log of stock movements, where every receipt and every sale is a row with a date, an item code and a quantity. The current stock is calculated from the log, so you never overwrite a number by hand. For a very small business you can start with one sheet and a stock column that you update manually, and move to the two-part design when the list grows.

Step 1: the product list

Create a sheet named Products with the columns Item, Stock, Reorder level, Max stock and Unit cost. Use the sample data below.

ItemStockReorder levelMax stockUnit cost
Pens120502000.50
Notebooks35401002.00
Folders80301201.20
Markers1520601.50

Convert it to a table with Insert, Table, and give each item a unique name. Unique names are important, because lookups and totals depend on them. Avoid spelling variants such as Pen and Pens.

Step 2: stock value

Add a column named Value with =B2*E2, which multiplies the stock by the unit cost. For the sample, pens are worth 120 times 0.50, which is 60. Notebooks are 35 times 2, which is 70. Folders are 80 times 1.20, which is 96. Markers are 15 times 1.50, which is 22.50. The total stock value is =SUM(F2:F5), which is 248.50. Our guide to multiplying in Excel explains these formulas and SUMPRODUCT, which can give the total value in one step with =SUMPRODUCT(B2:B5,E2:E5).

Step 3: reorder alerts

Add a Status column with =IF(B2<=C2,"Reorder","OK"). If the stock is at or below the reorder level, it shows Reorder. For the sample, pens at 120 are OK, notebooks at 35 are below the reorder level of 40, so they show Reorder, folders at 80 are OK, and markers at 15 are below 20, so they show Reorder. Then add conditional formatting that shades the Reorder rows in orange or red, using =$G2="Reorder". The colors make it easy to see what needs ordering at a glance. See our IF statements guide for more on this type of formula.

Step 4: how much to order

Add a column named Order qty with =IF(B2<=C2,D2-B2,0). It returns the amount needed to bring the stock up to the maximum, but only for items that need reordering. For notebooks, the maximum of 100 minus the stock of 35 gives 65. For markers, 60 minus 15 gives 45. Pens and folders show 0. The order cost is the order quantity times the unit cost, which is 65 times 2, or 130 for notebooks, and 45 times 1.50, or 67.50 for markers. The total order is 197.50. Add a total cell at the top so you know the budget needed before you place an order.

Step 5: the stock movements log

Create a sheet named Log with the columns Date, Item, Type and Quantity. Type is a drop-down with In and Out, as covered in our drop-down list guide, and the Item column uses a drop-down from the product list. Each delivery is a row with Type In and each sale a row with Type Out. Convert the log to a table so that it grows. Never delete rows from the log, and correct mistakes by adding a correcting row, so you keep an audit trail of every change.

Step 6: calculate current stock from the log

In the Products sheet, the stock can be calculated with SUMIFS. If the opening stock is in a column named Opening, use =Opening+SUMIFS(Log[Quantity],Log[Item],A2,Log[Type],"In")-SUMIFS(Log[Quantity],Log[Item],A2,Log[Type],"Out"). For pens with an opening stock of 100, a delivery of 50 and sales of 30, the current stock is 100 plus 50 minus 30, which is 120. The formula adds up the receipts, subtracts the sales and always shows the latest number. Our SUMIFS guide explains the syntax. With this design, you only enter new rows in the log, and every other number updates by itself.

Step 7: look up item details

When you enter a sale, you can look up the unit cost or the supplier automatically. With XLOOKUP, =XLOOKUP(B2,Products[Item],Products[Unit cost]) returns the cost of the item in B2, as shown in our XLOOKUP guide. In older versions, use VLOOKUP, and see our VLOOKUP troubleshooting guide if the lookup fails. A lookup lets you calculate the value of each movement and the cost of goods sold without typing prices again, which reduces mistakes.

Step 8: summary block

At the top of the Products sheet, show the number of items with =COUNTA(A2:A5), the total stock value with =SUM(F2:F5), the number of items to reorder with =COUNTIF(G2:G5,"Reorder"), which is 2 for the sample, and the total order cost with =SUMPRODUCT(H2:H5,E2:E5), which is 197.50. Add a bar chart of stock value by item, so you can see which products tie up the most money. Make sure the totals respond when you change a number, and test the file with an item that has zero stock.

Stock turnover and slow movers

To find out which items sell slowly, add a column with the total quantity sold in the last 90 days, using SUMIFS with a date condition on the log. Divide the sales by the average stock to get a turnover figure. Items with very low turnover tie up cash and may need a discount or a smaller order. Items with high turnover and low stock deserve a higher reorder level. You do not need advanced statistics: a simple sorted list of items by quantity sold is often enough, as shown in our sorting guide. Review it every month and adjust the reorder levels.

Keep the data clean

Use unique item codes or names, and make sure that the log uses the same spelling as the product list by using drop-downs. Remove extra spaces from imported data, as explained in our guide to removing spaces in Excel. Make sure quantities are numbers, not text, as shown in our guide to converting text to numbers in Excel. Protect formulas, back up the file, and count the physical stock regularly, then compare it with the sheet and record any difference as an adjustment row in the log.

Worked example: a week of stock movements

Start the week with the sample stock: pens 120, notebooks 35, folders 80 and markers 15. On Monday you sell 10 pens and 5 notebooks. On Tuesday a supplier delivers 65 notebooks and 45 markers, which is exactly the order quantity that the sheet suggested. On Wednesday you sell 8 markers and 12 folders. The log now has six rows. The current stock becomes pens 110, notebooks 35 minus 5 plus 65, which is 95, folders 68 and markers 15 plus 45 minus 8, which is 52. The stock value is 110 times 0.50, which is 55, plus 95 times 2, which is 190, plus 68 times 1.20, which is 81.60, plus 52 times 1.50, which is 78. The total is 404.60. No item is now at or below its reorder level, so the Order qty column shows zero for every product and the summary shows no items to reorder. This example shows the benefit of the log: you enter six simple rows, and the stock, status, value and order columns all update without further typing. It also gives you an audit trail, so if a count is wrong later, you can trace each movement.

Quick recap

Keep a product list and a movements log, calculate stock with SUMIFS, value it with multiplication, flag items at or below the reorder level with IF, and compute the order quantity up to the maximum stock. Add a summary block, use drop-downs to keep names consistent, protect formulas, and count the physical stock regularly.

Practice problems

Try these. Problem 1: stock 35 and unit cost 2 gives a value of 70. Problem 2: stock 15, reorder level 20 and max stock 60 gives an order quantity of 45. Problem 3: opening stock 100, receipts 50 and sales 30 give a stock of 120. Problem 4: a stock of 50 with a reorder level of 50 shows Reorder, since the test is less than or equal. Problem 5: add a new item with a stock of 10 and check that the summary counts it. Verify each result by hand before you trust the formulas.

Common mistakes

The most common mistake is to overwrite the stock number by hand and lose the history. Another is to use different names for the same item. A third is to set the reorder level too low, so you run out before the delivery arrives. A fourth is to forget about lead time, which is how long a supplier takes to deliver. A fifth is to skip physical counts. Set the reorder level as the daily usage multiplied by the lead time, plus a safety buffer.

Frequently asked questions

How do I make an inventory tracker in Excel?

Create a product list and a stock movements log, calculate current stock with SUMIFS, and add reorder alerts with IF.

How do I calculate stock value?

Multiply the stock by the unit cost, or use SUMPRODUCT on both columns.

How do I set a reorder alert?

Use IF to compare the stock with the reorder level, and add conditional formatting.

How do I calculate the reorder quantity?

Subtract the current stock from the maximum stock when the stock is at or below the reorder level.

Can Excel track stock in and out?

Yes. Record movements in a log and calculate the current stock from it.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *