Excel tables and cleaning data

In depth

Structured references

Excel writes structured references for you: start a formula, click a cell or a column of the table, and the reference appears by name. You can also type them. Inside the table, a reference to the same row needs no table name; outside it, name the table.

Structured references adjust themselves: when rows are added or removed, the column references cover the new extent, and when you rename a column or the table, Excel updates every formula in the workbook that uses it. Copying a formula keeps its structured references exactly as written. Filling a fully qualified reference across, as Microsoft puts it, adjusts the column specifiers “like a series”, so Orders[quantity] filled one column to the right becomes Orders[unit_price], the next column’s name.

Calculated columns and the total row

Type a heading in the empty column to the right of a table and the table widens to include it. Type a formula in one cell of that column, press Enter, and Excel fills it down the whole column: a calculated column. Rows added later receive the formula too, so the column never has a gap. Ana’s first calculated column checks every order’s arithmetic, the B02 cross-field check written as a formula: =[@order_total]-([@quantity]*[@unit_price]-[@discount]) should be 0 in every row.

The total row is switched on with Table Design › Total Row. Each cell in it has a drop-down of summary functions, and the defaults use SUBTOTAL: the sum of a column is written =SUBTOTAL(109,[order_total]). SUBTOTAL’s first argument chooses the calculation and what to do with hidden rows: 9 sums including rows you have hidden by hand, 109 sums excluding them, and both always leave out rows hidden by a filter. SUBTOTAL also ignores other SUBTOTALs inside its range, so subtotals are never counted twice.

That makes the total row a live summary of whatever the filter shows. Filter Orders to the app channel and the total row shows the app orders’ value, while =SUM(Orders[order_total]) elsewhere still shows all 231 orders. Neither is wrong; they answer different questions, so label them.

Sorting and filtering

Sorting rearranges rows: Sort A to Z or Sort Largest to Smallest on a column, or several levels at once with Data › Sort and Add Level. Sort whole records, never one column on its own: sorting part of a range separates the sorted cells from the rest of their records, and when Excel notices that you have selected only part of the data it offers to expand the selection, which you should accept. A table always sorts whole rows. If the original order matters (the order the export arrived in, say), keep an id column you can sort back by.

Filtering hides the rows that don’t meet a condition; nothing is deleted. A table has filter buttons on its headers already, and a plain range gets them from Data › Filter. Each button offers text, number or date filters to suit the column, such as channel equals app, order_total greater than 35, or order_date in May. Pair a filter with SUBTOTAL, not SUM, when the total should follow what you see.

Data validation

Data validation (Data › Data Validation) restricts what can be typed into cells. It guards new entries: B02’s validation rule checks data that already exist, while Excel’s data validation stops some errors at the moment of typing. Its Settings tab allows whole numbers, decimals, a list, dates, times, a text length or a custom formula, each with a condition.

An Input Message tab shows a hint when the cell is selected. The Error Alert tab chooses the response to a bad entry: Stop prevents it, Warning warns but lets the user keep it, and Information is the most lenient, letting the user accept or reject the value. Two limits matter. Microsoft notes that validation shows messages and blocks entries only when data are typed directly into a cell, so pasting or filling can bring invalid values in. And adding a rule to cells that already hold data doesn’t flag the bad ones; Data Validation › Circle Invalid Data marks them.

Remove Duplicates

Data › Remove Duplicates deletes rows that repeat values in the columns you tick. Microsoft lists its rules: it keeps the first occurrence and deletes the others; it compares what appears in the cells, not the underlying value; it removes whole rows even though only the ticked columns are compared; and the deletion is permanent apart from Undo. Work on a copy, and count before you delete: =COUNTIF($A$2:$A$21,A2) filled down counts each id’s occurrences (a conditional aggregate), so any count above 1 marks a repeated id.

Which columns to tick is a business decision, the one B02’s duplicates lesson calls the business key. Ticking every column removes only exact duplicates, such as order LC-10003, which appears twice in Lantern’s raw export with identical values. Ticking only order_id would also delete an updated version of an order, keeping whichever came first rather than the one that is right.

