Inventory Spreadsheet Template Explained: Fields, Example and How to Use One

An inventory spreadsheet template is a ready-made table that lets you track every product you hold, what it costs, how much is on hand and when to reorder. If you sell physical goods - whether that's candles, components, coffee beans or branded merch - it is the single document standing between you and the chaos of guessing what's in stock. This guide explains exactly what an inventory spreadsheet template contains, breaks down every field, walks through a realistic example, and shows you the mistakes and best practices that separate a clean stock record from a spreadsheet nobody trusts.

The goal is simple: a spreadsheet you actually update, that tells the truth about your stock, and that feeds clean numbers into your accounting and ordering decisions. Let's build it properly.

What Is an Inventory Spreadsheet Template?

An inventory spreadsheet template is a structured workbook - usually in Excel or Google Sheets - with predefined columns for every piece of information you need to manage stock. Each row is one product (or one product variant), and each column captures a single attribute: the SKU, the description, the supplier, the quantity on hand, the unit cost, the reorder point and so on.

Unlike a blank sheet, a template already has the headers, formatting and formulas in place. You drop in your products, enter quantities, and the sheet does the arithmetic - total value, reorder flags, low-stock highlighting. It turns a pile of "I think we have about twenty left" into a defensible record.

It is not the same as full inventory software. A spreadsheet is manual: you update it. But for a business with tens or low hundreds of SKUs, a well-built inventory spreadsheet template is fast, free and flexible - and it is where almost every product business should start.

Who uses one?

• Ecommerce sellers tracking stock across a handful of products before they scale.
• Retailers and market traders counting what's on the shelf versus what's in the back.
• Makers and small manufacturers tracking raw materials and finished goods.
• Service businesses that hold consumables - a salon tracking color tubes, a workshop tracking parts.
• Accountants and bookkeepers who need a year-end stock valuation from a client.

When Do You Need an Inventory Spreadsheet?

You need an inventory spreadsheet the moment "I'll remember" stops being true. That's usually sooner than people think. A few clear triggers:

• You've run out of a best-seller and only noticed when a customer asked for it.
• You've over-ordered and tied up cash in stock that isn't moving.
• Your accountant has asked for a closing stock figure and you don't have one.
• You're holding more than a dozen products and tracking them in your head.
• You're about to do a year-end count and want somewhere to record it.

There's also a financial reason. The value of your unsold stock is an asset that appears on your balance sheet and directly affects your cost of goods sold and taxable profit. Tax authorities expect a reasonable, consistent valuation method - both the UK's HMRC and the US IRS require businesses that hold stock to account for it properly. An inventory spreadsheet is how most small businesses produce that number.

The Core Sections and Fields Every Inventory Spreadsheet Needs

A good inventory spreadsheet template groups its columns into logical sections. You don't need every field below for every business, but you should consciously decide which to keep.

Identification fields

• SKU / item code - a unique identifier for each product
• Product name / description
• Category or product type
• Barcode or UPC (optional, for scanning)

Sourcing fields

• Supplier / vendor name
• Supplier item reference
• Lead time (days to restock)

Quantity fields

