# Inventory management in Google Sheets

> How to run inventory in Google Sheets with a movements ledger, working formulas, low-stock flags, protection and history, plus the limits to plan for.

Source: https://www.lightinventory.com/guides/inventory-management-google-sheets · Last updated: 2026-10-28

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.

| Tab | One row is | You edit it when |
|---|---|---|
| `Items` | One product or part | You add an item or change its details |
| `Locations` | One warehouse, room, shelf or van | You add or rename a place |
| `Movements` | One change in stock | Anything goes in, out or moves |

### Items

| Column | Header | Example | Notes |
|---|---|---|---|
| A | SKU | `BOLT-M8-40` | Unique, never reused. Format the column as plain text |
| B | Name | M8 × 40 mm hex bolt | |
| C | Unit | box | The unit every movement uses |
| D | Reorder point | 20 | When on-hand stock reaches this, reorder |
| E | On hand | *(formula)* | Never typed by hand |
| F | Status | *(formula)* | `Reorder` or `OK` |

### Locations

| Column | Header | Example |
|---|---|---|
| A | Location | `WH1` |
| B | Name | Main warehouse |

### Movements: the ledger

| Column | Header | Example | Notes |
|---|---|---|---|
| A | Date | 2026-10-28 | A real date value, not text |
| B | SKU | `BOLT-M8-40` | Dropdown from `Items` |
| C | Location | `WH1` | Dropdown from `Locations` |
| D | Qty | `-5` | Positive adds stock, negative removes it |
| E | Type | `Issue` | `Receive`, `Issue`, `Adjust` or `Transfer` |
| F | Reference | PO-1042 | Purchase order, job or count sheet |
| G | Entered by | Sam | Who recorded it |
| H | Note | Damaged in transit | Why, when it isn't obvious |

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](https://support.google.com/docs/answer/3238496) 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](https://support.google.com/docs/answer/3093343). 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)
```

> **Note:** QUERY expects one data type per column. Google's documentation says that with mixed types in a column, "the majority data type determines the data type of the column" and the other values are treated as empty. Keep SKUs as plain text and dates as real dates.

### 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](https://support.google.com/docs/answer/78413) 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](https://support.google.com/docs/answer/186103).

### 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"](https://support.google.com/docs/answer/1218656) 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](/help/google-sheets/protect-ranges).

### 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](https://support.google.com/drive/answer/2494822).

## 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](https://support.google.com/docs/answer/190843) 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](/help/google-sheets/restore-a-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](/help/google-sheets/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](https://support.google.com/docs/answer/91588).

## 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](/help/google-sheets/limits).

| Limit | What Google documents |
|---|---|
| Cells per spreadsheet | Up to 20 million cells or 100MB for spreadsheets created in or converted to Google Sheets ([Drive file limits](https://support.google.com/drive/answer/37603)). Google raised it from 10 million in September 2026 ([Workspace Updates](https://workspaceupdates.googleblog.com/2026/09/doubled-cell-limits-in-google-sheets-now-generally-available.html)) |
| People editing at once | A Google file can only be actively edited on up to 100 open tabs or devices at the same time ([sharing limits](https://support.google.com/drive/answer/2494822)) |
| Sharing | Up to 600 individual email addresses per file ([sharing limits](https://support.google.com/drive/answer/2494822)) |
| Named versions | Up to 15 per spreadsheet ([version history](https://support.google.com/docs/answer/190843)) |
| IMPORTRANGE | Capped at 10MB of received data per request; checks for updates every hour while the document is open ([IMPORTRANGE](https://support.google.com/docs/answer/3093340)) |

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](https://support.google.com/docs/answer/190843). 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](/features/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](/pricing), and how your data and access are handled is on [security](/security).