Stock management in Excel: free template, formulas, and limits

Yes, a spreadsheet is enough to keep a small shop’s stock, and it costs nothing: our Excel template is here to download, free, no sign-up. Six tabs, four formulas, no macros. It works out the stock left from your goods in and goods out, flags the lines that have fallen below their threshold, and prices the difference the day you count the shelf. What it does not do, which is worth knowing before you settle into it: it does not read barcodes, it cannot be open to two people at once, and it does not move when you sell.

The six tabs, and what you type into each

A stock file that survives keeps three things apart: the product record, the log of what comes in and goes out, and the actual count. Mix them into one sheet and you will never be able to explain a difference again.

Tab	What it is for	What you type in
How to use	The file’s five rules, in plain words.	Nothing.
Products	One line per reference: cost price, sale price, opening stock, alert threshold.	Your items. The computed columns are shaded.
Stock in	The goods-in log: date, reference, quantity, cost, supplier, delivery note number.	One line per delivery received.
Stock out	The log of what leaves: sale, waste, breakage, return, gift, with the value at cost.	One line per movement, or one line per product per day.
Stock count	The count: stock in theory, counted, difference, value of the difference.	Only the “counted” column.
Dashboard	Eight numbers read from the other tabs.	Nothing. It fills itself in.

The file arrives with twelve example lines: a neighbourhood grocery over one month, with its deliveries, its sales, one waste line and one breakage. They are there so you can watch the formulas run before typing anything. The prices they carry are illustrative, not a wholesale list: delete the twelve lines, or type over them.

---

The four formulas, and why those four

Nothing else. No macros, no pivot tables, no add-in to install, and so nothing that breaks when the file is opened on another computer.

• SUMIF, twice: =SUMIF('Stock in'!B:B, A2, 'Stock in'!C:C) adds every quantity received for the line’s reference, and the same formula against the Stock out tab adds what left. This is the heart of the file: stock is not a number you keep up to date, it is the result of a log.
• The subtraction: =G2+H2-I2, opening stock plus goods in minus goods out. Three columns and one sum: if the answer is wrong, the log is incomplete, never the formula.
• IF, for the threshold: =IF(J2<=K2, "REORDER", "OK") compares the stock left with the threshold you set on that line. This is what turns a table you look at into a shopping list.
• VLOOKUP, on the count: =VLOOKUP(A2, Products!A:N, 10, 0) fetches the theoretical stock of the line being counted, and a second VLOOKUP its cost price, to put a value on the difference. A difference in units gets argued about; a difference in dirhams gets fixed.

One note that saves half an hour of confusion: Excel shows function names in the interface language. SUMIF in English and Arabic, SOMME.SI in French, SUMMEWENN in German. It is the same function and the file holds only one version, the internal one: open it in any language and the formulas display translated and work.

The threshold in column K is the one thing the file cannot guess: it is a decision that depends on your daily sales and your supplier’s lead time. The calculation is three numbers and we wrote it up separately, in avoiding stockouts without tying up cash. Put a rough value in to begin with and correct it after two months of real sales.

Getting it running in ten minutes

In this order. Step 2 is the one everybody skips, and it is the one that decides whether the file is still alive in six months.

1. Download the file and open it. Save a copy under your shop’s name straight away, and keep the blank original somewhere.
2. Decide your reference scheme before typing the first line: a prefix per family and three digits is plenty (EP-001, BOI-014). That code will be copied into the Stock in and Stock out tabs hundreds of times; it needs to be short and unambiguous.
3. Enter your products in the Products tab, starting with the thirty lines that make most of your turnover. The other eight hundred can wait, or never arrive.
4. Count the stock once, shelf by shelf, and put the result in the “opening stock” column. A file started on approximate stock will produce approximate differences forever.
5. Set an alert threshold on those same lines, by eye to begin with if you must.
6. Get into the habit of the two logs: the delivery note goes into Stock in the day it arrives, waste and breakage go into Stock out the day they happen. Not on Saturday for the whole week: nobody remembers on Saturday.
7. Read the dashboard once a week, on the same day, before you place orders.

A two-click addition that changes daily use: select the Status column, then Conditional Formatting, Highlight Cell Rules, Text that Contains, and type “REORDER”. The lines to order colour themselves in and you no longer have to read the rest.

