Level 3 · Quiz: sort by category, then by revenue

Sorting looks harmless: two clicks and the sheet is in order. It’s also one of the easiest ways to corrupt a dataset without noticing. This quiz is about the order of sort levels, and the one sort that breaks rows apart.

Sorting one column, the safe way

Click one cell inside the column, not the whole column, then Data → Sort A to Z or Sort Z to A (Home → Sort & Filter has the same buttons). Excel detects the whole block around the cell and moves entire rows. Numbers sort smallest to largest, dates oldest to newest, text A to Z.

If you select a column first, Excel asks whether to Expand the selection or Continue with the current selection. Always expand. “Continue” sorts that column alone and leaves the others where they were, so each line_total now sits next to a different product. Nothing shows an error, and once you’ve saved, the original pairing is gone.

Multi-level sort

Data → Sort opens the Sort dialog. Tick My data has headers, pick the first column, then Add Level for each tiebreaker:

Level	Column	Order
Sort by	category	A to Z
Then by	line_total	Largest to Smallest

Excel orders by the first level, then breaks ties with the second, and so on. The dialog’s Sort On box also sorts by Cell Color, Font Color or Conditional Formatting Icon, which is how you bring every red-highlighted row to the top after the formatting levels later in this chapter. Order → Custom List sorts text in your own order (poor, fair, good, excellent) instead of alphabetically.

Google Sheets: Data → Sort range → Advanced range sorting options, tick “Data has header row”, Add another sort column.

Shortcuts that make big sheets manageable

Keys	What it does
Ctrl+Arrow	jump to the edge of the data in that direction
Ctrl+Shift+Arrow	select from here to the edge of the data
Ctrl+A	select the whole block around the active cell
Ctrl+Home	go to A1
F2	edit the active cell
Ctrl+Shift+L	turn filters on or off

On a Mac, Cmd replaces Ctrl for most of these. Pair them with View → Freeze Panes → Freeze Top Row, so the headers stay put while you jump to row 5,000.

Before you sort raw data

• Make sure there are no blank rows or columns inside the block: Excel’s auto-detection stops at them and sorts only part of the data.
• Keep an id or a row-number column so you can sort back to the original order.
• Sort the source, never a copy of one column. If you only need a sorted view, a PivotTable or a filter is safer.

Common mistakes

• Putting the tiebreaker first: the levels run top to bottom, first one wins.
• “Continue with the current selection”, which detaches a column from its rows.
• Sorting a range with a blank row in the middle, so only the top half is sorted.
• Sorting ordinal text A to Z and getting excellent, fair, good, poor.