Inventory Spreadsheet Template: Columns and Formulas
By mise · Updated October 7, 2026
Quick answer: a good inventory spreadsheet has: item, category, unit, cost, par, on-hand count, value (=count×cost), reorder quantity (=MAX(0,par−count)) and variance. Keep one tab per count date.
A spreadsheet is the classic inventory tool. With the right columns it works; the limit is the time it takes to fill.
| Column | Formula / note |
|---|---|
| Item, category, unit | Text |
| Unit cost | Latest purchase price |
| Par | Target stock |
| Count | Entered at the count |
| Value | =Count × Cost |
| Order qty | =MAX(0, Par − Count) |
| Variance | =Count − Expected |
Build it in 10 minutes
- Put the headers in row 1: A Item, B Category, C Unit, D Unit cost, E Par, F Count, G Value, H Order qty.
- List items in the order they sit on your shelves, grouped by location (walk-in, dry store, bar).
- In G2 type
=F2*D2(count × unit cost) and copy it down. - In H2 type
=MAX(0,E2-F2)(par minus count, never negative) and copy it down. - Add a total at the bottom of column G, for example
=SUM(G2:G200). - Duplicate the tab for each count date, so you can compare counts.
Usage and variance between two tabs
| Item | Last count | Received | This count | Usage | Sold (POS) | Variance |
|---|---|---|---|---|---|---|
| Vodka 750 ml | 12 | 24 | 15 | 21 | 19.5 (from pours) | −1.5 bottles |
| Chicken breast (lb) | 40 | 120 | 35 | 125 | 118 (from recipes) | −7 lb |
| Paper cups 12 oz (sleeve) | 20 | 40 | 28 | 32 | — | n/a |
Usage = last count + received − this count. When you can work out expected usage from sales (pours, recipes), the difference is your variance; see inventory variance.
Ready-made templates
- Restaurants: restaurant inventory sheet and restaurant inventory template.
- Bars: bar inventory spreadsheet (Excel) and liquor inventory sheet.
- UK: stock control spreadsheet and stock take template.
- Par levels: par level template.
Common spreadsheet mistakes
- Mixed units: a case one week, single units the next. Write the unit in its own column and never change it.
- Old costs: unit costs copied from last year's sheet make the value wrong. Update from the latest invoice.
- Overwritten formulas: someone types a number in the Value column. Protect formula columns.
- One tab for every count missing: overwriting the same tab destroys the history you need for usage and variance.
- Rows out of shelf order: counters skip items when the sheet does not follow the room.
Scan the barcode or snap a photo: mise's AI finds the product and categorizes it. Free trial.
Start freeFrequently asked questions
Google Sheets or Excel?
Either works; Sheets is easier to share.
How do I count faster than a spreadsheet?
Scan with a phone: mise fills the item for you.
Can mise export to a spreadsheet?
Yes, to Excel and PDF.
What is the most important column?
Unit cost at the latest purchase price. Without it the count has no value, and food cost and variance cannot be calculated.
Should I lock formula cells?
Yes. Protecting the formula columns stops counters overwriting them, which is one of the most common spreadsheet errors.
How do I share an inventory spreadsheet with my team?
Google Sheets lets several people edit at once; protect the formula columns and give each counter their own area of the sheet to avoid typing over each other.
How many tabs should the spreadsheet have?
One master list plus one tab per count date works well; a summary tab can pull the totals from each count.
Can I try mise for free?
Yes. You get a free trial, with no card needed.