• Quantity on hand (current units in stock)
• Quantity on order (units you've already purchased but not received)
• Reorder point (the level that triggers a new order)
• Reorder quantity (how many to order each time)

Cost and value fields

• Unit cost (what you paid per unit)
• Total value (quantity on hand × unit cost)
• Selling price (optional, for margin)

Status and tracking fields

• Location (shelf, bin, warehouse zone)
• Last restocked date
• Last counted date
• Status (in stock / low / out of stock)
• Notes

A Section-by-Section Breakdown

Here's what each of the most important fields actually does and how to fill it in.

SKU / item code

The SKU (stock keeping unit) is the backbone of the whole sheet. It must be unique and consistent. A good SKU is short, human-readable and encodes useful info - for example `CAN-LAV-200` for a 200g lavender candle. Avoid spaces and special characters so the code plays nicely with formulas and any software you migrate to later.

Product name and category

The name is for humans; the SKU is for the system. Keep names consistent ("Lavender Candle 200g", not sometimes "Lav candle"). Category lets you filter and group - for example to value all "Candles" separately from "Packaging".

Quantity on hand

This is the single most important number in the sheet and the one most likely to drift from reality. It should reflect actual physical units. Update it whenever you receive stock, sell stock, or find a discrepancy during a count.

Reorder point and reorder quantity

The reorder point is the stock level at which you should place a new order - set high enough that you won't run out before new stock arrives. A simple, reliable formula is:

Reorder point = (average daily sales × lead time in days) + safety stock

If you sell 5 units a day, your supplier takes 7 days, and you want a 10-unit buffer, your reorder point is (5 × 7) + 10 = 45. When quantity on hand drops to 45, order more.

Unit cost and total value

Unit cost is what you paid per unit, not what you sell it for. Total value is `quantity on hand × unit cost` and should be a formula, not a typed number. The sum of this column is your inventory valuation - the figure your accountant wants.

Last counted date

This single column does more for trust than any other. It tells you how stale each row is. If a product hasn't been counted in six months, you know to treat its quantity with suspicion.

A Realistic Example: Bloom & Thread Candle Co.

Meet Priya, who runs Bloom & Thread Candle Co., a small online candle business. She sells through her own site and a couple of markets, and she holds around 30 SKUs - different scents, sizes and a few gift sets.

For a year, Priya tracked stock in her head and in the occasional note. Then she sold out of her bestselling lavender candle the week before a craft fair, lost a stack of sales, and decided to fix it. She built an inventory spreadsheet template.

Her sheet has one row per SKU. For the lavender 200g candle she enters:

• SKU: CAN-LAV-200
• Product name: Lavender Candle 200g
• Category: Candles
• Supplier: WaxWorks Ltd
• Lead time: 7 days
• Quantity on hand: 38
• Reorder point: 45
• Unit cost: $4.20
• Selling price: $14.00
• Total value: $159.60 (a formula: 38 × $4.20)
• Last counted: 2026-06-01

The Status column runs an IF formula: if quantity on hand is below the reorder point, it shows "Low" and turns amber; if it hits zero, it shows "Out" and turns red. The moment her lavender candle dropped to 38 against a reorder point of 45, the row flagged amber. Priya saw it on her Monday review, placed a 100-unit order with WaxWorks, and never missed a fair again.

At the bottom of the sheet, a single SUM cell adds up the Total value column. At year-end, Priya hands her bookkeeper one number - her closing stock valuation of $4,180 - backed by a sheet that shows exactly how it was built. The bookkeeper reconciles it against purchase records and signs it off in minutes.

The spreadsheet didn't just prevent stockouts. It showed Priya that three of her SKUs hadn't sold a unit in months and were tying up $600 of cash. She discounted them, freed the money, and reordered her winners. That's the quiet power of a stock record you actually trust.

Inventory Spreadsheet vs Related Documents

Inventory tracking touches several other business documents. Here's how an inventory spreadsheet differs from the ones it's most often confused with.

The inventory spreadsheet is the master record. The others feed it: a purchase order increases quantity on order, a goods received note increases quantity on hand, and a stock take corrects it against reality.

Pros and Cons of Using a Spreadsheet for Inventory

A spreadsheet is the right tool for many businesses - but not all. Weigh it honestly.

Pros

• Free or near-free; you likely already have Excel or Google Sheets.
• Fully customisable - add any column or formula you want.
• No learning curve beyond basic spreadsheet skills.
• Easy to share, back up and email.
• Portable; you can export to almost any future system.

Cons

• Entirely manual - accuracy depends on discipline.
• No real-time link to sales channels, so it drifts between updates.
• Risk of broken formulas, overwritten cells and version confusion.
• Hard to scale past a few hundred SKUs or multiple locations.
• No automatic reorder alerts or barcode scanning out of the box.
• One shared file can create conflicts when a team edits at once.

Common Mistakes to Avoid

These are the errors that turn a useful inventory spreadsheet into a misleading one.

• Typing total value instead of using a formula. Manual totals fall out of sync the moment a quantity changes. Always make Total value calculate itself.
• Inconsistent SKUs. Mixing `CAN-LAV-200`, `canlav200` and `Lavender 200` for the same product breaks filtering and any future migration.
• No reorder point. Without a trigger level, you only discover a stockout when a customer asks. Set one for every active SKU.
• Confusing cost and selling price. Inventory is valued at cost, not at retail. Valuing stock at selling price inflates your asset and distorts your accounts.
• Never doing a physical count. The sheet drifts from reality through breakages, theft (shrinkage) and miscounts. A spreadsheet that's never reconciled is fiction.
• One giant unstructured sheet. Dumping everything into one tab with no categories, no last-counted date and no status column makes the data unusable at scale.
• No backups or version control. A single overwritten file can wipe your only stock record. Use cloud storage with version history.
• Tracking quantity but not location. When you hold stock in two places, "we have 40" is useless if you don't know where.

Best Practices for an Inventory Spreadsheet

Follow these in order to build a sheet you'll actually trust.

1. Start with one row per SKU and consistent column headers. Decide your fields up front and don't improvise new columns mid-sheet.
2. Make every calculation a formula. Total value, status flags and reorder alerts should compute automatically so they never lie.
3. Set a reorder point for every active product. Use the lead-time formula above; revisit it as your sales patterns change.
4. Add a "last counted" date column. It instantly shows you which rows are stale and need a physical check.
5. Do regular cycle counts. Instead of one painful annual count, count a slice of your SKUs each week so the whole sheet stays close to reality.
6. Reconcile against purchase and sales records. Tie quantity changes back to goods received notes and sales so discrepancies surface fast.
7. Use one consistent valuation method. Pick FIFO (first-in, first-out) or average cost and stick with it, so your year-on-year figures are comparable.
8. Back up to the cloud with version history. Google Sheets and OneDrive both keep automatic version histories - use them.
9. Protect formula cells. Lock the cells that contain formulas so a stray paste doesn't break the maths.
10. Review weekly. A short, fixed weekly review of low-stock flags is what turns the sheet from a record into a decision tool.

How the Inventory Spreadsheet Fits Your Business Workflow

An inventory spreadsheet doesn't live in isolation - it sits at the center of your buying, selling and accounting cycle.

When stock runs low, the sheet's reorder flag tells you to raise a purchase order to your supplier. When the delivery arrives, you check it against a goods received note and add the units to quantity on hand. When you sell, you reduce the quantity - and, crucially, you raise an invoice for the sale.

This is where the chore of stock management meets the part of the business that actually gets you paid. Every unit that leaves your shelf should be matched by a clean, professional invoice. Tools like Aviy let you generate that invoice from a single plain-language sentence - "Invoice The Gift Loft $420 for 30 lavender candles due in 14 days" - so the selling side of your workflow is as fast as the tracking side. Your spreadsheet keeps the stock honest; your invoicing keeps the cash flowing.

At period end, the sheet produces your closing stock valuation, which flows into your accounts and affects your taxable profit. Keeping the spreadsheet accurate all year means there's no scramble at year-end - the number is simply there.

A simple monthly rhythm

• Weekly: review low-stock flags, raise purchase orders, log deliveries.
• Monthly: cycle count a portion of SKUs, reconcile against records, review slow movers.
• Quarterly: review reorder points against actual sales, retire dead stock.
• Year-end: full count, final valuation, hand off to your accountant.

When you're tracking dozens of SKUs and several markets, this rhythm keeps everything calm. When you outgrow it - multiple locations, hundreds of SKUs, a team editing at once - that's your signal to graduate from a spreadsheet to dedicated software. But the spreadsheet is almost always the right first step, and for many small businesses it's all they ever need.

How to Build Your Inventory Spreadsheet Step by Step

If you're starting from a blank file, here's the fastest path to a working sheet. You can do all of this in Excel or Google Sheets in under an hour.

1. Add your header row. In row one, type your column names: SKU, Product name, Category, Supplier, Lead time, Quantity on hand, Quantity on order, Reorder point, Unit cost, Total value, Selling price, Location, Last counted, Status, Notes. Freeze this row so it stays visible as you scroll.
2. Enter your products, one per row. Start with your active SKUs. Don't try to capture everything in one sitting - get your fast movers in first and add the long tail later.
3. Write the Total value formula. In the first data row's Total value cell, enter a formula multiplying quantity on hand by unit cost, then copy it down the column. Now it updates itself forever.
4. Add a status formula. Use a nested IF so the cell reads "Out" when quantity is zero, "Low" when it's at or below the reorder point, and "OK" otherwise. Apply conditional formatting to color it red, amber and green.
5. Total the value column. Drop a SUM beneath the Total value column. That single cell is your live inventory valuation.
6. Lock and protect. Protect the formula columns so a careless paste can't overwrite the maths, then turn on version history.

That's a complete, self-maintaining inventory spreadsheet. Everything after this is discipline, not setup.

Formatting tips that keep it readable

• Use a single date format (ISO `YYYY-MM-DD` sorts cleanly and never confuses day with month).
• Keep currency formatting consistent across the cost and value columns.
• Use a filter on the header row so you can isolate one category, one supplier or all "Low" items in a click.
• Avoid merged cells entirely - they break sorting and filtering.

Key Inventory Metrics Your Spreadsheet Can Reveal

Once the data is clean, a spreadsheet becomes more than a list - it's a lightweight analytics tool. A few metrics are worth adding as extra columns or a small summary tab.

• Inventory turnover - how many times you sell through and replace stock in a period. Low turnover on a SKU signals dead stock tying up cash.
• Days of stock remaining - quantity on hand divided by average daily sales. It tells you, in plain days, how long until you run out.
• Stock value by category - sum the Total value column grouped by category to see where your money is parked.
• Shrinkage - the gap between what the sheet says you should have and what a physical count finds. Persistent shrinkage points to theft, breakage or sloppy logging.

These metrics turn ordering from a gut feel into a decision. When Priya from Bloom & Thread spotted three SKUs with near-zero turnover, the sheet had already done the analysis - she just had to act on it.

Summary

An inventory spreadsheet template is the simplest, cheapest and most flexible way for a small business to take control of its stock. Build it with one row per SKU, consistent codes, formula-driven totals and a reorder point for every product, and you'll prevent both stockouts and overstocking while producing a clean valuation for your accounts.

The template only works if you work it: make calculations automatic, count regularly, reconcile against your purchase and sales records, and review the low-stock flags every week. Do that, and your inventory spreadsheet becomes a genuine decision tool rather than a dusty file. When stock movements connect smoothly to your purchasing and invoicing, the whole operation runs tighter - and you spend less time guessing and more time growing.

Frequently asked questions

What is an inventory spreadsheet template?

It's a structured workbook in Excel or Google Sheets with predefined columns for tracking every product you hold - including SKU, description, quantity on hand, unit cost, reorder point and total value. Each row is one product. The template already includes headers and formulas so totals and low-stock flags calculate automatically, giving you a single, trustworthy record of your stock.

What columns should an inventory spreadsheet have?

At minimum: SKU, product name, category, supplier, quantity on hand, reorder point, unit cost and total value. Strong templates add quantity on order, selling price, location, last restocked date, last counted date and a status flag. Identification, sourcing, quantity, cost and tracking are the five field groups every good inventory spreadsheet should cover.

How do I make an inventory spreadsheet in Excel or Google Sheets?

Create one row per product and add column headers for SKU, name, quantity, unit cost, reorder point and total value. Make Total value a formula (quantity × unit cost) and use an IF formula for a status flag. Add a SUM at the bottom for your total valuation, then enter your products and current quantities.

How do you calculate the reorder point in a spreadsheet?

Use: reorder point = (average daily sales × supplier lead time in days) + safety stock. If you sell 5 units a day, your supplier takes 7 days, and you want a 10-unit buffer, the reorder point is 45. Put it in its own column and compare it to quantity on hand with an IF formula to flag low stock.

How do you value inventory in a spreadsheet?

Multiply quantity on hand by unit cost (what you paid, not what you sell for) in a Total value column, then sum that column. Use one consistent method - FIFO or average cost - across periods so figures stay comparable. This total is your inventory valuation, which your accountant uses for closing stock and cost of goods sold.

When should I move from a spreadsheet to inventory software?

When manual updates can't keep up - typically multiple stock locations, hundreds of SKUs, several team members editing at once, or a need for real-time sync with sales channels and barcode scanning. Until then, a well-built inventory spreadsheet is faster, cheaper and perfectly adequate for most small businesses.

What's the difference between an inventory spreadsheet and a stock take sheet?

An inventory spreadsheet is the ongoing master record, updated continuously as stock moves. A stock take (or count) sheet is a one-time snapshot of a physical count, often done at year-end or periodically. The count corrects the spreadsheet against reality; the spreadsheet is what you rely on day to day.

Can a spreadsheet handle multiple stock locations?

Yes, up to a point. Add a Location column and, if needed, separate quantity columns per location with a combined total. Beyond a couple of sites with shared SKUs, formulas get fragile and version conflicts grow. At that scale, dedicated inventory software usually pays for itself in saved time and fewer errors.

How often should I update my inventory spreadsheet?

Update quantities whenever stock moves - receipts and sales - and review low-stock flags weekly. Run cycle counts monthly on a portion of SKUs and a full physical count at year-end. The single most important habit is a fixed weekly review; without it, even the best template drifts from reality and loses trust.

Is an inventory spreadsheet enough for tax and accounting?

For many small businesses, yes. Tax authorities expect a reasonable, consistent stock valuation, and a clean spreadsheet that values stock at cost using a consistent method (FIFO or average cost) provides exactly that. Keep it backed up with version history and reconcile it against purchase records so the closing figure is defensible.

Conclusion

An inventory spreadsheet template is the foundation of stock control for any product business that isn't yet ready for full software. Built well - one row per SKU, formula-driven totals, a reorder point for every product and a disciplined weekly review - it prevents stockouts, frees up cash tied in dead stock, and hands your accountant a clean closing valuation. The template does the structure; your habit of updating and reconciling it does the rest.

Treat your inventory spreadsheet as a living decision tool, not a file you open once a year. Connect it to your purchasing and invoicing, count regularly, and back it up. Get those basics right and you'll always know exactly what you have, what it's worth, and what to order next.

Related guides

• Goods Received Note (GRN) Template Explained
• Delivery Note Template Explained: Fields, Example and How to Use One
• When Should You Use a Purchase Order? A Practical Guide
• Business Expense Tracker Template Explained: Fields, Example and How to Use It
• How to Improve Cash Flow in Your Business
• Word vs Excel Invoice Templates: Which Is Better?

Sources and further reading

• IRS - Inventories (Publication 538, Accounting Periods and Methods)
• GOV.UK - Business records if you're self-employed
• Investopedia - Inventory Valuation Methods (FIFO, LIFO, Average Cost)
• SBA - Manage your inventory
• Investopedia - Reorder Point Definition