Mathew K Analytics

Lesson 3 · Excel: Zero to Expert

Excel Tables, Data Cleaning, Sorting & Filtering (Full Course) | Excel 103

Sheet: Good Data Workbook: tablescleaning.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by…

⬇ Download workbook (.xlsx)

What you'll learn

Files for this lesson

Open the workbook in Excel (Microsoft 365 or Excel 2021) and follow along with the video.

📝 Step-by-step guide

Tables, Data Cleaning, Sorting and Filtering#

Sheet: Good Data

Workbook: tables_cleaning.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by step with the video. Each section below lists exactly what to click and type.

What good data looks like#

  • In A1 type Orders, January to March 2026
  • Format A1: bold, font size 14
  • Paste: at A3, rows [['Order ID', 'Date', 'Customer', 'Region', 'Product', 'Qty', 'Unit Price'], [1001, '7-Jan-2026', 'Riverbend School', 'North', 'Monitor', 14, 320], [1002, '8-Jan-2026', 'Wattle Accounting', 'North', 'Keyboard', 5, 85], [1003, '10-Jan-2026', 'Coastal Physio', 'West', 'Laptop', 1, 1450], [1004, '11-Jan-2026', 'Outback Freight', 'West', 'Monitor', 4, 320], [1005, '12-Jan-2026', 'Southern Cross Dental', 'North', 'Keyboard', 5, 85], [1006, '15-Jan-2026', 'Harbour Cafe', 'South', 'Laptop', 2, 1450], [1007, '17-Jan-2026', 'Outback Freight', 'North', 'Webcam', 6, 110], [1008, '19-Jan-2026', 'Koala Print', 'North', 'Keyboard', 23, 85], [1009, '21-Jan-2026', 'Outback Freight', 'North', 'Headset', 5, 150], [1010, '22-Jan-2026', 'Banksia Florist', 'West', 'Keyboard', 8, 85], [1011, '24-Jan-2026', 'Banksia Florist', 'East', 'Webcam', 20, 110], [1012, '26-Jan-2026', 'Summit Fitness', 'South', 'Monitor', 7, 320], [1013, '27-Jan-2026', 'Outback Freight', 'West', 'Keyboard', 11, 85], [1014, '29-Jan-2026', 'Redback Builders', 'North', 'Mouse', 16, 40], [1015, '30-Jan-2026', 'Koala Print', 'East', 'Keyboard', 15, 85], [1016, '1-Feb-2026', 'Harbour Cafe', 'North', 'Webcam', 15, 110], [1017, '3-Feb-2026', 'Wattle Accounting', 'West', 'Headset', 24, 150], [1018, '6-Feb-2026', 'Redback Builders', 'West', 'Laptop', 1, 1450], [1019, '7-Feb-2026', 'Summit Fitness', 'East', 'Laptop', 6, 1450], [1020, '10-Feb-2026', 'Coastal Physio', 'East', 'Headset', 24, 150], [1021, '11-Feb-2026', 'Koala Print', 'North', 'Webcam', 13, 110], [1022, '14-Feb-2026', 'Redback Builders', 'South', 'Laptop', 2, 1450], [1023, '16-Feb-2026', 'Banksia Florist', 'North', 'Webcam', 14, 110], [1024, '18-Feb-2026', 'Outback Freight', 'East', 'Webcam', 14, 110], [1025, '20-Feb-2026', 'Redback Builders', 'West', 'Webcam', 19, 110], [1026, '22-Feb-2026', 'Southern Cross Dental', 'South', 'Mouse', 14, 40], [1027, '23-Feb-2026', 'Southern Cross Dental', 'South', 'Monitor', 6, 320], [1028, '24-Feb-2026', 'Koala Print', 'East', 'Webcam', 20, 110], [1029, '26-Feb-2026', 'Coastal Physio', 'East', 'Laptop', 2, 1450], [1030, '28-Feb-2026', 'Outback Freight', 'North', 'Monitor', 24, 320], [1031, '3-Mar-2026', 'Coastal Physio', 'West', 'Mouse', 19, 40], [1032, '6-Mar-2026', 'Banksia Florist', 'West', 'Webcam', 5, 110], [1033, '7-Mar-2026', 'Southern Cross Dental', 'West', 'Monitor', 4, 320], [1034, '9-Mar-2026', 'Lighthouse Media', 'North', 'Laptop', 3, 1450], [1035, '10-Mar-2026', 'Koala Print', 'North', 'Laptop', 5, 1450], [1036, '12-Mar-2026', 'Bluegum Legal', 'South', 'Keyboard', 2, 85], [1037, '15-Mar-2026', 'Redback Builders', 'East', 'Monitor', 22, 320], [1038, '17-Mar-2026', 'Bluegum Legal', 'West', 'Webcam', 5, 110], [1039, '20-Mar-2026', 'Redback Builders', 'North', 'Webcam', 17, 110], [1040, '22-Mar-2026', 'Wattle Accounting', 'East', 'Laptop', 6, 1450]]
  • AutoFit the width of column B:G (double-click its right edge)
  • Format G4:G43: number format currency

Let's check this against our rules. Row three is a single header row: Order ID, Date, Customer, Region, Product, Qty, and Unit Price. Every row below it is exactly one order. Order 1001, on 7 January, for Riverbend School in the North region, was fourteen monitors at 320 dollars each. Each column holds one kind of thing. Dates are real dates, quantities are plain numbers, prices are numbers with a currency format. And there are no blank rows anywhere. The list runs from row four down to row forty-three without a single gap. That's forty orders.

  • Click C10
  • Press Ctrl+A

The entire list is selected, from A3 down to G43, including the headers. Look at the status bar at the bottom of the window. It says Count, 287. That's forty-one rows, the header plus forty orders, times seven columns. Excel found the edges of the data by itself. Sorting, filtering, tables and PivotTables all use this same trick to find your data.

  • Click A20
  • Press Shift+Space
  • Press Ctrl+Shift+Plus
  • Click C10
  • Press Ctrl+A

This time the selection stops at row nineteen. The status bar count drops to 119, which is just seventeen rows times seven columns. Excel thinks the data ends at the blank row. Everything below the gap is invisible to it. If you sorted now, only the top half would be sorted, and your orders would be scrambled. This is one of the most common mistakes I see in real workbooks. One blank row, and half the data silently drops out of a report.

  • Press Ctrl+Z
  • Click A4
  • Press Ctrl+Down
  • Press Ctrl+Up

Control plus an arrow key jumps to the edge of the data in that direction. On a list with ten thousand rows, that saves a lot of scrolling. And again, it only works properly when there are no gaps. A blank cell is like a wall that stops the jump.

Excel Tables#

