Track inventory in Excel: a stock sheet for a small shop

A small shop does not always need inventory software. If you sell gifts, plants, books, candles or handmade goods and your catalogue is a few hundred items, a well-made spreadsheet can tell you what you have, what is running low and what your stock is worth. This guide shows how the sums work, so you can build the sheet yourself or judge one you buy.

When a spreadsheet is enough

A stock sheet fits when one person, or two people taking turns, record the movements; when most sales happen in one place; and when your range is counted in hundreds of products, not tens of thousands. It stops fitting when you need live stock across a shop and a web store, barcode scanning, or several staff updating at the same moment. A workbook is a single file, and two people editing two copies is how stock records go wrong.

What to record

Three tables are enough.

1. Products: one row per item, with a short unique code (a SKU), name, category, supplier, what it costs you, what you sell it for, the stock you had when you started, the level at which you reorder, and the usual quantity you order.
2. Stock in: one row per delivery, with date, product code and quantity.
3. Stock out: one row per sale, with date, product code, quantity and, if you want it, the sales channel and the price you actually charged.

Use the code, not the name, to link the tables. Names get typed in different ways, so "Blue mug" and "mug, blue" count as two products. A drop-down list of codes avoids that.

How stock on hand is worked out

The rule is plain:

stock on hand = opening stock + total received - total sold

In Excel, SUMIFS adds up the quantities that match a product code. Microsoft's documentation describes SUMIFS as adding the cells that meet more than one criterion. A formula of this shape does the job for one product, where the codes are in column A of the Products sheet:

`= opening stock + SUMIFS(Stock In quantities, Stock In codes, A2) - SUMIFS(Stock Out quantities, Stock Out codes, A2)`

Here is a worked example with made-up numbers. A shop sells scented candles at EUR 9.50. They cost EUR 4.00 each.

At cost the stock is worth 29 x 4.00 = EUR 116.00. At the selling price it is worth 29 x 9.50 = EUR 275.50. Each candle earns EUR 5.50 over its cost, a margin of 57.9 per cent of the price. If the shop sells five more candles at a discounted EUR 8.50, the revenue is 5 x 8.50 = EUR 42.50, not 5 x 9.50 = EUR 47.50. That is why it helps to record the real price on a sale and not rely on the list price.

Setting a reorder level

A reorder level is the stock count at which you place the next order. A rule of thumb is: weekly sales x weeks the supplier needs to deliver + a small buffer. If candles sell about 6 a week, the supplier needs 2 weeks, and you want a buffer of one week's sales, the level is 6 x 2 + 6 = 18. When stock falls to 18 or below, order.

This is a starting point, not a law. Sales rise before holidays, and some suppliers set a minimum order. Review the levels every few months, and set them higher for the products that you really cannot afford to run out of.

A colour is the easiest alert. In the sheet described below, a row turns amber when stock reaches the reorder level, red at zero, and purple when more has been sold than received. A purple row is not a stock level. It is a warning that a record is missing, for example a delivery you forgot to log.

What a stock value tells you, and what it does not

Stock value at cost is quantity times what you paid. It tells you how much money is tied up on your shelves, and it is the number to watch if you want to avoid over-ordering. Stock value at the selling price is what the goods would bring if all were sold at full price, which they usually are not. Neither figure is cash, and neither is an accounting valuation. For your tax return, the rules of your own country decide how stock is counted, so ask a qualified adviser.

A monthly sales summary, with units sold, revenue, cost of goods and gross profit by category, tells you which lines carry the shop. It is only complete if every sale is logged.

Keeping the sheet honest

A sheet shows what your records say, not what is on the shelf. Sales that you did not log, breakage, theft, returns and miscounts all make it drift. Three habits keep the drift small:

• Count a few products every month. Pick the best sellers and any item the sheet shows as low. Compare your count with the sheet and correct it by adding the difference as a movement, with a note.
• Log on the day. A delivery written down a week later is easy to forget.
• Treat a negative stock as a question. If the sheet says -3, find the missing delivery.

Research on spreadsheets supports this caution. Powell, Baker and Lawson (2008) reviewed studies of spreadsheet errors and found that errors in real spreadsheets are common. Their review is about spreadsheets in general, not about stock sheets, but it is a good reason to test your formulas on a small example, like the candle above, before you trust them with the whole shop.

Spreadsheet or software?

Software costs money every month, and it takes time to set up. In exchange it gives you live counts when several people sell, connection to a web shop, barcode scanning and an audit trail. A spreadsheet gives you none of that, and it costs nothing after you have it. A sensible path is to start with a sheet, and to move when you notice one of these signs: two people need to update at once, you sell in two places, or you spend more time fixing the sheet than running the shop.

Capacity matters too. A workbook has limits on how many rows it can hold in a practical way, and a busy shop can fill a sales log. A simple fix is to keep one copy of the workbook per year and carry the closing stock over as the next year's opening stock.

Tool that fits

The Inventory & Stock Tracker for Small Shops (Excel, EUR 19, paid once) holds 200 products with SKU, category, supplier, cost, price, margin, reorder level and reorder quantity. Stock in has room for 500 rows and stock out for 1,500 rows, with product codes in drop-downs. Stock on hand uses SUMIFS, and rows turn amber, red or purple as described above. It shows stock value at cost and at selling price, a 12-month sales summary by month and category, and a dashboard with a reorder list that suggests a quantity and the top 10 sellers. Example data for a small gift shop is included, and it can be cleared in a few steps. It works in Excel 2016 and later, Google Sheets and LibreOffice, with no macros. It is a planning tool for your own records. It does not replace a physical stock count, and it is not accounting or tax advice.

Questions people ask

Sources

1. Microsoft Support, SUMIFS function (adds the cells that meet several criteria)
2. Powell, Baker and Lawson (2008), A critical review of the literature on spreadsheet errors, Decision Support Systems 46(1), 128-138

We checked each source on 17 October 2026. If a fact here is out of date, tell us at hello@hiddenway.info.

Keep reading

• Monthly profit and loss in Excel for a small business
• Invoice and quote workflow for freelancers in Excel
• More in Small business tools