An example inventory spreadsheet can use columns for SKU, item name, category, storage location, quantity on hand, reorder point, unit cost, inventory value, reorder status, and last-counted date. In Excel for Microsoft 365, calculate each row’s value with =E2*G2 and flag low stock with =IF(E2<=F2,"Reorder","OK"), assuming those columns and a first item row of 2.
Example inventory spreadsheet layout
Start with one row per tracked item and one column per attribute. Use a unique SKU or other identifier so that items with similar names can still be distinguished. A simple worksheet might have these headings in row 1:
- SKU: a unique stock-keeping identifier, such as CAB-USB-C-01.
- Item: a name people can recognize, such as USB-C cable, 1 m.
- Category: a consistent group, such as Cables or Office supplies.
- Location: where the stock is stored, such as Shelf A2 or Warehouse.
- Qty on hand: the number currently counted as available.
- Reorder point: the quantity at or below which someone should review replenishment.
- Unit cost: an optional per-unit cost for an approximate stock valuation.
- Inventory value: quantity multiplied by unit cost.
- Reorder status: a simple indication that the count has reached its reorder point.
- Last counted: the date the quantity was physically checked.
In this layout, put the headings in A1:J1 in the order listed. Enter the SKU in column A, item name in B, category in C, location in D, quantity in E, reorder point in F, unit cost in G, value in H, status in I, and last-counted date in J. Keep the header names short and unambiguous; “Qty on hand” is clearer than a general heading such as “Amount.”
Sample rows and what they show
The following small sample illustrates how the rows fit together. The costs and quantities are made-up examples for demonstrating the spreadsheet, not recommended prices or accounting values.
| SKU | Item | Category | Location | Qty on hand | Reorder point | Unit cost | Inventory value | Reorder status | Last counted |
|---|---|---|---|---|---|---|---|---|---|
| CAB-01 | USB-C cable, 1 m | Cables | Shelf A2 | 18 | 10 | $4.50 | $81.00 | OK | 2026-10-28 |
| AUD-02 | Wired headset | Audio | Cabinet B1 | 6 | 8 | $32.00 | $192.00 | Reorder | 2026-10-28 |
| OFF-03 | Notebook, A5 | Office supplies | Shelf C3 | 25 | 12 | $2.20 | $55.00 | OK | 2026-10-28 |
The three sample rows contain 49 units altogether. Their displayed inventory values add up to $328.00: $81.00 for cables, $192.00 for headsets, and $55.00 for notebooks. The headset row is marked for review because 6 is less than its reorder point of 8. A status flag is a prompt to check purchasing needs; it does not place an order or decide how many units to buy.
Build the sheet in Excel
- Open a blank workbook and enter the headings in row 1. Avoid blank columns inside the list, since gaps can make sorting and filtering less reliable.
- Enter one item per row, keeping quantities as numbers and dates as dates. Do not type units such as “pieces” into quantity cells; put that information in a separate unit-of-measure column if it matters.
- Select a cell in the list and choose Insert > Table. Confirm the range and select “My table has headers.” Excel tables provide header filters and normally extend formatting and formulas when new rows are added.
- Apply a suitable number format to unit cost and inventory value, and a date format to Last counted. Use a number format rather than entering currency symbols as part of the cell’s text.
- Enter the calculations in the first data row, then fill them down or let the Excel table extend the formulas to other rows.
On Excel for Microsoft 365 for Windows, these menu paths apply as written. Excel for Mac also has the Insert > Table command, though ribbon placement and keyboard shortcuts can differ. In Excel for the web, the table and formula approach is similar, while some ribbon controls may be arranged differently.
Formulas for value, reorder flags, and totals
With the layout above, enter the value formula in H2. With quantity in E2 and unit cost in G2, =E2*G2 returns $81.00 for the sample cable row: 18 multiplied by $4.50. Format column H as currency or an appropriate number format; the formula itself returns a numeric result.
For the status in I2, with quantity in E2 and reorder point in F2, use =IF(E2<=F2,"Reorder","OK"). On the headset example it returns “Reorder” because 6 is less than or equal to 8. On the cable row it returns “OK” because 18 is above 10. Copy each formula down the relevant rows, or use the table so Excel can propagate the calculation as rows are added.
To calculate a total stock value below the sample entries, put a total label in one cell and use =SUM(H2:H4) in the total cell, assuming the three item values are in H2:H4. It returns $328.00 for the sample. If you add rows, extend the range or place the summary outside the table and use a range that covers all item rows. For a changing or larger list, Excel table totals can help summarize a column without manually adjusting a fixed range.
Formulas are only as useful as the values entered. A blank unit cost makes the row’s value appear as zero when multiplied, which could be mistaken for a true zero-cost item. If costs are optional, make that limitation visible in the workbook or use a separate review column to identify missing information.
Make the sheet easier to use and maintain
Inventory lists become more useful when people can find and update records consistently. Excel tables include filter controls on the headings, letting a user show a particular category, location, or reorder status without deleting other rows. To keep headings visible while scrolling, select View > Freeze Panes > Freeze Top Row. This is especially helpful once the list extends beyond the screen.
Consider adding a unit-of-measure column if items are counted in different ways: each, box, roll, kilogram, or another unit. A quantity of 5 is ambiguous if one person means five individual units and another means five cartons. For items stored in more than one place, either track a separate row for each SKU-location combination or maintain a more detailed movement log. The choice depends on whether users need to know the total stock only or the quantity available at each location.
For a visible low-stock cue, select the status cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, then set a rule for “Reorder.” A color is a visual aid, not a replacement for the text label. If users may type categories or locations, Data > Data Validation can restrict entries to a list and reduce variations such as “Warehouse,” “warehouse,” and “Whse.” Agree on the approved choices before setting up the list.
Common inventory spreadsheet problems
Duplicate item names: Two products can have similar names, and one product may be described in several ways. Use a unique SKU and keep naming conventions consistent. If the same SKU is stocked in multiple locations, decide explicitly how the rows will distinguish those records; otherwise a location-level count can be confused with the total.
Counts drift from reality: A spreadsheet does not automatically know when stock is received, sold, used, damaged, or moved. Assign responsibility for recording changes and schedule physical counts appropriate to the operation. Record the count date, investigate meaningful differences, and update the quantity only after following the organization’s chosen process.
Reorder points are treated as purchase quantities: A reorder point answers when to review stock, not how much to order. A business may consider usage, supplier lead time, order constraints, and available storage when setting replenishment rules. The example’s thresholds are illustrative; they should not be copied as operational guidance.
Sorting only one column: Sorting a quantity column independently can detach counts from the item names beside them. Use the table’s header filter or sort command so the entire record moves together. Keep a backup before making broad edits, and be cautious when pasting data into a filtered list.
Unit cost is mistaken for an official valuation: Multiplying a count by a single unit cost gives a straightforward estimate, as shown here, but the result may not match the accounting method or reporting requirements used by an organization. Treat it as an operational estimate unless the appropriate accounting policy says otherwise. Confirm valuation methods and reporting questions with a qualified professional.
When a spreadsheet is enough—and when to move on
A spreadsheet can suit a small, relatively stable list when a limited number of people update it and the consequences of a delayed or incorrect update are manageable. It is inexpensive to start, familiar to many users, and easy to tailor with extra columns. For occasional stock checks, a basic file may be more practical than adopting a complex system.
Its limitations matter as activity grows. A shared workbook can be difficult to control if many people edit records simultaneously, changes are not logged consistently, or transactions need to connect to sales and purchasing. More advanced inventory software may offer permissions, transaction histories, barcode workflows, or links to other business systems; available features vary by product. Evaluate the actual workflow and data controls needed rather than assuming that a larger spreadsheet alone will solve them. Keep a dated backup and decide who can change the workbook’s structure and formulas.
Frequently Asked Questions
What should an inventory spreadsheet include?
A useful starting point is a unique item ID, item name, category, location, quantity, reorder point, and last-counted date. Add cost and calculated value if they are useful for the task, and include a unit of measure when quantities could be ambiguous.
How do I calculate inventory value in Excel?
With quantity in E2 and unit cost in G2, enter =E2*G2 in H2. For the sample cable row, the result is $81.00. This simple multiplication is an estimate and does not, by itself, establish an accounting valuation method.
Can Excel automatically flag items that need reordering?
Yes. With quantity in E2 and reorder point in F2, =IF(E2<=F2,"Reorder","OK") displays a status based on the values in that row. It flags a condition; it does not send an order or determine the appropriate order quantity.
Should each product have its own row?
Usually, yes: one row per distinct item record makes filtering and counting straightforward. If a product is stored in several locations and location-level quantities matter, use a separate row for each SKU-location combination or another clearly defined structure.
How often should I update the inventory spreadsheet?
Update it whenever stock changes if the list is used for day-to-day availability decisions, and record the date of physical counts. The right count schedule depends on how quickly stock moves and how costly errors are; a spreadsheet cannot prescribe one universal schedule.
Related
- How to Add Bullet Transitions to PowerPoint Slides
- Inventory Management Spreadsheet Template: Setup and Use
- Debt Snowball Spreadsheet: Build and Use One in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Share a Google Sheets Link and Control AccessTo share a Google Sheets link, open the sheet in a browser, select Share, choose who can access…
- How Replace Works in Excel: Find and ReplaceIn Excel, “replace” can mean the REPLACE function, which swaps characters at a specified position in text, or…
- Is Row Excel Open Source? Ownership and AlternativesIf you mean Row or Rows, the online spreadsheet often described as an Excel alternative, no: it is…
- Excel Automatic Goal Seek: Options for Repeated ModelsExcel Goal Seek does not automatically rerun whenever a worksheet changes: you must start it again, or use…
- Excel Data Entry: Enter and Manage Cell ValuesTo enter a value in Excel, select a cell, type a number, text, date, or formula, and press…
- Box Plot in Excel: Create and Read One Step by StepTo create a box plot in Excel for Microsoft 365 on Windows, place each group of raw numeric…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.