Excel Order Tracking with Validation

Build a multi-sheet Excel order tracker with data validation and a live dashboard.

Introduction

30 Second Summary

Every business that tracks orders knows the pain of typos, wrong dates, and mystery statuses buried in a spreadsheet. One bad entry can delay a shipment or confuse an entire team.

In this project, you will build an Excel order tracking system that catches data entry errors before they happen and gives you a live summary dashboard for instant operational visibility.

What You'll Build

A multi-sheet Excel workbook with structured tables, dropdown lists, data validation rules, and a live dashboard powered by COUNTIF formulas and conditional formatting.

By the end of this project, you'll have:

A structured Orders table with dropdown menus that prevent invalid entries for Status and Product fields.
Data validation rules that block incorrect quantities, past-date deliveries, and duplicate Order IDs with custom error messages.
A live Dashboard using COUNTIF formulas and conditional formatting that shows order counts by status at a glance.
Secret Mission: add an overdue orders alert system that automatically highlights orders past their promised delivery date.

Are there any prerequisites?

You only need basic familiarity with Excel, such as opening workbooks, entering data in cells, and navigating the ribbon. No formulas or advanced features experience is required.

Before We Start

Before We Start...

Set Up Your Workbook Structure

Every order tracking system needs a solid foundation. Without a clear structure, data entry becomes inconsistent and mistakes slip through unnoticed.

In this step, you will create the three-sheet workbook that powers the entire system. Each sheet has a dedicated job: one for entering orders, one for storing reference data, and one for the dashboard.

In this step, get ready to:

Create a workbook with three named sheets (Orders, Settings, Dashboard).
Build reference tables on the Settings sheet for dropdown lists.
Set up the Orders table with proper column headers.

Create the workbook and sheets

The order tracking system uses three sheets working together. Separating data entry, reference data, and summaries keeps the workbook organized and easier to maintain.

In Excel, click File in the top-left corner.
Click New, then select Blank workbook.
At the bottom of the screen, right-click the default sheet tab (named Sheet1).
Select Rename and type Orders.
Press Enter to confirm the name.
Click the + icon next to the sheet tabs to add a new sheet.
Right-click the new tab and rename it to Settings.
Click the + icon again to add one more sheet.
Right-click the new tab and rename it to Dashboard.

You should now see three sheet tabs at the bottom of your workbook: Orders, Settings, and Dashboard.

Why three separate sheets?

The Orders sheet is where you enter data day-to-day. The Settings sheet holds reference lists (like allowed statuses) that feed into dropdown menus. The Dashboard sheet summarizes everything at a glance.

Keeping these separate means you can protect reference data from accidental edits while still letting the dashboard update automatically.

Don't see the sheet tabs?

Sheet tabs sit at the very bottom-left of the Excel window. If you do not see them, right-click the navigation arrows at the bottom-left and check that Show Sheet Tabs is enabled.

Help me find the sheet tabs in Excel.

Build the Settings reference lists

The Settings sheet stores the values that will power your dropdown menus later. By organizing these as Excel tables, any new items you add in the future will automatically appear in connected dropdowns.

Click the Settings sheet tab at the bottom of the workbook.
Click cell A1 and type Status Options.
In cell A2, type Pending.
In cell A3, type Processing.
In cell A4, type Shipped.
In cell A5, type Delivered.
Select the range A1:A5 (click A1 and drag down to A5).
Press Ctrl+T (macOS: Cmd+T) to open the Create Table dialog.
Make sure the My table has headers checkbox is checked, then click OK.
Click anywhere inside the new table. The Table Design tab appears in the ribbon.
In the Table Design tab, find the Table Name field on the left side.
Clear the existing name and type StatusList, then press Enter.

You should see the data formatted as a striped table with a filter arrow in the Status Options header cell. The Table Name field should display StatusList.

What is an Excel table?

An Excel table is a structured range that automatically expands when you add new rows or columns. It also gives each column a named reference you can use in formulas.

The key benefit for this project: dropdowns connected to a table auto-update when you add or remove items. No manual range adjustments needed.

Now create the Products reference list in the same way.

