Client Orders Tracker in Excel

Build a clean, interactive Excel dashboard from messy order data.

Introduction

30 Second Summary

Remote admin and data entry job postings all ask for the same thing: proof you can handle Excel. Not just opening a spreadsheet, but cleaning messy data, writing formulas, and building reports your boss would actually use.

In this project, you will build a Client Orders Tracker workbook that takes messy, inconsistent order data and transforms it into a clean, interactive dashboard.

What You'll Build

A professional Excel workbook that demonstrates data entry, data cleaning, formula writing, Pivot Table creation, and dashboard design in a single file.

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

  • A cleaned and structured Excel Table with standardized names, removed duplicates, and validated formats.
  • A calculations sheet with VLOOKUP, XLOOKUP, SUMIF, COUNTIF, and IF formulas pulling key metrics like total revenue per client and average order value.
  • An interactive dashboard with Pivot Charts, slicers, and a timeline filter that responds to clicks.
  • Secret Mission: Add conditional formatting rules that flag overdue orders and highlight your top clients by revenue.

Are there any prerequisites?

You just need Microsoft Excel (desktop version 2016 or later) installed on your computer. No prior formula knowledge is required.

Set Up Your Workbook and Enter Raw Data

Every data project starts the same way: you receive a pile of messy information and need to organize it into something useful.

In this step, you will create your workbook structure and enter 30+ rows of realistic order data. The data is intentionally messy because that is what real client data looks like when it lands in your inbox.

In this step, get ready to:
  • Create a new workbook with four organized sheets.
  • Enter 30+ rows of realistic order data with intentional errors.
  • Save your workbook for the rest of the project.
Create your workbook and set up sheets

Your workbook needs four sheets, each with a specific job: raw data entry, cleaned data, calculations, and the final dashboard.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Excel and press Enter to open Microsoft Excel.
  • Click Blank workbook to create a new file.

You should see a fresh spreadsheet with one default sheet tab at the bottom of the window.

Now you need three additional sheets. Excel starts you with one sheet, so you will add three more and rename all four.

  • Click the + icon to the right of the sheet tabs at the bottom of the window.
  • Click + two more times so you have four sheet tabs total.

Now rename each sheet. You will double-click each tab, type the new name, and press Enter.

  • Double-click the first sheet tab at the bottom.
  • Type RawData and press Enter.
  • Double-click the second sheet tab.
  • Type Clean and press Enter.
  • Double-click the third sheet tab.
  • Type Calculations and press Enter.
  • Double-click the fourth sheet tab.
  • Type Dashboard and press Enter.

You should now see four tabs at the bottom of your workbook: RawData, Clean, Calculations, and Dashboard.

Why four separate sheets?

Separating raw data from cleaned data is a professional best practice. It means you always have the original to go back to if something goes wrong during cleaning.

The Calculations and Dashboard sheets keep your formulas and visualizations isolated from the data itself. This is how real analysts structure workbooks at work.

Don't see all four tabs?

Make sure you clicked the + icon three times (once for each new sheet). You can also press Shift+F11 to insert a new sheet.

If you accidentally deleted the original sheet, just click + once more and rename it.

help me set up my workbook sheets

Enter the raw order data

Now comes the data entry portion. You will type in 34 rows of client order data that simulate what a real admin might receive from a messy CRM export or client email.

The data includes intentional mistakes. Do not fix them as you type. These errors are what you will clean in the next step.

  • Click the RawData tab at the bottom of your workbook.
  • Click cell A1 and type the following header row across columns A through I:
OrderID    OrderDate    ClientName    Email    ProductCategory    ProductName    Quantity    UnitPrice    Status

Use Tab to move between cells

After typing each header, press Tab to move to the next column. When you reach the last column (Status), press Enter to move down to row 2.

Now type in the order data below. Use the same Tab-to-move approach for each row. Type each value exactly as shown, including the intentional errors (wrong capitalization, extra spaces, and inconsistent dates).