Sheet: Tables

  • In A1 type Orders table
  • Format A1: bold, font size 14
  • Paste: at A3, rows [['Order ID', 'Date', 'Customer', 'Region', 'Product', 'Qty', 'Unit Price'], [1001, '7-Jan-2026', 'Riverbend School', 'North', 'Monitor', 14, 320], [1002, '8-Jan-2026', 'Wattle Accounting', 'North', 'Keyboard', 5, 85], [1003, '10-Jan-2026', 'Coastal Physio', 'West', 'Laptop', 1, 1450], [1004, '11-Jan-2026', 'Outback Freight', 'West', 'Monitor', 4, 320], [1005, '12-Jan-2026', 'Southern Cross Dental', 'North', 'Keyboard', 5, 85], [1006, '15-Jan-2026', 'Harbour Cafe', 'South', 'Laptop', 2, 1450], [1007, '17-Jan-2026', 'Outback Freight', 'North', 'Webcam', 6, 110], [1008, '19-Jan-2026', 'Koala Print', 'North', 'Keyboard', 23, 85], [1009, '21-Jan-2026', 'Outback Freight', 'North', 'Headset', 5, 150], [1010, '22-Jan-2026', 'Banksia Florist', 'West', 'Keyboard', 8, 85], [1011, '24-Jan-2026', 'Banksia Florist', 'East', 'Webcam', 20, 110], [1012, '26-Jan-2026', 'Summit Fitness', 'South', 'Monitor', 7, 320], [1013, '27-Jan-2026', 'Outback Freight', 'West', 'Keyboard', 11, 85], [1014, '29-Jan-2026', 'Redback Builders', 'North', 'Mouse', 16, 40], [1015, '30-Jan-2026', 'Koala Print', 'East', 'Keyboard', 15, 85], [1016, '1-Feb-2026', 'Harbour Cafe', 'North', 'Webcam', 15, 110], [1017, '3-Feb-2026', 'Wattle Accounting', 'West', 'Headset', 24, 150], [1018, '6-Feb-2026', 'Redback Builders', 'West', 'Laptop', 1, 1450], [1019, '7-Feb-2026', 'Summit Fitness', 'East', 'Laptop', 6, 1450], [1020, '10-Feb-2026', 'Coastal Physio', 'East', 'Headset', 24, 150], [1021, '11-Feb-2026', 'Koala Print', 'North', 'Webcam', 13, 110], [1022, '14-Feb-2026', 'Redback Builders', 'South', 'Laptop', 2, 1450], [1023, '16-Feb-2026', 'Banksia Florist', 'North', 'Webcam', 14, 110], [1024, '18-Feb-2026', 'Outback Freight', 'East', 'Webcam', 14, 110], [1025, '20-Feb-2026', 'Redback Builders', 'West', 'Webcam', 19, 110], [1026, '22-Feb-2026', 'Southern Cross Dental', 'South', 'Mouse', 14, 40], [1027, '23-Feb-2026', 'Southern Cross Dental', 'South', 'Monitor', 6, 320], [1028, '24-Feb-2026', 'Koala Print', 'East', 'Webcam', 20, 110], [1029, '26-Feb-2026', 'Coastal Physio', 'East', 'Laptop', 2, 1450], [1030, '28-Feb-2026', 'Outback Freight', 'North', 'Monitor', 24, 320], [1031, '3-Mar-2026', 'Coastal Physio', 'West', 'Mouse', 19, 40], [1032, '6-Mar-2026', 'Banksia Florist', 'West', 'Webcam', 5, 110], [1033, '7-Mar-2026', 'Southern Cross Dental', 'West', 'Monitor', 4, 320], [1034, '9-Mar-2026', 'Lighthouse Media', 'North', 'Laptop', 3, 1450], [1035, '10-Mar-2026', 'Koala Print', 'North', 'Laptop', 5, 1450], [1036, '12-Mar-2026', 'Bluegum Legal', 'South', 'Keyboard', 2, 85], [1037, '15-Mar-2026', 'Redback Builders', 'East', 'Monitor', 22, 320], [1038, '17-Mar-2026', 'Bluegum Legal', 'West', 'Webcam', 5, 110], [1039, '20-Mar-2026', 'Redback Builders', 'North', 'Webcam', 17, 110], [1040, '22-Mar-2026', 'Wattle Accounting', 'East', 'Laptop', 6, 1450]]
  • Table: range A3:G43, name Orders

And there it is. The data now has a coloured style, with banded rows, which means every second row is shaded so your eye can follow a row across the screen. Every heading now has a small arrow button. Those are filter buttons, and we'll use them soon. When a cell inside the table is selected, a new tab appears on the ribbon called Table Design. On the far left of it is the Table Name box, and that's where our name, Orders, lives. The default name would be Table1, which tells you nothing. Always give tables a short, clear name with no spaces, like Orders, Customers or Sales.

  • Format G4:G43: number format currency
  • AutoFit the width of column B:G (double-click its right edge)

Calculated columns#

  • In H3 type Sales
  • In H4 type the formula =[@Qty]*[@[Unit Price]]
  • Format H4:H43: number format currency
  • AutoFit the width of column H:H (double-click its right edge)

Look what happened. I typed the formula once, in H4, and Excel filled it all the way down to row forty-three. All forty orders have their sales. Order 1001 is fourteen times 320, which is 4,480 dollars. And the last order, 1040, is six laptops at 1,450 each, 8,700 dollars. Click any cell in that column and the formula bar shows exactly the same formula. That's the point of a calculated column: one formula, used consistently in every row, so nobody can accidentally overwrite one row with something different.

Structured references#

  • Starting in J3, type these rows (one value per cell, moving right):

    | Total sales | =SUM(Orders[Sales]) | |---|---|

  • Starting in J4, type these rows (one value per cell, moving right):

    | Orders | =ROWS(Orders) | |---|---|

  • Starting in J5, type these rows (one value per cell, moving right):

    | Average sale | =AVERAGE(Orders[Sales]) | |---|---|

  • Format K3:K5: number format currency
  • Format K4: number format general
  • AutoFit the width of column J:K (double-click its right edge)

Total sales for all forty orders is 98,575 dollars. ROWS of Orders gives forty. Notice it counts only the data rows, not the header. And the average sale is about 2,464 dollars.

Tables grow automatically#

  • Starting in A44, type these rows (one value per cell, moving right):

    | 1041 | 24-Mar-2026 | Harbour Cafe | South | Monitor | 3 | 320 | |---|---|---|---|---|---|---|

  • Click H44

The Sales formula filled itself in for the new row. Three times 320 is 960 dollars. The currency format and the banded colours came along too. And look up at our summary. Total sales went from 98,575 dollars to 99,535 dollars. ROWS now says forty-one, and the average sale is about 2,428 dollars. We didn't touch a single formula. That's why I put almost all my data in tables. Reports built on tables keep working as the data grows.

  • Click B10
  • Press Ctrl+Shift+T
  • Click H45

Excel automatically added a total under the last column. It shows 99,535 dollars, the same as our SUM formula. But look at the formula bar. It isn't SUM. It says SUBTOTAL, open bracket, one hundred and nine, comma, then the Sales column. SUBTOTAL is a special function that only counts rows you can see. The one hundred and nine means sum, and ignore hidden rows. That will matter a lot when we filter in a few minutes. When you click a cell in the Total Row, a small drop-down arrow appears next to it, with a list of functions: Average, Count, Max, Min, Sum and more. Choosing one just writes a SUBTOTAL formula with a different number.

  • In F45 type the formula =SUBTOTAL(109,Orders[Qty])
  • In G45 type the formula =SUBTOTAL(101,Orders[Unit Price])
  • In C45 type the formula =SUBTOTAL(103,Orders[Customer])

The quantity total is 431 units. The average unit price is about 439 dollars. And the customer column counts forty-one orders. So the Total Row can hold a different function in every column. The numbers to remember are one hundred and nine for sum, one hundred and one for average, and one hundred and three for count.

Sorting#

Sheet: Sorting

  • In A1 type Sorting orders
  • Format A1: bold, font size 14
  • Paste: at A3, rows [['Order ID', 'Date', 'Customer', 'Region', 'Product', 'Qty', 'Unit Price'], [1001, '7-Jan-2026', 'Riverbend School', 'North', 'Monitor', 14, 320], [1002, '8-Jan-2026', 'Wattle Accounting', 'North', 'Keyboard', 5, 85], [1003, '10-Jan-2026', 'Coastal Physio', 'West', 'Laptop', 1, 1450], [1004, '11-Jan-2026', 'Outback Freight', 'West', 'Monitor', 4, 320], [1005, '12-Jan-2026', 'Southern Cross Dental', 'North', 'Keyboard', 5, 85], [1006, '15-Jan-2026', 'Harbour Cafe', 'South', 'Laptop', 2, 1450], [1007, '17-Jan-2026', 'Outback Freight', 'North', 'Webcam', 6, 110], [1008, '19-Jan-2026', 'Koala Print', 'North', 'Keyboard', 23, 85], [1009, '21-Jan-2026', 'Outback Freight', 'North', 'Headset', 5, 150], [1010, '22-Jan-2026', 'Banksia Florist', 'West', 'Keyboard', 8, 85], [1011, '24-Jan-2026', 'Banksia Florist', 'East', 'Webcam', 20, 110], [1012, '26-Jan-2026', 'Summit Fitness', 'South', 'Monitor', 7, 320], [1013, '27-Jan-2026', 'Outback Freight', 'West', 'Keyboard', 11, 85], [1014, '29-Jan-2026', 'Redback Builders', 'North', 'Mouse', 16, 40], [1015, '30-Jan-2026', 'Koala Print', 'East', 'Keyboard', 15, 85], [1016, '1-Feb-2026', 'Harbour Cafe', 'North', 'Webcam', 15, 110], [1017, '3-Feb-2026', 'Wattle Accounting', 'West', 'Headset', 24, 150], [1018, '6-Feb-2026', 'Redback Builders', 'West', 'Laptop', 1, 1450], [1019, '7-Feb-2026', 'Summit Fitness', 'East', 'Laptop', 6, 1450], [1020, '10-Feb-2026', 'Coastal Physio', 'East', 'Headset', 24, 150], [1021, '11-Feb-2026', 'Koala Print', 'North', 'Webcam', 13, 110], [1022, '14-Feb-2026', 'Redback Builders', 'South', 'Laptop', 2, 1450], [1023, '16-Feb-2026', 'Banksia Florist', 'North', 'Webcam', 14, 110], [1024, '18-Feb-2026', 'Outback Freight', 'East', 'Webcam', 14, 110], [1025, '20-Feb-2026', 'Redback Builders', 'West', 'Webcam', 19, 110], [1026, '22-Feb-2026', 'Southern Cross Dental', 'South', 'Mouse', 14, 40], [1027, '23-Feb-2026', 'Southern Cross Dental', 'South', 'Monitor', 6, 320], [1028, '24-Feb-2026', 'Koala Print', 'East', 'Webcam', 20, 110], [1029, '26-Feb-2026', 'Coastal Physio', 'East', 'Laptop', 2, 1450], [1030, '28-Feb-2026', 'Outback Freight', 'North', 'Monitor', 24, 320], [1031, '3-Mar-2026', 'Coastal Physio', 'West', 'Mouse', 19, 40], [1032, '6-Mar-2026', 'Banksia Florist', 'West', 'Webcam', 5, 110], [1033, '7-Mar-2026', 'Southern Cross Dental', 'West', 'Monitor', 4, 320], [1034, '9-Mar-2026', 'Lighthouse Media', 'North', 'Laptop', 3, 1450], [1035, '10-Mar-2026', 'Koala Print', 'North', 'Laptop', 5, 1450], [1036, '12-Mar-2026', 'Bluegum Legal', 'South', 'Keyboard', 2, 85], [1037, '15-Mar-2026', 'Redback Builders', 'East', 'Monitor', 22, 320], [1038, '17-Mar-2026', 'Bluegum Legal', 'West', 'Webcam', 5, 110], [1039, '20-Mar-2026', 'Redback Builders', 'North', 'Webcam', 17, 110], [1040, '22-Mar-2026', 'Wattle Accounting', 'East', 'Laptop', 6, 1450]]
  • AutoFit the width of column B:G (double-click its right edge)
  • Sort: range A3:G43, by Customer, order asc

