Chapter 07 · Keeping it honest · 7 min

Sort, filter, and what they don't change

Sorting and filtering are the two things everyone does to a table within seconds of opening it. Both are reversible. Both feel like looking rather than editing.

One of them is an edit — a permanent, unlogged rewrite of your data that leaves every total unchanged and every row wrong. The other is genuinely only looking, which is its own trap, because the totals beside a filtered table keep answering about the rows you can no longer see.

This chapter is the two failures side by side, on the same seven-order table.

The grid

The same six orders twice. A1:B7 is the table as it arrived. A10:B16 is what it became after somebody selected the amount column on its own and sorted it smallest to largest. Compare the two blocks row by row before you run anything.

The table as it arrived

Start with the truth, so there is something to compare against.

Six orders in the top block. The total is the number anybody would quote, and Penang's share is the number somebody actually asked for — the 51.25 in row 2 and the 44.00 in row 5.

Run the total first and keep it in mind. It is about to become the least useful number on the page.

Add the six amounts in the top block. What is the total?

	A	B	D
1	city	amount_myr
2	Penang	51.25	=SUM(B2:B7)
3	Kuala Lumpur	22.00
4	Kuala Lumpur	38.50
5	Penang	44.00
6	Johor Bahru	17.90
7	Ipoh	29.00
8
9
10	city	amount_myr
11	Penang	17.90
12	Kuala Lumpur	22.00
13	Kuala Lumpur	29.00
14	Penang	38.50
15	Johor Bahru	44.00
16	Ipoh	51.25

Editable, try changing it

Penang, before

Now the per-city figure, from the intact table. Two Penang orders, and the formula finds them by looking along each row: this city, therefore this amount.

That is the only reason it works. A row is a single fact — one order, its city, its value — and every conditional total on earth depends on the cells of a row staying together.

The two Penang orders are 51.25 and 44.00. What does the conditional total return?

	A	B	D
1	city	amount_myr
2	Penang	51.25
3	Kuala Lumpur	22.00	=SUMIFS(B2:B7,A2:A7,"Penang")
4	Kuala Lumpur	38.50
5	Penang	44.00
6	Johor Bahru	17.90
7	Ipoh	29.00
8
9
10	city	amount_myr
11	Penang	17.90
12	Kuala Lumpur	22.00
13	Kuala Lumpur	29.00
14	Penang	38.50
15	Johor Bahru	44.00
16	Ipoh	51.25

Editable, try changing it

The sort that broke it

The lower block is what happens when somebody clicks into the amount column, hits sort ascending, and Excel obediently sorts that column alone.

The amounts are now in order. The cities never moved. Every row in the lower block is a city glued to an amount that belonged to a different order — and there is no error, no warning, and no visual difference beyond the amounts looking suspiciously tidy.

Start with the total, because the total is what most people check.

The same six amounts are present, in a different order. Does the total differ from the top block?

	A	B	D
1	city	amount_myr
2	Penang	51.25
3	Kuala Lumpur	22.00
4	Kuala Lumpur	38.50	=SUM(B11:B16)
5	Penang	44.00
6	Johor Bahru	17.90
7	Ipoh	29.00
8
9
10	city	amount_myr
11	Penang	17.90
12	Kuala Lumpur	22.00
13	Kuala Lumpur	29.00
14	Penang	38.50
15	Johor Bahru	44.00
16	Ipoh	51.25

Editable, try changing it

Penang, after

Identical to the cent. Same six values, same sum, and a column total cannot tell you which row each value is sitting in.

The row count agrees too — still six orders, still two of them Penang, because column A was never touched.

So every check people habitually run comes back clean. Run the per-city total and watch the first number that does not.

Penang's two rows in the lower block now hold 17.90 and 38.50. What does the conditional total return, and how far is that from the top block?

	A	B	D
1	city	amount_myr
2	Penang	51.25
3	Kuala Lumpur	22.00
4	Kuala Lumpur	38.50
5	Penang	44.00	=SUMIFS(B11:B16,A11:A16,"Penang")
6	Johor Bahru	17.90
7	Ipoh	29.00
8
9
10	city	amount_myr
11	Penang	17.90
12	Kuala Lumpur	22.00
13	Kuala Lumpur	29.00
14	Penang	38.50
15	Johor Bahru	44.00
16	Ipoh	51.25

