Yes. You can make a free inventory spreadsheet in Excel with columns for each item’s SKU, description, location, unit cost, quantity on hand, reorder level, and stock value. Copy the header row below into a blank workbook, then add the two formulas provided to calculate inventory value and flag items that need reordering.
Copy-ready inventory template layout
Start with one row per distinct product or stock-keeping unit (SKU). Copy this tab-separated header line and paste it into cell A1 in Excel; each label should land in its own column:
SKU Item Category Supplier Location Unit Cost Qty On Hand Reorder Level Reorder Qty Stock Value Status Last Count Date
For example, a small shop might record a ceramic mug as SKU MUG-01, assign it to the Kitchen category, keep it in Shelf A, and enter its current unit cost and counted quantity. A different mug size or color should usually have its own SKU if it is tracked and replenished separately. The spreadsheet is a structure for recording and calculating stock; it does not connect automatically to a checkout system or verify quantities in a storeroom.
Use one consistent unit of measure for each item. If a product is counted as individual pieces, record pieces—not cases—in Qty On Hand, or clearly define a case as the unit and use it consistently for both stock and cost. Mixing units can make a mathematically correct spreadsheet operationally wrong.
What each column is for
Give each column a specific job, and decide what counts as a unique item before entering a large catalog. These fields cover a basic inventory without making the sheet difficult to maintain:
- SKU: A unique internal code for the item or variant. Avoid reusing a code for a different product.
- Item and Category: A readable product name and a broad grouping, such as Office Supplies or Hardware.
- Supplier: The vendor you normally order from. If an item has several suppliers, note the preferred one or add a separate supplier reference.
- Location: Where the item is stored, such as Warehouse 1, Bin C4, or Shop Floor.
- Unit Cost: The cost assigned to one tracked unit. Record what the number represents and use a consistent approach; the sheet does not decide accounting treatment for you.
- Qty On Hand: The latest physically counted quantity, adjusted for known receipts, sales, use, returns, or transfers.
- Reorder Level and Reorder Qty: The threshold at which a replenishment should be considered and the usual amount to order. These are different: the threshold is not necessarily the purchase quantity.
- Stock Value, Status, and Last Count Date: A calculated value, a simple attention flag, and the date the quantity was last checked.
For a very small list, you may not need every field. Keep SKU, item, quantity, and count date at a minimum; add supplier, location, cost, and reorder information when they help someone make a decision.
Build the sheet in Excel
In Excel for Microsoft 365 on Windows, open a blank workbook, paste the headers into A1, and enter your items below them. Select the used range, including the header row, then choose Insert > Table and confirm that the table has headers. A table gives the list filter controls and usually carries formatting and formulas into newly added rows. On a Mac, use the same Insert > Table ribbon command; the appearance of the ribbon can vary with the window layout.
Make the data easy to scan before adding many records:
- Format Unit Cost and Stock Value as currency using Home > Number controls. Choose the appropriate currency for your records.
- Format quantities as numbers with no decimal places if you count whole units. If you track measured goods, choose a suitable decimal format instead.
- Format Last Count Date as a date, and enter actual dates rather than text such as “last Tuesday.”
- Use the table’s filter arrows to show one category, supplier, location, or status at a time.
- To keep headings visible while scrolling, choose View > Freeze Panes > Freeze Top Row.
For categories or locations that must use consistent labels, select the relevant cells and choose Data > Data Validation to create a list of allowed entries. This can reduce variations such as “Back room,” “Backroom,” and “Back Room,” which otherwise appear as separate filter choices. Keep a copy of the original workbook before changing a layout used by other people.
Use formulas for stock value and reorder flags
Formulas reduce repeated arithmetic, but they only work as intended when the source quantities and costs are current. In the template, Stock Value is unit cost multiplied by quantity on hand. With Unit Cost in F2 and Qty On Hand in G2, enter this formula in J2: =F2*G2. If F2 is 12.50 and G2 is 8, the result is 100.00 in the currency used for the cost. Fill the formula down the column, or enter it in the first data row of the Excel table so the table can extend it to other rows.
A reorder flag can compare the current quantity with the threshold. With the SKU in A2, Qty On Hand in G2, and Reorder Level in H2, enter this formula in K2: =IF(A2="","",IF(G2<=H2,"REORDER","OK")). For SKU A101 with 8 units on hand and a reorder level of 10, the result is REORDER. The first test leaves unused rows blank; the second treats a quantity equal to the threshold as a reorder warning. Fill it down the Status column.
For a total value across a small example with item values in J2:J4, enter =SUM(J2:J4) in a summary cell outside the item rows. If the three values are 100, 90, and 0, the result is 190. Keep summary cells outside the table so they are not mistaken for inventory records. This total is only as meaningful as the costs and quantities entered, and it may not match a formal accounting valuation method.
You can make reorder flags stand out with Home > Conditional Formatting > Highlight Cells Rules, choosing a rule suited to the text in the Status column. Formatting is a visual aid, not a substitute for checking the actual quantity and order context.
Keep quantities and records reliable
An inventory file becomes stale when transactions are not reflected in it. Decide who records receipts, sales or usage, damaged goods, customer returns, and transfers between locations. If stock is moved from one bin to another, adjust both locations rather than simply changing the destination’s number. If you only update the sheet during periodic counts, label it as a snapshot and do not treat it as a live balance.
Set a repeatable count routine. A person can count the physical stock, compare it with the spreadsheet, investigate a meaningful difference, and record the corrected quantity and date. For instance, a sheet showing 18 boxes but a count finding 15 should prompt a check for recent shipments, unrecorded use, damage, or a counting mistake before the number is changed. Keeping a brief note about significant adjustments can help explain later why the balance changed.
Protect the file from accidental confusion by naming it clearly, storing it in an approved shared location if several people need it, and establishing who may edit it. Excel’s version history depends on where and how a workbook is stored, so do not assume every local copy has a recoverable history. Avoid keeping several files called “inventory final” with different figures.
Common spreadsheet problems to prevent
Many inventory errors come from inconsistent records rather than complicated formulas. A few simple rules help:
- Duplicate items: Search for an existing SKU before adding a new row. Different spellings of a name can hide duplicates, so use the code as the primary identifier.
- Overwriting formulas: Keep formulas in calculated columns and avoid typing manual totals over them. If a result looks wrong, inspect the referenced cost and quantity first.
- Blank or negative quantities: A blank may mean “not counted,” while zero means “counted and none found.” Agree on the distinction. A negative figure can reveal a missed transaction or a data-entry problem and should be investigated.
- Unclear reorder thresholds: A threshold can depend on supplier lead time, demand, and the consequences of running out. Treat it as a planning setting that needs review, not a universal number generated by Excel.
- Accidental sorting: Sort the whole table, not a single column by itself, so item details stay on the same row as their SKU.
- Untracked edits: If several people update the sheet, agree on when changes are entered and how a physical count is documented. A spreadsheet cannot resolve conflicting edits by itself.
Also distinguish inventory quantity from sales or purchase history. A single current-balance sheet can show what is recorded as available, but it cannot explain every change unless you maintain a separate transaction log with dates, item codes, quantities, and transaction types.
When Excel is enough—and when to consider alternatives
A spreadsheet is often a practical starting point for a small catalog, a single location, or a stock list that one person updates. It is free if you already have access to Excel, easy to reshape, and useful for filtering or making a quick count sheet. The main trade-off is that users must maintain the data and coordinate changes themselves.
Consider a more specialized inventory system when several locations or users need coordinated updates, stock changes must connect to sales and purchasing, barcode workflows matter, or an audit trail is important. A shared workbook can support collaboration, but it does not automatically provide the controls or integrations of dedicated software. Before switching, list the specific tasks the current sheet cannot handle and compare software costs, access controls, export options, and migration effort. A spreadsheet remains useful for analysis or exports even when it is no longer the main stock system.
There is no one correct threshold for moving away from a spreadsheet. The key question is whether the effort and risk of keeping the file accurate are greater than the cost and training involved in another tool.
Frequently Asked Questions
Is there a free inventory spreadsheet template in Excel?
Yes. The header row and formulas on this page provide a simple template you can build in a blank Excel workbook without buying an add-in. Whether Excel itself is free depends on your device, account, and licensing; the layout does not require a paid inventory-specific template.
What should an inventory spreadsheet include?
At a minimum, include a unique item identifier, description, quantity on hand, and date counted. Add location, supplier, unit cost, reorder level, and reorder quantity if those details support how you manage stock.
How do I calculate inventory value in Excel?
Multiply the unit cost by the quantity on hand for each item, then sum the item values if you need a total. Check that the cost basis and units are consistent; a spreadsheet calculation alone does not determine an appropriate accounting valuation.
Can Excel tell me when to reorder stock?
It can flag a row when the quantity is at or below a threshold you enter. The flag does not know supplier delays, expected demand, or pending orders unless you record and account for them, so review those details before placing an order.
Can multiple people use the same inventory spreadsheet?
They can work with a shared workbook when it is stored in a location that supports collaboration, but users still need clear editing and counting procedures. For frequent simultaneous updates or tightly controlled stock movements, dedicated inventory software may be more suitable.
Related
- How to Paste a Picture in Excel and Keep It in Place
- How to Open an XLB File in Excel (and What It Does)
- How to Open XML Files in Excel and Import Their Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Dropdown Formula: How It Really WorksGoogle Sheets does not create a dropdown with a standalone cell formula: use Data validation to make the…
- Schedule an Outlook Email to Send Every WeekOutlook’s built-in Schedule Send feature sends a message once; it does not repeat it every week. To send…
- Google Sheets Date Dropdown: How to Add OneYes. In Google Sheets in a web browser, select the cells, choose Data > Data validation > Add…
- Outlook Image Cache: How to Clear It on Any DeviceTo clear stale images in Outlook, use the method for the version you have: in classic Outlook for…
- How to Add Color to a Dropdown List in Excel with RulesTo add color to a dropdown selection in Excel, keep the dropdown list as it is and use…
- Google Sheets Project Tracker: Setup and Best PracticesA Google Sheets project tracker is a shared spreadsheet for organizing tasks, owners, deadlines, priorities, and progress in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.