The whole list is now sorted by customer name. At the top are the 4 orders from Banksia Florist, then Bluegum Legal, and so on down to Wattle Accounting at the bottom. And notice that each order kept its own date, product and quantity. Excel moved complete rows, not just names. Within Banksia Florist, the orders are still in their original order, 1010, 1011, 1023 and 1032. Excel keeps the existing order for ties.

  • Sort: range A3:G43, by Qty, order desc

At the top are three orders of twenty-four units each: Coastal Physio, Outback Freight and Wattle Accounting. Next comes Koala Print with twenty-three, then Redback Builders with twenty-two. And at the very bottom are the single-laptop orders, with a quantity of one.

Multi-level sorting#

  • Sort: range A3:G43, by ['Region', 'Qty'], order ['asc', 'desc']

The regions now come in alphabetical order. East first, with 9 orders, then North with 14, South with 6 and West with 11. Inside East, the biggest order is at the top: Coastal Physio, 24 headsets. Then Redback Builders with 22. Scroll down to where North begins, and it starts again with its biggest order, Outback Freight with 24 monitors. The second level only breaks ties inside the first level. That's the key idea of multi-level sorting.

Custom sort order#

  • Sort: range A3:G43, by Order ID, order asc

And we're back to the original order, 1001 at the top and 1040 at the bottom. Here's a practical tip. Always keep an ID column, or a simple row number, in your data. Then no matter how many times you sort, you can always get back to the original order.

Filtering#

Sheet: Filtering

  • In A1 type Filtering orders
  • Format A1: bold, font size 14
  • Paste: at A6, rows [['Order ID', 'Date', 'Customer', 'Region', 'Product', 'Qty', 'Unit Price'], [1001, '7-Jan-2026', 'Riverbend School', 'North', 'Monitor', 14, 320], [1002, '8-Jan-2026', 'Wattle Accounting', 'North', 'Keyboard', 5, 85], [1003, '10-Jan-2026', 'Coastal Physio', 'West', 'Laptop', 1, 1450], [1004, '11-Jan-2026', 'Outback Freight', 'West', 'Monitor', 4, 320], [1005, '12-Jan-2026', 'Southern Cross Dental', 'North', 'Keyboard', 5, 85], [1006, '15-Jan-2026', 'Harbour Cafe', 'South', 'Laptop', 2, 1450], [1007, '17-Jan-2026', 'Outback Freight', 'North', 'Webcam', 6, 110], [1008, '19-Jan-2026', 'Koala Print', 'North', 'Keyboard', 23, 85], [1009, '21-Jan-2026', 'Outback Freight', 'North', 'Headset', 5, 150], [1010, '22-Jan-2026', 'Banksia Florist', 'West', 'Keyboard', 8, 85], [1011, '24-Jan-2026', 'Banksia Florist', 'East', 'Webcam', 20, 110], [1012, '26-Jan-2026', 'Summit Fitness', 'South', 'Monitor', 7, 320], [1013, '27-Jan-2026', 'Outback Freight', 'West', 'Keyboard', 11, 85], [1014, '29-Jan-2026', 'Redback Builders', 'North', 'Mouse', 16, 40], [1015, '30-Jan-2026', 'Koala Print', 'East', 'Keyboard', 15, 85], [1016, '1-Feb-2026', 'Harbour Cafe', 'North', 'Webcam', 15, 110], [1017, '3-Feb-2026', 'Wattle Accounting', 'West', 'Headset', 24, 150], [1018, '6-Feb-2026', 'Redback Builders', 'West', 'Laptop', 1, 1450], [1019, '7-Feb-2026', 'Summit Fitness', 'East', 'Laptop', 6, 1450], [1020, '10-Feb-2026', 'Coastal Physio', 'East', 'Headset', 24, 150], [1021, '11-Feb-2026', 'Koala Print', 'North', 'Webcam', 13, 110], [1022, '14-Feb-2026', 'Redback Builders', 'South', 'Laptop', 2, 1450], [1023, '16-Feb-2026', 'Banksia Florist', 'North', 'Webcam', 14, 110], [1024, '18-Feb-2026', 'Outback Freight', 'East', 'Webcam', 14, 110], [1025, '20-Feb-2026', 'Redback Builders', 'West', 'Webcam', 19, 110], [1026, '22-Feb-2026', 'Southern Cross Dental', 'South', 'Mouse', 14, 40], [1027, '23-Feb-2026', 'Southern Cross Dental', 'South', 'Monitor', 6, 320], [1028, '24-Feb-2026', 'Koala Print', 'East', 'Webcam', 20, 110], [1029, '26-Feb-2026', 'Coastal Physio', 'East', 'Laptop', 2, 1450], [1030, '28-Feb-2026', 'Outback Freight', 'North', 'Monitor', 24, 320], [1031, '3-Mar-2026', 'Coastal Physio', 'West', 'Mouse', 19, 40], [1032, '6-Mar-2026', 'Banksia Florist', 'West', 'Webcam', 5, 110], [1033, '7-Mar-2026', 'Southern Cross Dental', 'West', 'Monitor', 4, 320], [1034, '9-Mar-2026', 'Lighthouse Media', 'North', 'Laptop', 3, 1450], [1035, '10-Mar-2026', 'Koala Print', 'North', 'Laptop', 5, 1450], [1036, '12-Mar-2026', 'Bluegum Legal', 'South', 'Keyboard', 2, 85], [1037, '15-Mar-2026', 'Redback Builders', 'East', 'Monitor', 22, 320], [1038, '17-Mar-2026', 'Bluegum Legal', 'West', 'Webcam', 5, 110], [1039, '20-Mar-2026', 'Redback Builders', 'North', 'Webcam', 17, 110], [1040, '22-Mar-2026', 'Wattle Accounting', 'East', 'Laptop', 6, 1450]]
  • Table: range A6:G46, name FilterOrders
  • In H6 type Sales
  • In H7 type the formula =[@Qty]*[@[Unit Price]]
  • Format G7:H46: number format currency
  • AutoFit the width of column B:H (double-click its right edge)
  • Starting in J1, type these rows (one value per cell, moving right):

    | SUM | =SUM(FilterOrders[Sales]) | |---|---|

  • Starting in J2, type these rows (one value per cell, moving right):

    | SUBTOTAL | =SUBTOTAL(109,FilterOrders[Sales]) | |---|---|

  • Starting in J3, type these rows (one value per cell, moving right):

    | AGGREGATE | =AGGREGATE(9,5,FilterOrders[Sales]) | |---|---|

  • Starting in J4, type these rows (one value per cell, moving right):

    | Visible orders | =SUBTOTAL(103,FilterOrders[Order ID]) | |---|---|

  • Format K1:K3: number format currency

  • AutoFit the width of column J:K (double-click its right edge)

Right now nothing is filtered, so all three sums agree: 98,575 dollars. And there are forty visible orders. Keep your eye on these four numbers while we filter.

  • Filter: range A6:H46, field Region, criteria North