Editable, try changing it

Nothing above this line can be undone

The same two Penang rows now total RM 38.85 less than they did. That money moved into cities it never belonged to, the grand total never flickered, and a count of Penang orders still says two.

That is the shape of the failure. A one-column sort is invisible to every aggregate you would normally trust, because every one of them is an aggregate of the column, and the column is fine. Only the relationship between columns is destroyed, and no total can see a relationship.

The defences are small and worth making automatic:

Select the whole table before sorting, or click a single cell inside it and let Excel extend the selection. Never select one column and sort it.
Keep an id column. The order numbers in the raw block of the previous chapter exist for exactly this — a row you can identify is a row you can put back.
Sort a copy. If the sort is for looking at, it belongs beside the data, not in it.

Run a count over the lower block's cities and confirm for yourself that it still says two — the check that would have caught this does not exist.

Column A was not touched by the sort. How many Penang rows does the count find?

	A	B	D
1	city	amount_myr
2	Penang	51.25
3	Kuala Lumpur	22.00
4	Kuala Lumpur	38.50
5	Penang	44.00
6	Johor Bahru	17.90	=COUNTIF(A11:A16,"Penang")
7	Ipoh	29.00
8
9
10	city	amount_myr
11	Penang	17.90
12	Kuala Lumpur	22.00
13	Kuala Lumpur	29.00
14	Penang	38.50
15	Johor Bahru	44.00
16	Ipoh	51.25

Editable, try changing it

Filtering hides rows; SUM does not care

Filtering is the safe one — it changes what you see and nothing about what is stored. That safety is exactly why it catches people.

Filter this table to Penang only, and two rows remain on screen. The total in the cell beside it does not move, because SUM was given a range of cells and a hidden cell is still a cell. You are then looking at two Penang orders next to a total of every order, with nothing on screen to say so.

SUBTOTAL is the function that knows the difference. Given the code 109 it adds the range the way SUM does, except that it skips rows the filter has hidden — so the number beside a filtered table follows the filter instead of contradicting it.

With nothing filtered the two agree exactly, which is what you will see here: this grid evaluates formulas and values only, with no filter buttons to click, the same honest limit the Excel tutorial states about pivot tables and charts. Run it, confirm it matches the top block's total, and use SUBTOTAL rather than SUM in any sheet that has a filter on it.

Filter-aware total, top block

In D7, total the top block with the filter-aware function, then write a plain total beside it and confirm the two agree while nothing is hidden.

	A	B
1	city	amount_myr
2	Penang	51.25
3	Kuala Lumpur	22.00
4	Kuala Lumpur	38.50
5	Penang	44.00
6	Johor Bahru	17.90
7	Ipoh	29.00
8
9
10	city	amount_myr
11	Penang	17.90
12	Kuala Lumpur	22.00
13	Kuala Lumpur	29.00
14	Penang	38.50
15	Johor Bahru	44.00
16	Ipoh	51.25

Where this track has got to

Two operations, two different relationships with your data. Sorting rewrites it and reports nothing. Filtering hides it and reports nothing. Neither announces itself, and the totals you would check are the wrong instrument for both.

That is the thread running through everything here. A spreadsheet will not refuse a bad value, will not tell you a column changed type, will not object to a header inside your range, will not distinguish a blank from a zero, will not stop you typing over the only copy of the source, and will not mention that half the rows are hidden. None of that is a flaw — it is the deal. The grid is unopinionated, and the opinions have to be yours.

The habits that replace them are all small: count a column twice before trusting it, keep one row per thing, one header row above the data, raw and summary in separate blocks, and a check at the bottom that has to come to zero.

From here, the Excel tutorial is the other half — lookups, conditional aggregation, text and date work, and the traps that fail candidates in an interview. It is free too, and it starts at your first formula. If your sheets are messy rather than your formulas, you have just done the harder half.