ORD001    2024-01-15    John Smith    john.smith@email.com    Electronics    Laptop Stand    2    45.99    Completed
ORD002    01/20/2024    sarah connor    sarah.connor@email.com    Office Supplies    Printer Paper    10    8.99    Completed
ORD003    2024-02-03    Michael Brown    m.brown@email.com    Electronics    USB Hub    1    29.99    Pending
ORD004    15-Feb-2024    john smith    john.smith@email.com    Office Supplies    Sticky Notes    5    3.49    Completed
ORD005    2024-02-20     Sarah Connor    sarah.connor@email.com    Furniture    Desk Lamp    1    67.50    Shipped
ORD006    03/01/2024    Lisa Wang    lisa.wang@email.com    Electronics    Webcam    2    54.99    Completed
ORD007    2024-03-10    Michael Brown    m.brown@email.com    Furniture    Office Chair    1    299.00    Pending
ORD008    2024-03-15    JOHN SMITH    john.smith@email.com    Electronics    Monitor Cable    3    12.99    Shipped
ORD009    20-Mar-2024    David Lee    david.lee@email.com    Office Supplies    Binder Clips    20    2.49    Completed
ORD010    2024-04-02    lisa wang    lisa.wang@email.com    Furniture    Standing Desk    1    450.00    Overdue
ORD011    04/10/2024    Sarah Connor    sarah.connor@email.com    Electronics    Keyboard    1    89.99    Completed
ORD012    2024-04-18    John Smith    john.smith@email.com    Office Supplies    Envelopes    50    0.15    Completed
ORD013    2024-05-01    david lee    david.lee@email.com    Electronics    Mouse Pad    2    15.99    Shipped
ORD014    05/12/2024    Michael Brown    m.brown@email.com    Office Supplies    Printer Paper    5    8.99    Pending
ORD015    2024-05-20    Lisa Wang    lisa.wang@email.com    Electronics    USB Hub    1    29.99    Completed
ORD016    2024-06-01     John Smith    john.smith@email.com    Furniture    Bookshelf    1    175.00    Overdue
ORD017    15-Jun-2024    Sarah Connor    sarah.connor@email.com    Office Supplies    Paper Clips    30    1.99    Completed
ORD018    2024-06-22    David Lee    david.lee@email.com    Electronics    Laptop Stand    1    45.99    Shipped
ORD019    07/03/2024    michael brown    m.brown@email.com    Furniture    Desk Lamp    2    67.50    Completed
ORD020    2024-07-15    Lisa Wang    lisa.wang@email.com    Office supplies    Sticky Notes    10    3.49    Pending
ORD021    2024-07-20    John Smith    john.smith@email.com    Electronics    Webcam    1    54.99    Completed
ORD022    08/01/2024    sarah connor    sarah.connor@email.com    Furniture    Office Chair    1    299.00    Shipped
ORD023    2024-08-12    David Lee    david.lee@email.com    Office Supplies    Binder Clips    15    2.49    Completed
ORD024    2024-08-20     Michael Brown    m.brown@email.com    Electronics    Monitor Cable    2    12.99    Overdue
ORD025    01-Sep-2024    Lisa Wang    lisa.wang@email.com    Electronics    Keyboard    2    89.99    Completed
ORD026    2024-09-15    SARAH CONNOR    sarah.connor@email.com    Office Supplies    Envelopes    25    0.15    Shipped
ORD027    2024-09-22    John Smith    john.smith@email.com    Furniture    Standing Desk    1    450.00    Pending
ORD028    10/05/2024    David Lee    david.lee@email.com    electronics    Mouse Pad    1    15.99    Completed
ORD029    2024-10-18    Lisa Wang    lisa.wang@email.com    Furniture    Bookshelf    1    175.00    Completed
ORD030    2024-11-01    michael brown    m.brown@email.com    Office Supplies    Printer Paper    8    8.99    Shipped
ORD031    11/15/2024    John Smith    john.smith@email.com    Electronics    USB Hub    2    29.99    Overdue
ORD032    2024-11-20    Sarah Connor    sarah.connor@email.com    Furniture    Desk Lamp    1    67.50    Completed
ORD003    2024-02-03    Michael Brown    m.brown@email.com    Electronics    USB Hub    1    29.99    Pending
ORD011    04/10/2024    Sarah Connor    sarah.connor@email.com    Electronics    Keyboard    1    89.99    Completed

What are the intentional errors?

This data contains four types of errors you will fix in Step 2:

  • Inconsistent capitalization: "john smith", "JOHN SMITH", and "John Smith" are all the same client.
  • Extra leading spaces: some names like " Sarah Connor" and " John Smith" have a space before the first letter.
  • Duplicate rows: rows 33 and 34 are exact copies of earlier rows (ORD003 and ORD011).
  • Mixed date formats: some dates use YYYY-MM-DD, others use MM/DD/YYYY, and others use DD-Mon-YYYY.

Speed up your data entry

For repeating values like email addresses, type the first few characters and Excel may offer an AutoComplete suggestion. Press Enter to accept it, or keep typing to ignore it.

However, do NOT use AutoComplete for the ClientName column. You need to type the messy variations (lowercase, uppercase, extra spaces) exactly as shown.

  • Verify your data by scrolling down to confirm you have 34 data rows (rows 2 through 35) plus the header row.
  • Spot-check row 5: the ClientName should have a space before "Sarah Connor".
  • Spot-check row 34: it should be an exact duplicate of row 3 (ORD003).

Data looks wrong or columns are misaligned?

If values ended up in the wrong columns, you likely pressed Enter instead of Tab between values. Select the misaligned cells, press Delete to clear them, and retype those values using Tab to move across columns.

If Excel changed a date you typed (for example, turning "2024-01-15" into "1/15/2024" automatically), do not worry. Excel interprets dates internally. The visual format differences are still useful for learning data cleaning.

help me fix misaligned data in my Excel spreadsheet

Save your workbook
  • Press Cmd+S (macOS) or Ctrl+S (Windows) to save.
  • In the Save dialog, navigate to your Desktop.
  • Type Client-Orders-Tracker as the file name.
  • Make sure the file format is set to Excel Workbook (.xlsx).
  • Click Save.

