Inventory management in Excel: a free template and its limits

Inventory management in Excel works well if it follows one rule: you never type a stock quantity, you calculate it. This guide builds that file step by step, with the exact formulas. It also gives you the free template, then says plainly where Excel stops.

How do you set up inventory management in Excel?

Create two Excel tables. The first lists your items: SKU, description, unit cost, opening stock and reorder point. The second records every movement on its own line: date, SKU, in or out, quantity. Each item's current stock is then calculated from those movements, with no manual entry.

Many stock files start as a single sheet, with a "Quantity" column that gets corrected at every delivery. It's quick on day one. A month later, a wrong figure can no longer be explained: nobody knows who changed it, when, or why.

Keeping items and movements apart solves this. Every delivery, every sale, every breakage becomes a dated line with a comment. An item's stock is no longer a fragile value. It's a sum you can always rebuild.

The whole principle fits in one formula:

Use real Excel tables (Home, then Format as Table), not plain ranges. A table grows on its own when you add a row, and its formulas follow.

What's in the free template?

The inventory management Excel template is a two-sheet workbook, each sheet formatted as an Excel table. The "Items" sheet holds fifteen sample items and all the formulas. The "Movements" sheet holds forty stock-in and stock-out entries so you can see the calculations at work. The two tables are named Items and Movements. No sign-up is needed to download it.

The "Items" sheet has thirteen columns:

The "Movements" sheet has five columns: Date, SKU, Type, Quantity and Comment. The Type column offers two values, "In" or "Out", from a drop-down list. On the Items sheet, rows to reorder are highlighted. To use it, replace the samples with your own items, then record movements as they happen.

How do you calculate current stock with SUMIFS?

The SUMIFS function adds up the values in a column that meet several conditions. Here, it adds up the quantities on the Movements sheet where the SKU matches the item and the type is "In". The same formula with "Out" gives stock out. Current stock follows from the two.

The syntax is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …), according to Microsoft's SUMIFS page. The range to sum comes first, then each pair of range and criterion.

In the template, the Stock in column (H) contains, in row 2:

=SUMIFS(Movements[Quantity],Movements[SKU],A2,Movements[Type],"In")

The Stock out column (I) uses the same formula with "Out":

=SUMIFS(Movements[Quantity],Movements[SKU],A2,Movements[Type],"Out")

The Current stock column (J) adds opening stock (G) and stock in (H), then subtracts stock out (I):

=G2+H2-I2

Movements[Quantity] is a structured reference: it points to the whole Quantity column of the Movements table, even as the table grows. A2 is the item's SKU on the same row. The template mixes both styles to stay readable.

If you'd rather use structured references throughout, replace A2 with [@SKU]. The @ refers to the cell on the current row, as the documentation on structured references explains. Both versions give the same result.

With UK regional settings, Excel separates arguments with commas. In a table, a formula entered in row 2 fills down the whole column on its own.

How do you set a reorder point and build a reorder list?

The reorder point is the quantity below which you need to order more. Enter it for each item, then compare it with current stock using the IF function. The "Reorder" column shows "Yes" as soon as stock reaches or falls below the reorder point. Filter on "Yes" and you have today's order list.

The IF function is written IF(logical_test, value_if_true, [value_if_false]), according to Microsoft's IF page. In the Reorder column:

=IF(J2<=K2,"Yes","No")

J2 is current stock and K2 is the reorder point. The <= sign switches the item to "Yes" as soon as it reaches its reorder point, not only once it drops below.

A simple rule is enough to set the reorder point. Multiply average daily usage by the supplier's lead time in days, then add a margin. An item that goes out at 4 units a day, with a 5-day lead time, needs at least 20 units when you order. With a margin of 10 units, the reorder point becomes 30.

How do you calculate stock value?

An item's stock value is its current stock multiplied by its unit cost, excluding VAT. Total stock value is the sum of that column. It tells you how much money is sitting on the shelves. It's also a useful starting point for the year-end stock count.

In the template, the Stock value column (M) multiplies current stock (J) by the unit cost excluding VAT (F):

=J2*F2

The file doesn't include a total. To get one, enter this in a cell outside the table:

=SUM(Items[Stock value])

The template values each item at a single unit cost, which is enough for day-to-day management. If your costs vary a lot from one delivery to the next, ask your accountant which valuation method to use.

To track this value over time, with a chart by category or by supplier, see our guide to building an Excel dashboard.

Safer data entry: drop-down lists and conditional formatting

Two settings prevent most errors, and the template already includes both. Data validation offers "In" or "Out" in the Type column, which avoids typos such as "Inn" that SUMIFS would ignore. Conditional formatting highlights items at or below their reorder point.

