Skip to content

How to Create an Inventory Spreadsheet in Excel, in 5 Steps

How to create an inventory spreadsheet in Excel: the columns, the three formulas for value, reorder status and days left, a count sheet, the finished file.

By Koa Sterling. Product specialist at TaroStack and small business owner. Yes, I am a real human, and I actually sit in front of a computer and write these articles. Reviewed November 21, 2026 · 7 min read

An inventory spreadsheet in Excel is a short job and needs only three formulas. You make a row for each item with ten columns, then let the sheet work out three things you'd otherwise do in your head: what each row is worth (on hand × unit cost), whether it's time to reorder (on hand at or below a level you set), and, for anything with a date, how many days are left. Add a total at the top and a second sheet for counting, and you have something you can run a small business on. Below are the steps with the exact formulas, using a made-up coffee roaster, and the finished file if you'd rather start from it.

The short version

  • Row 1 is headings. One item to a row below it.
  • Ten columns: item, category, unit, on hand, reorder at, unit cost, value, status, supplier, where kept.
  • Value: =D2*F2.
  • Status: =IF(D2<=E2,"REORDER","OK").
  • Days left on a dated lot: =G2-TODAY().
  • Freeze the top row, and make REORDER show in red.
  • TaroStack keeps the same list current without retyping. More below.

Step 1: the columns

Open a blank workbook, name the first sheet Inventory, and type these headings in row 1.

Column Heading What goes in it
A Item A name specific enough to reorder from: "Bag, 12 oz, with valve"
B Category Green coffee, Packaging, Finished
C Unit The unit you count in: lb, each, bag
D On hand What you counted
E Reorder at The level at which you order more
F Unit cost What one unit cost you, delivered
G Value A formula
H Status A formula
I Supplier Who you buy it from
J Where kept Rack, shelf or room

Then make the headings stay put: select row 2, and on the View tab choose Freeze Panes. The headings now stay visible as you scroll.

Step 2: the two formulas

In G2, the value of the row:

=IF(OR(D2="",F2=""),"",D2*F2)

In H2, the reorder flag:

=IF(OR(A2="",D2="",E2=""),"",IF(D2<=E2,"REORDER","OK"))

The plain versions are =D2*F2 and =IF(D2<=E2,"REORDER","OK"). The longer ones do the same sums and leave the cell blank on an empty row, so you can copy them down a hundred rows without a column of zeros. Select G2 and H2, and drag the small square at the corner of the selection down as far as you want rows.

Format F and G as currency. For the total, pick an empty cell to the right and type =SUM(G2:G100). To count what needs ordering: =COUNTIF(H2:H100,"REORDER").

Step 3: fill it in

Item Unit On hand Reorder at Unit cost Value Status
Green coffee, Colombia lb 320 300 $4.85 $1,552.00 OK
Green coffee, Ethiopia lb 180 150 $5.60 $1,008.00 OK
Green coffee, decaf lb 60 75 $6.10 $366.00 REORDER
Bag, 12 oz, with valve each 1400 1000 $0.38 $532.00 OK
Label, 12 oz bag each 900 1000 $0.07 $63.00 REORDER
Shipping box, 6 bags each 220 150 $0.92 $202.40 OK

Eight rows in all, $4,367.80, with three to reorder: decaf green coffee, labels and roasted decaf. The labels are the row to notice. They're $63.00 of stock, the smallest number on the sheet, and without them nothing ships.

To make REORDER stand out, select column H, and on the Home tab choose Conditional Formatting, Highlight Cells Rules, Equal To. Type REORDER and pick red text.

How to choose the reorder level for each row is in reorder point example.

Step 4: a sheet for dates

Anything with a best-by date needs more than one number, because two deliveries of the same item have different dates. Add a second sheet, Lots, with a row for each delivery or batch: Received, Item, Supplier, Lot, Quantity, Unit, Best by. Then two more formulas.

Days left, in H2:

=IF(G2="","",G2-TODAY())

Status, in I2:

=IF(H2="","",IF(H2<0,"EXPIRED",IF(H2<=30,"USE FIRST","OK")))

TODAY() is today's date, so the count changes every day you open the file. The 30 is the warning window: change it to suit what you sell. More of these are in Excel formula to track expiration dates.

Step 5: a count sheet

The On hand column is only as good as the last count. Add a third sheet that pulls the item names and the sheet's quantities across, with empty columns for Counted and Difference:

  • Item, in A2: =Inventory!A2
  • On the sheet, in D2: =Inventory!D2
  • Difference, in F2: =IF(E2="","",E2-D2)
  • Worth, in G2: =IF(E2="","",F2*Inventory!F2)

Print it, walk the shelves, and type what you counted. The Worth column says what each difference cost. Then type the counted figures into Inventory, and you start the next period right.

Other layouts, for stock in and out, recipes and batches, are in inventory spreadsheet examples. The same build in Google Sheets is in inventory spreadsheet in Google Sheets, and which columns suit which business in inventory list format.

Get the finished file

Download the inventory spreadsheet. Four sheets: Inventory, Lots, Count sheet and a read-me, built as above, with the roaster's rows in it. Click any Value or Status cell to see its formula. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.

Where this stops working

Every formula above is correct, and the sheet still depends on someone typing. The On hand column doesn't know that Tuesday's roast used 60 lb of the Colombia or that 200 bags went out on Wednesday. Either you subtract by hand each day, or the sheet is right only on count day. And one roasted bag is really four rows changing at once: coffee down, a bag down, a label down, a finished bag up. Excel will do that sum if you build it. Most people don't, and the labels run out on a Thursday.

How TaroStack does it

TaroStack keeps the same columns and does the subtracting.

A recipe says what a finished item takes: the coffee, the bag, the label. Each batch takes them out of stock and adds what it made.

Receiving adds stock as it arrives, with or without a purchase order, each delivery as a lot with its date.

A reorder list in days of cover, with each supplier's lead time and minimum, and reorder points suggested from how fast things really go.

Counting works on a phone with no signal. A count shows what each difference is worth.

"What could I make right now" says how many bags today's stock allows, labels included.

The job Excel In TaroStack
Value and reorder flags Formulas you copy down Kept for every item
Taking materials out when you make something By hand, or a sheet you build Each batch does it
Days left on each lot A formula on a second sheet An alert before the date
The count Print, count, retype On the phone, valued

TaroStack isn't a spreadsheet, and you can bring yours in: items, recipes, suppliers and opening stock import from a CSV file, with a preview of every row. Stock, recipes and the reorder list are on every plan, from $9.99 a month (50 items, 5 recipes) or $39 a month (3,000 items, 50 recipes). The first 14 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.

Questions people also ask

How do I create an inventory spreadsheet in Excel?

Type ten headings in row 1, one item to a row below, and add two formulas: value is on hand times unit cost, and status is REORDER when on hand is at or below the reorder level. Freeze the top row and total the value column.

What formulas do I need for inventory in Excel?

Three cover most of it: =D2*F2 for a row's value, =IF(D2<=E2,"REORDER","OK") for the reorder flag, and =G2-TODAY() for days left on a dated lot. SUM and COUNTIF give the totals.

Is Excel good enough for inventory?

For a short list counted regularly, yes. It gets hard when stock changes many times a day, when making one item uses several others, or when you need to know which lot went where.

We use AI to help with the research for these articles. Every one is read, checked against its sources and edited by Koa before it's published. Spot a mistake? Tell us and we'll fix it and say so. How we write these.

Let’s get your stack in order.

Get TaroStack

Sign up now for your free 14 day trial. No credit card required.