Now only the North orders are showing. There are 14 of them. The row numbers on the left turn blue and skip, which tells you rows are hidden. The Region button now shows a little funnel icon, so you can see which column is filtered. Now look at the top. SUM still says 98,575 dollars. SUM doesn't care about filters. It adds every row, hidden or not. But SUBTOTAL and AGGREGATE both say 35,105 dollars. That's the sales for just the North orders we can see. And the count says 14 visible orders. This is the big lesson. If you want a total that matches what's on screen after filtering, use SUBTOTAL or AGGREGATE, not SUM.

  • Filter: range A6:H46, field Sales, criteria >2000

Now we see North orders with sales over 2,000 dollars. Only 4 rows remain. They are order 1001 from Riverbend School, 4,480 dollars of monitors. Order 1030, Outback Freight, 7,680 dollars of monitors. Order 1034, Lighthouse Media, 4,350 dollars of laptops. And order 1035, Koala Print, 7,250 dollars of laptops. SUBTOTAL and AGGREGATE now show 23,760 dollars, and the visible count is 4. SUM still shows the grand total of 98,575 dollars.

  • Filter: range A6:H46, clear True

All forty rows are back, and all three sums agree again at 98,575 dollars.

  • Filter: range A6:H46, field Product, criteria ['Laptop', 'Monitor']

Now we see 16 orders, every laptop and monitor order, and the visible sales come to 66,520 dollars. So laptops and monitors are 16 of our forty orders, but about 67 percent of the sales. That's the kind of insight a quick filter gives you.

  • Filter: range A6:H46, clear True

Preview: the FILTER function#

  • In J6 type North orders over $2,000
  • In J7 type the formula =FILTER(FilterOrders[[Customer]:[Product]],(FilterOrders[Region]="North")*(FilterOrders[Sales]>2000))
  • AutoFit the width of column J:L (double-click its right edge)

One formula, and Excel spills the answer into 4 rows and three columns, with a thin blue border around the spilled area. The same four orders we found with the filter buttons: Riverbend School, Outback Freight, Lighthouse Media and Koala Print, with their regions and products. The difference is that this result is live. Change an order in the table, and the list updates by itself. We'll do much more with FILTER, SORT and UNIQUE in video five.

Freeze panes#

Sheet: Freeze

  • In A1 type Freeze panes
  • Format A1: bold, font size 14
  • Paste: at A3, rows [['Order ID', 'Date', 'Customer', 'Region', 'Product', 'Qty', 'Unit Price'], [1001, '7-Jan-2026', 'Riverbend School', 'North', 'Monitor', 14, 320], [1002, '8-Jan-2026', 'Wattle Accounting', 'North', 'Keyboard', 5, 85], [1003, '10-Jan-2026', 'Coastal Physio', 'West', 'Laptop', 1, 1450], [1004, '11-Jan-2026', 'Outback Freight', 'West', 'Monitor', 4, 320], [1005, '12-Jan-2026', 'Southern Cross Dental', 'North', 'Keyboard', 5, 85], [1006, '15-Jan-2026', 'Harbour Cafe', 'South', 'Laptop', 2, 1450], [1007, '17-Jan-2026', 'Outback Freight', 'North', 'Webcam', 6, 110], [1008, '19-Jan-2026', 'Koala Print', 'North', 'Keyboard', 23, 85], [1009, '21-Jan-2026', 'Outback Freight', 'North', 'Headset', 5, 150], [1010, '22-Jan-2026', 'Banksia Florist', 'West', 'Keyboard', 8, 85], [1011, '24-Jan-2026', 'Banksia Florist', 'East', 'Webcam', 20, 110], [1012, '26-Jan-2026', 'Summit Fitness', 'South', 'Monitor', 7, 320], [1013, '27-Jan-2026', 'Outback Freight', 'West', 'Keyboard', 11, 85], [1014, '29-Jan-2026', 'Redback Builders', 'North', 'Mouse', 16, 40], [1015, '30-Jan-2026', 'Koala Print', 'East', 'Keyboard', 15, 85], [1016, '1-Feb-2026', 'Harbour Cafe', 'North', 'Webcam', 15, 110], [1017, '3-Feb-2026', 'Wattle Accounting', 'West', 'Headset', 24, 150], [1018, '6-Feb-2026', 'Redback Builders', 'West', 'Laptop', 1, 1450], [1019, '7-Feb-2026', 'Summit Fitness', 'East', 'Laptop', 6, 1450], [1020, '10-Feb-2026', 'Coastal Physio', 'East', 'Headset', 24, 150], [1021, '11-Feb-2026', 'Koala Print', 'North', 'Webcam', 13, 110], [1022, '14-Feb-2026', 'Redback Builders', 'South', 'Laptop', 2, 1450], [1023, '16-Feb-2026', 'Banksia Florist', 'North', 'Webcam', 14, 110], [1024, '18-Feb-2026', 'Outback Freight', 'East', 'Webcam', 14, 110], [1025, '20-Feb-2026', 'Redback Builders', 'West', 'Webcam', 19, 110], [1026, '22-Feb-2026', 'Southern Cross Dental', 'South', 'Mouse', 14, 40], [1027, '23-Feb-2026', 'Southern Cross Dental', 'South', 'Monitor', 6, 320], [1028, '24-Feb-2026', 'Koala Print', 'East', 'Webcam', 20, 110], [1029, '26-Feb-2026', 'Coastal Physio', 'East', 'Laptop', 2, 1450], [1030, '28-Feb-2026', 'Outback Freight', 'North', 'Monitor', 24, 320], [1031, '3-Mar-2026', 'Coastal Physio', 'West', 'Mouse', 19, 40], [1032, '6-Mar-2026', 'Banksia Florist', 'West', 'Webcam', 5, 110], [1033, '7-Mar-2026', 'Southern Cross Dental', 'West', 'Monitor', 4, 320], [1034, '9-Mar-2026', 'Lighthouse Media', 'North', 'Laptop', 3, 1450], [1035, '10-Mar-2026', 'Koala Print', 'North', 'Laptop', 5, 1450], [1036, '12-Mar-2026', 'Bluegum Legal', 'South', 'Keyboard', 2, 85], [1037, '15-Mar-2026', 'Redback Builders', 'East', 'Monitor', 22, 320], [1038, '17-Mar-2026', 'Bluegum Legal', 'West', 'Webcam', 5, 110], [1039, '20-Mar-2026', 'Redback Builders', 'North', 'Webcam', 17, 110], [1040, '22-Mar-2026', 'Wattle Accounting', 'East', 'Laptop', 6, 1450]]
  • AutoFit the width of column B:G (double-click its right edge)
  • Click A4 and choose View → Freeze Panes
  • Press Ctrl+Down

We're down at order 1040, on row forty-three, but look at the top of the screen. Rows one to three, with the title and the headings, are still there. There's a thin line under row three. That's the freeze line. Everything above it stays put while the rest scrolls underneath.

  • Press Ctrl+Home
  • Click B4 and choose View → Freeze Panes
  • Choose View → Freeze Panes → Unfreeze Panes

Data cleaning#

