The fastest way to get an Excel inventory tracker is to use a built-in template: open Excel, go to File > New, and search for “inventory.” You’ll find options like “Inventory List” and “Inventory with Categories,” which you can download and start using immediately. For a more customized tracker, you can build your own in about 15 minutes using basic Excel tables, formulas, and conditional formatting—this article shows you both approaches.
Why Use a Spreadsheet for Inventory at All
Small businesses, home organizers, and even hobbyists often track inventory in Excel because it is flexible and requires no extra software. Unlike dedicated inventory management systems that cost monthly fees, Excel runs on a computer you already own. You can track product names, quantities, reorder levels, costs, and locations in one place. Spreadsheets also allow you to share the file with team members via OneDrive or SharePoint, so multiple people can edit simultaneously in Excel for Microsoft 365. The main trade-off is that Excel is manual—there is no barcode scanning or automatic stock deduction unless you build those features with macros or link to other tools.
Built-In Excel Inventory Templates: What You Get
Microsoft provides several free inventory templates inside Excel. To find them, click File > New and type “inventory” in the search box. The most common ones include:
- Inventory List: A simple table with columns for item name, category, quantity, unit cost, and total value.
- Inventory with Categories: Adds a category column and a summary sheet that totals value by category.
- Inventory Manager: A more advanced template with a dashboard, reorder alerts, and a place to record stock movements.
These templates are pre-formatted with formulas, so you can start typing your data right away. However, they are not always tailored to your specific needs—for example, if you need to track expiration dates or serial numbers, you may need to add columns yourself.
How to Build a Custom Inventory Tracker From Scratch
If the built-in templates don’t fit, create your own. Here’s a step-by-step method that works in Excel for Microsoft 365 on Windows (and similarly on Mac).
- Set up your columns. In row 1, enter headers: Item ID, Item Name, Category, Quantity on Hand, Reorder Level, Unit Cost, and Total Value. Add any extra columns you need, like Location or Expiration Date.
- Format as a table. Select the range (e.g., A1:G100), then press Ctrl+T (or Cmd+T on Mac). Check “My table has headers.” This makes filtering and sorting automatic.
- Add a Total Value formula. In cell G2 (assuming your table starts at row 2), enter
=E2*F2(Quantity × Unit Cost). Drag the formula down the column. Excel will auto-fill it for new rows if you type in the table. - Add a summary row. Below the table, use
=SUM(G2:G100)to get the total inventory value. If your table has more rows, adjust the range or use=SUM(Table1[Total Value])if you named the table. - Set up conditional formatting for low stock. Select the Quantity on Hand column (E2:E100). Go to Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter your reorder level (e.g., 10) and choose a red fill. This visually flags items that need reordering.
This basic tracker works for most small inventories. You can add a dropdown list for categories using Data > Data Validation, or create a pivot table to analyze sales by category.
Choosing the Right Template for Your Situation
Not all inventory trackers are the same. Before you download or build one, consider what you actually need to track:
- For a small retail shop: You need columns for SKU, product name, cost, selling price, quantity, and reorder point. You may also want a “Low Stock” column that uses an IF formula.
- For a home pantry or craft supplies: A simple list with categories and quantities is enough. You might not need cost tracking at all.
- For a business with multiple locations: Add a “Location” column and use filters to view each store separately. You can also create separate sheets for each location.
- For items with serial numbers or expiration dates: Add columns for those and use conditional formatting to highlight expired or soon-to-expire items.
If your inventory is complex—like thousands of SKUs with multiple variants—Excel can still work, but you may hit performance issues. In that case, consider a database or dedicated software.
Common Problems and How to Fix Them
People often run into the same issues when using Excel for inventory:
- Duplicate entries: Use conditional formatting to highlight duplicates in the Item ID column. Select the column, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Formulas not updating: If you add rows below the table, the SUM formula may not include them. Solution: use Excel tables (Ctrl+T) so formulas auto-expand, or use a dynamic range like
=SUM(G:G)(but be careful not to include the header). - Accidental data deletion: Protect the sheet by going to Review > Protect Sheet. You can allow editing only in certain cells.
- File becomes slow: If you have thousands of rows, avoid whole-column references like
=SUM(G:G)because they calculate over a million cells. Instead, use a defined range or a table.
Costs and Alternatives: Free vs. Paid
Excel inventory templates are free if you already have Microsoft 365 or a standalone Excel license. If you don’t, you can use Excel for the web for free with a Microsoft account, but the templates may be limited. Alternatives include Google Sheets, which has similar templates and is free, and dedicated inventory software like Odoo, Zoho Inventory, or inFlow, which offer barcode scanning and multi-user access but cost money. For a single user or a small operation, Excel is often the most cost-effective choice—there’s no monthly fee, and you can customize it to your exact workflow.
Safety and Accuracy: Keep Your Data Reliable
Inventory data is only useful if it’s accurate. Here are a few practices to keep your tracker reliable:
- Always back up your file—save a copy to OneDrive or an external drive regularly.
- Use data validation to restrict inputs, such as allowing only whole numbers for quantity. Select the quantity column, go to Data > Data Validation, choose “Whole number” and set a minimum of 0.
- If you use macros (VBA) to automate data entry, save a copy of your workbook before running any macro, because macros cannot be undone with Ctrl+Z. Test macros on a copy first.
- Periodically reconcile your spreadsheet with a physical count. A common method is to print a count sheet and compare it to the actual stock.
Frequently Asked Questions
Can I use Excel to track inventory with barcodes? Yes, but not out of the box. You can purchase a USB barcode scanner that types the scanned number into the selected cell, or you can use Excel’s built-in camera feature to create a QR code. For a full barcode system, you may need a macro that looks up the scanned code in a product table.
What is the difference between an inventory list and an inventory manager template? An inventory list is a simple table of items and quantities. An inventory manager typically includes a dashboard with charts, a reorder alert system, and a separate sheet for recording stock in/out transactions. The manager is more powerful but more complex.
How do I track inventory that has multiple variants, like sizes or colors? Add a column for “Variant” or “Size” and combine it with the Item Name in a unique ID. For example, “T-Shirt-M-Blue” as the Item ID. This keeps each variant as a separate row, which is easier for tracking quantities.
Can I share my Excel inventory tracker with my team? Yes. Save the file to OneDrive or SharePoint and use the Share button in Excel. In Microsoft 365, multiple people can edit simultaneously. To prevent accidental changes, use Review > Protect Sheet or set permissions to “View only” for some users.
What if I need to track inventory across multiple warehouses? Add a “Location” column and use filters or a pivot table to see totals per location. You can also create a separate sheet for each warehouse and use a summary sheet with formulas like =SUM(Warehouse1!E2:E100) to combine totals.
Is Excel better than Google Sheets for inventory? It depends on your needs. Excel has more advanced formulas and offline access, while Google Sheets is free, works in a browser, and has easier sharing. For most small inventories, either works. If you need to use Excel-specific features like Power Query or complex macros, choose Excel.
Related
- Excel Conditional Formatting Per Row: How To
- Excel Conditional Formatting for Duplicates: How-To
- Excel Conditional Formatting for Past Dates: How to Set It Up
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 Compare Differences in Excel: Cells and SheetsTo compare values in the same row, enter a comparison formula such as =IF(A2=B2,"Match","Different") in a spare column…
- How to Remove Merged Cells in Google SheetsTo remove merged cells in Google Sheets, select a merged cell or range, then choose Format > Merge…
- How to Search in Google SheetsTo search in Google Sheets, press Ctrl+F on Windows or Command+F on Mac, enter a word or number,…
- How to Locate Duplicates in Google Sheets: A WalkthroughTo locate duplicates in Google Sheets, select the cells you want to check, choose Format > Conditional formatting,…
- Excel Data Validation Custom Lists: Setup and TipsIn Excel for Microsoft 365 on Windows, create a custom drop-down by selecting the cells, choosing Data >…
- How to Add a Company Logo as a Watermark in Microsoft WordTo add a company logo as a watermark in Word for Microsoft 365 on Windows, open the document…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.