Skip to content
Tutorials Center
My learning

Topics

Lesson 85 of 93 ยท Real-World Projects

Project: Inventory and Reorder Tracker

Build an Excel inventory sheet with reorder alerts, order quantities, conditional formatting, XLOOKUP item search and dashboard totals, step by step.

Almost every small business, club or workshop keeps a stock list: what we have, what we need, and what to buy next. In this project you build an inventory and reorder tracker that answers three questions on its own: which items are running low, how many should I order, and what is my stock worth?

You will use only tools from earlier lessons: IF, COUNTIF and SUMIF, XLOOKUP, Excel tables and conditional formatting. Excel cannot run on this site, so the formulas appear in plain blocks (that is what you type in Excel). Alongside them you get a small JavaScript spreadsheet engine that really evaluates the same formulas, so you can see every result.

The tracker in four parts

Stock table One row per item.
Alerts Status and order quantity.
Lookup Find an item by SKU.
Dashboard Totals at a glance.

Inputs are typed; everything else is a formula.

1 / 2

Step 1: lay out the stock table

Start with a header row and one row per item. Keep inputs (things a person types) separate from calculated columns (things formulas produce):

Column Header Kind Meaning
A SKU input Unique code for the item
B Item input Name
C On hand input Units in stock now
D Reorder level input Order when stock reaches this number
E Unit cost input Price per unit
F Status formula "REORDER" or "OK"
G Stock value formula On hand times unit cost
H Order qty formula How much to buy

Press Ctrl+T to turn the range into an Excel table. A table grows when you add a row and copies formulas into the new row by itself, which is exactly what a stock list needs. The examples below use ordinary A1 references (C2, D2) so that the engine can run them; in a table you would see structured references such as [@[On hand]] instead.

Step 2: the alert formulas

Status. An item needs ordering when stock is at or below its reorder level:

TEXT
F2:  =IF(C2<=D2,"REORDER","OK")

Use <=, not <. An item that sits exactly on its reorder level has no safety margin left, so it should already raise the alert.

Stock value is a plain multiplication:

TEXT
G2:  =C2*E2

Order quantity. A simple, common rule is "order enough to get back to twice the reorder level":

TEXT
H2:  =IF(C2<=D2,D2*2-C2,0)

The result is 0 for healthy items, so the column can be summed without special cases. The factor 2 is a business rule. Do not bury such numbers inside formulas: put it in a labeled cell (for example K1 named RestockFactor) and write D2*$K$1-C2. The lesson on cell references explains why the dollar signs matter here.

Step 3: build it in the engine

The engine below is our own mini spreadsheet: cells hold values or strings that start with =, and a formula is translated to JavaScript and evaluated. It supports the handful of functions this project needs (IF, SUM, COUNTIF, SUMIF, ROUND, XLOOKUP). It is a stand-in for Excel, not Excel's own code, but the formulas are the ones you would type.

JS The stock table and its formulas

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice how COUNTIF(F2:F7,"REORDER") counts the alerts. This is why the status column returns plain text: other formulas, filters and colors can use it.

Step 4: reorder alerts with conditional formatting

A status word is good, but a red row is faster to spot. Select A2:H7 and add a formula-based conditional formatting rule (Home, Conditional Formatting, New Rule, "Use a formula to determine which cells to format"):

TEXT
=$F2="REORDER"

Two details matter. The formula is written for the first selected cell (row 2), and the column is locked with $F so every cell in a row looks at that row's status, while the row number stays relative so each row checks itself. Pick a light red fill with dark red text. See conditional formatting for rule order and managing rules.

You can add a second rule for items at less than half their reorder level, for example =$C2<$D2/2 in a stronger color, and put it above the first one. Excel applies rules from the top and the first matching fill wins when two rules set the same property.

Also consider a data bar on column C. It shows relative stock levels without adding any formula.

Step 5: find any item with XLOOKUP

Add a small search box: type a SKU in K3 and show the item's name and stock.

TEXT
L3:  =XLOOKUP(K3,A2:A7,B2:B7,"Unknown SKU")
M3:  =XLOOKUP(K3,A2:A7,C2:C7,"")

The fourth argument is the text to show when nothing matches, so a mistyped SKU gives a friendly message instead of #N/A. For the SKU cell itself, use data validation with a list pointing at A2:A7, and nobody has to type it at all. If you have an older Excel without XLOOKUP, INDEX and MATCH does the same job.

Step 6: the dashboard

Put four numbers at the top of the sheet, each a single formula:

Label Formula
Total stock value =SUM(G2:G7)
Items to reorder =COUNTIF(F2:F7,"REORDER")
Estimated order cost =SUMPRODUCT(H2:H7,E2:E7)
Healthy items =COUNTIF(F2:F7,"OK")

SUMPRODUCT multiplies each order quantity by its unit cost and adds the results, which gives the cost of all pending orders in one step. See SUMPRODUCT.

Step 7: simulate a week of movement

Real stock changes daily. In Excel you change the On hand cells (or better, keep a separate log table of receipts and sales and sum it with SUMIFS). The stand-in below keeps the same rules in plain JavaScript, applies a few movements and re-evaluates status, order quantity and a text "data bar".

JS Sales, deliveries and alerts

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The ๐Ÿ”ด and ๐ŸŸข marks play the role of an icon set or a conditional fill. Selling 85 notebooks pushed them below their level of 40, so the tracker flags them with no manual step.

Improvements to try

  • Supplier column and lead time. Add them and make the reorder level "average daily sales times lead time days".
  • A movement log. One table with Date, SKU and Change; On hand becomes =Opening + SUMIFS(Change, SKU, [@SKU]).
  • Protect the formulas. Lock calculated columns and leave input cells unlocked, then protect the sheet (collaboration and protection).
  • A pivot table of stock value by supplier or category: PivotTable basics.
  • Avoid blanks. Use data validation so On hand cannot be negative or text.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the status formula use C2<=D2 instead of C2<D2?

    Reorder level means "order when stock reaches this number", so equality counts as needing a reorder.
  2. 2. In the conditional formatting rule =$F2="REORDER" applied to A2:H7, why is the column locked with $F?

    Locking the column keeps the reference on F for all eight columns, and the relative row lets each row check itself.
  3. 3. What does the fourth argument of =XLOOKUP(K3,A2:A7,B2:B7,"Unknown SKU") do?

    XLOOKUP's if_not_found argument replaces the #N/A error with your own value.
  4. 4. Which dashboard formula gives the cost of all pending orders in one step?

    SUMPRODUCT multiplies each order quantity by its unit cost and adds the products.

Summary

  • Separate inputs, calculated columns and outputs, and keep business rules such as the restock factor in labeled cells.
  • =IF(C2<=D2,"REORDER","OK") creates a status that counting, filtering and coloring can all use.
  • A formula-based conditional format with $F2 colors whole rows from one status column.
  • XLOOKUP with a not-found text makes a safe item search; COUNTIF, SUMIF and SUMPRODUCT build the dashboard.
  • The JavaScript engine is a stand-in that evaluates the same formulas; Excel itself runs on your computer.

Next: Project: Loan and Savings Planner.