Sheet: Cleaning

  • In A1 type Customer export
  • Format A1: bold, font size 14
  • Format C4:C28: number format text
  • Format E4:E28: number format text
  • Paste: at A3, rows [['Name', 'Email', 'Phone', 'State', 'Joined'], [' sarah connor', 'Sarah.Connor@Mail.com', '0412 345 678', 'nsw', '9-Jan-2026'], ['JAMES WILSON', 'J.WILSON@OUTLOOK.COM', '0423-456-789', 'Victoria', '14 January 2026'], ['priya patel', ' priya.p@gmail.com', '+61 434 567 890', 'QLD', '2026-01-20'], ["liam o'brien", 'liam.obrien@mail.com', '(02) 9876 5432', 'NSW ', ' 23-Jan-2026'], ['Chloe Nguyen ', 'chloe.n@GMAIL.com', '0445678901', 'vic', '2 Feb 2026'], ['oliver SMITH', 'OSmith@bigpond.com', '(03) 8765 4321', 'Victoria', '5-Feb-2026'], ['Mia Thompson', 'mia.t@outlook.com ', '0456 789 012', 'wa', '2026-02-11'], ['noah brown', 'Noah.Brown@mail.com', '+61 467 890 123', 'sa', '14 February 2026'], ['Sarah Connor ', 'sarah.connor@mail.com', '0412-345-678', 'NSW', '2026-01-09'], ['AVA MARTIN', 'ava.martin@gmail.com', '0478 901 234', 'qld', '19-Feb-2026'], [' ethan lee', 'ETHAN.LEE@MAIL.COM', '0489012345', 'Nsw', '25-Feb-2026'], ['grace kelly', 'grace.k@outlook.com', '0490-123-456', 'tas', '2 March 2026'], ['Lucas Rossi', 'Lucas.Rossi@gmail.com', '+61 401 234 567', 'VIC', '6-Mar-2026'], ['zoe campbell', 'zoe.c@mail.com', '0411 223 344', 'nsw', '2026-03-10'], ['JACK MURPHY ', 'jack.murphy@bigpond.com', '0422-334-455', 'Qld', '13-Mar-2026'], ['isla walker', 'Isla.W@Gmail.com', '0433445566', 'WA', '18 Mar 2026'], ['Ethan Lee', 'ethan.lee@mail.com ', '+61 489 012 345', 'NSW', '2026-02-25'], ['henry mcdonald', 'henry.mcd@mail.com', '0444 556 677', 'NSW', '24-Mar-2026'], ['ruby chen', 'Ruby.Chen@Outlook.com', '0455-667-788', 'Victoria', '27-Mar-2026'], ['Leo Kowalski', 'leo.k@gmail.com', '+61 466 778 899', ' SA', '1 April 2026'], ['emily DAVIS', 'emily.davis@mail.com', '0477 889 900', 'QLD', '2026-04-07'], ['RUBY CHEN', 'ruby.chen@outlook.com ', '0455 667 788', 'vic', '27 March 2026'], ['samuel ali', 'Sam.Ali@gmail.com', '0488-990-011', 'nsw', '10-Apr-2026'], ['Hannah Scott', 'HANNAH.S@OUTLOOK.COM', '+61 3 9988 7766', 'victoria', '15 April 2026'], [' daniel park ', 'dan.park@mail.com', '0499 001 122', 'WA', '21-Apr-2026']]
  • AutoFit the width of column A:E (double-click its right edge)

Let's look at this mess. Names are in every style. Some all lower case, like sarah connor, some all capitals, like JAMES WILSON, and some have extra spaces before, after, or in the middle. Emails have random capital letters, and a few have a sneaky space at the end, which you can't even see. Phone numbers come in five different formats. Spaces, dashes, brackets around the area code, and plus sixty-one at the start instead of a zero. The State column says NSW, nsw, Victoria, vic, all meaning the same few states. And the Joined dates are text in three different styles. And if you look closely, some people appear twice. Sarah Connor is on row four and again on row twelve, typed differently. We'll find all of those.

TRIM, PROPER, UPPER, LOWER and CLEAN#

  • Starting in G3, type these rows (one value per cell, moving right):

    | Name | Email | Phone | State | Joined | |---|---|---|---|---|

  • Format G3:K3: bold

  • In G4 type the formula =PROPER(TRIM(CLEAN(A4)))

  • In H4 type the formula =LOWER(TRIM(B4))
  • Fill G4:H28 down (drag the fill handle, or select it and press Ctrl+D)
  • AutoFit the width of column G:H (double-click its right edge)

Look at the clean names. sarah connor with two leading spaces is now Sarah Connor. JAMES WILSON is James Wilson. And priya with two spaces in the middle is Priya Patel with just one. Liam O'Brien even got a capital B after the apostrophe, because PROPER treats the apostrophe as the start of a new word. And every email is now lower case with no stray spaces. Sarah's email, which was Sarah dot Connor at Mail dot com with capitals, is now all lower case.

  • Starting in M3, type these rows (one value per cell, moving right):

    | Length before | Length after | |---|---|

  • Starting in M4, type these rows (one value per cell, moving right):

    | =LEN(A4) | =LEN(G4) | |---|---|

  • Fill M4:N6 down (drag the fill handle, or select it and press Ctrl+D)

On row four, the messy name is fourteen characters long, but the clean name is only twelve. Sarah Connor is twelve characters, so the two extra were the hidden spaces at the front. On row six, priya with a double space was twelve characters, and the clean Priya Patel is eleven. TRIM squashed the double space down to one. LEN is my favourite detective tool for cleaning. When two values look identical but Excel says they're different, compare their lengths, and there's usually a hidden space.

  • Clear M3:N6 (Delete key)
  • In J4 type the formula =UPPER(TRIM(D4))
  • Fill J4:J28 down (drag the fill handle, or select it and press Ctrl+D)

nsw, Nsw and NSW with a space are all now plain NSW. vic is VIC, qld is QLD. But look at the rows that said Victoria. They're now VICTORIA in capitals, not VIC. There are 4 of those. UPPER can fix capitals, but it can't turn a full name into a code. We'll fix those with Find and Replace in a minute.

SUBSTITUTE for phone numbers#

  • In I4 type the formula =SUBSTITUTE(C4," ","")

Sarah's number was zero four one two, three four five, six seven eight, with spaces. Now it's ten digits with no spaces. That works for this row. But other rows have dashes, brackets and plus sixty-one. One SUBSTITUTE only handles one thing, so we nest them, each one wrapped around the last.

  • In I4 type the formula =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(C4)," ",""),"-",""),"(",""),")",""),"+61","0")
  • Fill I4:I28 down (drag the fill handle, or select it and press Ctrl+D)
  • AutoFit the width of column I:I (double-click its right edge)

Every phone number is now ten digits. The landline in brackets, zero two, nine eight seven six, five four three two, is now zero two nine eight seven six five four three two. And the mobile that started with plus sixty-one four three four, is now zero four three four, five six seven, eight nine zero. The country code became a leading zero. Because SUBSTITUTE returns text, the leading zeros are kept. If these were turned into numbers, the zeros would vanish. Long nested formulas look scary, but read them from the inside out and each layer does one tiny job.

DATEVALUE for text dates#

  • In K4 type the formula =DATEVALUE(TRIM(E4))
  • Fill K4:K28 down (drag the fill handle, or select it and press Ctrl+D)

We get numbers like 46,031. Remember from video two, Excel stores every date as a serial number, the count of days since the start of nineteen hundred. So 46,031 is 9 January 2026. It just needs a date format to look like one.

  • Format K4:K28: number format short date
  • AutoFit the width of column K:K (double-click its right edge)

Now they're proper dates, all in the same style, and they sit on the right side of the cells. DATEVALUE understood all three text styles: 9 dash Jan dash 2026, 14 January 2026 written out, and the year-first style, 2026 dash 01 dash 20. One warning: DATEVALUE reads text using your computer's regional settings. On an Australian computer, a text date like 3 slash 11 means the third of November, but on an American computer it means the eleventh of March. Dates with the month written as a word avoid that problem.

Paste as values#

  • Select G3:K28
  • Press Ctrl+C
  • Press Ctrl+Alt+V
  • Press V
  • Press Enter
  • Press Esc
  • Click G4

The formula bar now shows just the text, Sarah Connor. No formula. The value is locked in place, and it no longer depends on column A. The Home tab route is Paste, then under Paste Values, the button with one two three on it. Both give the same result.

  • Select A:F
  • Press Ctrl+Minus
  • In A1 type Customers (clean)
  • Format A1: bold, font size 14
  • AutoFit the width of column A:E (double-click its right edge)

Now we have one tidy list in columns A to E, with clean names, emails, phones, states and real dates. A tip: in real work, I'd usually keep the original export on its own sheet and never delete it. Then if a cleaning step goes wrong, you can always start again.

Find and Replace#

  • Select D4:D28
  • Press Ctrl+H
  • Type VICTORIA
  • Press Tab
  • Type VIC
  • Press Alt+A
  • Press Enter
  • Press Esc

Every state is now a short code. NSW, VIC, QLD, WA, SA and TAS. By default, Find and Replace isn't case sensitive, and it matches part of a cell too. If you search for VIC, it would also find the VIC inside VICTORIA. The Options button lets you tick Match case or Match entire cell contents when you need to be precise.

Remove Duplicates#

  • Select A3:E28
  • Ribbon: path Data > Remove Duplicates, ok True
  • Press Enter

The message said three duplicate values were found and removed, and twenty-two unique values remain. The second copies of Sarah Connor, Ethan Lee and Ruby Chen are gone. Ruby is a great example: one copy said Victoria and the other said vic. Only after Find and Replace did they match. Our list now runs from row four to row twenty-five. Twenty-two real customers. A warning: Remove Duplicates deletes rows permanently. If you're not sure, highlight duplicates first with conditional formatting, which we'll see shortly, and look before you delete.

  • In A19 type Henry McDonald

Functions get you ninety-nine percent of the way. Always scan the result with your own eyes for the last few odd cases.

Flash Fill#

  • In F3 type First name
  • In F4 type Sarah
  • Ribbon: path Data > Flash Fill