Your title bar should now show Client-Orders-Tracker.xlsx as the file name.

Don't see a Save dialog?

If Excel saved directly without showing a dialog, your file may have been saved to a default location (often the Documents folder or OneDrive). Click File, then Save As, then choose your Desktop and name the file Client-Orders-Tracker.

help me save my Excel workbook to a specific location

Your raw data is ready. In the next step, you will clean this messy data using formulas and Excel features that standardize everything into a professional, consistent format.

Clean and Structure Your Data

Your raw data is entered, but it looks like a mess. Names are in different cases, extra spaces hide in cells, and duplicate rows lurk throughout the sheet.

In this step, you will clean that messy data and convert it into a structured Excel Table. This is the exact workflow admin professionals follow when they receive inconsistent data from clients or imports.

In this step, get ready to:
  • Standardize client names using TRIM and PROPER functions.
  • Remove duplicate rows and convert your data into an Excel Table named "Orders".
  • Add a calculated TotalAmount column and verify your data is clean.
Copy your raw data to the Clean sheet

You always want to keep your original data untouched. The RawData sheet stays as-is so you can refer back to it if something goes wrong.

  • Click on the RawData sheet tab at the bottom of your workbook.
  • Select all of your data including headers by pressing Ctrl+A (Windows) or Cmd+A (macOS).
  • Copy the selection by pressing Ctrl+C (Windows) or Cmd+C (macOS).
  • Click on the Clean sheet tab.
  • Click cell A1 and paste by pressing Ctrl+V (Windows) or Cmd+V (macOS).

You should see all of your raw data now duplicated on the Clean sheet. This is the copy you will clean up.

Data not pasting correctly?

Make sure you clicked cell A1 on the Clean sheet before pasting. If data landed in the wrong spot, press Ctrl+Z to undo, click A1, and paste again.

help me fix my data paste on the Clean sheet

Standardize client names with TRIM and PROPER

Your ClientName column has inconsistent capitalization like "john smith" and "JOHN SMITH" mixed together. It also has hidden extra spaces. You will use a helper column with a formula to fix both issues at once.

  • On the Clean sheet, click the column header to the right of your last column (column J) to select it.
  • Right-click and select Insert to add a temporary helper column.
  • In cell J1, type the header CleanName.
  • In cell J2, enter this formula:
=PROPER(TRIM(C2))

What does this formula do?

TRIM removes all extra spaces from the text, leaving only single spaces between words. PROPER then capitalizes the first letter of each word and makes everything else lowercase.

So " john SMITH " becomes "John Smith" in one step.

  • Press Enter to confirm the formula.
  • Click cell J2 again, then double-click the small green square at the bottom-right corner of the cell (the fill handle) to copy the formula down to all rows.
  • Select all the values in column J (from J2 down to your last row of data).
  • Copy them with Ctrl+C (Windows) or Cmd+C (macOS).
  • Click cell C2 (the first data cell in the ClientName column).
  • Right-click, select Paste Special, then select Values.
  • Right-click the column J header and select Delete to remove the helper column.

Scroll through your ClientName column. Every name should now be in proper case with no extra spaces.

Why Paste Special Values?

Paste Special with Values pastes only the text result, not the formula. If you used a regular paste, the cells would still reference the helper column (which you are about to delete). Pasting values locks in the cleaned text permanently.

  • Now fix inconsistent category names using Find & Replace. Press Ctrl+H (Windows) or Cmd+H (macOS) to open Find & Replace.
  • In the Find what field, type the inconsistent category name (for example, office supplies if some rows use lowercase).
  • In the Replace with field, type the correct version (for example, Office Supplies).
  • Click Replace All.
  • Repeat for any other inconsistent categories in your data (check for variations like "electronics" vs "Electronics" or "Software" vs "software").
  • Close the Find & Replace dialog when done.

Check all your categories quickly

Click any cell in the ProductCategory column, then go to Data tab and click the Filter button. Click the dropdown arrow on ProductCategory to see all unique values. If you spot duplicates with different casing, run another Find & Replace.

Find & Replace not catching everything?

Find & Replace is case-sensitive only if you check the Match case box. Leave it unchecked to catch all variations regardless of capitalization.

help me fix inconsistent category names with Find and Replace in Excel

Remove duplicates and convert to an Excel Table

Duplicate rows sneak in when data is entered multiple times or copied incorrectly. Excel has a built-in tool that finds and removes exact duplicate rows in one click.

  • Click any cell inside your data on the Clean sheet.
  • Click the Data tab in the ribbon.
  • Click Remove Duplicates in the Data Tools group.
  • In the dialog box, make sure Select All is checked so Excel compares all columns.
  • Click OK.

Excel displays a message telling you how many duplicate rows were removed and how many unique rows remain. Click OK to dismiss it.

What counts as a duplicate?

Excel considers a row a duplicate only when every single cell in that row matches another row exactly. If even one column differs, the row is kept.

