Excel guide · 3 min read

Inventory tracking formulas in Excel

Q: What is the formula for stock on hand in Excel?

Opening stock plus stock received minus stock sold: =C2+D2-E2. Fill it down for every item.

Q: How do I calculate total inventory value in Excel?

Multiply quantity by unit cost and add it up in one step with =SUMPRODUCT(quantity_range,cost_range).

Q: How do I set up reorder alerts in Excel?

Add a reorder level column and use =IF(stock Conditional Formatting > Highlight Cells Rules > Text that Contains.

Quick answerStock on hand is opening stock plus received minus sold: =C2+D2-E2. Flag low stock with =IF(C2+D2-E2<=F2,"Reorder","OK"), and value everything with =SUMPRODUCT(C2:C7+D2:D7-E2:E7,G2:G7), which multiplies each item's stock by its unit cost and adds the results.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D	E	F	G	I
1	SKU	Item	Opening	Received	Sold	Reorder at	Unit cost	Look up
2	CB-250	Cold brew can 250ml	48	96	121	24	1.35	ES-1KG
3	OM-1L	Oat milk 1L	30	24	41	12	2.1
4	ES-1KG	Espresso beans 1kg	12	10	15	6	18.5
5	CUP-12	Paper cups 12oz (50)	40	0	22	15	4.8
6	LID-12	Lids 12oz (50)	38	0	38	15	2.95
7	SYR-VAN	Vanilla syrup 750ml	9	6	8	4	7.25

Download this sheet as an Excel practice file (.xlsx)

Stock on hand

Never overwrite the stock count by hand after each sale. Keep three numbers per item for the period (opening, received, sold) and calculate what should be on the shelf. When you do a physical count, the difference between this figure and the shelf tells you about waste, breakage or theft.

This sample covers four weeks at a small cafe.

=C2+D2-E2

Result: 23

48 cans plus 96 received minus 121 sold leaves 23 cold brew cans.

Reorder and out-of-stock flags

Test the most urgent case first. Zero or less is Out of stock; at or below the reorder level is Reorder; everything else is OK. If you test the reorder level first, an empty shelf just says Reorder.

A negative stock figure means sales were recorded that the stock records cannot explain, usually a delivery that was not logged. Treat it as a data problem to fix, not as stock.

=IF(C2+D2-E2<=0,"Out of stock",IF(C2+D2-E2<=F2,"Reorder","OK"))

Result: Reorder

23 cans is at or below the reorder level of 24, so the flag is Reorder.

Total stock value

SUMPRODUCT multiplies two lists pair by pair and adds the results. The first list is calculated on the fly (stock on hand for each row), so you do not need a helper column. Use what you paid per unit, not the selling price: that is the figure your accountant and insurer want.

=SUMPRODUCT(C2:C7+D2:D7-E2:E7,G2:G7)

Result: 325

The six items are worth $325.00 at cost.

How many items need ordering

COUNTIF cannot compare each row with its own reorder level, but SUMPRODUCT can. The comparison returns TRUE or FALSE per item and the double minus turns those into 1 and 0. Here the cold brew and the lids (which have run out) both qualify.

Swap the double minus for a multiplication to price the order: =SUMPRODUCT((C2:C7+D2:D7-E2:E7<=F2:F7)*F2:F7*G2:G7) costs ordering the reorder quantity of each flagged item, $76.65 here.

=SUMPRODUCT(--(C2:C7+D2:D7-E2:E7<=F2:F7))

Result: 2

2 items are at or below their reorder level.

Look up one SKU

Type a SKU in I2 and return its stock. MATCH finds the row (0 means exact match) and INDEX returns the value. This works in every Excel version. In Excel 365 or 2021 and later, =XLOOKUP(I2,A2:A7,B2:B7,"Not found") does the same with a built-in message for unknown codes.

Lookups fail on SKUs with trailing spaces or codes stored as numbers in one place and text in another. Keep SKUs as text with letters in them and TRIM anything pasted in.

=INDEX(C2:C7+D2:D7-E2:E7,MATCH(I2,A2:A7,0))

Result: 7

ES-1KG is in row 3 of the list, and 12 + 10 - 15 leaves 7 bags of espresso beans.

Days of cover and a movement log

A fixed reorder level ignores how fast things sell. Days of cover divides stock on hand by average daily sales (sold over 28 days). Reorder when cover falls below your supplier's delivery time plus a few days' buffer. Espresso beans have 13.1 days left; cold brew has only 5.3.

As the list grows, log every movement on a second sheet (date, SKU, type In or Out, quantity) and calculate Received and Sold with SUMIFS, for example =SUMIFS(Moves!D:D,Moves!B:B,A2,Moves!C:C,"In"). For a ready-made version with flags and a SKU lookup, download the free inventory tracker template at /templates/inventory-tracker-template/.

=ROUND((C2+D2-E2)/(E2/28),1)

Result: 5.3

23 cans at about 4.3 a day lasts roughly 5.3 days.

Common mistakes

Typing the current stock instead of calculating it

Keep opening, received and sold, and calculate on hand. Then a stock count reveals losses instead of hiding them.

Valuing stock at selling price

Use unit cost. Selling price overstates what your stock is worth on the books.

Testing Reorder before Out of stock

In a nested IF the first true test wins. Check the empty shelf first.

FAQ

What is the formula for stock on hand in Excel?

Opening stock plus stock received minus stock sold: =C2+D2-E2. Fill it down for every item.

How do I calculate total inventory value in Excel?

Multiply quantity by unit cost and add it up in one step with =SUMPRODUCT(quantity_range,cost_range).

How do I set up reorder alerts in Excel?

Add a reorder level column and use =IF(stock<=reorder_level,"Reorder","OK"), then color the flags with Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.

Can Excel track inventory in and out automatically?

Excel will not see sales by itself, but if you log every movement on one sheet, SUMIFS totals the ins and outs per SKU and the stock figures update as soon as you add a row.

Functions used

Related guides

Updated November 2, 2026