Cleaning Lantern’s raw export, step by step

The first 20 rows of B02’s raw export show the usual problems. Every price and total arrived as text, 13 of the 20 prices with a dollar sign; 18 dates are ISO 8601, 1 is written out and 1 is a slash date; one email has a stray leading space and capitals; order LC-10003 appears twice; 6 ratings are blank; and LC-10016’s total doesn’t equal quantity × price − discount.

TRIM removes every space except single spaces between words, but only the ordinary space character (code 32): the non-breaking space (code 160) that web pages use survives it, and needs =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). CLEAN removes the non-printing control characters with codes 0 to 31. PROPER capitalises the first letter and every letter that follows a non-letter, and lowers the rest, so it turns “house blend 250g” into “House Blend 250G”: harmless for a lookup, which ignores case, but check its output before you publish it.

VALUE converts text that looks like a number, date or time in a format Excel recognises: =VALUE("$1,000") is 1000, and arithmetic such as =I2*1 converts the same way. SUM does not: it skips text, as B02 showed. After converting, =COUNT(L2:L21) should equal the number of rows; any shortfall is a value that failed to convert and needs a look.

Dates need the most care. DATEVALUE converts date text to a serial number, and Microsoft warns that the computer’s system date setting can change its results, so the same text can give different dates on different machines. With US settings, “05/03/2026” becomes 3 May 2026, silently. B02 established that Lantern’s web shop writes the day first, so this order was placed on 5 March; =DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2)) builds the date from its parts in the order you decide, and gives the same answer everywhere. Use DATEVALUE only for unambiguous text (ISO dates, written-out months), and test a few results against the source.

Each step goes in the log, B02-style: what the column does, the rule, and the count it affected. For example: “unit_price: VALUE of the raw text; 20 of 20 converted.” “order_date: DATEVALUE for ISO and written dates (19 rows), DATE from parts for day-first slash dates (1 row).” “LC-10003: exact duplicate, second copy removed; 20 rows become 19.” “LC-10016: total disagrees with its parts by $6.50; set to $14.85, as B02 decided.”

Text to Columns, Flash Fill, Find and Replace and Power Query

Power Query is the tool built for the monthly export. It connects to the data, transforms it, can combine it with other sources and loads the result to a worksheet, leaving the original source unchanged. Each transformation appears in the editor’s Applied Steps list, where steps can be renamed, deleted, edited or reordered, and when the query is refreshed each step runs again automatically: a cleaning log that executes itself. Microsoft states that it is available in every stand-alone version of Excel for Windows from Excel 2016, and on a Mac to Microsoft 365 subscribers. This lesson stays with formulas, which work everywhere and show their logic cell by cell; B07 SQL returns to the same steps as queries.

Dynamic arrays: UNIQUE, SORT, SORTBY and FILTER

The lookups lesson showed a formula that returns several values and spills them into neighbouring cells. Four dynamic array functions make that their job. Excel draws a border round the spill range while you select it, only the top-left cell can be edited, and the range grows or shrinks by itself when the source changes. Pointed at a table column, they keep up with the table.

FILTER’s include argument is a column of TRUE and FALSE values as tall as the array. Multiply conditions for AND and add them for OR: =FILTER(Orders,(Orders[region]="Central")*(Orders[channel]="web")) keeps Central’s web orders, all columns. If nothing qualifies and if_empty is left out, FILTER returns #CALC!, because Excel has no empty array. UNIQUE’s exactly_once set to TRUE keeps only values that occur once, and wrapping UNIQUE in ROWS counts the distinct values: =ROWS(UNIQUE(Orders[customer_id])) gives 112 customers.

A spilled formula fails with #SPILL! when it can’t spill. The usual causes are these: the spill range isn’t empty (delete or move whatever is in the way), the formula is inside a table (spilled array formulas aren’t supported in tables, so put it beside the table), the result would run off the edge of the sheet, or it would spill into merged cells. To refer to a whole spill range, put # after its first cell: if D2 holds =SORT(UNIQUE(Orders[region])), then =COUNTA(D2#) counts what it spilled, however many rows that becomes.