Now convert your cleaned data into a formal Excel Table. Tables give your data structured references, automatic formatting, and make formulas much easier to write.

  • Click any cell inside your data on the Clean sheet.
  • Press Ctrl+T (Windows) or Cmd+T (macOS) to open the Create Table dialog.
  • Confirm that My table has headers is checked.
  • Click OK.

Your data is now formatted as a Table with alternating row colors and filter arrows on each header.

  • Click anywhere in the table. In the Table Design tab (it appears when the table is selected), find the Table Name field on the left side.
  • Replace the default name with Orders and press Enter.

Why name your Table?

A named Table lets you write formulas that reference it by name (like Orders[ClientName]) instead of cryptic cell ranges. This makes your workbook much easier to read and maintain.

Don't see the Table Design tab?

The Table Design tab only appears when your cursor is inside the table. Click any cell within your data and it should appear in the ribbon. On some versions of Excel it may be labeled Design under Table Tools.

help me find the Table Name field to rename my Excel Table

Add a TotalAmount column and verify your data

Every order has a Quantity and a UnitPrice, but no total. You will add a calculated column that multiplies these together so each row shows its full order value.

  • Click the header cell in the first empty column to the right of your table (this should be the column after Status).
  • Type TotalAmount as the header and press Enter.
  • In the first data cell of the TotalAmount column, enter this formula:
=[@Quantity]*[@UnitPrice]

What does this formula do?

The @ symbol is Excel's structured reference syntax for Tables. [@Quantity] means "the Quantity value in this same row." This formula multiplies Quantity by UnitPrice for each row automatically.

Because your data is in an Excel Table, the formula auto-fills to every row the moment you press Enter. No dragging required.

  • Press Enter to confirm the formula. The entire TotalAmount column fills automatically.
  • Verify the values look correct by spot-checking a few rows. For example, if a row has Quantity 3 and UnitPrice 25, the TotalAmount should show 75.
  • Click the dropdown arrow on the ClientName header and select Sort A to Z to sort alphabetically. Scan the sorted names to confirm there are no remaining duplicates or formatting issues.
  • Save your workbook by pressing Ctrl+S (Windows) or Cmd+S (macOS).

Seeing a #VALUE! error in TotalAmount?

This usually means a Quantity or UnitPrice cell contains text instead of a number. Click on the cell showing the error, then check the Quantity and UnitPrice cells in that row. Delete any accidental text or spaces and re-type the number.

help me fix a #VALUE! error in my TotalAmount formula

✔️ Awesome, I've got everything!

Great. Double check you have saved your workbook. Your Clean sheet should now be a named Table called Orders with properly cased names, no duplicates, consistent categories, and a TotalAmount column.

ⓧ I'd like to double check the full setup

Here is what your Clean sheet should look like at this point:

  • Data is formatted as an Excel Table named Orders (you can see alternating row colors and filter arrows).
  • Columns are: OrderID, OrderDate, ClientName, Email, ProductCategory, ProductName, Quantity, UnitPrice, Status, TotalAmount.
  • ClientName values are in proper case with no extra spaces (e.g., "John Smith" not "john smith").
  • ProductCategory values are consistent (e.g., all say "Office Supplies" not a mix of "office supplies" and "Office Supplies").
  • No duplicate rows remain.
  • The TotalAmount column uses the formula =[@Quantity]*[@UnitPrice] and every row shows a calculated value.

Your data is clean, structured, and ready for analysis. Next up, you will build formulas that pull insights from this table.

Build Lookup and Summary Formulas

Your data is clean, structured, and living inside a proper Excel Table. Now it is time to make that data work for you.

In this step, you will build formulas that pull information, calculate totals, and flag important orders automatically. These are the exact Excel skills hiring managers test for in admin and data entry interviews.

In this step, get ready to:
  • Create a client reference table with VLOOKUP and XLOOKUP formulas.
  • Calculate revenue per client with SUMIF, count orders with COUNTIF, and flag high-value orders with IF.
  • Build a summary section with total revenue, total orders, and average order value.
Create your client reference table

A client reference table gives you a single place to see each client's details and metrics at a glance. You will build it on the Calculations sheet, leaving the top rows empty for a summary section you will add later.

  • Click the Calculations sheet tab at the bottom of your workbook.
  • Click cell A7 and type your column headers across the row:
ClientName    Email    Revenue    Orders    Flag
  • Starting in cell A8, type each unique client name from your Orders table, one per row. Check the Clean sheet if you need to remind yourself of the names.

Grab unique names quickly

On the Clean sheet, click the dropdown arrow on the ClientName column header. The filter list shows every unique name. Use it as your reference while typing them into the Calculations sheet.

Now you will use a lookup formula to pull each client's email address from the Orders table automatically. VLOOKUP searches for a value in the first column of a range and returns a value from another column in that range.

  • Click cell B8 (the Email column, next to your first client name) and enter this formula:
=VLOOKUP(A8,Orders[[ClientName]:[Email]],2,FALSE)