Click cell C1 and type Products.
In cell C2, type Widget A.
In cell C3, type Widget B.
In cell C4, type Gadget Pro.
In cell C5, type Gadget Lite.
In cell C6, type Accessory Pack.
Select the range C1:C6.
Press Ctrl+T (macOS: Cmd+T) to open the Create Table dialog.
Confirm My table has headers is checked and click OK.
Click inside the new table, go to the Table Design tab, and change the Table Name to ProductList.
Press Enter to confirm.

Your Settings sheet now has two formatted tables side by side. The StatusList table sits in column A and the ProductList table sits in column C.

Table Design tab not showing up?

The Table Design tab only appears when your cursor is inside a table. Click any cell within the striped table area and the tab will appear in the ribbon.

Help me find the Table Design tab to rename my table.

Create the Orders table headers

The Orders sheet is where all order data lives. Setting it up as a structured table now means every validation rule and formula you add later will reference clean, named columns.

Click the Orders sheet tab.
In cell A1, type Order ID.
In cell B1, type Customer Name.
In cell C1, type Product.
In cell D1, type Quantity.
In cell E1, type Order Date.
In cell F1, type Promised Date.
In cell G1, type Status.
In cell H1, type Notes.
Select the header row A1:H1.
Go to the Home tab in the ribbon.
Click Format as Table and pick any table style you like.
In the Create Table dialog that appears, confirm My table has headers is checked, then click OK.
Click inside the table, then go to the Table Design tab.
Change the Table Name to OrdersTable and press Enter.

Your Orders sheet now shows a formatted table with filter arrows on each header. The Table Name field should display OrdersTable.

Why name every table?

Named tables let you write readable formulas like =COUNTIF(OrdersTable[Status],"Pending") instead of cryptic cell ranges like =COUNTIF(G2:G50,"Pending"). You will use these names throughout the project.

Seeing an error when naming the table?

Table names cannot contain spaces. Make sure you type OrdersTable as one word with no spaces. Also confirm no other table in the workbook already uses that name.

Help me fix a table naming error in Excel.

✔️ Awesome, I've got everything!

Great. Save your workbook now by pressing Ctrl+S (macOS: Cmd+S). Give it a name like Order-Tracker.xlsx and save it to your Desktop.

ⓧ I'd like to double check my setup

Here is what each sheet should look like at this point.

Settings sheet:

Column A is a table named StatusList. Cell A1 reads Status Options (the header). Cells A2:A5 contain: Pending, Processing, Shipped, Delivered.
Column C is a table named ProductList. Cell C1 reads Products (the header). Cells C2:C6 contain: Widget A, Widget B, Gadget Pro, Gadget Lite, Accessory Pack.

Orders sheet:

Row 1 (A1:H1) contains headers: Order ID, Customer Name, Product, Quantity, Order Date, Promised Date, Status, Notes.
The range is formatted as a table named OrdersTable. No data rows yet.

Dashboard sheet:

This sheet exists but is currently empty. You will build it in a later step.

To verify a table name, click inside the table and check the Table Name field in the Table Design tab.

Save your workbook by pressing Ctrl+S (macOS: Cmd+S).
Name the file Order-Tracker.xlsx and save it to your Desktop.

Your workbook skeleton is ready. Next up, you will connect these reference lists to dropdown menus that prevent bad data from ever reaching the Orders table.

Add Dropdown Data Validation

Your workbook now has three sheets with structured tables ready to hold data. The next challenge is making sure the data going into those tables is actually correct.

Without guardrails, anyone entering orders could type "Shpped" instead of "Shipped" or invent a status that does not exist. In this step, you will add dropdown lists powered by data validation so that the Status and Product columns only accept values from your Settings reference tables.

In this step, get ready to:

Create a Status dropdown that only allows Pending, Processing, Shipped, or Delivered.
Create a Product dropdown that only allows items from your product catalog.
Test both dropdowns to confirm invalid entries are blocked.

Create the Status dropdown

The Status column controls the lifecycle of every order. By restricting it to a dropdown, you guarantee consistency across all entries.

Switch to the Orders sheet.
Click on cell G2 (the first data cell under the Status header).
Hold Ctrl+Shift+End to select down, then adjust your selection to cover G2:G50.
Click the Data tab in the ribbon.
Click Data Validation in the Data Tools group.

