Inventory management in Google Sheets

You can run reliable inventory in Google Sheets if you never type over a stock quantity. Record every receipt, issue, adjustment and transfer as a new row in a movements ledger, and let formulas calculate what is on hand. That one design choice gives you per-location stock, low-stock flags and a history you can read, all with built-in functions.

This guide walks through the sheet design, the formulas, data validation, protection, history and the Google Sheets limits that matter. It ends with an honest look at where Sheets alone stops being enough.

Design the spreadsheet

Use one spreadsheet with three tabs. Each tab has one job.

Items

Locations

Movements: the ledger

A transfer is two rows: -5 at the source location and +5 at the destination, both with type Transfer and the same reference. A stock count that finds 3 fewer than expected is one Adjust row with -3.

Why an append-only ledger beats a quantity column

The most common inventory sheet has a single "Quantity" column that people overwrite. It works until something goes wrong, and then it can't tell you what happened.

• An overwritten number has no story. If 40 becomes 25, the sheet doesn't say whether 15 were sold, lost or miscounted. A ledger row says -15, Issue, JOB-311, with a date and a name.
• Two people can't safely edit one number. If two people each take 5 boxes and both type over 40 with 35, you lose a movement. Two appended rows of -5 add up correctly.
• Mistakes are fixed by adding, not erasing. A wrong row is corrected with an opposite row, so the mistake and the fix both stay visible.
• Stock on any date is a formula away. Sum the movements up to that date.
• Per-location stock comes for free. The same rows give totals per item, per location or both.

The rule for everyone who uses the sheet is simple: add rows to Movements, never edit or delete old ones.

Formulas for stock on hand

All formulas below assume the tab and column layout above, with headers in row 1.

On hand per item

In Items!E2, then fill down:

=SUMIFS(Movements!$D:$D, Movements!$B:$B, $A2)

SUMIFS adds every Qty in the ledger whose SKU matches the item in that row.

To fill the whole column with a single formula instead, put this in Items!E2 and leave the cells below it empty:

=ARRAYFORMULA(IF(A2:A="", "", SUMIF(Movements!B:B, A2:A, Movements!D:D)))

On hand per item and location

For one item at one location, for example the location typed in cell H1 of Items:

=SUMIFS(Movements!$D:$D, Movements!$B:$B, $A2, Movements!$C:$C, $H$1)

For a full stock table on its own tab, use QUERY. This lists every item and location pair that has movements:

=QUERY(Movements!A1:H, "select B, C, sum(D) where B is not null group by B, C label sum(D) 'On hand'", 1)

This turns locations into columns, one row per item:

=QUERY(Movements!A1:H, "select B, sum(D) where B is not null group by B pivot C", 1)

Stock on a past date

To see what was on hand at the end of a given day, for example the date in Items!H2:

=SUMIFS(Movements!$D:$D, Movements!$B:$B, $A2, Movements!$A:$A, "<="&$H$2)

Catch negative stock

A location can't hold fewer than zero. This lists every item and location pair below zero, which usually means a missing receipt or a typo:

=QUERY(QUERY(Movements!A1:H, "select B, C, sum(D) where B is not null group by B, C", 1), "select * where Col3 < 0", 1)

The inner QUERY builds the per-location totals; the outer one keeps only the negative rows. An empty result means no location is below zero.

Low-stock flags and reorder points

A status column

In Items!F2, then fill down:

=IF(D2="", "", IF(E2<=D2, "Reorder", "OK"))

The status is empty when no reorder point is set, so new items don't show a false alarm.

Highlight the rows that need reordering

1. Select A2:F on the Items tab.
2. Click Format → Conditional formatting.
3. Under Format cells if, choose Custom formula is.
4. Enter =AND($D2<>"", $E2<=$D2).
5. Choose a fill color and click Done.

The dollar signs keep the columns fixed while the row changes, so the whole row turns color. Google notes that conditional formatting formulas can only reference the same sheet unless you use INDIRECT. That is why on-hand stock and the reorder point live on the Items tab next to each other.