Excel looked at my example, saw that Sarah is the part before the space in Sarah Connor, and filled in every first name down to row twenty-five. James, Priya, Liam, Chloe and so on. Flash Fill is fantastic for quick one-off jobs: first names, initials, pulling numbers out of codes, or joining text together. But there's a catch. Flash Fill types plain values, not formulas. If a name changes later, the first name won't update. When you need something that stays live, use a formula.

TEXTBEFORE, TEXTAFTER and TEXTSPLIT#

  • Starting in G3, type these rows (one value per cell, moving right):

    | First | Last | |---|---|

  • In G4 type the formula =TEXTBEFORE(A4," ")

  • In H4 type the formula =TEXTAFTER(A4," ")

  • Fill G4:H25 down (drag the fill handle, or select it and press Ctrl+D)

G4 says Sarah and H4 says Connor. Liam O'Brien splits into Liam and O'Brien. And on row 19, the last name is McDonald with a capital D, because the formula is reading the cell we fixed by hand. Formulas stay live.

  • Starting in J3, type these rows (one value per cell, moving right):

    | Split 1 | Split 2 | |---|---|

  • In J4 type the formula =TEXTSPLIT(A4," ")

  • Fill J4:J25 down (drag the fill handle, or select it and press Ctrl+D)

  • AutoFit the width of column F:K (double-click its right edge)

One formula in J4, but the result spills into two cells, J4 and K4. Sarah and Connor. Click K4, and the formula bar shows the formula in grey, because it lives in J4. If a name had three parts, like Mary Anne Smith, TEXTSPLIT would spill into three cells. So always leave room on the right. TEXTSPLIT can also split down into rows, and use several delimiters at once, like commas and semicolons. It's the formula version of the old Text to Columns tool on the Data tab.

A cleaning checklist#

Data validation#

Sheet: Validation

  • In A1 type New orders

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | Order ID | Region | Product | Qty | Status | |---|---|---|---|---|

  • Format A3:E3: bold, font colour White, Background 1, fill Blue, Accent 1

  • Validation: range B4:B15, list ['North', 'South', 'East', 'West'], input_message Pick a region from the list.
  • Validation: range C4:C15, list ['Laptop', 'Monitor', 'Headset', 'Webcam', 'Keyboard', 'Mouse'], input_message Pick a product from the list.
  • Validation: range D4:D15, whole True, between [1, 100], input_message Whole number from 1 to 100.
  • Validation: range E4:E15, list ['Pending', 'Shipped', 'Delivered'], input_message Pending, Shipped or Delivered.
  • Click B4
  • Press Alt+Down
  • Press Esc

Drop-down lists are the best way to keep spelling consistent. Nobody can type Nth, or north with a space, or Victoria instead of VIC. That's the problem we spent all that time cleaning up, solved before it starts.

  • Starting in A4, type these rows (one value per cell, moving right):

    | 2001 | North | Laptop | 2 | Pending | |---|---|---|---|---| | 2002 | South | Mouse | 12 | Shipped | | 2003 | East | Monitor | 4 | Delivered |

All three rows were accepted, because every value follows the rules.

  • Starting in A7, type these rows (one value per cell, moving right):

    | 2004 | Wst | |---|---|

  • Press Esc

  • Starting in B7, type these rows (one value per cell, moving right):

    | West | Webcam | 150 | |---|---|---|

  • Press Esc

  • Starting in D7, type these rows (one value per cell, moving right):

    | 15 | Pending | |---|---|

The fourth order is in, with a quantity of fifteen. The rules caught both mistakes before they could get into our data. On the Error Alert tab of the Data Validation box, you can write your own error message, and choose a style. Stop blocks the entry completely, which is what we saw. Warning and Information just warn people but let them carry on.

Conditional formatting#

Sheet: Formatting

  • In A1 type Conditional formatting
  • Format A1: bold, font size 14
  • Paste: at A3, rows [['Order ID', 'Date', 'Customer', 'Region', 'Product', 'Qty', 'Unit Price', 'Sales'], [1001, '7-Jan-2026', 'Riverbend School', 'North', 'Monitor', 14, 320, 4480], [1002, '8-Jan-2026', 'Wattle Accounting', 'North', 'Keyboard', 5, 85, 425], [1003, '10-Jan-2026', 'Coastal Physio', 'West', 'Laptop', 1, 1450, 1450], [1004, '11-Jan-2026', 'Outback Freight', 'West', 'Monitor', 4, 320, 1280], [1005, '12-Jan-2026', 'Southern Cross Dental', 'North', 'Keyboard', 5, 85, 425], [1006, '15-Jan-2026', 'Harbour Cafe', 'South', 'Laptop', 2, 1450, 2900], [1007, '17-Jan-2026', 'Outback Freight', 'North', 'Webcam', 6, 110, 660], [1008, '19-Jan-2026', 'Koala Print', 'North', 'Keyboard', 23, 85, 1955], [1009, '21-Jan-2026', 'Outback Freight', 'North', 'Headset', 5, 150, 750], [1010, '22-Jan-2026', 'Banksia Florist', 'West', 'Keyboard', 8, 85, 680], [1011, '24-Jan-2026', 'Banksia Florist', 'East', 'Webcam', 20, 110, 2200], [1012, '26-Jan-2026', 'Summit Fitness', 'South', 'Monitor', 7, 320, 2240], [1013, '27-Jan-2026', 'Outback Freight', 'West', 'Keyboard', 11, 85, 935], [1014, '29-Jan-2026', 'Redback Builders', 'North', 'Mouse', 16, 40, 640], [1015, '30-Jan-2026', 'Koala Print', 'East', 'Keyboard', 15, 85, 1275], [1016, '1-Feb-2026', 'Harbour Cafe', 'North', 'Webcam', 15, 110, 1650], [1017, '3-Feb-2026', 'Wattle Accounting', 'West', 'Headset', 24, 150, 3600], [1018, '6-Feb-2026', 'Redback Builders', 'West', 'Laptop', 1, 1450, 1450], [1019, '7-Feb-2026', 'Summit Fitness', 'East', 'Laptop', 6, 1450, 8700], [1020, '10-Feb-2026', 'Coastal Physio', 'East', 'Headset', 24, 150, 3600], [1021, '11-Feb-2026', 'Koala Print', 'North', 'Webcam', 13, 110, 1430], [1022, '14-Feb-2026', 'Redback Builders', 'South', 'Laptop', 2, 1450, 2900], [1023, '16-Feb-2026', 'Banksia Florist', 'North', 'Webcam', 14, 110, 1540], [1024, '18-Feb-2026', 'Outback Freight', 'East', 'Webcam', 14, 110, 1540], [1024, '20-Feb-2026', 'Redback Builders', 'West', 'Webcam', 19, 110, 2090], [1026, '22-Feb-2026', 'Southern Cross Dental', 'South', 'Mouse', 14, 40, 560], [1027, '23-Feb-2026', 'Southern Cross Dental', 'South', 'Monitor', 6, 320, 1920], [1028, '24-Feb-2026', 'Koala Print', 'East', 'Webcam', 20, 110, 2200], [1029, '26-Feb-2026', 'Coastal Physio', 'East', 'Laptop', 2, 1450, 2900], [1030, '28-Feb-2026', 'Outback Freight', 'North', 'Monitor', 24, 320, 7680], [1031, '3-Mar-2026', 'Coastal Physio', 'West', 'Mouse', 19, 40, 760], [1032, '6-Mar-2026', 'Banksia Florist', 'West', 'Webcam', 5, 110, 550], [1033, '7-Mar-2026', 'Southern Cross Dental', 'West', 'Monitor', 4, 320, 1280], [1034, '9-Mar-2026', 'Lighthouse Media', 'North', 'Laptop', 3, 1450, 4350], [1035, '10-Mar-2026', 'Koala Print', 'North', 'Laptop', 5, 1450, 7250], [1036, '12-Mar-2026', 'Bluegum Legal', 'South', 'Keyboard', 2, 85, 170], [1037, '15-Mar-2026', 'Redback Builders', 'East', 'Monitor', 22, 320, 7040], [1038, '17-Mar-2026', 'Bluegum Legal', 'West', 'Webcam', 5, 110, 550], [1039, '20-Mar-2026', 'Redback Builders', 'North', 'Webcam', 17, 110, 1870], [1040, '22-Mar-2026', 'Wattle Accounting', 'East', 'Laptop', 6, 1450, 8700]]
  • Format G4:H43: number format currency
  • AutoFit the width of column B:H (double-click its right edge)

Highlight cell rules#

  • Conditional formatting on F4:F43: highlight cells greater than 20

5 cells light up. Koala Print with twenty-three, three orders of twenty-four, and Redback Builders with twenty-two. Everything twenty or below stays plain. The rule is strictly greater than, so exactly twenty would not be highlighted.

  • Conditional formatting on A4:A43: highlight duplicates