The Data Validation dialog opens with three tabs. You will configure each one.

On the Settings tab, set the Allow dropdown to List.
In the Source field, type: =INDIRECT("StatusList[Status Options]").
Make sure the In-cell dropdown checkbox is checked.
Click the Input Message tab.
Check Show input message when cell is selected.
Set Title to Order Status.
Set Input message to Select a status from the dropdown list.
Click the Error Alert tab.
Set Style to Stop.
Set Title to Invalid Status.
Set Error message to Please select a status from the dropdown. Custom entries are not allowed.
Click OK.

Why use INDIRECT with a table reference?

Excel's Data Validation dialog does not natively resolve structured table references like StatusList[Status Options] in the Source field. Wrapping it in INDIRECT() converts the text string into a reference Excel can evaluate.

The big benefit is that your dropdown automatically updates whenever you add or remove items from the StatusList table on the Settings sheet. No need to edit the validation rule each time.

Why use the Stop error style?

The Stop style completely prevents invalid data from being entered into the cell. The Warning and Information styles show a message but still allow the invalid value through. For an order tracking system where data integrity matters, Stop is the right choice.

Create the Product dropdown

The Product column follows the same pattern as Status. You will point it at the ProductList table on your Settings sheet.

Select cells C2:C50 in the Product column on the Orders sheet.
Click Data > Data Validation.
On the Settings tab, set Allow to List.
In the Source field, type: =INDIRECT("ProductList[Products]").
Make sure the In-cell dropdown checkbox is checked.
Click the Input Message tab.
Check Show input message when cell is selected.
Set Title to Product.
Set Input message to Select a product from the catalog.
Click the Error Alert tab.
Set Style to Stop.
Set Title to Invalid Product.
Set Error message to Please select a product from the catalog. Custom entries are not allowed.
Click OK.

Auto-updating dropdowns

Because your source lists are Excel tables, any new product you add to the ProductList table on the Settings sheet will automatically appear in the Product dropdown. You never need to re-edit the Data Validation rule.

Test your dropdowns

A validation rule is only useful if it actually blocks bad data. Let's confirm both dropdowns work as expected.

Click on cell G2 on the Orders sheet.
Confirm you see a small dropdown arrow on the right side of the cell and the input message tooltip appears.
Click the dropdown arrow to verify all four statuses appear: Pending, Processing, Shipped, Delivered.
Instead of selecting from the list, type Cancelled directly into the cell and press Enter.
Verify that a Stop error alert appears with the title Invalid Status and your custom message.
Click Retry or Cancel to dismiss the error.
Click on cell C2 and repeat the same test for the Product dropdown.
Click the dropdown arrow to verify all five products appear.
Type Widget Z and press Enter to confirm the error alert blocks it.

Dropdown arrow not showing up?

Make sure you selected the correct cell range (G2:G50 for Status, C2:C50 for Product) before opening Data Validation. If you accidentally applied validation to row 1 (the header), clear it and reapply to the data cells only.

If the dropdown appears but shows no items, double-check that your Source formula matches the exact table and column names from the Settings sheet. The table name StatusList and column header Status Options must be character-for-character identical.

Help me troubleshoot my Excel data validation dropdown

Both dropdowns are locked down and pulling from your Settings tables. Next up, you will add validation rules for quantities and dates to catch even more entry errors before they happen.

Add Date and Quantity Validation

Your dropdown validations are working. The Status and Product columns now only accept values from your predefined lists.

Dropdowns solve one type of data entry problem, but what about numeric fields that need boundaries or dates that must follow logical rules? In this step, you'll add validation rules that enforce number ranges, date logic, and uniqueness.

In this step, get ready to:

Apply whole number validation to the Quantity column.
Add date validation that prevents deliveries promised before the order date.
Create a custom formula rule that blocks duplicate Order IDs.

Validate the Quantity column

Orders need a quantity, but that quantity should always be a positive whole number within a reasonable range. A typo like 0 or a nonsense value like 99999999 would break inventory planning.

Switch to the Orders sheet.
Select cells D2:D50 in the Quantity column.
Click the Data tab in the ribbon.
Click Data Validation in the Data Tools group.

The Data Validation dialog opens with three tabs. You'll configure all three.

