How to track inventory in Google Sheets

A downloaded file is only useful until something in it breaks and you can't see why. This guide shows the build: what each sheet is for, the formula that turns a log into stock on hand, and the two settings that stop other people breaking it. Follow it and you'll have the same system as our free template, because the guide and the template use the same design: a Products sheet you fill in, a Movements log you add to, and a Stock sheet that is all formulas.

The one rule that makes the whole thing work: never edit a stock number by hand. Stock on hand is always calculated from the log. If a count is wrong, you don't overwrite it; you log a correction, and the history of every change stays on the record.

1. List what you sell

Open a new spreadsheet and name the first sheet Products. One row per product, with a header row:

• SKU is the product's ID and the key every formula matches on. Short, unique, no spaces: MUG-01, not "speckled mug (new glaze)". Once movements reference a SKU, don't rename it.
• Reorder at is the stock level where you want a warning. Step 4 turns it into a flag.
• Opening stock is what you physically have today, counted once when you set the sheet up. Everything after today goes through the log.
• Cost and price aren't needed for counting stock, but recording them now means the same sheet can value your stock and price your profit later. If you also want fees, shipping, category or supplier columns, add them here. Extra columns on Products never break the stock math.

2. Write down every change

Add a second sheet named Movements. This is the only sheet you'll touch day to day, and it only ever grows downward:

Three movement types cover everything: In is a restock or a new batch you made, Out is sold or sent, and Adjust is a correction, such as breakage, a recount or a gift. Adjust quantities can be negative (-1 for a broken mug) or positive (found two more in a box).

Now make typos impossible, because one misspelled SKU silently vanishes from the totals. Both fixes live in Data → Data validation:

1. Select the Type column (C2 down), add a rule, choose Dropdown, and give it exactly three items: In, Out, Adjust.
2. Select the SKU column (B2 down), add a rule, choose Dropdown (from a range), and point it at the SKU column on Products: =Products!$A$2:$A$301.

Set both rules to reject invalid input. From now on the log can only contain real SKUs and real types, which is what lets the next formula be trusted.

3. Let the sheet do the counting

Add a third sheet named Stock, with columns SKU, Product, On hand, Reorder at, Status. In plain words, the number you want per product is:

on hand = opening stock + everything In − everything Out + every Adjust

SUMIFS does each of those pieces: it adds up the Qty column, but only the rows where the SKU matches this row's product and the Type matches. Pull the SKU into A2 with =Products!A2, the product name into B2 the same way, then put this in C2.

In human terms, the formula below says: start with what you had on day one, add everything that came in, take off everything that went out, then apply any corrections, counting only the log rows that belong to this product.

=IF(A2="","",IFERROR(
  N(Products!$F2)
  + SUMIFS(Movements!$D$2:$D$3001, Movements!$B$2:$B$3001, A2, Movements!$C$2:$C$3001, "In")
  - SUMIFS(Movements!$D$2:$D$3001, Movements!$B$2:$B$3001, A2, Movements!$C$2:$C$3001, "Out")
  + SUMIFS(Movements!$D$2:$D$3001, Movements!$B$2:$B$3001, A2, Movements!$C$2:$C$3001, "Adjust"),
0))

Every argument, in order:

• Movements!$D$2:$D$3001: the sum range: the Qty column of the log, rows 2 to 3001. A bounded range recalculates faster than a whole-column reference and this one holds 3,000 movements; extend it when you need more.
• Movements!$B$2:$B$3001, A2: the first condition pair: only count rows whose SKU column matches A2, this row's product.
• Movements!$C$2:$C$3001, "In": the second condition pair: only rows of this type. The three SUMIFS are identical except for this word, and they're added, subtracted and added to match the plain-words formula (Adjust rows carry their own sign, so adding them handles both corrections).
• N(Products!$F2): opening stock, column F in the six-column Products sheet above. (Our template records fees, shipping, category and supplier too, so its ten-column layout puts opening stock in J. Same formula, different letter.) N() turns a blank cell into 0 instead of an error.
• IFERROR(…, 0): if anything inside fails, show 0 rather than an error code that breaks every total built on top.
• IF(A2="","", …): rows with no product stay blank instead of showing a zero for a product that doesn't exist.
• The $ signs lock the ranges so they don't drift when you copy the formula down; the unanchored A2 is the part that's meant to change per row.

Copy C2 down as many rows as Products has, and check it against the log above: the speckled mug opened at 24, has no In rows, went Out 3 + 6 + 11 = 20, and has no Adjust rows, so C2 shows 4. If your number doesn't match a hand count, the log is missing a movement, and the Note column tells you which ones you did record.

4. Get told before you run out

Pull each product's reorder point into column D with =Products!E2 (column I in the template's layout), then the Status column is one comparison.

In human terms: if you're down to your reorder level or below, say REORDER; otherwise say OK.

=IF(A2="","",IF(C2<=D2,"REORDER","OK"))

If on hand (C2) has dropped to or below the reorder point (D2), the row says REORDER; otherwise OK. To make it impossible to miss, add a highlight: select the Stock rows, open Format → Conditional formatting, choose Custom formula is, enter =$E2="REORDER" and pick a red fill. The $ locks the test to the Status column so the whole row lights up.

5. Stop anyone breaking it

The system now has exactly one fragile spot: someone helpfully typing a "corrected" number over a formula on the Stock sheet. Sheets can simply forbid that. Right-click the Stock tab, or open Data → Protect sheets and ranges, choose the Stock sheet, and set permissions so only you can edit it. Do the same for the header row and validation columns on Movements if others log stock for you. Helpers can then add movement rows all day, which is their job, but can't touch a formula.

6. When a spreadsheet stops being enough

This system tracks one pool of stock that people log by hand, and it does that well. It has two limits. If you hold stock in several locations and need to know where each unit is, or you sell on Shopify or Etsy fast enough that hand-logging orders falls behind, a spreadsheet starts costing you accuracy, and software that syncs orders automatically is worth its fee. Until you feel one of those two pains, you don't have them.

What a spreadsheet is quietly great at is being your whole back office in one place. The money side of this same setup: our free Google Sheets bookkeeping template tracks income and expenses the way Movements tracks stock, and the invoice template turns the same product list into something you can bill from.

If you want costing, stock, a ledger and invoices on one product list, that is the Seller Back-Office Kit, planned at $29. It can't be bought yet; the page has a list for one email when it can.

And if you'd rather start from the finished file than build along, the free inventory template is this guide's system with the sample data from these tables already in it. Download it, delete the samples, and you're at step 2.

Questions