Two cells light up. Order 1024 appears twice, on row twenty-seven and on row twenty-eight. The second one should have been 1025. This is the safe way to check duplicates. You see them first and decide what to do, rather than deleting rows blindly.

  • Conditional formatting on H4:H43: highlight the top 5 values

The five biggest sales are highlighted: two orders of 8,700 dollars, then 7,680 dollars, 7,250 dollars and 7,040 dollars. The same menu has Bottom 10, Top 10 percent, and Above Average, which is great for spotting unusually good or bad results.

  • Ribbon: path Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet

All the highlights are gone, but the data is untouched. Clearing rules never changes values.

Data bars and colour scales#

  • Conditional formatting on H4:H43: highlight data_bar
  • Conditional formatting on F4:F43: highlight color_scale

Each sale now has a blue bar. The longest bars belong to the two 8,700 dollar laptop orders, and the tiny bars are the small keyboard and mouse orders. You can read the pattern in a second without reading a single number. In the Qty column, the colour scale shades the biggest quantities green, the middle ones yellow, and the smallest red. It's like a heat map. Data bars work best on one column of amounts. Colour scales are great for big grids of numbers, like sales by month and region.

  • Ribbon: path Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet

Formula-based rules for whole rows#

  • Conditional formatting on A4:H43: highlight formula

Every North order is shaded light blue across the whole row, all 14 of them. Order 1001 on row four, 1002, 1005 and so on. If someone changed a region to North, that row would turn blue straight away. The most common mistake here is forgetting the dollar sign. Without it, the rule slides sideways too, so column B looks at E, column C looks at F, and you get random stripes of colour.

Project: clean and analyse a sales export#

Sheet: Project

  • In A1 type Sales export, July to September 2026
  • Format A1: bold, font size 14
  • Paste: at A3, rows [['Order ID', 'Order Date', 'Ship Date', 'Rep', 'Region', 'Product', 'Qty', 'Unit Price'], [5001, '3-Jul-2026', '10-Jul-2026', 'ben carter', 'west', 'Webcam', 18, 110], [5002, '6-Jul-2026', '8-Jul-2026', 'Ben Carter', 'WEST', 'Mouse', 21, 40], [5003, '8-Jul-2026', '15-Jul-2026', 'Tom Nguyen', 'South', 'Laptop', 1, 1450], [5004, '11-Jul-2026', '17-Jul-2026', 'Tom Nguyen', 'South', 'Keyboard', 2, 85], [5005, '12-Jul-2026', '13-Jul-2026', 'Tom Nguyen', ' SOUTH', 'Monitor', 21, 320], [5006, '14-Jul-2026', '22-Jul-2026', 'TOM NGUYEN', 'South', 'Keyboard', 9, 85], [5007, '15-Jul-2026', '18-Jul-2026', 'PRIYA SHARMA', 'North', 'Webcam', 22, 110], [5008, '17-Jul-2026', '20-Jul-2026', 'Kate Wilson', 'East', 'Monitor', 18, 320], [5009, '20-Jul-2026', '23-Jul-2026', 'PRIYA SHARMA', 'North', 'Webcam', 5, 110], [5010, '21-Jul-2026', '22-Jul-2026', 'PRIYA SHARMA', 'North ', 'Monitor', 8, 320], [5011, '23-Jul-2026', '31-Jul-2026', 'BEN CARTER', ' West', 'Laptop', 4, 1450], [5012, '24-Jul-2026', '28-Jul-2026', 'Kate Wilson', 'east', 'Headset', 2, 150], [5013, '25-Jul-2026', '30-Jul-2026', 'PRIYA SHARMA', 'north', 'Laptop', 1, 1450], [5014, '28-Jul-2026', '31-Jul-2026', 'tom nguyen ', 'South', 'Webcam', 18, 110], [5015, '30-Jul-2026', '2-Aug-2026', 'Priya Sharma', 'NORTH', 'Webcam', 15, 110], [5016, '2-Aug-2026', '4-Aug-2026', ' Kate Wilson', 'East', 'Laptop', 4, 1450], [5017, '3-Aug-2026', '6-Aug-2026', 'Tom Nguyen', ' SOUTH', 'Monitor', 16, 320], [5018, '5-Aug-2026', '7-Aug-2026', 'ben carter', 'west', 'Laptop', 2, 1450], [5017, '3-Aug-2026', '6-Aug-2026', 'Tom Nguyen', ' SOUTH', 'Monitor', 16, 320], [5019, '6-Aug-2026', '11-Aug-2026', 'kate wilson', 'East', 'Headset', 21, 150], [5020, '9-Aug-2026', '17-Aug-2026', 'tom nguyen ', ' SOUTH', 'Webcam', 7, 110], [5021, '10-Aug-2026', '12-Aug-2026', 'tom nguyen ', 'South', 'Mouse', 8, 40], [5022, '12-Aug-2026', '14-Aug-2026', 'Priya Sharma', 'North', 'Webcam', 3, 110], [5023, '15-Aug-2026', '19-Aug-2026', 'KATE WILSON ', 'East', 'Monitor', 25, 320], [5024, '18-Aug-2026', '20-Aug-2026', 'Ben Carter', 'west', 'Headset', 18, 150], [5025, '19-Aug-2026', '26-Aug-2026', 'Ben Carter', ' West', 'Keyboard', 22, 85], [5026, '20-Aug-2026', '22-Aug-2026', 'TOM NGUYEN', 'South', 'Mouse', 2, 40], [5027, '22-Aug-2026', '24-Aug-2026', 'TOM NGUYEN', ' SOUTH', 'Laptop', 1, 1450], [5028, '25-Aug-2026', '27-Aug-2026', 'Tom Nguyen', ' SOUTH', 'Laptop', 6, 1450], [5029, '28-Aug-2026', '29-Aug-2026', 'Tom Nguyen', ' SOUTH', 'Keyboard', 3, 85], [5030, '30-Aug-2026', '4-Sep-2026', 'Priya Sharma', 'NORTH', 'Laptop', 2, 1450], [5031, '1-Sep-2026', '6-Sep-2026', 'TOM NGUYEN', 'South', 'Laptop', 2, 1450], [5032, '2-Sep-2026', '10-Sep-2026', ' Kate Wilson', 'East', 'Headset', 4, 150], [5033, '4-Sep-2026', '5-Sep-2026', 'PRIYA SHARMA', 'North ', 'Webcam', 20, 110], [5034, '7-Sep-2026', '9-Sep-2026', 'Priya Sharma', 'North', 'Keyboard', 4, 85], [5035, '9-Sep-2026', '13-Sep-2026', 'Ben Carter ', 'WEST', 'Mouse', 12, 40], [5036, '11-Sep-2026', '17-Sep-2026', 'Priya Sharma', 'NORTH', 'Keyboard', 26, 85], [5037, '14-Sep-2026', '17-Sep-2026', 'Priya Sharma', 'North ', 'Webcam', 2, 110], [5038, '17-Sep-2026', '18-Sep-2026', 'BEN CARTER', ' West', 'Headset', 30, 150], [5039, '18-Sep-2026', '22-Sep-2026', 'Tom Nguyen', 'South', 'Keyboard', 7, 85], [5040, '19-Sep-2026', '22-Sep-2026', 'kate wilson', 'East', 'Webcam', 23, 110], [5033, '4-Sep-2026', '5-Sep-2026', 'priya sharma', 'north', 'Webcam', 20, 110], [5041, '20-Sep-2026', '27-Sep-2026', 'TOM NGUYEN', 'south ', 'Webcam', 9, 110], [5042, '21-Sep-2026', '22-Sep-2026', 'Tom Nguyen', ' SOUTH', 'Webcam', 13, 110], [5043, '22-Sep-2026', '25-Sep-2026', 'priya sharma', 'North ', 'Webcam', 17, 110], [5044, '24-Sep-2026', '1-Oct-2026', 'Ben Carter ', ' West', 'Mouse', 12, 40], [5045, '25-Sep-2026', '2-Oct-2026', 'Priya Sharma', 'NORTH', 'Headset', 29, 150], [5046, '28-Sep-2026', '5-Oct-2026', 'Kate Wilson', 'east', 'Headset', 22, 150], [5047, '30-Sep-2026', '3-Oct-2026', 'ben carter', 'west', 'Monitor', 2, 320], [5048, '1-Oct-2026', '9-Oct-2026', ' Priya Sharma', 'North', 'Webcam', 5, 110]]
  • AutoFit the width of column B:H (double-click its right edge)

