Super Sale WeekClaude Skills — 20% OFF
Tips

How to Make an Inventory Tracker in Excel: Step by Step

Powerdrill Bloom·
How to Make an Inventory Tracker in Excel: Step by Step

To make an inventory tracker in Excel, build two tables. The first lists your items, with columns for item ID, name, unit cost, opening stock, and reorder point. The second logs every movement, with the date, item ID, type (In or Out), and quantity. Then use SUMIFS to add up stock in and stock out, and subtract them to get stock on hand.

Add a status column that flags items at or below the reorder point, and highlight those rows with conditional formatting. A short summary at the top shows total stock value and how many items need reordering.

This guide covers each step, from the table structure to the formulas and alerts. It also walks through a full example, a small office supply stockroom, and a faster way to build the tracker from an export. Steps come from Microsoft's support pages, read on November 18, 2026.

What an inventory tracker does

An inventory tracker answers three questions at any moment:

  • How much of each item do we have? This is stock on hand.
  • What is it worth? This is stock value, based on unit cost.
  • What do we need to order? These are items at or below their reorder point.

A good tracker also keeps a history. When a count looks wrong, you can scroll back through the movement log and find when stock went in or out.

An inventory tracker in Excel works well for small and medium inventories. It suits stockrooms, small shops, office supplies, spare parts, and event equipment. Once you track thousands of items across several sites, a dedicated inventory system is usually a better fit.

Plan the tracker: two tables

Before you type any formulas, decide how the inventory tracker will be structured. Many simple trackers use one table with a single quantity column that people overwrite. That breaks quickly. Nobody knows when a number changed or why.

The better design separates the item list from the movements:

Table One row per Main columns
Items Product or part Item ID, Name, Category, Unit cost, Opening stock, Reorder point
Movements Stock change Date, Item ID, Type (In or Out), Quantity, Note

The Items table holds facts that rarely change. The Movements table grows every day. Formulas in the Items table read the Movements table and calculate the current stock.

This design has three benefits. You never overwrite a number. Every change has a date. And you can answer questions later, such as how much stock went out in March.

It also makes the inventory tracker safer to share. Staff only add rows to the Movements table. The Items table, with its formulas, can stay locked.

Put each table on its own sheet. Name the sheets "Items" and "Movements" so formulas are easy to read.

Build the item list as a table

Start with the Items sheet. Type the headers in row 1: Item ID, Name, Category, Unit cost, Opening stock, and Reorder point. Then enter one row per item.

Next, turn the range into an Excel table. Microsoft explains why: "To make managing and analyzing a group of related data easier, you can turn a range of cells into an Excel table." To create one:

  1. Select any cell in the data.
  2. Select Home > Format as Table.
  3. Pick a table style.
  4. Make sure the box for headers is checked, then select OK.

Name the table "Items" on the Table Design tab. Do the same for the movement log on the other sheet, and name it "Movements."

Tables help in two ways. They grow automatically when you add rows. And formulas in a table column fill down on their own. Microsoft says a formula entered in one cell "is instantly applied to all other cells in that table column."

Keep item IDs short and unique, such as A-101. Use them everywhere instead of names, because names get spelled differently.

Add drop-downs to the movement log

Typing errors are the main reason inventory counts drift. A drop-down list stops most of them.

Microsoft explains the purpose in one line. "Use data validation to restrict the type of data or the values that users enter into a cell, like a dropdown list." For the Type column:

  1. Select the Type column in the Movements table.
  2. Select Data > Data Validation.
  3. On the Settings tab, in the Allow box, select List.
  4. In the Source box, type In,Out.
  5. Make sure In-cell dropdown is checked, then select OK.

Do the same for the Item ID column, but point the source at the Item ID column of the Items table. Now people can only pick an item that exists.

Drop-downs also speed up entry. A stockroom clerk can log a delivery in a few seconds by picking the item and the type, then typing the quantity.

You can also restrict the Quantity column. In the Allow box, choose Whole Number and set a minimum of 1. That blocks blank and negative entries.