The drop-down list for movement type

Select the Type column of the Movements table. On the Data tab, in the Data Tools group, choose Data Validation. In the Allow box, pick List, then enter the two values in the Source box, following Microsoft's instructions. Microsoft separates them with commas: In,Out, as in the template.

The same method works for the SKU column. A list that points to the SKUs on the Items sheet prevents mistyped codes. A movement on an unknown SKU isn't counted anywhere, which skews stock without any warning.

Highlighting items below the reorder point

Select the data rows of the Items table, without the header. On the Home tab, open Conditional Formatting, then New Rule. Choose "Use a formula to determine which cells to format", as Microsoft's page on conditional formatting explains.

In the template, the header is in row 1 and the Reorder column is column L. The rule therefore uses this formula:

=$L2="Yes"

The dollar sign locks the column, not the row: each row tests its own cell.

How do you run a stock count and handle discrepancies?

A stock count means physically counting each item, then comparing that count with the current stock in the file. The difference is a discrepancy. You don't correct the opening stock. Instead, you record the discrepancy as a movement: stock in if the file shows too few units, stock out if it shows too many.

How often you need a full count depends on your business. Your accountant can tell you what applies to yours.

The method with the template:

1. Temporarily add two columns next to the Items table: "Counted" and "Discrepancy".
2. Count the items and enter the quantities, ideally when no stock is moving.
3. Calculate the discrepancy: counted quantity minus current stock.
4. For each discrepancy, add a line to Movements, with the count date and the comment "Stock count".
5. Check that current stock now equals the count, then delete the temporary columns.

Stock count movements stay visible. A discrepancy that keeps coming back on the same item points to a problem: an outgoing movement nobody recorded, a breakage, a misunderstood pack size. The comment helps you track it down.

Good habits for keeping the file reliable

A stock file stays accurate if movements are recorded on the day, by one person or under a clear rule. Keep one unique SKU per item, never delete a movement line, and protect the calculated columns. A few habits are worth more than one extra formula.

• One SKU, one item. No duplicates, and no SKU reused for another product.
• Correct with a movement. A data entry error is fixed with an opposite movement and a comment, not by deleting the line.
• Record it on the day. A delivery note entered at the end of the week means five days of wrong stock.
• Protect the formulas. All cells are locked by default. Unlock the input columns, then protect the sheet (Review tab, Protect Sheet).
• Archive by year. When the Movements sheet gets long, start a new file with the counted stock as opening stock.

Where does an Excel stock spreadsheet reach its limits?

Excel works very well for one person managing a few dozen or a few hundred items from a desk. It reaches its limits when several people record movements, or when entries are made in the stockroom on a phone. Or when a stockout proves costly because nobody looked at the Reorder column.

These points come from Microsoft's pages on co-authoring, Show Changes and sheet protection. Microsoft also suggests giving each person their own area or sheet to avoid conflicts. It can be done, but the file then becomes a matter of organisation rather than formulas.

If you've reached that point, there are two routes: dedicated inventory software, or a tool where you keep your own template. Our round-up of free inventory software reviews the options, Rangee included.

How do you move the same template into a shared tool?

Import the workbook into Rangee. Smart import recognises a stock file from its columns (SKU, quantity, reorder point, cost) and suggests its stock and inventory template. It builds a base and an interface from it: a home page, a catalogue, a reorder list and a page for each item. Nothing is created until you approve it.

The home page shows the number of items, the stock value and the items below their reorder point. The catalogue lists every item. The "Reorder" page keeps only those that have reached their reorder point, and the item page brings together everything about one product. You keep or drop each suggested table, column and page. To read your file, the AI receives only its profile: column names, types and at most ten sample values per column, never the whole file.

Three additions address the limits described above:

• A movement form. The person in the stockroom picks the item, the type and the quantity from their phone. Interfaces display in a single column on mobile.
• An automation on the reorder point. It runs when an item matches the condition "current stock less than or equal to reorder point". It can then alert the buyer by email, in Slack or in Teams. You test it before switching it on.
• Permissions and a history. Revision history keeps every change along with its author. Readers and commenters are free: management can check stock value without a paid seat.

To be clear about volumes: every movement is a record. The Free plan allows 500 records per base, which suits a small catalogue over a few weeks or months. Beyond that, the Pro plan costs €5 per editor per month and goes up to 100,000 records per base (see pricing). As you approach the limit, you're warned at 80%, and no data is lost.

Download the template, fill it with your items, and keep using it for as long as it does the job. The day several people need to write to it, import it into Rangee: the Free plan doesn't expire, and your file becomes a shared tool in minutes.