What does this formula do?

  • A8 is the value to search for (the client name you typed).
  • Orders[[ClientName]:[Email]] tells Excel to search within the ClientName-to-Email range of the Orders table. VLOOKUP always searches the first column of the range you give it.
  • 2 means "return the value from the 2nd column of that range" (the Email column).
  • FALSE forces an exact match. Without it, Excel might return the wrong client's email.
  • Press Enter. You should see the email address for that client appear in cell B8.
  • Copy cell B8 down to fill the Email column for all your clients. Select B8, then drag the small green square at the bottom-right corner of the cell down to your last client row.

Seeing #N/A instead of an email?

This means Excel could not find the client name. Check that the name in your Calculations sheet matches the cleaned name in the Orders table exactly (same spelling, no extra spaces).

help me fix my VLOOKUP #N/A error

XLOOKUP is the modern replacement for VLOOKUP. It does not require the lookup value to be in the first column, and it defaults to exact match without needing FALSE.

✔️ I have Excel 365, 2024, or 2021

Your version supports XLOOKUP. Try it alongside your VLOOKUP to see the difference.

  • Click an empty cell (for example F8) and enter this XLOOKUP formula to test it:
=XLOOKUP(A8,Orders[ClientName],Orders[Email],"Not found")

You should see the same email address your VLOOKUP returned. Once you have confirmed it works, you can delete the test cell. Your VLOOKUP in column B is the one you will keep for this project (so it works for anyone opening your file, regardless of their Excel version).

ⓧ I have Excel 2019 or 2016

XLOOKUP is not available in Excel 2019 or 2016. Your VLOOKUP formula in column B works perfectly. You are good to continue.

Why learn both VLOOKUP and XLOOKUP?

Many workplaces still use Excel 2016 or 2019, so VLOOKUP remains a must-know skill. Job postings frequently list it by name. XLOOKUP is easier to read and more flexible, but it only works in newer versions.

Knowing both means you can work in any environment. Microsoft recommends XLOOKUP as the improved replacement for VLOOKUP.

Add revenue, order count, and flag formulas

Your client table now has names and emails. Next you will add formulas that calculate how much each client has spent, how many orders they have placed, and whether they qualify as high value.

  • Click cell C8 (the Revenue column for your first client) and enter this SUMIF formula:
=SUMIF(Orders[ClientName],A8,Orders[TotalAmount])

What does SUMIF do?

SUMIF adds up values, but only for rows that match a condition. This formula says: look at every ClientName in the Orders table. When it matches the name in A8, add that row's TotalAmount to the sum. The result is total revenue for that one client.

  • Press Enter. You should see a dollar amount representing that client's total spend.
  • Click cell D8 (the Orders column) and enter this COUNTIF formula:
=COUNTIF(Orders[ClientName],A8)

What does COUNTIF do?

COUNTIF counts how many cells match a condition. This formula counts every row in the Orders table where the ClientName equals A8. The result is the number of orders that client has placed.

  • Press Enter. You should see a whole number (the order count for that client).
  • Click cell E8 (the Flag column) and enter this IF formula:
=IF(C8>500,"High Value","")

What does this IF formula do?

IF checks a condition and returns one value if true, another if false. This formula checks whether that client's total revenue (the SUMIF result in C8) exceeds $500. If yes, it displays "High Value". If no, it leaves the cell blank.

  • Press Enter. You should see either "High Value" or a blank cell depending on that client's revenue.
  • Select cells B8:E8 (all four formula cells in your first client row).
  • Drag the fill handle (the small green square at the bottom-right corner of the selection) down to your last client row to copy all formulas at once.

Every row in your client table should now show an email, revenue total, order count, and a flag for high-value clients.

Seeing zeros or errors in your formula columns?

If SUMIF returns 0, the client name in column A does not match the Orders table exactly. Check for trailing spaces or capitalization differences.

If you see a #VALUE! error in the Flag column, make sure your formula references C8 (the Revenue cell in the same row) and not a text cell.

help me fix errors in my SUMIF or COUNTIF formulas

Build the summary section at the top

Anyone opening this workbook should immediately see the big-picture numbers. You will add three key metrics above your client table: total revenue, total orders, and average order value.

  • Click cell A1 and type Metric. In cell B1, type Value.
  • In cell A2, type Total Revenue. In cell B2, enter this formula:
=SUM(Orders[TotalAmount])
  • In cell A3, type Total Orders. In cell B3, enter this formula:
=COUNTA(Orders[OrderID])
  • In cell A4, type Average Order Value. In cell B4, enter this formula:
=AVERAGE(Orders[TotalAmount])

What do these summary formulas do?

  • SUM adds up every value in the TotalAmount column. This is your total revenue across all orders.
  • COUNTA counts all non-empty cells in the OrderID column. Each order has a unique ID, so this gives you the total number of orders.
  • AVERAGE divides the sum by the count automatically. This tells you how much a typical order is worth.
  • Save your workbook by pressing Ctrl+S (Windows) or Cmd+S (macOS).
  • Check that cell B2 shows a number (your total revenue), B3 shows the count of orders matching your data, and B4 shows the average. None should display an error.