What a spreadsheet will never do

It cannot be open to two people. The file lives on one computer, and two people editing it at once produce two versions, one of which will be lost. Sharing it on Google Sheets fixes exactly that point, at the price of another: you then need a connection to record a stock movement, which is precisely the trade-off we set out in offline POS or cloud POS.

It does not read barcodes and it does not update itself when you sell. That is the limit that really counts, because it does not show up in the first month: it waits for the busy day, the eve of Eid or the school return, when the double entry stops. The file falls a week behind, nobody trusts it any more, and a file nobody trusts is worse than no file: it provides false comfort. A barcode scanner plugged into POS software does that entry for you, the second the item crosses the counter.

Three signals say it is time to change tool, and one is enough: a second person needs to type, you pass two or three hundred lines, or you have stopped updating the file for more than a week. The move need not be expensive: BelloPOS Lite is free for life, and your two main columns, reference and description, import from this same spreadsheet. What you gain is described in our stock management guide.

The example month, read off the dashboard

The figures below are the ones the file shows when you open it, computed from the twelve lines and the month of movements shipped with it. They are also the demonstration of what kept stock tells you in ten seconds.

What the dashboard says	On the example month	What you do with it
Lines tracked	12	The size of the file. Past two hundred it gets heavy to keep.
Stock value, at cost	4 905 MAD	The money tied up in the shop, to compare month on month.
Lines below their threshold	2	Here Eggs and Sliced bread: the shopping list for the next wholesaler run.
Waste and breakage, in value	180 MAD	The number nobody works out and everybody pays.
Last count difference	-6 units, -55 MAD	Goods that left without being recorded. To be explained, not quietly corrected.

Mistakes to avoid

• Keeping a single “stock” column corrected by hand. Without a log of goods in and goods out, no difference can ever be explained.
• Typing sales in one by one when POS software already records them. In that case put only waste, breakage and returns into Stock out.
• Starting with all eight hundred lines. Thirty lines kept up beats eight hundred abandoned.
• Starting without counting. Opening stock is the one number in the file that nothing can work out for you.
• Keeping the file on one computer with no copy. A dying disk takes the stock record with it: one copy a week onto a stick or into online storage.

---

Frequently asked questions

Is this Excel stock management template really free?

Yes, with no sign-up and no account to create. It is an ordinary .xlsx file with no macros, and it opens in Excel, LibreOffice Calc, Numbers and Google Sheets. You can change it, rename it and share it as your own.

How do you calculate remaining stock in Excel?

Opening stock plus the sum of goods in minus the sum of goods out. In the template both sums are SUMIF formulas that search the logs for every line carrying the product’s reference, which saves you keeping a running total by hand.

Does it work in Google Sheets?

Yes. Import the file from Google Drive: SUMIF, IF and VLOOKUP exist identically and the dashboard keeps computing. You gain working as a team and you lose working offline, since typing anything in will need a connection.

How many lines can a spreadsheet really track?

Technically thousands. In practice the limit is not Excel but the data entry: past two or three hundred lines moving every day, the log stops being kept, and the whole file goes wrong.

Do I have to enter every sale in the Stock out tab?

No, and you should not force yourself to. One line per product per day is enough for the fast movers. If you already take payment on POS software, copy nothing: keep the Stock out tab for waste, breakage and returns.

Is this file enough for my accounts, or for the tax authority?

No, and that is not its job. It gives you the value of your stock, which your accountant will ask for at year end, but it issues no invoice and does not stand in for a compliant document. The obligations attach to the invoice and the receipt, not to your spreadsheet: see is a DGI-compliant till mandatory.

What to take away

A spreadsheet is the right first stock management tool, on one condition: that it rests on a log of goods in and goods out, and not on a column corrected from memory. The template above does exactly that with four formulas, and it will tell you itself when you have outgrown it, on the day the double entry stops. On that day the question is no longer Excel or not Excel, but who does the typing: you, or the till.

Read next

Other practical guides on the same subject:

• Stock management step by step: a guide for small shops
• Doing a stock take without closing the shop
• Avoiding stockouts without tying up cash
• The ten numbers to check every week