Google Sheets Inventory Spreadsheet: Setup and Tips

Yes. Google Sheets can serve as an inventory spreadsheet for a small shop, office, classroom, or household: list each item, record stock changes, and use formulas to calculate what remains and flag items to reorder. A transaction log is usually more reliable than repeatedly overwriting a single quantity, and Sheets makes the file easy to share and update in a web browser.

What a Google Sheets inventory spreadsheet can do

An inventory spreadsheet is a structured record of the items you own or manage. At its simplest, it shows an item name and the quantity currently available. A more useful version also tracks identifying codes, storage locations, suppliers, costs, reorder thresholds, and the dates of stock movements or counts.

Google Sheets is a reasonable fit when inventory is limited, changes are manageable, and a few people can follow the same process. For example, a volunteer group could track boxes of event supplies by location, while a small retailer could monitor stock and flag products below a minimum quantity. Sheets does not automatically know that a sale, delivery, return, or damaged item occurred; someone must enter that change or connect the spreadsheet to another system.

That distinction matters: a spreadsheet calculates from the information entered, but it does not guarantee that the information is complete or correct. It is a recordkeeping tool, not a barcode-based stock-control system by itself.

Choose a structure before entering items

First decide whether you need a simple current-count list or a history of every stock movement. A count-only sheet is quicker to start, but users must replace the quantity whenever stock changes. A transaction-based design takes more discipline, but it preserves the explanation for how the current balance was reached.

Count-only list: Keep one row per item and edit the on-hand quantity after each count. This can work for a pantry or a small set of office supplies.
Inventory plus transaction log: Keep one row per item in an Inventory tab and append each receipt, sale, return, or adjustment to a Transactions tab. Calculate current stock from the log.
Multiple locations: Record a location in each stock movement, or maintain separate quantities by location. A single total can hide the fact that an item is unavailable at the location where it is needed.

Use a stable item identifier, such as an SKU or an internally assigned code. Names can change or be spelled inconsistently; a consistent code makes it easier to match transactions to the right product. If you have multiple sizes or colors, treat each sellable variation as its own item where its stock must be counted separately.

Set up the tabs and columns

Create a Google spreadsheet and rename the first tab Inventory. Use one row per distinct item, a single header row, and no merged cells in the data area. A practical starting set of columns is:

SKU: A unique code for the item.
Item: A recognizable name, including size or variation when needed.
Category: A grouping such as packaging, food, or equipment.
Location: The shelf, room, or storage area.
Unit cost: Optional; record what one unit costs the organization, not a changing total.
On hand: The current calculated or counted quantity.
Reorder point: The level at which someone should review or replenish stock.
Supplier and last counted: Optional fields that help with purchasing and physical checks.

For a transaction-based workbook, add a tab named Transactions with columns for Date, SKU, Quantity change, Type, and Reference or note. Use positive quantities for stock coming in and negative quantities for stock going out. For example, receiving 24 units is +24, selling 6 is -6, and removing 2 damaged units is -2. Write the reason in Type or Note so an adjustment is not mistaken for a sale.

Calculate stock from a transaction log

With the example layout above, put each SKU in Inventory column A and list transaction SKUs in Transactions column B, with signed quantity changes in column C. If a product has a receipt of 24 and a sale of 6, its calculated balance is 18. In Inventory cell F2, enter this formula, then fill it down for the other product rows:

With the layout just described—SKU in Inventory!A2 and transaction SKUs and quantity changes in Transactions!B2:B1000 and Transactions!C2:C1000—use =SUMIF(Transactions!$B$2:$B$1000,A2,Transactions!$C$2:$C$1000). For transactions of +24 and -6 for that SKU, the result is 18.

The fixed dollar signs keep the transaction ranges from shifting when you copy the formula down; A2 changes to A3, A4, and so on. Increase the ending row if your log will exceed row 1000. A spreadsheet’s formulas only include the ranges you specify, so check that new entries fall inside them. Google Sheets can also use open-ended ranges, but a defined range can make the intended data area easier to review.

Keep the transaction log append-only as a working habit: add a correction as a new adjustment rather than silently changing a past movement. This preserves a useful explanation of the balance, though it is not the same as a controlled accounting audit trail. A spreadsheet owner or editor can still change data or formulas.

Add a clear reorder alert

A reorder point is a prompt to review stock, not an automatic purchase instruction. The appropriate threshold depends on how quickly an item is used, supplier lead time, delivery frequency, and how much extra stock is appropriate. A frequently used item with a long delivery lead time may need a higher threshold than an item that is easy to replace locally.

Assuming the calculated on-hand quantity is in F2 and the reorder point is in G2, enter this in H2 and copy it down. If the example item has 18 on hand and a reorder point of 20, the result is REORDER:

With on-hand stock in F2 and the reorder point in G2, use =IF(F2<=G2,"REORDER","OK"). At 18 on hand and a threshold of 20, the formula returns REORDER.

