Make an Inventory Tracker in Excel

An inventory tracker in Excel works best as two tables: a product list that shows what you have, and a log that records every delivery and sale. SUMIF adds up the log for each product, so current stock is always calculated, never typed. This guide builds a tracker for a small office stockroom with reorder alerts, stock value and lookups. The same build works for a shop, a warehouse shelf or equipment loans.

How the inventory tracker is organised#

Split the tracker into two sheets, because they do different jobs.

Keeping movements in a log, rather than editing a stock number, gives you a history. If a count looks wrong, you can see every change that led to it.

Step 1: Set up the product list#

The product list holds the facts about each item. Give every item a unique SKU (stock keeping unit), a short code that never changes, even if the name does.

Opening is the stock on the day you start the tracker, from a physical count. Leave columns D, E, F and H free for the formulas below.

Step 2: Log every movement#

The Log sheet gets one row each time stock arrives or leaves. Use separate In and Out columns so the numbers stay positive and easy to check.

Column E is a lookup, so nobody types item names twice. In E2:

=XLOOKUP(B2,Products!$A$2:$A$100,Products!$B$2:$B$100,"Unknown SKU")

XLOOKUP finds the SKU in the product list and returns its name. The fourth argument shows "Unknown SKU" instead of #N/A when a code is mistyped, which is itself a useful check. XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365. In older versions, use VLOOKUP with FALSE for an exact match:

=VLOOKUP(B2,Products!$A$2:$B$100,2,FALSE)

The XLOOKUP function guide covers the other options, such as searching from the bottom up.

Step 3: Total stock in and out with SUMIF#

SUMIF adds the numbers in rows that match a condition. Back on the Products sheet, in D2 (Stock in):

=SUMIF(Log!$B$2:$B$500,A2,Log!$C$2:$C$500)

It checks the SKU column of the log, keeps the rows that match A2, and adds their In amounts. In E2 (Stock out), use the Out column, Log!$D$2:$D$500, instead. Fill both down to row 5.

The ranges run to row 500, longer than the log, so new movements are counted without editing the formula. The $ signs keep them fixed when you fill down, while A2 moves to A3, A4 and A5. See how to use SUMIF in Excel and the SUMIF function guide for more criteria.

Step 4: Calculate current stock and reorder alerts#

Current stock is the opening count plus everything in, minus everything out. In F2:

=C2+D2-E2

Then compare it with the reorder level in H2:

=IF(F2<=G2,"Reorder","OK")

Fill both down. The finished summary:

<= means the alert shows when stock reaches the reorder level, not only when it falls below it. Choose the level so a new order arrives before you run out.

Step 5: Color the items to reorder#

Conditional formatting makes the alerts visible from across the room.

1. Select the product rows, A2:J5.
2. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter =$H2="Reorder", choose a light red fill and click OK.

The $ before H makes every cell in the row check the Status column. When a delivery is logged and stock climbs back above the reorder level, the color disappears by itself.

Step 6: Work out the stock value#

Stock value is current stock times unit cost. In J2, type =F2*I2 and fill down, then total it with =SUM(J2:J5).

This uses the latest unit cost for every item. Accounting methods that value each delivery at its own cost need a more detailed log.

How to keep the tracker accurate#

• Count regularly. Compare a physical count with the Current column. Log a correction (In or Out) with a note rather than changing the opening stock.
• Use a drop-down for SKUs in the log (Data > Data Validation > List, with the product SKUs as the source), so codes can't be mistyped.
• Turn both tables into Excel tables with Ctrl+T (Mac: ⌘T), so new rows are formatted and included automatically.
• Keep each SKU once in the product list. A duplicate SKU makes XLOOKUP return only the first match, and both rows would show the same SUMIF totals. Sorting either table is safe, because SUMIF and XLOOKUP find each SKU wherever it sits.

Stock tracking with SUMIF and lookups is a classic task in Excel skills for sales and operations roles, and a common format in an Excel test for a job interview. For the next project, an invoice with formulas uses the same lookups to price each line, and a gradebook practises IF and AVERAGE.

Practise it#

Do the exercise on this page to calculate current stock from a log. Then practise VLOOKUP exact match for the item lookups, practise SUMIF for stock totals, and practise conditional formatting to build the reorder alerts in a live spreadsheet.