How to track inventory in Google Sheets or Excel
One row per thing, locations as paths, a minimum column with a highlight rule, and a USB scanner that types into the search box. It works well for one person and one room, and it's honest to know where it stops.
What you need
- Google Sheets or Excel
- The inventory spreadsheet template, or your own sheet
- Optional: a USB barcode scanner (they act like a keyboard)
-
Start from the right columns
Name, Type (quantity or unique), Quantity, Unit, Location, Min quantity, Max quantity, Serial number, Barcode, Category, Value, Notes. One fact per column. The template has these with notes and examples.
-
Keep one row per thing
No merged cells, no colour as data, no subtotal rows in the middle. Counted stock gets a quantity; each unique item (a specific drill) gets its own row with a serial. A sheet laid out like this sorts, filters and imports cleanly later.
-
Write locations as paths
Building / room / shelf, always in the same form: Store room / Shelf 2. Sort by it before a count and you walk each shelf once.
-
Highlight what's running low
Add a conditional format to the row: in Google Sheets, Format, Conditional formatting, Custom formula
=AND($C2<>"",$C2<=$F2)(Quantity in C, Min in F). Excel's version is the same formula under Conditional Formatting, New Rule. Low rows turn amber. -
Use a barcode scanner for lookups
Put the barcode from the box in the Barcode column. A USB scanner types the code and presses Enter, so with the search box open (Ctrl+F or Cmd+F) a scan jumps to the row.
-
Protect the sheet from accidents
Freeze the header row, protect the header and formula columns, and turn on version history. Decide who edits; everyone else gets view access.
-
Record changes, not just numbers
Add a second tab as a log: date, item, in, out, who, why. Without it, a wrong number has no history and nobody can say when it went wrong.
Where a spreadsheet stops
A spreadsheet is a good list and a poor record of change. It doesn’t know who took the last box, it can’t make someone sign for a camera, and two people editing at once leaves you with two truths. If those don’t bother you yet, keep the sheet. When they do, moving it into ItemTally takes an afternoon and keeps every column.
Formulas worth having
- To order:
=IF(C2<=F2, G2-C2, "")with Max in G. Filter the column to non-blank for a shopping list. - Stock value:
=C2*K2(Quantity × Value). Sum it for the insurer. - Overdue loans (on a lending tab): highlight rows where the due date is before
TODAY()and the returned date is blank.
Try it on your own stuff. Free for one editor and ten borrowers, with unlimited items. No card.
Start freeQuestions people ask
Can Google Sheets scan barcodes?
Not by itself. A USB or Bluetooth scanner types the code into whichever cell or search box has focus. Phone scanning into Sheets needs a third-party add-on or an app.
Is Excel or Google Sheets better for inventory?
Google Sheets is easier to share and edit together; Excel is better offline and with large files. For a team, the sharing usually matters more.
When should I stop using a spreadsheet for inventory?
When more than one person updates it during the day, when things are lent to people and you need to know who has them, or when the numbers are wrong by Friday and nobody can say why.
Can I move my spreadsheet into ItemTally later?
Yes. Save it as CSV and upload it on the Import screen. Columns like the ones above match on their own, and importing the same file again later updates the items rather than duplicating them.