You can use the same feature to flag ledger rows whose sign doesn't match their type. Select A2:H on Movements and use:

=OR(AND($E2="Receive", $D2<0), AND($E2="Issue", $D2>0))

Reorder point basics

A reorder point answers one question: how low can stock go before you must order, so the new delivery arrives before you run out? The basic formula is:

Reorder point = average daily usage × lead time in days + safety stock

• Average daily usage comes from your ledger.
• Lead time is the number of days between ordering and having the stock on the shelf.
• Safety stock is the buffer for slow deliveries or busy weeks.

Add three helper columns to Items: G "Avg daily use (30 days)", H "Lead time (days)" and I "Safety stock". In G2:

=-SUMIFS(Movements!$D:$D, Movements!$B:$B, $A2, Movements!$E:$E, "Issue", Movements!$A:$A, ">="&(TODAY()-30))/30

The minus sign turns the negative Issue quantities into a positive usage rate. Then set the reorder point in D2:

=ROUNDUP(G2*H2 + I2, 0)

For example, an item used at 4 boxes a day, with a 5-day lead time and 10 boxes of safety stock, has a reorder point of 4 × 5 + 10 = 30. Review lead times and safety stock when suppliers or seasons change; the formula is only as good as those two inputs.

Data validation and dropdowns

Dropdowns stop the typos that break SUMIFS and QUERY: a SKU with a trailing space or a location written two ways never matches.

Add a dropdown from a range

1. On Movements, select B2:B.
2. Click Data → Data validation → Add rule.
3. Under Criteria, choose Dropdown from a range and enter Items!A2:A.
4. Click Done.

Repeat for C2:C with Locations!A2:A. For E2:E, choose Dropdown and add the items Receive, Issue, Adjust and Transfer. Google also offers Insert → Dropdown and a right-click Dropdown option; all of them open the same data validation rules panel.

Reject or warn

By default, Google Sheets rejects an entry that doesn't match the list. If you want to allow it with a warning instead, open Advanced options and, under If the data is invalid:, select Show a warning. For an inventory ledger, keep rejection on: a movement against an unknown SKU is almost always a mistake.

Also add a validation rule on D2:D that only accepts numbers, and on A2:A that only accepts dates. A quantity typed as text is ignored by SUMIFS, so stock silently stays wrong.

Protect ranges and share carefully

Google Sheets permissions have two layers, and both matter.

1. Sharing decides who can open the file and whether they are a Viewer, Commenter or Editor. You set it with Share, and it applies to the whole file.
2. Protected sheets and ranges narrow what editors can change inside the file.

Protect what nobody should type into

1. Click Data → Protect sheets and ranges.
2. Click Add a sheet or range.
3. Choose Range and select Items!E2:F (the formula columns).
4. Click Set permissions.
5. Select Restrict who can edit this range, choose Only you, and click Done.

Protect the whole Locations tab the same way if the list rarely changes. On Movements, people must be able to add rows, so a strict protection doesn't fit. Use Show a warning when editing this range on the existing rows instead; it asks people to confirm before they change history.

Know what protection doesn't do. Google says protection "shouldn't be used as a security measure" and that "people can print, copy, paste, and import and export copies of a protected spreadsheet." The owner can always edit a protected range, and you can't protect data with a password. The step-by-step details are in Protect a sheet or range.

Share with the narrowest role

• Give Viewer to people who only need to look up stock.
• Give Editor only to people who record movements.
• Google's sharing settings let you prevent editors from sharing the file, as described in Share files from Google Drive.

Version history and cell edit history

Google Sheets keeps two kinds of history: versions of the whole file and the edit history of a single cell. You can't see version history without permission to edit the file.

Version history

To see earlier versions, click Last edit at the top right of the spreadsheet. Pick a version in the right panel to see who changed what. By default, Google Sheets hides unmodified rows; click Show unmodified rows to see everything.

