Excel Inventory Management: How To Track Inventory in Excel

Inventory is one of the most important parts of running a business—and one of the most challenging to manage. Overstocking ties up cash, while stockouts lead to missed sales and unhappy customers. Keeping track of what you have, what you’ve sold, and when you need to reorder is critical. 

Although advanced inventory management software exists, you might already have the tool you need: Microsoft Excel. In this guide, learn how to use Excel to organize your inventory, prevent stockouts, improve operational efficiency, and minimize excess inventory. You’ll also explore helpful templates and formulas and discover when it might be time to upgrade to a dedicated inventory management system.

How to use Excel for inventory management

An Excel inventory management system is simply a spreadsheet that lists each product and its key details. This inventory data typically includes product name, stock keeping unit (SKU), supplier, costs, retail prices, and quantity in stock. Maintaining this data offers a real-time picture of your business’s inventory position.

Open Excel and create a new workbook—your first sheet becomes your main inventory database. Each row represents a unique product, and each column represents a type of information—like price, supplier, or quantity—that helps track and manage your products efficiently. As you work with the spreadsheet, you’ll determine which columns you want to include. 

Your spreadsheet needs specific columns to track what matters:

• Unique identifier. A product ID or SKU unique to each item.

• Product name. The item’s name.

• Description. Size, color, model, and other characteristics.

• Supplier information. The vendor or supplier name.

• Unit price. The cost your business pays per item.

• Retail price. The price customers pay.

• Quantity in stock. Current number of units available.

• Inventory value. Total value of the stock (Unit price x Quantity in stock).

• Reorder level. Minimum stock quantity that triggers a reorder.

After building this framework, populate it carefully with your product data. As you make sales or receive new stock, regularly update this sheet to keep it accurate. Your inventory system works only if you maintain it consistently—reliable data entry keeps you from overselling items or missing reorder deadlines.

Excel template for inventory management

To get started, use this effective inventory management template. 

This template includes an inventory list sheet, a sales orders sheet, and a purchase orders sheet. Keeping sales orders and purchase orders separate creates a clear audit trail and lets you use formulas that automatically update the main inventory list. This setup helps you manage inventory more accurately and minimize errors that come from manual data entry.

The template divides your tracking into three interconnected sheets, each serving a specific purpose:

Inventory list

An inventory list is a detailed, itemized record of all the goods your business has in stock. It forms the foundation of your Excel inventory management system by providing an accurate picture of your current inventory. Maintaining a comprehensive inventory list helps you monitor inventory levels as a whole, ensuring the right products are available to meet customer demand. Each entry typically includes key details, such as:

• Stock keeping unit (SKU)

• Product name

• Product description

• Quantity in stock

• Cost per unit

• Total value

• Reorder level

• Supplier information 

Sales Orders sheet

Whenever you sell a product, log it in the Sales Orders sheet. This record ensures that inventory levels decrease automatically when linked formulas are in place. 

Record these details, for every sale:

• Sales order ID

• Date

• SKU

• Quantity sold

Purchase Orders sheet

When you receive new stock, record it in the Purchase Orders sheet. Similar to the Sales Orders sheet, it helps you track stock increases and provides a record of purchases for reconciliation. 

Track these essentials for each shipment:

• Purchase order ID

• Date

• SKU

• Quantity received

Excel inventory management best practices

1. Establish a unique identifier for each item
2. Audit your physical inventory regularly
3. Use data validation to minimize errors
4. Set up a dashboard with key metrics
5. Back up your Excel file

Although Excel is a useful tool, maintaining an accurate and reliable inventory system depends on consistent updates and careful verification. Five practices keep your spreadsheet accurate and actionable:

1. Establish a unique identifier for each item

Every product variation—size, color, model, etc.—needs a unique SKU or product ID. This prevents confusion and ensures that when you sell a medium red t-shirt, your inventory template reflects a decrease only for that specific item, not for all t-shirts. 

2. Audit your physical inventory regularly

Even with a well-designed spreadsheet, inventory discrepancies can occur due to theft, damage, or data entry mistakes. Schedule regular physical inventory counts—quarterly or monthly—and compare the numbers to the data in your Excel inventory template. This verification step keeps your records accurate and highlights trends or recurring issues.

