Excel Tables, sort and filter for clean data

#Why it matters

Before any pivot or chart, you sort and filter the raw export to understand it: what are the biggest orders, what do cancellations look like, which rows are junk. Doing Excel sort and filter on a proper Table rather than a loose range is what separates a safe analyst from one who scrambles a client's data with one click. Junior analyst postings name Excel in 57% of the job descriptions we read, and every Excel assessment starts with a raw export like this one.

#The concept

#Excel Tables (Ctrl+T)

Click anywhere in your data and press Ctrl+T (or Insert, Table), confirm "My table has headers". The range becomes a Table, which gives you:

• Auto-expansion. Type or paste a row directly below the Table and it joins the Table. Formulas, formatting, pivots and charts built on the Table include it on the next refresh.

• Calculated columns. Type a formula once in a new column and Excel fills it to every row, and keeps filling it as rows are added.

• Banding and a filter button on every header.

• A Total row (Table Design, Total Row) with a dropdown per column: Sum, Count, Average, Max and more.

• Headers that stay visible. When the active cell is inside the Table and you scroll down, the column letters A, B, C are replaced by the Table's header names.

Give every Table a real name in Table Design, Table Name: sales, not Table1. The name appears in every formula that uses it. Names cannot contain spaces.

#Structured references

Inside a Table, formulas refer to columns by name instead of by address:

sales[Revenue] never needs dollar signs and never goes stale: it always covers exactly the Table's rows, however many there are. That is why a formula like =[@Revenue]/SUM(sales[Revenue]) is safer than the $I$2:$I$541910 version from Excel formulas: relative vs absolute cell references. One gotcha: dragging a structured reference sideways moves it to the next column, just like a relative reference. Copy and paste keeps it.

#Sort: one level, many levels, custom lists

• Quick sort: click a cell in a column, Data, A to Z or Z to A. Excel sorts the whole Table or contiguous range by that column.

• Multi-level sort: Data, Sort, Add Level. For example Country A to Z, then Revenue largest to smallest. Levels apply in order, top level first.