Seeing a #REF! or #NAME? error?

A #NAME? error means Excel does not recognize the table or column name. Make sure your Excel Table on the Clean sheet is still named Orders (check under the Table Design tab when you click inside the table). Column names like TotalAmount and OrderID must match the headers in your table exactly.

help me fix my summary formula errors on the Calculations sheet

✔️ Awesome, I've got everything!

Great. Double check you have saved your file.

ⓧ I'd like to double check the full layout

Here is what your Calculations sheet should look like. Your client names and exact numbers will differ, but the structure and formulas should match this pattern.

Row 1:  Metric              | Value
Row 2:  Total Revenue       | =SUM(Orders[TotalAmount])
Row 3:  Total Orders        | =COUNTA(Orders[OrderID])
Row 4:  Average Order Value  | =AVERAGE(Orders[TotalAmount])
Row 5:  (empty)
Row 6:  (empty)
Row 7:  ClientName | Email | Revenue | Orders | Flag
Row 8:  John Smith | =VLOOKUP(A8,Orders[[ClientName]:[Email]],2,FALSE) | =SUMIF(Orders[ClientName],A8,Orders[TotalAmount]) | =COUNTIF(Orders[ClientName],A8) | =IF(C8>500,"High Value","")
Row 9:  (next client, same formulas adjusted to A9, C9, etc.)
...

Your Calculations sheet now pulls, counts, sums, and flags data automatically. Next up, you will turn this data into Pivot Tables that summarize everything by client, category, and month.

Create Pivot Tables for Reporting

Your Calculations sheet already answers specific questions like "how much did Client X spend?" using individual formulas.

Now you need a way to summarize all your order data at once, sliced by different dimensions like client and month. Pivot Tables let you drag and drop fields to instantly generate summary reports without writing any formulas.

In this step, get ready to:
  • Insert a Pivot Table showing total sales by client and product category.
  • Insert a second Pivot Table showing monthly sales over time.
  • Format and rename both Pivot Tables for a professional look.
Insert a Pivot Table for sales by client

The first Pivot Table will show how much each client spent, broken down by product category. This gives you a cross-tabulated view of revenue at a glance.

  • Click anywhere inside your Orders table on the Clean sheet.
  • Click the Insert tab in the ribbon.
  • Click PivotTable.
  • In the dialog that appears, confirm the Table/Range shows your Orders table.
  • Under Choose where you want the PivotTable report to be placed, select Existing Worksheet.
  • Click inside the Location field, then navigate to the Calculations sheet and click an empty cell well below your existing formulas (e.g. cell A20).
  • Click OK.

The PivotTable Fields pane appears on the right side of your screen.

  • Drag ClientName from the field list to the Rows area at the bottom of the pane.
  • Drag ProductCategory to the Columns area.
  • Drag TotalAmount to the Values area.

What does this Pivot Table show?

Each row is a client name, each column is a product category, and the cells show the sum of all order amounts for that combination.

Excel automatically uses Sum of TotalAmount as the calculation because TotalAmount is a numeric field.

You should see a table with client names down the left side and product categories across the top, with dollar amounts in each cell.

Pivot Table shows Count instead of Sum?

If TotalAmount shows as Count instead of Sum, click the dropdown arrow next to Count of TotalAmount in the Values area, select Value Field Settings, and change the summarize function to Sum.

This usually happens when your TotalAmount column has blank cells or text values mixed in. help me fix my Pivot Table values showing Count instead of Sum

Insert a second Pivot Table for monthly sales

The second Pivot Table will show revenue grouped by month so you can spot trends over time.

  • Click anywhere inside your Orders table on the Clean sheet again.
  • Click Insert > PivotTable.
  • Select Existing Worksheet and click a cell on the Calculations sheet that is well to the right of your first Pivot Table (e.g. cell F20).
  • Click OK.

In the PivotTable Fields pane for this new Pivot Table:

  • Drag OrderDate to the Rows area.
  • Drag TotalAmount to the Values area.

Right now the table shows every individual date as its own row. You need to group the dates into months.

  • Right-click any date value in the Pivot Table's Row Labels column.
  • Select Group.
  • In the Grouping dialog, under By, click Months so it is highlighted. Deselect any other options like Days if they are selected.
  • Click OK.

What is date grouping?

Date grouping collapses individual dates into larger time periods. Instead of seeing one row per order date, you now see one row per month with the total revenue for that month.

This is what makes Pivot Tables powerful for spotting trends. You can always ungroup later by right-clicking and selecting Ungroup.

You should now see a compact table with month names down the left side and a Sum of TotalAmount column.

Can't see the Group option?

Make sure you right-clicked directly on a date value in the Pivot Table (not the header). If your dates are stored as text rather than real date values, Excel cannot group them.

Go back to the Clean sheet and check that your OrderDate column is formatted as dates (right-click the column, select Format Cells, and confirm the category is Date). help me fix date grouping in my Pivot Table

Format and rename both Pivot Tables