3. Use data validation to minimize errors

Typos and inconsistencies in your spreadsheet lead to inventory errors that cost real money. Excel’s data validation feature (under “Data” in Excel’s top toolbar) prevents these mistakes by creating drop-down menus for columns like “Category” or “Supplier.” This ensures the data entered is consistent, reducing typos and simplifying analysis. When every entry follows the same format, it’s easier to sort, filter, and summarize your inventory data.

4. Set up a dashboard with key metrics

Spreadsheet data is useless if you can’t quickly spot what matters—which products are moving, what’s eating up cash, when to reorder. Set up a separate sheet in your Excel workbook to use as a dashboard. You can create charts and tables that show your most important key metrics, such as total inventory value, top-selling products, items low in stock, and your inventory turnover rate (cost of goods sold divided by average inventory). Visualizing this data with charts or tables gives you quick insight into performance and helps identify areas for improvement.

5. Back up your Excel file

Because an Excel file is a single point of failure, create backups often. Store copies in a secure cloud service or external drive. Regular backups also help with version control issues if multiple users want access to the file.

Useful formulas and features for Excel inventory management

• SUMIF
• VLOOKUP
• IF statement
• SUMPRODUCT
• PivotTable

Excel formulas can automate calculations, save time, and reduce the risk of manual errors, while built-in features like PivotTables help analyze your data visually. You can enter these formulas directly into cells or use tables to summarize trends efficiently. 

Once you’ve downloaded the template above, use it to see these examples in action:

SUMIF

You can use the SUMIF function to connect your Sales Orders and Purchase Orders sheets to your main Inventory List so that stock levels update automatically. This formula is the key to automating your spreadsheet. It’s one of the most useful formulas for inventory because it lets you sum values in a range that meet specific conditions. For example, you can use it to calculate the total quantity of a specific SKU sold from your Sales Orders sheet. 

For this to work, your main Inventory List sheet should have a Starting Quantity column (for your initial count) and formula-driven columns for Total Received, Total Sold, and Current Quantity in Stock. 

Formula:

=SUMIF(range, criteria, [sum_range])

Note that brackets in formula syntax—like [sum_range]—indicate optional arguments that Excel can run without.

Example:

This formula tells you the quantity of a given product you’ve received.

=SUMIF(PurchaseOrders!C:C, A2, PurchaseOrders!D:D)

What it does: This formula looks at the entire “SKU” column (C:C) on your Purchase Orders sheet for a match to the SKU in cell A2 (your product). It then sums all the corresponding values from the Quantity Received column (D:D).

Where to put it: On your Inventory List sheet, in the Total Received column (cell D2).

You would use a similar formula in your Total Sold column (e.g., in cell E2) to pull data from the Sales Orders sheet:

=SUMIF(SalesOrders!C:C, A2, SalesOrders!D:D)

Your Current Quantity in Stock column on your main inventory sheet then becomes a simple formula: 

=[Starting Quantity] + [Total Received] – [Total Sold], or =C2+D2-E2

VLOOKUP

Typing product details, repeatedly across multiple sheets creates inconsistencies and typos. VLOOKUP pulls accurate information automatically. The VLOOKUP formula is perfect for pulling product information from your main inventory list into another sheet, like a sales invoice or a new order form. Let’s use the example of the Sales Invoice sheet (included in the template). You can enter the SKU and quantity in the Excel sheet for your sales invoice, and have the tool pull the corresponding product name and retail price from your main inventory sheet. 

Formula:

=VLOOKUP(lookup value, table array, column index number, [range lookup])

Example 1:

This formula allows you to pull in the Product Name.

=VLOOKUP(A2, InventoryList!A:L, 2, FALSE)

What it does: 

• A2 looks at the SKU you typed in cell A2 of your invoice (“DB-015”). 

• InventoryList!A:L searches for that SKU in the entire A:L column range on your main inventory sheet. (It must search in the first column of the range you select, which is why we start with A.) 

• The 2 indicates that when the search finds DB-015, it counts two columns to the right (column B) and returns the value it finds there (“Drill bit set”).

• False ensures it finds only an exact match for the SKU.

Where to put it: On your Sales Invoice sheet, in the Product Name column (cell B2).

Example 2:

This formula allows you to pull in the price.

