Thinkvale

How to make an inventory spreadsheet in Excel or Google Sheets

Build a simple inventory spreadsheet for a small business in Excel or Google Sheets: the columns you need, the formulas to copy, and automatic reorder alerts.

By Thinkvale Editorial · Published

Illustration of a spreadsheet next to warehouse shelves and a checklist

A good inventory spreadsheet does three things: it tells you what you have, it tells you when to reorder, and it lets you find mistakes. Most templates you can download only do the first.

This guide shows how to build your own in about thirty minutes. It works the same way in Excel and Google Sheets, and you will understand every cell, which is what makes it easy to fix later.

The idea: three sheets, not one

The most common mistake is a single list where you type over the stock number after every sale. When the number is wrong, you have no way to know why.

Use three sheets (tabs) instead:

Sheet What it holds How often it changes
Products One row per product or variant When you add a product
Movements One row for every time stock goes in or out Every day
Stock The current stock, calculated automatically Never by hand

Nothing is ever overwritten. Stock is always the sum of the movements, so every number can be traced back.

Sheet 1: Products

Create a sheet called Products with these columns:

Column Header Example
A SKU MUG-BLU-L
B Product name Blue mug, large
C Supplier Northside Ceramics
D Unit cost 4.20
E Reorder point 44
F Storage location Shelf B2

Each variant (size, colour) gets its own row and its own SKU. If you do not have SKUs yet, start with our guide on how to create SKU numbers.

Sheet 2: Movements

Create a sheet called Movements. Every time stock changes, add a new row at the bottom:

Column Header Example
A Date 2026-11-13
B SKU MUG-BLU-L
C Type Sale
D Quantity -2
E Note Order 1042

Use positive numbers for stock coming in (deliveries, customer returns) and negative numbers for stock going out (sales, damaged items). For the Type column, keep a short fixed list: Opening stock, Delivery, Sale, Return, Adjustment.

Your very first rows are the opening stock: one row per SKU with the quantity you counted today.

Sheet 3: Stock

Create a sheet called Stock. Put the SKU in column A (copy the list from the Products sheet), then add these formulas in row 2 and copy them down.

Product name (column B)

=XLOOKUP(A2, Products!A:A, Products!B:B)

Current stock (column C)

=SUMIF(Movements!B:B, A2, Movements!D:D)

This adds up every movement for the SKU. It is the heart of the spreadsheet.

Reorder point (column D)

=XLOOKUP(A2, Products!A:A, Products!E:E)

Status (column E)

=IF(C2<=0, "Out of stock", IF(C2<=D2, "Reorder", "OK"))

Stock value (column F)

=C2 * XLOOKUP(A2, Products!A:A, Products!D:D)

If your version of Excel does not have XLOOKUP, use =VLOOKUP(A2, Products!A:F, 2, FALSE) and change the 2 to the number of the column you want.

Add automatic reorder alerts

The Status column already says “Reorder”. Make it impossible to miss:

  1. Select the Status column in the Stock sheet.
  2. Open conditional formatting (in the Format menu in Google Sheets, on the Home tab in Excel).
  3. Add a rule: text is exactly “Reorder”, fill the cell orange.
  4. Add a second rule: text is exactly “Out of stock”, fill the cell red.

To set good reorder points, use the formulas in our guide on reorder point and safety stock.

Prevent typing mistakes

A SKU typed slightly wrong in the Movements sheet is a movement that never reaches your stock. Two small protections avoid most errors:

  • Drop-down list for SKUs. Select column B of the Movements sheet, open data validation, and allow only values from the SKU column of the Products sheet.
  • Drop-down list for Type. Do the same for column C with your fixed list of movement types.
  • Protect the Stock sheet so nobody types over a formula by accident.

How to use it day to day

  • A sale: one new row in Movements with a negative quantity. If your store exports orders as a file, paste the rows in once a day instead of typing them.
  • A delivery from a supplier: one new row with a positive quantity.
  • A damaged or lost item: one row of type Adjustment with a negative quantity and a note.
  • Once a month: count a few products and compare with the Stock sheet. Our stock count checklist explains how to do it quickly.

Useful extras

  • Total stock value: =SUM(Stock!F:F) gives you the value of everything on your shelves, which your bookkeeping needs at year end.
  • Units sold in a period: =-SUMIFS(Movements!D:D, Movements!B:B, A2, Movements!C:C, "Sale") gives total units sold for a SKU.
  • A filter on Status shows your whole reorder list in two clicks.

The limits of a spreadsheet

This setup works well for one person, one stock location and up to a few hundred products. It starts to struggle when:

  • several people edit it at the same time;
  • you sell on several channels and need stock to update automatically;
  • you spend more than a couple of hours a week keeping it up to date.

When that happens, read inventory spreadsheet or inventory software for the warning signs and how to switch, and the best free inventory software for tools that cost nothing to try.

Key takeaways

  • Use three sheets: Products, Movements and Stock. Never overwrite a stock number.
  • Current stock is one formula: a SUMIF over the movements.
  • A Status column with conditional formatting gives you free reorder alerts.
  • Drop-down lists prevent most typing mistakes.
  • A spreadsheet is enough for many small shops. Switch when keeping it up to date costs more than the software would.

← More inventory guides