On the Settings tab, set Allow to Whole Number.
Set Data to between.
Set Minimum to 1.
Set Maximum to 10000.

Click the Input Message tab.
Check Show input message when cell is selected.
Set Title to Quantity.
Set Input message to Enter a whole number between 1 and 10,000..

Click the Error Alert tab.
Set Style to Stop.
Set Title to Invalid Quantity.
Set Error message to Quantity must be a whole number between 1 and 10,000..
Click OK.

Why use the Stop style?

Excel offers three error alert styles. Stop completely prevents invalid data from being entered. Warning and Information show a message but still allow the bad value through.

For an order tracking system where data integrity matters, Stop is the right choice. You want to catch errors at entry time, not discover them later.

Click on cell D2 to verify your input message tooltip appears with the title Quantity.
Type 0 and press Enter. You should see the error alert pop up blocking the entry.
Click Retry or Cancel to dismiss the error and clear the cell.

Not seeing the input message?

Make sure you checked the Show input message when cell is selected checkbox on the Input Message tab. Without this, the tooltip won't appear.

Also confirm you selected the correct range D2:D50 before opening Data Validation. If you applied it to the wrong cells, select D2:D50, reopen Data Validation, and reapply.

help me fix my data validation input message

Validate the Promised Date column

A promised delivery date should never be earlier than the order date. That would mean promising to deliver a product before the order was even placed.

Select cells F2:F50 in the Promised Date column.
Click the Data tab, then click Data Validation.

On the Settings tab, set Allow to Date.
Set Data to greater than or equal to.
In the Start date field, type =E2.

Why does =E2 work across all rows?

When you apply data validation to a range, Excel evaluates the formula relative to each cell in that range. For cell F2, it checks against E2. For cell F10, it checks against E10.

This means each row's Promised Date is compared to its own Order Date automatically. You only need to write the formula once.

Click the Input Message tab.
Check Show input message when cell is selected.
Set Title to Promised Delivery Date.
Set Input message to Enter a date on or after the order date..

Click the Error Alert tab.
Set Style to Stop.
Set Title to Invalid Date.
Set Error message to The promised delivery date cannot be before the order date..
Click OK.

To test, type a date in cell E2 (for example, 2026-06-10).
In cell F2, type a date earlier than the one in E2 (for example, 2026-06-01) and press Enter.
You should see the error alert with the title Invalid Date blocking the entry.
Click Retry or Cancel to dismiss, then delete the test values in E2 and F2.

Error alert not appearing for the date?

Make sure you entered a valid date in E2 first. If E2 is empty, the formula =E2 evaluates to zero, which means almost any date will pass validation.

Also confirm you typed =E2 (with the equals sign) in the Start date field. Without the equals sign, Excel treats it as literal text.

help me fix my date validation

Validate Order ID uniqueness with a custom formula

Duplicate Order IDs would make it impossible to look up a specific order. Excel's Custom validation type lets you write a formula that must evaluate to TRUE for the entry to be accepted.

Select cells A2:A50 in the Order ID column.
Click the Data tab, then click Data Validation.

On the Settings tab, set Allow to Custom.
In the Formula box, enter:

=COUNTIF(A:A,A2)<=1

What does this formula do?

COUNTIF(A:A,A2) counts how many times the value in A2 appears in the entire A column. The <=1 part means the formula returns TRUE only when that value appears once or not at all.

If someone tries to enter a duplicate, the count becomes 2, the formula returns FALSE, and Excel blocks the entry.

Click the Input Message tab.
Check Show input message when cell is selected.
Set Title to Order ID.
Set Input message to Enter a unique order identifier..

Click the Error Alert tab.
Set Style to Stop.
Set Title to Duplicate Order ID.
Set Error message to This Order ID already exists. Please enter a unique ID..
Click OK.

To test, type ORD-001 in cell A2 and press Enter.
Type ORD-001 again in cell A3 and press Enter.
You should see the error alert with title Duplicate Order ID blocking the duplicate.
Click Cancel to dismiss, then delete the test values in A2 and A3.

Duplicate entry not being blocked?

Confirm you typed the formula exactly as =COUNTIF(A:A,A2)<=1 with no extra spaces. The cell reference A2 must match the first cell in your selected range.

