SPREADSHEETS & SETUP
Small business inventory spreadsheet: a practical template
A useful starting sheet has one row for each item at each location, a quantity counted in one consistent unit, and a dated log for every later stock change. Keep the first version simple enough that people will maintain it.
Download the blank CSV template
1. Choose the columns
Start with fields that answer practical questions: what is the item, how do people identify it, where is it, what unit is counted, how much was counted, and when should someone review replenishment?
| Column | Why it helps |
|---|---|
| Item name | A clear name staff can search and recognize. |
| SKU or internal ID | A unique code for distinguishing similar items; optional for a very small list. |
| Product family | Optional shared name for related sellable SKUs, such as one style in several sizes. |
| Variant label | Optional size, color, material, or other combination label within a product family. |
| Barcode | An identifier to scan when the workflow supports barcode scanning. |
| Category | A grouping for browsing and simple reports. |
| Unit | The product base unit used for balances, such as each, box, meter, or kilogram. |
| Quantity unit | Optional for an existing item with configured alternate units. The import converts this quantity into the base unit. Leave blank to use Unit. |
| Location | The place where that balance is physically counted and managed. |
| Quantity counted | The observed quantity, with zero reserved for a confirmed empty location. |
| Reorder point | A threshold for review, not an automatic order quantity. |
| Last counted | The date of the latest physical check. |
| Notes | Short context that does not belong in a consistent field. |
Do not create every possible column at the start. Add a field only when the team knows what it means and how it will be kept current. For more on configurable item records, read the small business inventory setup guide.
2. Use one row per item and location
If the same item is stored in two places, use two balance rows. Do not put several location quantities into one cell or combine them into a company-wide number that hides a local stockout.
| Item | SKU | Unit | Location | Counted quantity | Reorder point |
|---|---|---|---|---|---|
| Fastener M8 × 20 mm | HW-008 | each | Workshop | 240 | 60 |
| Fastener M8 × 20 mm | HW-008 | each | Service van | 18 | 10 |
| Cutting disc, 115 mm | TL-115 | each | Workshop | 12 | 8 |
These values are illustrative. The important part is that each quantity has one item, one location, and one unit attached to it. For existing products, an optional Quantity unit column can use a configured alternate unit such as case; the preview shows both the file quantity and normalized base quantity.
3. Keep a separate movement log
A balance sheet tells you what the record says now. A movement log helps explain how it got there. Add one dated row for each receipt, sale or use, transfer, return, damaged item, or count correction.
| Date | Item ID | Location | Reason | Quantity change | Reference |
|---|---|---|---|---|---|
| 2026-10-20 | HW-008 | Workshop | Receipt | +24 each | PO-302 |
| 2026-10-21 | HW-008 | Workshop | Used on job | −3 each | JOB-184 |
Use signed quantities consistently and record transfers at both their source and destination. Keep the reason and reference short but useful. The inventory counting guide explains how to investigate and document differences.
4. Clean the sheet before relying on it
- Remove duplicate item rows and give similar items distinct identifiers.
- Use the same spelling for an item, location, and unit everywhere.
- Store numbers as numbers, dates as dates, and one value per cell.
- Do not use a blank cell to mean zero, unknown, and not counted interchangeably.
- Count the physical stock before setting an opening quantity.
- Protect formulas and keep a dated backup before a major cleanup.
For a fuller count process, follow these steps for preparing, checking, and reconciling a count.
5. Know when a spreadsheet stops helping
A spreadsheet may be enough for one person and one location. It becomes harder to trust when several people edit at once, changes overwrite one another, the same item has balances in many places, or nobody can explain a difference.
Before moving to software, compare the item list and location balances with a physical count. Check how the target product handles imports and exports. The Inventory workspace supports CSV import with a preview before applying changes. Start with Name, Unit, and On hand, then follow the optional-field and location rules in the import screen. You can learn how its independent workflow works in inventory management without a POS.
Inventory spreadsheet questions
What columns should an inventory spreadsheet have?
Start with item name, a unique internal ID or SKU, unit of measure, location, counted quantity, reorder point, and last-count date. Add a barcode, category, supplier reference, or notes only when they help your team identify or manage the item.
Should each location have its own row?
Yes. Use one row for each item at each location. For batch-tracked items, use one row per lot at that location so separate expiry dates and balances remain traceable. This keeps stockroom quantities separate from workshop or storefront balances and makes transfers easier to reconcile.
Should I overwrite the quantity when stock changes?
Keep a current-balance sheet for a quick snapshot, and record each receipt, use, transfer, return, or correction in a separate dated movement log. A history makes differences easier to investigate.
Can I import this template into Inventory?
Yes. Inventory accepts CSV imports with Name, Unit, and On hand columns. SKU, Barcode, Product family, Variant label, Quantity unit, Category, Description, Location, Reorder point, and configured custom fields are optional. Include family and variant labels to distinguish same-name variants. Set Tracking mode to BATCH to create a lot-tracked product; use one row per lot and include every current lot for an existing product at that location; Lot code, Manufactured at, and Expires at preserve lot identity and history. Preview changes first; the importer supports up to 500 rows and 1 MB.