To create an inventory spreadsheet in Excel, set up columns for each item’s SKU, description, location, quantity, reorder point, and cost; convert the list to a table; then add formulas to calculate stock value and flag items that need reordering. Before you start, decide what you need to track and gather the latest item counts, costs, and supplier details. A consistent setup makes stock easier to find, count, and replenish.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The steps use the standard desktop ribbon; Excel for Mac has the same core features, though some shortcuts and ribbon placement differ.
- A computer, phone, or paper record to refer to while entering item details and counting stock.
- A consistent identifier for each product, such as a SKU, barcode, or internal item code. If you do not already use one, create a unique code for every item.
- Current quantities, reorder points, unit costs, storage locations, and supplier information, as applicable. You can add unknown details later.
Step-by-Step
- Decide what the spreadsheet must answer. For a small stockroom, you may mainly need to know what is on hand and what to buy. A shop may also need a selling price, supplier, and product category. Keep the first version focused: every column should help identify, count, locate, value, or replenish stock. Avoid collecting details that nobody will maintain.
- Create a workbook and label the columns. Open a blank workbook in Excel and enter one heading per column in row 1. A useful starter layout is: A, SKU; B, Item; C, Category; D, Location; E, On hand; F, Reorder point; G, Unit cost; H, Stock value; I, Supplier; J, Last counted; K, Status. Use one row per distinct item or product variant. If sizes or colors are stocked separately, give each variant its own SKU and row.
- Make item identifiers unique and descriptions clear. A SKU should identify one stock-keeping unit only; do not reuse the same code for two products. Write descriptions that distinguish similar items, such as “Blue nitrile gloves, medium” rather than just “Gloves.” Use a predictable location format, for example “Aisle 2, Shelf B,” and use the same spelling every time. If there is no SKU system yet, create simple codes such as GLV-M-BLU, record them consistently, and avoid changing them once transactions refer to them.
- Enter the starting inventory carefully. Count items in their actual storage locations and enter the quantity in On hand. Put the minimum quantity that should trigger a purchase in Reorder point; this is a working threshold, not a prediction of demand. Enter Unit cost as the cost per single unit, not the cost of a case, unless your quantity is also recorded in cases. Enter dates as real Excel dates, and record the date each item was counted. For products with different lots or expiry dates, use separate rows or a dedicated lot-tracking sheet rather than combining quantities that need separate handling.
- Format the list as an Excel table. Click a cell in the heading-and-data range, then choose Insert > Table. Confirm that the range is correct and select “My table has headers,” then choose OK. A table adds filter buttons, consistent formatting, and automatic expansion when you add rows. On Windows, Ctrl+T also opens the Create Table dialog; on Mac, use Insert > Table if that shortcut differs in your setup. Give the table a meaningful name from Table Design > Table Name, such as Inventory, so it is easy to identify if you later build reports.
- Calculate the value of each line. Format column G as currency using Home > Number, and make sure every cost is a per-unit amount. With On hand in E, Unit cost in G, and Stock value in H, if E2 is 18 and G2 is $4.50, enter this in H2:
=E2*G2. The result is $81.00. In a table, Excel will usually fill the formula down the Stock value column; otherwise copy H2 down the rows you use. The value is an estimate based on the unit costs entered, so update those costs when relevant. - Add a reorder flag. With quantities in E, reorder points in F, and Status in K, if E2 contains 18 and F2 contains 20, enter this in K2:
=IF(E2<=F2,"Reorder","OK"). The result is “Reorder,” because 18 is no greater than the threshold of 20. If the quantity is above the threshold, the result is “OK.” Copy the formula down if the table does not fill it automatically. Decide whether a quantity equal to the reorder point should trigger an alert; this formula treats it as a reorder, which is often a useful conservative rule. - Make important values easy to spot. To highlight low-stock rows or status cells, select the relevant Status cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter Reorder, choose a visible format, and confirm. You can also use a simple fill color for items needing review, but retain the text label so the meaning is clear when printed or viewed without color. To keep headings visible while scrolling, choose View > Freeze Panes > Freeze Top Row.
- Add a controlled list for repeated entries. A dropdown can reduce variations such as “Back room,” “Backroom,” and “Back Room.” Enter approved locations in unused cells on a separate worksheet, select the Location cells, then choose Data > Data Validation. Set Allow to List and use the range containing the approved locations as the Source. You can use the same approach for categories or suppliers if their lists are short and maintained. If you use a comma-separated list directly in Source, keep the entries brief and update the rule when choices change.
- Check the totals and test the workflow. With Stock value in H and example values of $81 in H2 and $90 in H3, enter
=SUM(H2:H100)in a clearly labeled summary cell; if the remaining cells are blank, the result is $171. If Status is in K and K2 says Reorder while K3 says OK, enter=COUNTIF(K2:K100,"Reorder")in another summary cell; the result is 1. These ranges allow room for more rows. Test the sheet by changing one quantity below its threshold, confirming the alert changes, and adding a sample item at the bottom of the table. Remove test data before relying on the workbook. - Save a clean working copy and establish an update routine. Use File > Save As to give the workbook a recognizable name and store it in a location available to the people who maintain stock. Keep one authoritative file instead of several emailed copies. Decide who records incoming deliveries, sales or removals, and count corrections, and how often a physical count will be compared with the sheet. For a shared file, agree on how simultaneous edits are handled and avoid replacing the current file with an older copy.
Common Mistakes
Combining different products in one row: One row should represent one identifiable item or variant. If one row combines several sizes, users cannot tell which size needs replenishing. Using descriptions as identifiers: Descriptions can be edited or duplicated, so use a stable SKU or code as the reference. Mixing units: Counting some items as individual units and others as boxes makes totals and costs misleading. State the unit clearly and keep it consistent, or add a Unit column when products use different measures.
Another frequent error is treating a reorder point as the quantity to order. The flag only says that stock has reached a threshold; it does not calculate a purchase quantity or account for delivery time. Also avoid typing over formula cells with manual numbers, leaving old quantities after stock moves, or entering costs for a case beside quantities recorded as single items. Finally, do not assume a colored cell is an alert if the conditional-formatting rule was applied to the wrong range. Test the rule with a known low quantity.
Tips
Keep headings short but unambiguous, use the same date and location conventions throughout, and format quantities as numbers rather than text. Use filters on the table to show one category, supplier, or location at a time; clear filters before concluding that an item is missing. Protecting formula cells can help prevent accidental edits, but only do this if the people maintaining the sheet know how to enter stock changes in the intended columns.
For a frequently changing inventory, add transaction history rather than overwriting quantities without a record. A separate log can include date, SKU, quantity added or removed, reason, and person making the change. You can then use it to investigate discrepancies. Keep a backup before making major structural changes, and test new formulas on a copy. If the stock list becomes too large or several people need reliable simultaneous updates, consider whether inventory software is a better fit than a manually maintained workbook.
Troubleshooting
The formula appears as text: Check that the cell does not have the Text number format. Change it to General, select the cell, press F2, and then press Enter to make Excel interpret the formula. Confirm there is no apostrophe before the equals sign.
The stock value or status is wrong: Check that the formula references the intended row and columns, and that quantity and cost cells contain numbers rather than text. Check the unit basis too: multiplying 18 units by a case price will not give a meaningful unit value. If a formula was copied, compare its row references with the row’s data.
A reorder alert is missing: Confirm the quantity and reorder point are numeric, the Status formula was filled down, and the conditional-formatting rule covers the cells where the status appears. If the status text says Reorder but has no color, the formula is working and the formatting rule needs attention. If you edited a threshold, recalculate by pressing F9 on Windows; on Mac, use the equivalent recalculation command available in your Excel keyboard settings.
A count does not match the shelf: Recount the item and verify its location, SKU, and unit of measure. Check recent deliveries, sales, returns, damaged goods, and transfers that may not have been recorded. Correct the count only after identifying the likely cause, and record the date and reason for the adjustment so the same discrepancy is easier to trace next time.
The table or dropdown does not include a new item: Add the item directly in the row below the table so Excel can expand it, or resize the table from Table Design > Resize Table. For a dropdown, check that its Data Validation source range includes the new approved choice. If filters seem to hide the new row, clear the active filters and confirm the row is inside the table.
Frequently Asked Questions
What columns should an inventory spreadsheet have?
Start with a unique SKU, item description, location, quantity on hand, and reorder point. Add unit cost, supplier, category, last-counted date, and status if those fields help with your purchasing or stock checks.
Can I make an inventory spreadsheet in Excel without formulas?
Yes. You can manually record quantities and filter the table, but formulas reduce repeated arithmetic and make reorder thresholds more visible. Check formula results against a few manually calculated examples before using them for decisions.
How often should I update inventory?
Update the sheet whenever stock is received, sold, moved, or written off if you need a current balance. Also schedule physical counts. The right count frequency depends on how quickly stock moves and how costly an inaccurate count would be.
Should each product have its own SKU?
Yes, each distinct item you need to count separately should have a unique identifier. Variants such as different sizes or colors usually need separate SKUs if their quantities are managed independently.
Can more than one person use the workbook?
Yes, a workbook stored in OneDrive or SharePoint can support shared editing when users have access and use compatible Excel versions. Agree on who owns updates and avoid maintaining separate competing copies. For complex multi-user stock control, a dedicated inventory system may be more dependable.
How do I print or share only part of the inventory?
Use the table filters to display the category or location you need, then print the visible range or save a separate copy for sharing. Before sending a file, check for supplier, cost, or other information that recipients do not need to see.
Related
- Excel Online CSV Import: Steps, Settings, and Fixes
- Habit & Goal Tracker Spreadsheet: Build One in Excel
- How to Make a Cool Timeline in PowerPoint That Looks Great
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Power Query: Keep Only Selected ColumnsTo keep only certain columns in Excel Power Query, open the query, select the columns you want, then…
- Create an Outlook Distribution List: Step-by-StepTo create a personal distribution list in Outlook, make a contact group or contact list, give it a…
- How to Find a P-Value in ExcelTo find a p-value in Excel, use T.TEST for a two-sample or paired t-test, or use a distribution…
- How to Make Cell Borders White in Microsoft Excel 365To make cell borders white in Excel, select the cells, open Home > Borders > More Borders, choose…
- Excel Time Difference Between Two Dates and Times ExplainedTo find the elapsed time between two date-and-time values in Excel, subtract the earlier value from the later…
- How to Open a CSV File in Excel Without Losing DataTo open a CSV in Excel, double-click the file or use Excel’s File > Open command. If the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.