The five rules

1. One header row. A single row of column names at the top. No titles or notes above it.
2. One row per record. One order, one job, one invoice line per row.
3. One kind of thing per column. Dates in the date column, amounts in the amount column. No "TBC" in a number column.
4. No merged cells, blank rows or blank columns inside the data.
5. No totals in the middle. Totals belong in a report, not in the data.

Make it a Table

1. Click any cell inside your data.
2. Press Ctrl+T (Mac: Cmd+T).
3. Tick My table has headers → OK.
4. On the Table Design tab, change Table Name to something short, like Orders.

Now formulas fill down by themselves, new rows are picked up automatically, and you can write Orders[Qty] instead of C2:C145.

Quick check

Tidy the text

Add a helper column next to Customer and type:

=PROPER(TRIM([@Customer]))

TRIM removes extra spaces, PROPER fixes the capitals. When it looks right, copy the helper column, then Paste Special → Values over the original and delete the helper.

For codes like states, use =UPPER(TRIM([@Region])).

Fix dates stored as text

A real date sits on the right of the cell. A date stored as text sits on the left, and it won't sort or group by month.

1. Select the Date column.
2. Data → Text to Columns → Next → Next.
3. Choose Date: DMY → Finish.

Or use a formula: =IF(ISNUMBER([@Date]),[@Date],DATEVALUE([@Date])).

Remove duplicates

1. Save a copy first.
2. Click in the Table → Data → Remove Duplicates.
3. Tick Order ID only → OK.

Excel tells you how many rows it removed. Check the number makes sense.

Flash Fill

Type the result you want in the first cell next to your data (say, just the first word of a name), then press Ctrl+E. Excel copies the pattern down.

Quick check

The pattern

=XLOOKUP(what to find, where to look, what to bring back, [if not found])

In the Orders Table, add a column called Category and type:

=XLOOKUP([@Product],Products[Code],Products[Category],"Not found")

It fills down the whole Table by itself.

Why not VLOOKUP?

VLOOKUP counts columns, so it breaks when someone inserts one, and it guesses a match unless you add FALSE. XLOOKUP points straight at the columns and matches exactly by default.

XLOOKUP is in Microsoft 365 and Excel 2021 or later. On older versions use:

=INDEX(Products[Category],MATCH([@Product],Products[Code],0))

When it says 'Not found'

Nearly always the two values don't really match: an extra space, a different spelling, or a number stored as text. Clean both sides (lesson 2) and it will find it.

Quick check

Add Revenue

In the Orders Table add a column Revenue:

=[@Qty]*[@[Unit price]]

SUMIFS

=SUMIFS(what to add, where to check, what to match, …)

Revenue for Victoria:

=SUMIFS(Orders[Revenue],Orders[Region],"VIC")

Revenue for Victoria in March — two date conditions, on or after the 1st and before the next 1st:

=SUMIFS(Orders[Revenue],Orders[Region],"VIC",Orders[Date],">="&DATE(2026,3,1),Orders[Date],"<"&DATE(2026,4,1))

COUNTIFS and AVERAGEIFS

Same pattern. Orders over $1,000: =COUNTIFS(Orders[Revenue],">1000"). Average order in Victoria: =AVERAGEIFS(Orders[Revenue],Orders[Region],"VIC").

Tip: point at a cell instead of typing "VIC", so one formula can be copied across the whole grid.

Quick check

Build it

1. Click in the Orders Table → Insert → PivotTable → New Worksheet → OK.
2. Drag Customer to Rows.
3. Drag Revenue to Values.
4. Drag Date to Columns. Right-click a date → Group → Months (and Years).

Make it readable

• Right-click a number → Number Format → Currency.
• Right-click → Sort → Largest to Smallest to rank customers.
• Right-click → Show Values As → % of Column Total to see each customer's share.

Refresh

A PivotTable doesn't update by itself. After adding orders: Data → Refresh All (Ctrl+Alt+F5). Because it's built on a Table, new rows are included.

Quick check

Pick the numbers

Ask: what would I want to know every Monday? Usually revenue, number of orders or jobs, average value, and one trend. Put these as big numbers across the top.

Charts

1. Click in a PivotTable → PivotTable Analyze → PivotChart.
2. Line for a trend over time. Bar for ranking (top customers). Avoid pie charts with more than three slices.
3. Cut to the Dashboard sheet and tidy the titles.

Slicers

1. Click a PivotTable → PivotTable Analyze → Insert Slicer → Region.
2. Right-click the slicer → Report Connections → tick every PivotTable.
3. Now one click filters the whole page.

Quick check

Stop typos at the door

1. Select the Region column.
2. Data → Data Validation → Allow: List.
3. Source: VIC,NSW,QLD,SA,WA,TAS,NT,ACT → OK.

Now nobody can type "Vic" or "victoria".

Make problems visible

Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values on Order ID. For blanks: New Rule → Format only cells that contain → Blanks.

Reconcile

Your report total should equal your data total. On the Checks sheet:

=IF(ABS(B4-C4)<0.01,"OK","CHECK")

where B4 is the report total and C4 is =SUM(Orders[Revenue]).

Where did this number come from?

Click the cell → Formulas → Trace Precedents. Ctrl+` shows every formula on the sheet.

Quick check

Power Query in five steps

1. Click in the Orders Table → Data → From Table/Range.
2. In Power Query, select Customer → Transform → Format → Trim, then Capitalize Each Word.
3. Select Date → Data Type → Date.
4. Select Order ID → Home → Remove Rows → Remove Duplicates.
5. Home → Close & Load.

Every step is saved in Applied Steps. Next month, paste in the new export and press Data → Refresh All. To pull a whole folder of monthly files, use Data → Get Data → From File → From Folder.

When to stop

Excel is brilliant for analysis. It struggles as the system a business runs on. You've outgrown it when:

• Several people type the same information into different files
• A report takes a day and one person to produce
• There's no record of who changed what
• The file is slow, or breaks when a column moves
• You need it on a phone, on site, or in front of a customer

What next

That's the course. If your spreadsheets are the business, take the free Excel Health Check — it tells you whether to fix, automate or move off, and you can book a free call with us to talk it through.

Quick check