Also check that you set Allow to Custom (not List or another option). The Formula box only appears when Custom is selected.

help me fix my custom formula validation

Save your workbook by pressing Ctrl+S (Windows) or Cmd+S (macOS).

Your Orders table now catches invalid quantities, impossible delivery dates, and duplicate IDs at entry time. Next up, you'll build a live Dashboard that summarizes your order data with formulas and color-coded formatting.

Build the Dashboard with COUNTIF and Conditional Formatting

Your Orders sheet now catches invalid entries before they're saved. But when you have dozens of orders, scrolling through rows to count how many are pending or shipped is slow and error-prone.

In this step, you'll build a Dashboard sheet that uses COUNTIF formulas to automatically tally orders by status. You'll also add conditional formatting to color-code statuses on the Orders sheet for instant visual recognition.

In this step, get ready to:

Create a status summary on the Dashboard with COUNTIF formulas.
Add color-coded conditional formatting to the Status column on the Orders sheet.
Add data bars and borders to the Dashboard for a polished look.

Create the status summary on the Dashboard

The COUNTIF function counts how many cells in a range match a given criteria. You'll use it to count how many orders have each status.

Click the Dashboard sheet tab at the bottom of the workbook.
Click cell A1 and type Order Status Dashboard.
In cell A3 type Pending.
In cell A4 type Processing.
In cell A5 type Shipped.
In cell A6 type Delivered.
In cell B2 type Count.
Click cell B3 and enter this formula:

=COUNTIF(OrdersTable[Status],A3)

Select cell B3, then drag the fill handle (the small square at the bottom-right corner of the cell) down to cell B6.
Click cell A8 and type Total Orders.
Click cell B8 and enter this formula:

=COUNTA(OrdersTable[Order ID])

What do these formulas do?

COUNTIF counts how many cells in a range match a specific value. Here, =COUNTIF(OrdersTable[Status],A3) looks at every Status cell in your Orders table and counts how many match the value in A3 (which is "Pending").

COUNTA counts non-empty cells. It gives you a total order count by counting every filled Order ID cell in the table.

All formula cells should currently display 0 since no orders have been entered yet. You'll see these numbers update automatically once you add data in the next step.

Seeing a #REF! or #NAME? error?

If you see #REF!, the table name OrdersTable does not match what you named your table on the Orders sheet. Switch to the Orders sheet, click inside the table, then check the Table Design tab to confirm the name is exactly OrdersTable.

If you see #NAME?, check for typos in the formula. The column reference must be [Status] with square brackets and the exact header name from your Orders sheet.

Help me fix my COUNTIF formula on the Dashboard

Add conditional formatting to the Orders sheet

Conditional formatting changes a cell's appearance based on its value. You'll create four rules so each order status displays a different background color.

Switch to the Orders sheet.
Select cells G2:G50 (the Status column data range).
Click the Home tab in the ribbon.
Click Conditional Formatting, then select New Rule.
Select Format only cells that contain.
Set the condition to Cell Value equal to Pending.
Click Format, then select the Fill tab.
Choose a light yellow color and click OK.
Click OK again to save the rule.

Now repeat this process three more times, creating one rule for each remaining status:

Select G2:G50 again, then go to Home > Conditional Formatting > New Rule.
Create a rule for Processing with a light blue fill.
Create a rule for Shipped with a light orange fill.
Create a rule for Delivered with a light green fill.

Why color-code the Status column?

Color-coding turns your Status column into a visual dashboard right inside the data. Instead of reading each cell individually, you can scan the column and instantly see clusters of yellow (pending) or green (delivered) orders.

Verify your four rules exist by clicking Conditional Formatting > Manage Rules on the Home tab.
Confirm you see four rules listed for G2:G50, one for each status value.

Don't see all four rules?

In the Manage Rules dialog, make sure the Show formatting rules for dropdown is set to This Worksheet (not "Current Selection"). If a rule is missing, select G2:G50 and create the missing rule using the same steps above.

Help me check my conditional formatting rules for the Status column

Add conditional formatting to the Dashboard

Data bars add horizontal bars inside cells, sized proportionally to each value. They give you a quick visual comparison of order counts without reading the numbers.