Calculate stock on hand with SUMIFS

Now add the calculated columns to the Items table. SUMIFS adds numbers that meet more than one condition. Microsoft's syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Add a column called "Stock in." In its first cell, enter:

=SUMIFS(Movements[Quantity],Movements[Item ID],[@[Item ID]],Movements[Type],"In")

This adds every quantity in the Movements table where the item ID matches the row and the type is In. The table fills the formula down for every item.

Add a "Stock out" column with the same formula, but change "In" to "Out."

Then add "On hand":

=[@[Opening stock]]+[@[Stock in]]-[@[Stock out]]

Finally, add "Stock value":

=[@[On hand]]*[@[Unit cost]]

Check one item by hand after you build the formulas. Filter the Movements table to that item, add the In and Out rows, and compare the result with the On hand column.

Flag items to reorder

An inventory tracker is most useful when it tells you what to do. Add a "Status" column with this formula:

=IF([@[On hand]]<=[@[Reorder point]],"Reorder","OK")

Now each row says Reorder or OK. Set reorder points based on how long an order takes to arrive and how much you use in that time.

Make the Reorder rows stand out with conditional formatting:

  1. Select the Status column.
  2. Select Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
  3. Type Reorder and pick a fill color, such as light red.
  4. Select OK.

Microsoft notes that when the Text that Contains dialog box appears, you "enter the formatting option you want to apply and click OK." The highlight updates each time the stock changes.

You can also sort or filter the Status column to see only Reorder rows before you place orders.

Look up item details with XLOOKUP

The movement log shows item IDs, which are hard to read. A lookup column adds the item name next to each movement.

Microsoft describes XLOOKUP as a way "to find things in a table or range by row." Its syntax is:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Add a "Name" column to the Movements table and enter:

=XLOOKUP([@[Item ID]],Items[Item ID],Items[Name],"Unknown ID")

If someone enters an ID that does not exist, the cell shows "Unknown ID." That makes errors easy to spot.

Microsoft notes that "XLOOKUP is not available in Excel 2016 and Excel 2019." On those versions, use INDEX and MATCH, or VLOOKUP with the item ID in the first column.

Add a summary at the top

The last piece of the inventory tracker is a summary. A short summary helps anyone see the state of the stock without scrolling. Add a few cells above the Items table, or on a separate "Summary" sheet.

Use these formulas:

  • Total stock value: =SUM(Items[Stock value])
  • Items to reorder: =COUNTIF(Items[Status],"Reorder")
  • Units moved out this month: =SUMIFS(Movements[Quantity],Movements[Type],"Out",Movements[Date],">="&DATE(2026,11,1))

COUNTIF follows a simple pattern. In Microsoft's words, it asks "Where do you want to look?" and "What do you want to look for?"

You can add a bar chart of stock value by category, too. Select a summary range and choose Insert > Chart. Keep the summary small, with three to five numbers that matter.

A worked example: an office supply stockroom

Here is a full example of the inventory tracker in use. A small office tracks five supply items. The Items table looks like this before November:

Item ID Name Unit cost Opening stock Reorder point
A-101 Notebook A5 $2.50 120 50
A-102 Ballpoint pens (box) $6.00 40 20
B-201 Printer paper (ream) $4.20 80 30
B-202 Toner cartridge $45.00 12 5
C-301 Shipping box (medium) $0.80 300 100

During November, the Movements table records these changes. Notebooks: 85 out and 50 in. Pens: 25 out. Paper: 60 out and 40 in. Toner: 9 out. Boxes: 180 out.

After the formulas run, the calculated columns show:

Item ID Stock in Stock out On hand Stock value Status
A-101 50 85 85 $212.50 OK
A-102 0 25 15 $90.00 Reorder
B-201 40 60 60 $252.00 OK
B-202 0 9 3 $135.00 Reorder
C-301 0 180 120 $96.00 OK