Let's inspect it. The dates and numbers look fine. The problems are in the text columns. The Rep column has names like ben carter in lower case, BEN CARTER in capitals, and Tom Nguyen with two spaces in the middle. The Region column has west, WEST, a West with a space in front, and North with a space on the end. To Excel, every one of those is a different region, so any SUMIFS by region would be wrong. And there might be duplicate orders hiding in here too. Fifty rows, but let's see how many real orders there are.

Step 1: clean the text columns#

  • Starting in J3, type these rows (one value per cell, moving right):

    | Rep clean | Region clean | |---|---|

  • In J4 type the formula =PROPER(TRIM(D4))

  • In K4 type the formula =PROPER(TRIM(E4))
  • Fill J4:K53 down (drag the fill handle, or select it and press Ctrl+D)
  • AutoFit the width of column J:K (double-click its right edge)

Every rep name is now neat, like Ben Carter and Tom Nguyen, and every region is one of exactly four words: North, South, East and West.

  • Select J4:K53
  • Press Ctrl+C
  • Click D4
  • Press Ctrl+Alt+V
  • Press V
  • Press Enter
  • Press Esc
  • Clear J3:K53 (Delete key)

Columns D and E now hold the clean values directly. No formulas, no helpers. Just tidy data.

  • Select A3:H53
  • Ribbon: path Data > Remove Duplicates, ok True
  • Press Enter

Two duplicate values were found and removed, and forty-eight unique values remain. One duplicate was order 5017, which had simply been exported twice. The other was order 5033. Its copy said priya sharma and north in lower case, so it only became an exact match after we cleaned it. So we have forty-eight real orders, in rows four to fifty-one. If we hadn't cleaned first, we would have counted order 5033 twice in every total.

Step 2: a table with calculated columns#

  • Table: range A3:H51, name SalesData

  • Starting in I3, type these rows (one value per cell, moving right):

    | Sales | Days to Ship | Status | |---|---|---|

  • In I4 type the formula =[@Qty]*[@[Unit Price]]
  • In J4 type the formula =[@[Ship Date]]-[@[Order Date]]
  • In K4 type the formula =IF([@[Days to Ship]]>5,"Late","On time")
  • Format J4:J51: number format general
  • Format H4:I51: number format currency
  • AutoFit the width of column B:K (double-click its right edge)

Look at the first order, 5001. Ben Carter in the West sold 18 webcams at 110 dollars, so Sales is 1,980 dollars. It was ordered on 3 July and shipped on 10 July. That's 7 days, more than five, so the Status says Late. All three formulas filled down every one of the forty-eight rows automatically.

Step 3: protect the data with validation#

  • Validation: range E4:E51, list ['North', 'South', 'East', 'West'], input_message Choose North, South, East or West.
  • Validation: range G4:G51, whole True, between [1, 100], input_message Quantity must be a whole number from 1 to 100.
  • Click E10

Now nobody can type north with a space or Westt again. Next quarter's data will arrive much cleaner.

Step 4: sort and filter to explore#

  • Sort: range A3:K51, by Sales, order desc

The biggest order is at the top. Order 5028, on 25 August, by Tom Nguyen in the South region. 6 laptops for 8,700 dollars. Second is order 5023, Kate Wilson in the East, with 25 monitors for 8,000 dollars. Third is order 5005, 21 monitors for 6,720 dollars. So that answers question five. Our biggest single order was 8,700 dollars, from the South.

  • Sort: range A3:K51, by Order ID, order asc
  • Filter: range A3:K51, field Status, criteria Late

14 rows remain, out of forty-eight. These are all the orders that took more than five days to ship. Look down the Days to Ship column. Every value is six, seven or eight days. And look at the products. There are 4 keyboard orders in this short list, which is interesting.

  • Filter: range A3:K51, field Region, criteria South

Now 5 rows remain. These are the late orders from the South: orders 5003, 5004, 5006, 5020 and 5041, all handled by Tom Nguyen. Filters are great for exploring and for seeing the actual rows behind a number. But to answer questions properly, and keep the answers up to date, we'll build a summary with formulas.

  • Filter: range A3:K51, clear True

Step 5: a summary with SUMIFS and COUNTIFS#

Sheet: Summary

  • In A1 type Sales summary, July to September 2026

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | Region | Sales | Orders | Late | Late % | |---|---|---|---|---|

  • In B4 type the formula =SUMIFS(SalesData[Sales],SalesData[Region],A4)
  • In C4 type the formula =COUNTIFS(SalesData[Region],A4)
  • In D4 type the formula =COUNTIFS(SalesData[Region],A4,SalesData[Status],"Late")
  • In E4 type the formula =D4/C4
  • Fill B4:E7 down (drag the fill handle, or select it and press Ctrl+D)
  • Starting in B8, type these rows (one value per cell, moving right):

    | =SUM(B4:B7) | =SUM(C4:C7) | =SUM(D4:D7) | =D8/C8 | |---|---|---|---|

  • Format B4:B8: number format currency

  • Format E4:E8: number format percent

  • Format A3:E3: bold, font colour White, Background 1, fill Blue, Accent 1
  • Format A8:E8: bold, top border
  • AutoFit the width of column A:E (double-click its right edge)

Here's our answer to question one. South has the highest sales at 33,695 dollars, from 16 orders. East is second with 29,440 dollars, North third with 23,600 dollars, and West last with 22,190 dollars. Interesting: East has only 8 orders but is second in sales, so East's orders are bigger on average. North has 14 orders but they're smaller. The totals add up to 108,925 dollars from 48 orders, which tells us the summary covers every row of the table. Always check that your summary total matches the data total.

  • Click E7

Question three. Across the business, 14 of 48 orders shipped late. That's about 29 percent, more than one in four. Question four. South has the most late orders, 5, but it also has the most orders. The worst late rate is West: 4 late out of 10, which is 40 percent. South is 31 percent, East is 25 percent, and North is the best at 21 percent. That's why we look at rates and not just counts. The region with the most problems isn't always the one with the worst performance.

  • Starting in A11, type these rows (one value per cell, moving right):

    | Product | Sales | Units | Late | |---|---|---|---|

  • In B12 type the formula =SUMIFS(SalesData[Sales],SalesData[Product],A12)
  • In C12 type the formula =SUMIFS(SalesData[Qty],SalesData[Product],A12)
  • In D12 type the formula =COUNTIFS(SalesData[Product],A12,SalesData[Status],"Late")
  • Fill B12:D17 down (drag the fill handle, or select it and press Ctrl+D)
  • Format B12:B17: number format currency
  • Format A11:D11: bold, font colour White, Background 1, fill Blue, Accent 1

Laptops earn the most, 33,350 dollars, even though we only sold 23 of them. That's the power of a high price. Monitors are close behind at 28,800 dollars, and remarkably, none of the monitor orders shipped late. Webcams sold the most units, 177, but for only 19,470 dollars. And mice are the smallest at 2,200 dollars. Look at keyboards: 4 late orders out of only 7. That's a supply problem worth asking the warehouse about.

  • Starting in G3, type these rows (one value per cell, moving right):

    | Total sales | =SUM(SalesData[Sales]) | |---|---|

  • Starting in G4, type these rows (one value per cell, moving right):

    | Orders | =ROWS(SalesData) | |---|---|

  • Starting in G5, type these rows (one value per cell, moving right):

    | Average order | =AVERAGE(SalesData[Sales]) | |---|---|

  • Starting in G6, type these rows (one value per cell, moving right):

    | Late orders | =COUNTIFS(SalesData[Status],"Late") | |---|---|

  • Starting in G7, type these rows (one value per cell, moving right):

    | Late rate | =H6/H4 | |---|---|

  • Format H3: number format currency

  • Format H5: number format currency
  • Format H7: number format percent
  • AutoFit the width of column G:H (double-click its right edge)

Total sales 108,925 dollars, from 48 orders. The average order is about 2,269 dollars. 14 late orders, a late rate of 29 percent. These match the totals in our region table, which is a nice cross-check that nothing is missing.

Step 6: conditional formatting#

  • Conditional formatting on B4:B7: highlight data_bar
  • Conditional formatting on B12:B17: highlight data_bar
  • Conditional formatting on E4:E7: highlight cells greater than 0.3

The data bars show at a glance that South is the biggest region, and that laptops and monitors dominate the product sales. Two late rates turn red: South at 31 percent and West at 40 percent. Those are the regions to talk to about delivery times.

  • Go to the Project sheet
  • Conditional formatting on A4:K51: highlight formula

All 14 late orders are now shaded across the full row, from Order ID to Status. Order 5001 at the top is one of them. Because it's a rule, if a ship date gets corrected and an order becomes on time, its shading disappears by itself. And the Summary sheet updates too.

Recap and what's next#

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.