=VLOOKUP(A2, InventoryList!A:L, 10, FALSE)

What it does: This is the same search, but the 10 tells Excel to return the value from the 10th column (column J) which is your retail price: $39.99.

Where to put it: On your Sales Invoice sheet, in the Retail Price column (cell D2).

IF statement

An IF statement can automatically alert you when an item reaches its reorder point. You’ll place this formula in a Reorder Status column on your main Inventory List sheet.

Formula:

=IF(logical test, value if true, value if false)

Example:

This formula alerts you if your stock has dropped below the reorder point.

=IF(F2<=G2, “Reorder” , “OK”)

What it does: This formula checks if the value in your Current Quantity in Stock column (cell F2) is less than or equal to the value in your Reorder Level column (cell G2). If it is, it displays “Reorder”; otherwise, it displays “OK.”

Where to put it: On your main inventory sheet, in the Reorder Status column (cell H2).

SUMPRODUCT

Knowing your total inventory value helps you understand how much capital is tied up in stock. The SUMPRODUCT function calculates this in a single cell. It multiplies each item’s quantity by its unit price and then adds all those results together.

Formula:

This formula

=SUMPRODUCT(array1, [array2], [array3], …)

Example:

This formula allows you to find your total inventory value.

=SUMPRODUCT(F2:F13, I2:I13)

What it does: 

• F2:F13 is your Current Qty in Stock column. 

• I2:I13 is your Unit Price column.

• Excel multiplies F2 x I2 (Hammer: 40 x $12.50), then F3 x I3 (Screws: 45 x $8.00), and so on for every row.

• It then adds all totals to give you one grand total inventory value.

• Where to put it: In any open cell on your main inventory sheet where you can quickly view your total inventory value. For example, you may enter it under all your other information in cell K15 (adding a description for the data point in J15).

PivotTable

Although it’s not a formula, PivotTable is one of Excel’s most powerful tools because it’s used for analysis. It lets you quickly summarize, group, and analyze large amounts of inventory or sales data. The best sheet to analyze is your sales orders log, as it can quickly show you top-selling products. 

For example, to find out how many units of each SKU you have sold:

1. Go to your Sales Orders sheet.

2. Select all your sales data (cells A1 to D4 in our sample template).

3. Go to the Insert tab and click PivotTable.

4. A pop-up will ask you where to put it. Choose “New Worksheet” and click OK.

5. A new sheet will appear with a “Pivot Table Fields” pane on the right.

6. Drag the SKU field into the Rows area, and drag the Quantity Sold field into the Values area.

7. Excel will instantly create a simple report that shows your total sales for each item.

PivotTable reports update automatically when you refresh it after adding new sales data. If you add multiple tables to this new worksheet you’ve created, it can become your tracking dashboard.

Alternatives to Excel inventory management

Excel is a great starting point for small business owners—it’s inexpensive, flexible, and familiar. But as your business grows, Excel spreadsheets can become difficult to maintain. The need for manual data entry increases the risk of human error, and managing hundreds (or thousands) of products across multiple sales channels can quickly get unwieldy.

Excel also lacks several advanced features that larger or more complex businesses often need. It doesn’t automatically sync real-time data across locations or users, and collaboration can lead to version control issues when multiple users edit the same file. Critical features like barcode scanner integration or automatic tracking of expiration dates require manual workarounds. There’s also a risk of data loss if a file is deleted or becomes corrupted.

That’s where dedicated inventory management software comes in. Unlike a manual spreadsheet, this type of software provides a centralized system to automatically track stock levels, manage sales, and process orders in real-time. Many of these platforms, such as Shopify’s built-in inventory management tools, sync data across your online and in-person sales channels. 

Shopify’s inventory management features include:

• Track sales and automatically update stock levels in real time across your online store, social media, and physical locations

• Provide detailed reports and analytics on your sales and inventory performance

• Generate purchase orders to send to your suppliers when stock is low

• Support barcode scanner integration for faster and more accurate receiving and order fulfillment

• Allow multiuser access with defined permissions, eliminating version control problems

Although Excel is excellent for building your initial system, a platform like Shopify is designed to scale with your business. It reduces manual effort, improves accuracy, and gives you real-time visibility into your inventory across every channel—all within one centralized platform.