Switch to the Dashboard sheet.
Select cells B3:B6.
Click the Home tab in the ribbon.
Click Conditional Formatting.
Hover over Data Bars and select a gradient fill style.

Now add borders to make the summary area look like a clean table.

Select cells A3:B6.
Click the Home tab.
Click the Borders dropdown (in the Font group).
Select All Borders.

What should I see?

Your Dashboard now has a bordered summary table in cells A3:B6. The count cells (B3:B6) all show 0 with faint data bar formatting applied. Once you add orders in the next step, the bars will fill proportionally to each status count.

Data bars not visible?

Data bars only become visible when the cells contain non-zero values. Since your counts are currently 0, you will not see colored bars yet. This is expected. You'll confirm them in the next step when you add sample orders.

Help me verify that data bars are applied to my Dashboard

Your Dashboard is ready to give you real-time visibility into order status. Next up, you'll enter sample orders and watch the entire system come alive.

Enter Sample Data and Verify the System

Your order tracking system has all the structure and rules in place. The dropdowns are connected, the validation formulas are set, and the dashboard is wired up with COUNTIF formulas.

Now it is time to put real data through the system and confirm everything works together. You will enter sample orders, deliberately trigger every validation rule, and watch the Dashboard update in real time.

In this step, get ready to:

Enter 5-6 sample orders using the validated fields and dropdowns.
Test each validation rule to confirm it blocks invalid entries.
Verify the Dashboard updates automatically as you change order data.

Enter test orders

Entering a mix of orders with different statuses lets you see the color-coded conditional formatting in action and gives the Dashboard formulas real data to count.

Switch to the Orders sheet.
Click cell A2 and type ORD-001 as your first Order ID.
In B2, type Acme Corp as the Customer Name.
Click cell C2, then click the dropdown arrow that appears and select Widget A from the list.
In D2, type 50 for the Quantity.
In E2, type 2026-06-01 as the Order Date.
In F2, type 2026-06-10 as the Promised Date.
Click cell G2, click the dropdown arrow, and select Processing.
Optionally add a note in H2 (e.g., Rush order).

You should see cell G2 turn light blue, matching the conditional formatting rule for "Processing".

Now enter 4-5 more orders in rows 3 through 7, using different combinations of products and statuses.

Use a variety of statuses so the Dashboard has data to display. Here are suggested entries:

ORD-002: Customer "Beta Inc", Product "Gadget Pro", Qty 10, dates 2026-06-02 / 2026-06-12, Status Pending.
ORD-003: Customer "Gamma LLC", Product "Widget B", Qty 25, dates 2026-05-28 / 2026-06-05, Status Shipped.
ORD-004: Customer "Delta Co", Product "Accessory Pack", Qty 100, dates 2026-05-25 / 2026-06-01, Status Delivered.
ORD-005: Customer "Epsilon Ltd", Product "Gadget Lite", Qty 5, dates 2026-06-03 / 2026-06-15, Status Pending.

What should I see?

Each Status cell should show a different fill color based on your conditional formatting rules: light yellow for Pending, light blue for Processing, light orange for Shipped, and light green for Delivered.

If a cell does not change color, click on it and confirm the dropdown value matches exactly (no extra spaces). Conditional formatting is case-sensitive to the value you set in the rule.

Test validation rules

The whole point of data validation is catching mistakes before they corrupt your data. Now you will deliberately trigger each rule to confirm the error alerts appear.

Click any empty Quantity cell (e.g., D8) and type 0, then press Enter.

You should see an error alert dialog with the title "Invalid Quantity" and the message you set. Click Retry or Cancel to dismiss it.

Click an empty Promised Date cell and enter a date before the Order Date in the same row (e.g., type 2026-05-01 when the Order Date is 2026-06-01), then press Enter.

You should see the "Invalid Date" error alert blocking the entry.

Click an empty Status cell and type Cancelled manually (instead of using the dropdown), then press Enter.

You should see the "Invalid Status" error alert. The Stop style prevents the value from being saved.

Click an empty Order ID cell and type ORD-001 (a duplicate of your first order), then press Enter.

You should see the "Duplicate Order ID" error alert. This confirms your custom COUNTIF formula is catching repeated values.

