Inventory spreadsheet for a small shop, and when to move on

A spreadsheet can run the stock for a small shop for years. It needs one row for each product at each location, a fixed set of columns, and a regular count that corrects it. Most stock problems in a shared file come from a missing piece of that setup, not from Excel or Google Sheets.

Below are a column layout with example rows, the build steps, the habits that keep a shared file right, and honest signs that a shop has outgrown it.

Inventory spreadsheet columns

Keep one row per product per location. A candle on the shop floor and in the backroom gets two rows, and a formula adds them up, so a count or a move changes one or two rows instead of the whole sheet.

With On hand in column E and Reorder at in column F, the formula =IF(E2<=F2,"Low","") marks rows that need attention. The formula =SUMIF(A:A,A2,E:E) totals a SKU across its locations. On hand times Unit cost gives each row's stock value.

On a shop floor row, Reorder at tells staff when to refill from the backroom. On the backroom row, it tells you when to order from the supplier.

An inventory spreadsheet template with example rows

These made-up rows for a gift shop with a second store show the first six columns and Status.

A plain inventory list template, with names and one quantity each, works as a count sheet. An inventory tracking spreadsheet needs the location, reorder and date columns too, to show where stock is and when it was last checked.

How to make an inventory spreadsheet

1. Type the headers in row 1 and freeze that row.
2. Add drop-down lists for Location and Category with data validation, so "Backroom" and "back room" never split a total. Microsoft's guide to data validation covers Excel, and Google Sheets has the same feature under Data.
3. Enter one row per product per location from a full count, not from memory or old invoices.
4. Add the formulas, then a conditional formatting rule that shades every Low row.
5. Protect the headers and formula columns, so staff edit only On hand and Last counted.
6. Save a dated copy before each full count.

An Excel inventory template from the web saves typing, but some give each item a single quantity, which stops working at a second location. A Google Sheets inventory suits two people editing from different places, since everyone works in one live file and version history shows who changed what. Excel does the same when the file is stored in OneDrive or SharePoint.

Keeping the counts accurate

• Record changes the same day. Deliveries, moves, damage and, if your till does not feed the sheet, sales. A Changes tab with date, SKU, location, quantity, reason and initials shows why a number moved.
• Count on a schedule. Count a few categories each week so every row is checked each quarter, and do a full count at least once a year. IRS Publication 538 says a business keeping a running book inventory must take a physical inventory at reasonable intervals and adjust the book amount to agree with it.
• Count, then correct. Overwrite On hand, update Last counted, and log the difference so repeated losses show up.
• Give the layout one owner. Staff enter numbers; one person adds products, columns and formulas.
• Keep one file. An emailed copy splits the truth in two.

Spreadsheet vs inventory software

A spreadsheet costs little, fits any layout, and most staff already know it. Its limit is that it stores whatever someone types. It will not stop a move that takes units out of one location without adding them to another, and it keeps only today's number unless you build the checks and the log yourself. Inventory software records each delivery, move and count as an entry, checks it, and keeps the history. For one location and one person keeping the sheet, the spreadsheet is often the better tool.

When to switch from a spreadsheet to inventory software

Look for patterns that repeat, not one bad week:

• Two people change the same rows, and numbers get overwritten or entered twice.
• Stock moves between stores every week, and totals stop matching after a count.
• You cannot say where units went, because the sheet holds only the current number.
• The Changes tab gets skipped on busy days, so counts drift between full counts.
• Low rows go unnoticed because nobody filters the sheet before ordering.

If none of these sound familiar, keep the spreadsheet and spend the time on counting.

Moving the same layout into headoffis

headoffis uses the same shape: one product catalog, with stock counted per location. Every store has Backroom and Unassigned locations, and you can add named ones for shelves or bins.

Save your product rows as a CSV and choose Import CSV on the Products page. The import template asks for name, SKU, category and package width, height and depth with a unit, since headoffis plans shelves too, and every row is checked before anything is saved. Quantities are not imported, so enter opening counts with Count / alerts, which sets the quantity and an optional alert threshold at each location.

Receive stock and Transfer stock then record deliveries and moves, Stock activity keeps the history, and Export stock CSV gives you a file for your spreadsheet. A save made from an out-of-date copy is refused, so one person cannot silently overwrite another's numbers. Counts are whole units, and there is no POS connection, purchase orders or stock valuation, so keep Unit cost in your sheet if you value stock.

← All posts