← Data Analytics from Scratch: SQL, Spreadsheets and Metrics
Lesson
Sorting, filtering and pivot tables
Learner can sort and filter a small table and read a pivot table that aggregates a category, connecting it to SQL GROUP BY.
Sorting, filtering and pivot tables — the spreadsheet GROUP BY
Sorting, filtering and pivot tables — the spreadsheet GROUP BY
Sorting reorders all rows of a table by the values in a chosen column — ascending (A→Z, smallest→largest) or descending. Crucially, the entire row moves together; if you sort only one column, your data gets misaligned. Filtering hides rows that do not match a condition you set on one or more columns. Hidden rows still exist in the sheet — formulas like SUM that cover those rows may still include them, which is a common source of confusion. If you want to aggregate only visible rows, use SUBTOTAL instead of SUM.
A pivot table is the spreadsheet equivalent of SQL's GROUP BY. It takes a flat table of rows, groups them by a category column (like city or product), and aggregates a numeric column (like revenue or count) for each group. The result is a compact summary table. For example, from a sales table with columns City and Revenue, a pivot table grouping by City and summing Revenue gives one row per city with its total.
Let's connect to SQL: SELECT city, SUM(revenue) FROM sales GROUP BY city is exactly what a pivot table produces. The same concepts apply — group a dimension, aggregate a measure. Reading a pivot means tracing each cell back to the source rows: if London shows total revenue of 120, that means the underlying table has London rows whose Revenue values add up to 120.
A common error: after filtering a table, if you forget to adjust your formula range or use the wrong function, your totals will include filtered-out rows. Always check whether your formula should use SUBTOTAL(9, range) to sum only visible rows, or whether you genuinely want the unfiltered total.
Lesson notes
Sorting, filtering and pivot tables — the spreadsheet GROUP BY
Sorting reorders all rows of a table by the values in a chosen column — ascending (A→Z, smallest→largest) or descending. Crucially, the entire row moves together; if you sort only one column, your data gets misaligned. Filtering hides rows that do not match a condition you set on one or more columns. Hidden rows still exist in the sheet — formulas like SUM that cover those rows may still include them, which is a common source of confusion. If you want to aggregate only visible rows, use SUBTOTAL instead of SUM.
A pivot table is the spreadsheet equivalent of SQL's GROUP BY. It takes a flat table of rows, groups them by a category column (like city or product), and aggregates a numeric column (like revenue or count) for each group. The result is a compact summary table. For example, from a sales table with columns City and Revenue, a pivot table grouping by City and summing Revenue gives one row per city with its total.
Let's connect to SQL: SELECT city, SUM(revenue) FROM sales GROUP BY city is exactly what a pivot table produces. The same concepts apply — group a dimension, aggregate a measure. Reading a pivot means tracing each cell back to the source rows: if London shows total revenue of 120, that means the underlying table has London rows whose Revenue values add up to 120.
A common error: after filtering a table, if you forget to adjust your formula range or use the wrong function, your totals will include filtered-out rows. Always check whether your formula should use SUBTOTAL(9, range) to sum only visible rows, or whether you genuinely want the unfiltered total.