An inventory spreadsheet template is a worksheet for recording what you stock, where it is, how much is available, and when to reorder it. In Excel for Microsoft 365, start with columns for a unique item ID, description, location, quantity, reorder point, unit cost, and count date; add formulas to calculate stock value and flag items that need attention.
What an inventory spreadsheet should track
A useful sheet answers a few practical questions: What is the item? How many units should be on hand? Where is it stored? What should happen when the quantity gets low? Keep the first version focused. Columns that nobody maintains can make a tracker harder to trust rather than more informative.
Here is a simple starting layout. Put the headings in row 1 and enter each item on its own row, starting in row 2:
- SKU: A unique stock-keeping identifier for each item or variant.
- Item: A plain-language name, such as Blue mug, 12 oz.
- Category: A grouping such as packaging, ingredients, or equipment.
- Location: The shelf, room, or storage site where the item is counted.
- Supplier: The supplier name or an internal supplier reference.
- Qty on hand: The current physical quantity available.
- Reorder point: The quantity at which someone should review or place a replenishment order.
- Unit cost: The recorded cost for one unit, in a consistent currency and unit of measure.
- Inventory value: A calculated extension of quantity multiplied by unit cost.
- Status: A simple indicator such as Reorder or OK.
- Last counted: The date the physical quantity was checked.
Use one row per distinct item and variant. If a shirt has three sizes, for example, each size should have its own SKU and row; otherwise a quantity for one size can be mistaken for stock of another. Avoid merged cells and blank separator rows inside the list because they make sorting and filtering less dependable.
How to create the template in Excel
- Open a blank workbook and enter the headings above in row 1. Use a consistent naming style, such as Qty on hand, rather than switching among quantity, units, and stock.
- Enter one item per row. Assign each item a stable, unique SKU; do not use the row number as the identifier, because sorting changes row order.
- Select the heading row and the area where records will go, then choose Insert > Table. Confirm that the table has headers. An Excel table provides filter controls and generally carries formulas and formatting into newly added rows.
- Set suitable formats: dates for Last counted, numbers for quantities, and currency for Unit cost and Inventory value. Choose a number of decimal places that matches how the stock is purchased or counted.
- Freeze the header row if the list is long by choosing View > Freeze Panes > Freeze Top Row. Keep an untouched backup before major changes, and save the workbook in a location that authorized staff can access.
For a very small list, the sheet can stay simple. If it will be edited by several people, decide who can change master details such as SKU and reorder point, and who records counts. Consistent ownership helps prevent accidental changes to the fields other calculations depend on.
Useful formulas for value and reorder flags
Formulas reduce repetitive arithmetic, but they do not verify that the physical count or unit cost is correct. In the layout above, the first inventory item is on row 2. With quantity in F2 and unit cost in H2, enter this in I2 to calculate the value of that row:
=F2*H2
If F2 contains 24 and H2 contains 6.50, the result is 156.00. Format the result as currency if appropriate, then fill the formula down the column. If the range is an Excel table, a formula entered in a table column is typically copied to the other rows in that column.
With Qty on hand in F2 and Reorder point in G2, enter this formula in J2 to flag stock at or below its threshold:
=IF(F2<=G2,"Reorder","OK")
If F2 is 4 and G2 is 5, the result is Reorder; if F2 is 8 and G2 is 5, the result is OK. The threshold is a prompt to review stock, not an automatic purchase decision. Lead time, expected demand, pack size, and existing orders can all affect what to buy. A blank or zero reorder point also has meaning, so fill thresholds deliberately rather than treating missing values as accurate data.
Keeping quantities accurate
A spreadsheet is only as useful as its count process. Decide what counts as available stock: for example, whether damaged goods, items set aside for a customer, or stock already committed to an order should be included. Record that rule somewhere visible so that different people do not apply different definitions.
Choose a repeatable way to update quantities. A small store may count all items on a set schedule. A larger or busier operation may count selected groups at different times. Whichever approach is used, record the date and person responsible for a count if accountability matters. When a count differs from the sheet, correct the quantity only after checking the item, unit, location, and recent receipts or issues.
Do not silently overwrite history if you need to understand how stock changed. A basic current-balance sheet stores the latest number, not a full transaction record. For a simple audit trail, use a separate movement log with date, SKU, quantity received or issued, reason, and reference number. Reconcile the log with the current balance on a defined schedule. A spreadsheet that tracks only the latest count cannot reliably explain every past adjustment.
Choosing columns for different kinds of stock
The right template depends on what is being counted. A home pantry may need item, quantity, unit, expiry date, and storage location, but not supplier or unit cost. A small shop may need SKU, variant, supplier, reorder point, and cost. A workshop may need serial number, condition, assigned user, and maintenance date for equipment. Add fields because they support a real task, not simply because they might someday be useful.
Units deserve particular attention. A quantity of 10 could mean ten individual items, ten boxes, or ten kilograms. Add a Unit column when the item list uses more than one measure, and keep each SKU tied to one defined unit. If purchasing occurs in cases but counting occurs in individual units, document the conversion and use it consistently. Otherwise, quantities and calculated values can look plausible while being wrong.
For stock kept at multiple locations, decide whether each location needs a separate row for the same SKU. Separate SKU-and-location rows make it possible to see where stock sits, but the same SKU can appear more than once. Make both the SKU and Location clear, and take care not to count the same physical stock in two places. If the sheet needs to summarize totals across many sites, a carefully structured table or inventory system may be more suitable than manual duplicate entries.
Common spreadsheet problems and safeguards
- Duplicate or inconsistent identifiers: Two spellings for the same item can split its stock across rows. Establish a SKU convention and check for duplicates when adding records.
- Sorting only one column: Sorting quantities without the corresponding item rows can attach counts to the wrong products. Sort the Excel table, not an isolated column.
- Mixed units or currencies: Costs and quantities cannot be compared meaningfully if some records use cases and others use pieces, or costs use different currencies without a conversion rule.
- Overwriting formulas: A typed value in a calculated column can break future results. Protect formula cells where appropriate, and keep a backup before structural edits.
- Stale counts: A quantity with no recent count date may be less reliable than the sheet suggests. Filter or review Last counted to find records that need checking.
Excel can help make issues visible. To highlight low-stock status, select the Status cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, then set the text to Reorder and choose a format. Formatting is a visual cue, not a substitute for checking the underlying quantity and threshold. Use filters to show a category, location, or status without deleting other records.
When a spreadsheet is enough—and when it is not
A spreadsheet is often a practical starting point for a small, stable inventory with a limited number of editors and straightforward counting rules. It is flexible, familiar, and easy to adapt. Excel itself may be available through different licenses or plans, so check the terms that apply to your organization rather than assuming every user has the same access.
As complexity grows, manual files can become difficult to coordinate. Warning signs include frequent simultaneous edits, many stock locations, barcode scanning needs, serial or lot tracking, expiry control, purchase-order workflows, or a need to connect inventory with sales and accounting. A dedicated inventory application may offer controlled transactions and permissions, but it also requires setup, training, and ongoing cost. Compare the workflow and export options, not just the feature list, before migrating.
Moving to software does not remove the need for sound item identifiers, clear units, regular counts, or good access controls. A clean spreadsheet can help define those practices before a system change. Conversely, if one person maintains a short list of supplies, adopting a complicated platform can add work without solving a meaningful problem.
Frequently Asked Questions
Is there a built-in inventory spreadsheet template in Excel?
Excel may offer inventory-related templates through its template gallery, but the available choices can vary. You can also create the simple table above in a blank workbook, which makes it easier to tailor fields and rules to your own stock.
How do I calculate total inventory value?
Calculate each row as quantity on hand multiplied by unit cost, then sum the row values for a total. Treat that figure as a spreadsheet calculation based on the costs entered; it may not equal a formal accounting valuation, which can depend on costing methods and business rules.
How many items can an Excel inventory sheet hold?
Excel worksheets support large row counts, but practical limits often arrive earlier: the file can become harder to validate, share, and update reliably. Use tables, consistent identifiers, and a tested workflow, and consider inventory software if volume or transaction complexity makes manual control difficult.
Should I use one row per product or one row per stock movement?
A current inventory list usually uses one row per distinct item, or per item and location when location-level counts matter. A movement log uses one row per receipt, issue, or adjustment. Keeping these purposes separate can make the current balance easier to read while preserving a record of changes.
Can two people edit the inventory workbook?
Shared editing may be possible when the workbook is saved in a supported Microsoft cloud location and the users have appropriate access. Agree on who edits which fields, avoid passing around conflicting copies, and test the sharing arrangement before relying on it for live stock decisions.
Related
- Budget Spreadsheet Sheets: What Tabs to Include
- PowerPoint Timeline: The Easiest Way to Make One Quickly
- Budget Spreadsheet NZ: Build a Practical Excel Plan
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Sorting in Google Sheets: A Practical GuideTo sort data in Google Sheets in a browser, select a cell in the table and choose Data…
- Excel Trendlines: Can You Ignore Zero Values?Excel does not provide a built-in Ignore zero option for a trendline. To leave zero-valued observations out of…
- Copy a Word Document into Excel: Best Methods and TipsYes. To copy a Word document into Excel, select the content in Word, press Ctrl+C, click the destination…
- How to Double Space References in WordTo double space references in Word, select the reference-list entries, open the Paragraph settings, and set Line spacing…
- Excel Workbook Shortcut Keys for Everyday TasksFor Excel for Microsoft 365 on Windows, the most useful workbook shortcuts are Ctrl+N to create a workbook,…
- Excel Time Value: How Excel Stores and Calculates TimeIn Excel, a time value is a number representing a fraction of a 24-hour day: for example, 6:00…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.