The example also shows why the movement log matters. Paper looks fine at 60 reams, but the log shows 60 went out in one month. If that pace continues, paper will hit its reorder point within weeks. A single quantity column would hide that trend.

Check one row by hand. Notebooks started at 120, gained 50, and lost 85, so 120 + 50 − 85 = 85. That matches the On hand column.

The summary shows a total stock value of $785.50 and 2 items to reorder. Pens and toner are both below their reorder points, so they go on this week's order.

Make an inventory tracker in Excel with AI

Building the tracker takes an hour or two by hand. The slow part is often the data: cleaning an export from a point-of-sale system, matching item names, and fixing duplicate IDs.

An AI tool can do that cleanup and the analysis for you. Here is the method in three steps, using Powerdrill Bloom.

Step 1: Upload your Excel, CSV, or PDF file

Upload your stock export, a supplier list, or last month's count sheet. The Excel AI Assistant works with spreadsheet files directly.

Include both the item list and the movements if you have them, even in separate files.

Uploading a stock export to make an inventory tracker in Excel with Powerdrill Bloom

Step 2: Ask for the tracker in natural language

Describe what you need. For example: "Build an inventory tracker with on-hand stock, stock value, and a reorder flag for items at or below 20 units." Ask it to list the items that need ordering this week.

You can also ask follow-up questions, such as which category ties up the most cash.

Step 3: Review the results and export them

Check the on-hand numbers for a few items against your last count. Then export the cleaned table back to Excel, or turn the findings into a short report or slides for your team.

This is useful when the inventory tracker has fallen behind. A monthly export from your sales system can show what really went out, which you can compare with the log.

Keep the two-table design for daily updates. Use the AI step when you need a fast cleanup or a monthly review.

Reviewing on-hand stock before exporting the inventory tracker

Common mistakes to avoid

Most problems with an inventory tracker come from how people enter data, not from the formulas. These are the problems that come up most often with an inventory tracker in Excel.

  • Overwriting quantities. Log every change as a new movement instead.
  • Names instead of IDs. "Pens" and "Pen box" become two items. Use unique IDs.
  • No opening stock. Without a starting count, every on-hand figure is wrong.
  • Mixed units. Track boxes or single pieces, not both, for the same item.
  • No regular count. Compare the tracker with a physical count every month or quarter.
  • Reorder points never reviewed. Update them when usage or supplier lead times change.
  • Deleted log rows. Never delete a movement to fix a mistake. Add a correcting entry with a note instead.
  • Formulas typed over. Protect the Items sheet so nobody replaces a formula with a number.

For finding items that sit on the shelf too long, see our guide on how to spot slow-moving inventory. For forecasting demand, see our list of AI tools for inventory and demand forecasting.

When your stock data sits in a messy export, try Powerdrill Bloom to clean it into a ready tracker.

Frequently asked questions

How do I create a simple inventory tracker in Excel?

Make an Items table with item ID, name, unit cost, opening stock, and reorder point. Add a Movements table that logs each change. Then use SUMIFS to calculate stock in, stock out, and stock on hand.

What formula calculates stock on hand in Excel?

Stock on hand equals opening stock plus stock in minus stock out. Use SUMIFS to total the In and Out quantities for each item from the movement log.

How do I get Excel to flag low stock?

Add a Status column with an IF formula that returns "Reorder" when stock on hand is at or below the reorder point. Then highlight those cells with conditional formatting.

How often should I update an inventory tracker?

Log each movement when it happens, or at least once a day. Then compare the tracker with a physical count every month or quarter, and fix any gaps with an adjustment entry.

Can I track inventory in Excel for multiple locations?

Yes. Add a Location column to the Movements table, and add Location as a second condition in SUMIFS. Each item then gets an on-hand figure per location.

Sources: Microsoft Support, Overview of Excel tables · Microsoft Support, Apply data validation to cells · Microsoft Support, SUMIFS function · Microsoft Support, XLOOKUP function · Microsoft Support, Use conditional formatting to highlight information in Excel · Microsoft Support, Use the COUNTIF function in Microsoft Excel.