Excel Inventory Template Generator

About this template

This is a stock list: one row per SKU, the quantity you have, what it cost, what that stock is worth, and a flag that tells you when a line has fallen to its reorder point. It is the sheet a business keeps when the alternative - a full inventory system - would cost more attention than the stock is worth.

It suits a shop, a workshop, a maker selling online, a studio tracking equipment, or anyone doing a periodic count for the accounts. It is a snapshot rather than a movement ledger: there is no goods-in and goods-out history, and when you recount an item you type over the quantity. That is a limitation worth knowing before you build a process on it, and it is also why the sheet stays readable.

The Inventory sheet puts the title in row 1 and the column headers in row 3: SKU, Item name, Category, Location, Supplier, Qty on hand, Min level, Unit cost, Stock value and Reorder?. Forty blank item rows follow by default, and row 44 totals the quantity, the stock value, and the number of lines flagged for reorder. The header row and the first two columns are frozen, so the SKU and name stay visible when you scroll right.

A second sheet, Categories, holds your category list. It exists so the Category column can point a data-validation dropdown at a stable range that you can extend without touching the main sheet.

How to use it

1. Decide the columns before generating - Location and Supplier are optional and the row count is set here. Dropping a column you do not use is easier now than hiding it later, because the formulas are written to fit the columns you keep.
2. Fill one row per SKU - SKU, name, category and any location or supplier, then the three numbers that matter: Qty on hand, Min level, and Unit cost. Stock value and Reorder? are formulas and should be left alone.
3. Turn Category into a dropdown - Select C4:C43, then Data > Data Validation > List and enter =Categories!$A$2:$A$5 as the source - that is the range your categories were written to. Extend the range if you add more on the second sheet.
4. Set a real minimum level per line - The Min level is what the reorder flag compares against, so a blank one means that line will never flag. A useful starting point is the quantity you get through in the time it takes a supplier to deliver.
5. Work the reorder list - Select row 3 and turn on Data > Filter, then filter the Reorder? column to REORDER to see only what needs ordering. The count in the totals row tells you how many lines that is before you filter anything.
6. Add stock lines inside the block - Insert a row between the first and last item row so the SUM and COUNTIF in the totals row stretch to include it, then copy the Stock value and Reorder? cells down from the row above - an inserted row has no formulas of its own.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =IF(COUNT(F4,H4)=2,F4*H4,"") - Stock value on each row: quantity on hand times unit cost, but only when both are numbers. Rows you have not filled in yet stay blank instead of showing a zero value against a nameless item.
• =IF(COUNT(F4,G4)=2,IF(F4<=G4,"REORDER",""),"") - The reorder flag, guarded twice. The outer IF checks that both the quantity and the minimum level have been entered; the inner one compares them and writes REORDER when the quantity is at or below the minimum. Without the outer guard every empty row would flag, because a blank counts as zero.
• =SUM(I4:I43) - Total stock value in the totals row - the number your accountant asks for at year end. The quantity column is summed the same way, one column to the left.
• =COUNTIF(J4:J43,"REORDER") - How many lines are currently flagged. COUNTIF counts the cells whose value is exactly REORDER, and the blank strings the other rows return are not counted.

Customising it

• Make a flagged line impossible to miss: select A4:J43, add a conditional format with the formula rule =$J4="REORDER", and give it a red fill. The rule follows the flag as quantities change.
• Add a reorder quantity column with something like =IF(J4="REORDER",G4*2-F4,"") so the sheet suggests how much to buy, not just that you should.
• Add a Last counted date column and fill it in as you count. On a snapshot sheet, knowing how old a number is matters as much as the number.
• Record movements by adding Received and Issued columns and making Qty on hand a formula that adds and subtracts them from an opening figure - that converts the snapshot into a running balance.
• Extend the Categories sheet as your range grows, then widen the data-validation source range to match; the two sheets are deliberately independent so this never disturbs the item rows.