You can also highlight low quantities without adding a status column. Select the on-hand cells, such as F2:F, choose Format > Conditional formatting, and set a rule that formats cells less than or equal to the relevant threshold. If each product has a different threshold in column G, a status formula is generally easier to interpret than one fixed conditional-formatting number. Test the rule on a few rows before relying on it.

Make the sheet easier to use and maintain

A well-designed sheet reduces accidental edits and makes routine checks faster. In Google Sheets, select the header row and use View > Freeze > 1 row so column names remain visible while scrolling. Use Data > Create a filter to filter by category, location, or reorder status. Avoid sorting only one column: sort the whole table so item details stay attached to the correct SKU.

Use consistent units, such as each, box, or kilogram; do not mix units in the same quantity column without recording the unit.
Use Data > Data validation to offer consistent choices for fields such as category or transaction type.
Format dates as dates and costs as currency, while keeping quantities numeric so formulas can calculate them.
Protect formula columns or limit editing access when appropriate. Protection helps prevent routine mistakes, but it should not be treated as a substitute for careful sharing permissions.
Agree on who records deliveries, sales, returns, and counts, and when entries must be made. A shared file still becomes inaccurate if people update it inconsistently.

When several people edit at once, establish a simple rule for recording a movement promptly and avoiding duplicate entries. Google Sheets supports collaborative editing, but simultaneous access does not resolve conflicts in how staff interpret a process. For recoverable mistakes, use File > Version history > See version history to review earlier versions. Version history is useful for recovery, but it does not replace a separate backup or a formal record system.

Common problems and practical checks

The most common inventory errors are process problems rather than complicated formula failures. A missing transaction makes the computed stock wrong; duplicate SKUs can make a lookup or total ambiguous; and an unrecorded unit conversion can create a plausible-looking but incorrect quantity. Reconcile the spreadsheet with a physical count periodically, and record count differences as adjustments with a date and reason.

Blank or incorrect balance: Check for a typo or extra space in the SKU, and confirm that the transaction rows fall inside the formula range.
Negative quantity: This may indicate a missing receipt, an oversale, a timing issue, or a data-entry error. Investigate it rather than automatically changing it to zero.
Wrong item total: Check duplicate identifiers and confirm that each variation has its own code if it is stocked separately.
Unexpected formula result: Confirm that outgoing quantities use negative numbers and incoming quantities use positive numbers. Also check that the quantity cells are numeric rather than text.
Conflicting edits: Set a clear entry procedure and check the version history if an important change was overwritten.

Google Sheets formulas generally use commas between arguments in common U.S. locales, but the spreadsheet’s locale can affect separators and date or number formats. If a formula copied from a guide is rejected, check File > Settings and the locale settings, as well as the exact tab names and cell references. Excel formulas often look similar, but a workbook’s formatting, sharing, and collaboration features can behave differently when opened in Excel.

When Sheets is enough—and when to switch

Sheets can be a good choice when the item list is modest, transaction volume is low, users can follow a shared procedure, and basic stock visibility is the main need. It can also be useful as a prototype: building a clear list and identifying required fields helps reveal what a future inventory system must support.

Consider dedicated inventory or point-of-sale software when you need reliable barcode scanning, purchase orders, lot or serial-number tracking, expiration dates, multiple warehouses, integrations, role-based controls, or a dependable audit trail. A spreadsheet can be adapted to many of these ideas, but custom formulas and manual processes increase the chance of errors as complexity grows. The time spent checking and maintaining a spreadsheet is part of its cost, even if the tool itself is available at no charge.

For personal use, Google Sheets is available through a Google account without a separate spreadsheet purchase; organizations may use Google Workspace plans with different terms and costs. Check the current plan and storage or administration requirements for your account. If inventory data is sensitive, share the file only with people who need access, review link-sharing settings, and follow your organization’s security rules.

Frequently Asked Questions

Does Google Sheets have a free inventory template?
Google Sheets has templates that can provide a starting point, and third-party templates are also available. Template layouts vary, so verify that the columns and formulas match your workflow before entering live stock data.

Can Google Sheets automatically track inventory?
Not on its own. It updates calculated balances when data is entered or connected integrations send updates. Someone must record stock movements, or a linked system must supply them; otherwise, the displayed balance can become outdated.

Should I use one sheet or separate tabs?
For a very small list, one tab may be sufficient. For a transaction-based system, separate Inventory and Transactions tabs make it easier to distinguish the current item list from the history of stock changes.

Can I scan barcodes into Google Sheets?
A barcode scanner that behaves like a keyboard may enter a scanned code into a selected cell, but that alone does not validate the item, record a transaction, or prevent duplicates. Test the scanner and design a controlled entry process before depending on it for operations.

Can I open the inventory file in Excel?
Google Sheets can export files in Microsoft Excel format, but check formulas, formatting, and data validation after conversion. Both programs support many familiar spreadsheet functions, yet sharing and some features are program-specific.

How often should I count inventory?
There is no single interval for every organization. Count high-use, high-value, or error-prone items more frequently if needed, and reconcile the physical count with the sheet whenever a discrepancy matters. Choose a schedule that staff can follow consistently.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.