A professional Pivot Table has a clean style and a meaningful name. Named Pivot Tables are also easier to reference when you connect slicers in the next step.

  • Click anywhere inside your first Pivot Table (the one showing sales by client).
  • Click the Design tab that appears on the ribbon.
  • In the PivotTable Styles group, click the More button (small arrow at the bottom-right of the style gallery) to see all available styles.
  • Pick a light-colored style from the Medium section. Any clean, professional style works here.
  • Now rename this Pivot Table. Click the PivotTable Analyze tab (also called Analyze under PivotTable Tools).
  • On the far left of the ribbon, find the PivotTable Name text box.
  • Clear the existing text, type SalesByClient, and press Enter.

Now repeat the same steps for the second Pivot Table:

  • Click anywhere inside your monthly sales Pivot Table.
  • Click the Design tab and pick a matching style from the gallery.
  • Click the PivotTable Analyze tab.
  • In the PivotTable Name text box, clear the existing text, type MonthlySales, and press Enter.
  • Save your workbook by pressing Ctrl+S.

You should see two styled Pivot Tables on your Calculations sheet. The first shows revenue per client broken down by product category. The second shows total revenue grouped by month.

Why name your Pivot Tables?

Named Pivot Tables make it easier to connect Slicers and Timelines to the right tables in the next step. Without meaningful names, you would have to guess which one is "PivotTable1" vs "PivotTable2".

Don't see the PivotTable Analyze tab?

The PivotTable Analyze tab only appears when you click directly inside a Pivot Table. Click any cell inside the Pivot Table to activate the PivotTable Tools ribbon group.

help me find the PivotTable Analyze tab in Excel

Your Pivot Tables are ready. Next up, you will turn these tables into an interactive dashboard with charts, slicers, and a timeline filter.

Build Your Interactive Dashboard

Your Pivot Tables are summarizing data perfectly. Now it is time to turn those numbers into something visual and interactive.

In this step, you will build a dashboard that responds to clicks. Your boss (or a hiring manager reviewing your portfolio) can filter by client, product, or date range without touching a formula.

In this step, get ready to:
  • Insert Pivot Charts from your Pivot Tables and arrange them side by side.
  • Add Slicers and a Timeline that filter both charts at once.
  • Format the Dashboard sheet to look professional and portfolio-ready.
Insert Pivot Charts from your Pivot Tables

A Pivot Chart is a chart that stays connected to a Pivot Table. When the Pivot Table updates, the chart updates automatically.

  • Click the Calculations sheet tab at the bottom of your workbook.
  • Click any cell inside your SalesByClient Pivot Table.
  • On the ribbon, click the PivotTable Analyze tab.
  • Click PivotChart in the Tools group.
  • In the Insert Chart dialog, select Bar on the left.
  • Choose Clustered Bar (the first option).
  • Click OK.

The chart appears on the Calculations sheet. You need to move it to the Dashboard sheet.

  • Click anywhere on the chart you just created to select it.
  • On the ribbon, click the Design tab (under Chart Tools).
  • Click Move Chart in the Location group.
  • Select Object in, then choose Dashboard from the dropdown.
  • Click OK.

Excel switches you to the Dashboard sheet with your bar chart placed there. Now repeat the process for the line chart.

  • Click the Calculations sheet tab again.
  • Click any cell inside your MonthlySales Pivot Table.
  • Click the PivotTable Analyze tab on the ribbon.
  • Click PivotChart.
  • In the Insert Chart dialog, select Line on the left.
  • Choose Line with Markers (the second option).
  • Click OK.
  • Click the new line chart to select it.
  • Click the Design tab (under Chart Tools).
  • Click Move Chart.
  • Select Object in and choose Dashboard.
  • Click OK.

Both charts are now on the Dashboard sheet. Drag them to arrange them side by side, with the bar chart on the left and the line chart on the right.

Why two chart types?

A bar chart compares totals across clients at a glance. A line chart shows trends over time.

Together they answer the two most common questions a manager asks: "who are my biggest clients?" and "how are sales trending?"

Chart not appearing on Dashboard?

Make sure you selected Object in (not New sheet) in the Move Chart dialog. If you accidentally chose New sheet, a separate chart sheet was created. Delete that sheet and try again.

help me move my PivotChart to the Dashboard sheet

Add slicers and a timeline filter

A Slicer is a clickable button panel that filters your Pivot Tables and charts instantly. A Timeline does the same thing but specifically for date fields, letting you drag across months or quarters.

  • Click anywhere on the bar chart on your Dashboard sheet.
  • On the ribbon, click the PivotTable Analyze tab.
  • Click Insert Slicer.
  • In the dialog, check ClientName and ProductCategory.
  • Click OK.

Two slicer panels appear on your Dashboard. Now add the Timeline.

  • Click anywhere on the bar chart again to re-select it.
  • Click the PivotTable Analyze tab on the ribbon.
  • Click Insert Timeline.
  • Check OrderDate and click OK.

The Timeline appears on your Dashboard. It defaults to filtering by months. You can click the dropdown on the Timeline to switch between years, quarters, months, or days.

What is Report Connections?