Why use Stop instead of Warning?

Excel offers three Error Alert styles. The Stop style completely prevents invalid data from being entered. Warning and Information styles only show a notification but still allow the invalid value through.

For an order tracking system where data integrity matters, Stop is the right choice. It guarantees your formulas and Dashboard always work with clean data.

Validation not blocking your entry?

Make sure you are typing directly into the cell. Data validation does not block values that are pasted or filled (dragged) into cells. This is a known Excel limitation.

Also check that the cell you are testing is within the validated range (rows 2 through 50). If you click a cell below row 50, no validation rule exists there.

Help me figure out why my Excel data validation isn't blocking invalid entries.

Check the Dashboard

The Dashboard sheet uses COUNTIF formulas that reference the Orders table. Every time you change a status or add a row, the counts update automatically.

Click the Dashboard sheet tab at the bottom of the workbook.
Check that the counts in B3:B6 match the statuses you entered on the Orders sheet.

If you entered the sample data above, you should see Pending: 2, Processing: 1, Shipped: 1, Delivered: 1. The Total Orders count in B8 should show 5.

Switch back to the Orders sheet.
Change the Status of one order (e.g., change ORD-002 from Pending to Shipped using the dropdown).
Switch back to the Dashboard sheet.

The Pending count should decrease by 1 and the Shipped count should increase by 1. The data bars update proportionally to reflect the new values.

Why do the counts update automatically?

The formula =COUNTIF(OrdersTable[Status],A3) references the structured table column directly. Excel recalculates these formulas whenever any cell in that column changes. No manual refresh is needed.

Save your workbook by pressing Ctrl+S (Windows) or Cmd+S (macOS).

Dashboard showing all zeros?

If the counts are 0 even though you have data in the Orders sheet, click on cell B3 and check the formula bar. Make sure the formula says =COUNTIF(OrdersTable[Status],A3) with the table name spelled exactly as you named it.

Also confirm your Orders table is named OrdersTable (check on the Table Design tab when you click inside the table). If the name does not match, the COUNTIF formula cannot find the data.

Help me troubleshoot my Dashboard formulas.

Secret mission

Add an Overdue Orders Alert System

Your order tracker catches bad data at entry time. But what about orders that slip through the cracks after they're entered? Build an automatic alert that highlights any order past its promised delivery date that hasn't been marked as Delivered yet.

Clean Up Your Resources

This project runs entirely locally in Microsoft Excel, so there are no ongoing costs or cloud resources to worry about.

Decide whether to keep your workbook as-is, or delete it entirely.

Resources you used:

Excel workbook (.xlsx file) saved on your computer with Orders, Settings, and Dashboard sheets.

Keep everything running

No action needed. Choose this if you want to keep using your order tracking system or reference it later.

Your workbook stays on your computer wherever you saved it.
You can continue adding orders, updating the Settings lists, or customizing the Dashboard at any time.

Pause - I'll come back to this later

Close the workbook to free up system memory. Your file stays saved on disk for when you return.

Save the workbook if you have unsaved changes (Ctrl+S).
Close the workbook by clicking the X button or pressing Ctrl+W.
To reopen later, double-click the .xlsx file or use File > Open in Excel.

Delete - I don't want to use this again

Remove the workbook file from your computer entirely.

Close the workbook in Excel if it is still open.
Navigate to the folder where you saved the .xlsx file using Finder (macOS) or File Explorer (Windows).
Delete the file by right-clicking it and selecting Move to Trash (macOS) or Delete (Windows).
Empty your Trash or Recycle Bin to permanently remove the file.

Nice Work!

Congratulations! You have built a professional Excel order tracking system from scratch.

You've learned how to:

Built a structured multi-sheet workbook with interconnected reference tables and Excel tables that auto-update connected dropdowns.
Applied data validation rules (List, Date, Whole Number, and Custom) with custom input messages and error alerts that prevent incorrect entries at the point of data entry.
Designed a live Dashboard with COUNTIF formulas and conditional formatting that gives instant operational visibility into order status.
Added an overdue order detection system using conditional formatting with AND/TODAY formulas and COUNTIFS on the Dashboard.

Ready to quiz yourself?