You can restore a version or make a copy of it. Restoring a spreadsheet version clears the cell-by-cell edit history for changes made before the restore, so for a busy inventory file a copy is usually the safer choice. See Restore an earlier version.

You can name up to 15 versions per spreadsheet, and named versions aren't merged. Name one after every stock count, for example "Count 2026-10-31", so you can always return to it.

Cell edit history

Right-click a cell and choose Show edit history to see who changed that cell. Google lists changes that might not show up there:

• added or deleted rows and columns;
• changes to the cell's format;
• changes made before a past version was restored;
• changes made by formulas.

That last point matters for a formula-driven sheet: the On hand cells change through formulas, so their edit history won't explain a new number. The ledger rows will. More detail is in Find who changed a cell.

Edit notifications

Click Tools → Notification settings → Edit notifications to get an email when others change the spreadsheet, either right away or as a daily digest. Google notes that you can only set up notifications for yourself.

Google Sheets limits to plan for

These are the limits Google documents, as checked on 2026-10-28. The full list, with what to do near each one, is in Google Sheets limits for inventory.

A ledger grows by one row per movement. At 8 columns, 1,000 movements a month adds 8,000 cells a month, which is about 96,000 a year. Most small teams never reach the cell limit, but large files slow down well before it. Archive closed years into a separate file and carry the closing balance forward as one Adjust row per item and location.

When Sheets alone stops being enough

The design above works well for a few careful people. As the team grows, these gaps start to cost time or money.

• Concurrent edits. Google Sheets shows edits live, but nothing stops two people from changing the same row, or someone from sorting the ledger while another person types. Nothing checks a change before it lands.
• No approvals. Any editor can record a 500-unit adjustment or change a reorder point. There is no step where a second person reviews it first.
• History that is hard to audit. Version history is grouped and revisions may occasionally be merged. Cell edit history skips formula results, inserted and deleted rows and format changes, and a restore clears it. The owner of a file in a personal Google Account can permanently delete its unnamed versions. Nothing proves that the history you see is complete.
• Accidental overwrites. One paste over the wrong range or a "fill down" too far changes hundreds of ledger rows at once. Version history can recover it, if someone notices.
• Permissions are file-level. An editor can edit every tab and every location unless you build protections by hand, and protection isn't a security measure by Google's own description. You can't say "this person records movements for the north warehouse only."
• Rules live in people's heads. "Never edit old rows" and "always add a reference" are conventions. The sheet doesn't enforce them.

If these sound familiar, you have three options: accept the risk, move to a separate inventory system, or keep the Sheet and add control on top of it.

How LightInventory adds control to the same Sheet

LightInventory is an inventory app built on top of a Google Sheet you own. It is in development and not yet generally available. It uses the same ledger design as this guide, and the app enforces the rules above instead of leaving them to convention.

• Your data stays in your Sheet. Items, locations, movements and the change log are tabs in your own spreadsheet. If you stop using LightInventory, you keep the file.
• Every change is recorded. Each change is written to a Log tab with who made it, when, the value before and the value after. Mistakes are fixed with a compensating entry, never by erasing history.
• Tamper-evident history. A keyed hash chain of the log, without your values, is stored outside your file and anchored externally, so a quietly edited or deleted log row fails verification. See the audit trail.
• Approvals. You choose which changes need approval, for example price changes. The reviewer can't be the person who requested the change.
• Roles. Owner, Admin, Approver, Editor and Viewer, with optional per-location scope for Editors and Viewers. Team members work in the app and aren't added to the file unless the owner grants them direct edit.
• Direct-edit detection. If someone edits the Sheet directly, LightInventory detects the change and handles it by your policy: block it until an owner adopts or restores it, log it automatically, or send it for approval.

There is a Free plan for 1 user and 1 spreadsheet. Team costs $2.99 per user per month billed monthly, or $2.49 billed annually, excluding tax. Details are on pricing, and how your data and access are handled is on security.