Right now, your slicers and timeline only control the one Pivot Table you clicked when inserting them. Report Connections lets you link a single slicer to multiple Pivot Tables so one click filters everything at once.

Connect your slicers and timeline to both Pivot Tables so they filter the entire dashboard.

  • Click the ClientName slicer to select it.
  • On the ribbon, click the Options tab (under Slicer Tools).
  • Click Report Connections.
  • In the dialog, check both SalesByClient and MonthlySales.
  • Click OK.

Now do the same for the ProductCategory slicer.

  • Click the ProductCategory slicer to select it.
  • On the ribbon, click the Options tab (under Slicer Tools).
  • Click Report Connections.
  • Check both SalesByClient and MonthlySales.
  • Click OK.

Finally, connect the Timeline the same way.

  • Click the Timeline to select it.
  • On the ribbon, click the Options tab (under Timeline Tools).
  • Click Report Connections.
  • Check both SalesByClient and MonthlySales.
  • Click OK.

Test it out by clicking a client name in the ClientName slicer. Both charts should update instantly.

  • Click the slicer's clear filter button (the funnel icon with an X) to reset when you are done testing.

Slicer not filtering one of the charts?

This means the Report Connections dialog was not checked for both Pivot Tables. Click the slicer, go to the Options tab, click Report Connections, and confirm both checkboxes are ticked.

help me connect my slicer to multiple Pivot Tables

Format your dashboard for a professional look

A clean layout makes the difference between a practice file and something you can put in a portfolio. Removing visual clutter and adding a title gives the dashboard a finished, professional feel.

  • Make sure you are on the Dashboard sheet.
  • Click the View tab on the ribbon.
  • Uncheck the Gridlines checkbox in the Show group.

The grid disappears, giving you a clean white canvas. Now add a title row.

  • Click cell A1 on the Dashboard sheet.
  • Type your name followed by a space, then type Client Orders Dashboard.
  • Select the text you just typed.
  • On the Home tab, set the font size to 18.
  • Click Bold.
  • Drag your charts to sit below the title row. Place the bar chart on the left and the line chart on the right.
  • Position the slicers to the right of the charts or underneath them.
  • Resize the slicers so they are compact and evenly spaced.
  • Position the Timeline below the two charts, spanning the full width.

Align objects quickly

Hold Alt while dragging charts and slicers. This snaps them to the cell grid, making it easy to line everything up evenly even with gridlines turned off.

  • Press Ctrl+S to save your workbook.

Your Dashboard sheet now has a title, two charts, two slicers, and a timeline. Click different client names and date ranges to watch everything update in real time.

Charts overlapping or hard to arrange?

Click a chart edge (not inside the chart area) to get the move cursor. Drag by the border to reposition. Use the corner handles to resize proportionally.

If objects are stacked on top of each other, right-click and choose Send to Back or Bring to Front to reorder them.

help me arrange my Excel dashboard charts and slicers neatly

Secret mission

Add Conditional Formatting for Overdue Orders and Top Clients

Your dashboard shows the numbers, but can it highlight what matters most? Add visual intelligence with conditional formatting that flags overdue orders in red and spotlights your top clients in gold.

Clean Up Your Resources

Clean Up Your Resources

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

Decide whether to keep your workbook file, or delete it if you no longer need it.

Resources you used:

  • The Client-Orders-Tracker.xlsx workbook file saved on your computer.

Keep everything running

No action needed. Choose this if you want to keep building on your workbook, practice with more data, or use it as a portfolio piece.

  • Your Client-Orders-Tracker.xlsx file stays wherever you saved it.
  • Consider uploading the file to OneDrive or Google Drive so you can share a link with potential employers.

Pause - I'll come back to this later

Since this is a local Excel file, there is nothing to pause. Your workbook stays on your computer with no ongoing cost.

  • Close Excel if it is still open.
  • Your file remains saved and ready to reopen whenever you come back.

Delete - I don't want to use this again

Remove the project file from your computer entirely.

  • Close Excel if it is still open.
  • On macOS, open Finder, navigate to the folder where you saved Client-Orders-Tracker.xlsx, right-click the file, and select Move to Trash.
  • On Windows, open File Explorer, navigate to the folder where you saved Client-Orders-Tracker.xlsx, right-click the file, and select Delete.
  • Empty your Trash (macOS) or Recycle Bin (Windows) to permanently remove the file.

Nice Work!

Nice Work!

You just built a complete Client Orders Tracker and Dashboard in Excel. This is exactly the kind of artifact hiring managers want to see from remote admin and data entry candidates.

You've learned how to:

  • Cleaned messy data using TRIM, PROPER, Remove Duplicates, and Find & Replace to standardize inconsistent order records.
  • Written lookup and summary formulas including VLOOKUP, XLOOKUP, SUMIF, COUNTIF, and IF to calculate key business metrics.
  • Designed an interactive dashboard with Pivot Tables, charts, Slicers, and a Timeline filter that responds to clicks.
  • Added conditional formatting rules to flag overdue orders and highlight top clients by revenue.

Ready to quiz yourself?