• Custom lists: weekdays sorted alphabetically put Friday first. In the Sort dialog, choose Order, Custom List, and pick Monday, Tuesday, ... or type your own (Q1, Q2, Q3, Q4, or your company's region order). You can add permanent lists under File, Options, Advanced, Edit Custom Lists.

• Sort by colour: Sort On, Cell Color or Font Color, useful after conditional formatting.

#The expand-selection warning

If you select one column and sort it, Excel shows "Sort Warning: Microsoft Excel found data next to your selection". The choices are Expand the selection (correct, the rows stay together) and Continue with the current selection (sorts only that column and detaches every value from its row). Choosing the second on a customer list gives every customer someone else's phone number, and Ctrl+Z is the only rescue. A Table removes the risk: sorting any column of a Table always sorts whole rows.

#AutoFilter

The header dropdown filters by value checkboxes, or by type-specific rules:

• Text Filters: equals, begins with, contains, does not contain. C* in the search box also works (* matches any characters, ? one character).

• Number Filters: greater than, between, Top 10 (top N items or top N percent), above average.

• Date Filters: the checkbox tree groups by year, month and day; rules include This Month, Last Quarter, Between.

• Filter by Color: cells or fonts with a given colour.

Filters on several columns combine with AND. Clear them all with Data, Clear (or Ctrl+Shift+L twice to toggle filter buttons off and on).

#SUBTOTAL and AGGREGATE respect the filter; SUM does not

SUM adds every cell, visible or not. SUBTOTAL adds only the rows the filter shows:

The first argument picks the function: 1 average, 2 count numbers, 3 count non-blank, 9 sum, 4 max, 5 min. Add 100 to also skip rows you hid by hand. AGGREGATE (Excel 2010 and later) adds an options argument: 5 ignores hidden rows, 6 ignores errors, 7 ignores both, and it offers more functions such as LARGE (14) and PERCENTILE. SUBTOTAL also skips other SUBTOTALs inside its range, so nested subtotals do not double count. The Table's Total row writes SUBTOTAL(109, ...) for you.

#Freeze panes

View, Freeze Panes, Freeze Top Row keeps row 1 visible; Freeze First Column keeps column A; Freeze Panes freezes everything above and to the left of the active cell (click B2 first to freeze both row 1 and column A). On a Table you often do not need it, as described above, but on a range or a wide sheet you do.

#The 1,048,576-row limit

A worksheet holds at most 1,048,576 rows and 16,384 columns (XFD). The full Online Retail II workbook has 1,067,371 transaction lines across its two sheets, so the two years together do not fit on one sheet. Opening a bigger CSV directly gives the warning "File not loaded completely" and silently drops everything past the limit. Your options:

1. Power Query into the Data Model, which holds tens of millions of rows and still feeds pivot tables (covered in the Power Query and Power Pivot lessons of this skill).

2. Filter at the source (one region, one year) before it reaches the sheet.

3. Use a tool built for it: DuckDB, SQL or pandas. See SQL.

#Worked example: cancellations and the biggest lines in Online Retail II

Use the Year 2010-2011 sheet of the UCI Online Retail II workbook (CC BY 4.0, UCI page).

Step 1: make the Table. Click A1, press Ctrl+T, tick "My table has headers", and name it sales in Table Design.

Step 2: add calculated columns. In the first empty column header type Revenue, then in the row below:

=[@Quantity]*[@Price]

Excel fills all 541,909 rows. Add a second column IsCancel:

=LEFT([@Invoice],1)="C"

Cancellation invoices start with C. A helper column like this is more reliable than a text filter, because in the Excel file most invoice numbers are stored as numbers and only the cancellations are text.

Step 3: turn on the Total row and set the Revenue total to Sum. Excel writes:

=SUBTOTAL(109,[Revenue])

With no filter the total reads 9,747,747.93: every line, sales and cancellations together.

Step 4: filter cancellations. In the IsCancel dropdown tick only TRUE. The status bar shows how many records are visible, and the total row now reads the filtered sum:

Visible lines (IsCancel = TRUE)       9,288
Distinct cancellation invoices        3,836
Total row, SUBTOTAL(109,[Revenue])    -896,812.49
SUM(sales[Revenue]) in another cell   9,747,747.93   (ignores the filter)

Cancellations reduce revenue by about £0.9 million, roughly 8% of the gross £10,644,560.42 you get by filtering IsCancel to FALSE. A SUM next to the Table keeps showing 9.75 million whatever you filter, which is exactly why reports that people filter must use SUBTOTAL or AGGREGATE.

Step 5: sort by revenue. Clear the filter, click a Revenue cell, Data, Z to A. The top five lines:

Invoice  Description                     Quantity  Price     Revenue    InvoiceDate       Customer ID
581483   PAPER CRAFT , LITTLE BIRDIE     80995     2.08      168469.60  2011-12-09 09:15  16446
541431   MEDIUM CERAMIC TOP STORAGE JAR  74215     1.04      77183.60   2011-01-18 10:01  12346
556444   PICNIC BASKET WICKER 60 PIECES  60        649.50    38970.00   2011-06-10 15:28  15098
537632   AMAZON FEE                      1         13541.33  13541.33   2010-12-07 15:08  (blank)
A563185  Adjust bad debt                 1         11062.06  11062.06   2011-08-12 14:50  (blank)

Now sort A to Z and look at the bottom of the business:

Invoice  Description                     Quantity  Price     Revenue     InvoiceDate
C581484  PAPER CRAFT , LITTLE BIRDIE     -80995    2.08      -168469.60  2011-12-09 09:27
C541433  MEDIUM CERAMIC TOP STORAGE JAR  -74215    1.04      -77183.60   2011-01-18 10:17
C556445  Manual                          -1        38970.00  -38970.00   2011-06-10 15:31

Sorting both ways took ten seconds and told you more than any summary would: the two biggest orders in the year were cancelled within 16 minutes, the third was reversed by a manual adjustment three minutes later, and the next lines are not products at all (Amazon fees and bad-debt adjustments with no customer). Any "top products" report must handle these first.

Step 6: a custom sort. Add a column Weekday with =TEXT([@InvoiceDate],"dddd") and build a small summary (a pivot table or the SUMIFS from a later lesson). Sorted with the Monday-to-Sunday custom list:

Weekday     Lines     Revenue
Monday      95,111    1,588,609.43
Tuesday     101,808   1,966,182.79
Wednesday   94,565    1,734,147.01
Thursday    103,857   2,112,519.00
Friday      82,193    1,540,610.81
Sunday      64,375    805,678.89

Sorted alphabetically, Friday would come first and the pattern would be hard to read. Sorted by weekday, two things stand out: Thursday is the biggest day, and there is no Saturday at all. No orders were recorded on a Saturday all year, and you should state that before anyone asks why Saturday revenue is zero.

#Common mistakes

• Sorting one selected column. "Continue with the current selection" breaks every row. Always click a single cell (not a column) or work in a Table.

• Using SUM under a filtered list. The number does not change when someone filters, and they will assume it did. Use SUBTOTAL(109, ...) or the Table's Total row.

• Hidden rows plus SUBTOTAL(9, ...). Function 9 still counts rows you hid by hand (right-click, Hide). Use 109.

• Blank rows inside the data. Ctrl+T and quick sort detect the range by looking for blanks, so a blank row splits your data and the part below is left out. Delete blank rows first, or select the full range before Ctrl+T.

• Merged cells in the header or data. Sorting fails with "To do this, all the merged cells need to be the same size". Unmerge and use Center Across Selection for looks.

• Forgetting a filter is on. A filtered sheet looks complete. Check the status bar ("9288 of 541909 records found") and the funnel icons in the headers before copying data out.

• Opening a 2-million-row CSV in Excel and analysing what loaded. Only the first 1,048,576 rows are there. Use Power Query or a database.

#Interview angle

#Practice task

On the Year 2010-2011 sheet:

1. Convert the data to a Table named sales. Add Revenue and IsCancel calculated columns.

2. Turn on the Total row with Sum on Revenue. Record the unfiltered total.

3. Filter to cancellations. Record the visible line count and the filtered revenue.

4. Clear the filter and do a two-level sort: Country A to Z, then Revenue largest to smallest. Which non-UK country's biggest single line is the largest?

5. Filter InvoiceDate to December 2011 only and compare the filtered total with a full month like November. What should a report say about December?

6. In a cell outside the Table, write a formula that returns the second-largest Revenue line while ignoring any error values.

Done when: your Total row changes with every filter, and you have written two sentences on what the cancellation filter and the December filter told you.

#Further reading

• Microsoft: overview of Excel tables: every Table feature in one page.

• Microsoft: using structured references with Excel tables: the full syntax, including special items like #Totals.

• Microsoft: SUBTOTAL function: the function numbers 1 to 11 and 101 to 111.

• Microsoft: Excel specifications and limits: rows, columns and other hard limits.