Data Analysis: Sorting, Filters, Subtotals and PivotTables
Sorting safely
A sort applies to whole records, so always select the full table or a single cell inside it and let the tool detect the range. Multi-level sorts (Custom Sort) let you order by region, then by revenue descending. Traps: sorting a column that is not part of the data range scrambles rows, sorting text that looks numeric gives 1, 10, 2, and formulas using relative references to 'the row above' can break after re-sorting. Remove blank rows and unmerge cells first.
Filtering
AutoFilter shows a subset without deleting data. Use number, text and date filters, custom filters with AND/OR, and Top 10 or above-average filters. Advanced Filter needs a separate criteria range: criteria on the same row are ANDed, criteria on different rows are ORed. Remember that totals computed with SUM include hidden filtered rows; SUBTOTAL(9, range) (or 109 to ignore manually hidden rows too) respects the filter.
Subtotals and cleanup
Subtotal creates grouped outline levels with a summary per group and a grand total. It requires the data sorted by the grouping column first. Remove Duplicates and Text to Columns are the standard cleanup tools before analysis.
PivotTables
A pivot table summarises large data quickly: fields go to Rows, Columns, Values and Filters. Change the summary function (Sum, Count, Average), group dates by month or year, show values as % of total, and add slicers. Key trap: a pivot table does not refresh automatically when source data changes - use Refresh (or Refresh All); also, source data with blank headers or mixed types produces errors or dropped fields.
Worked example
To report average sales per salesperson per quarter: insert a pivot table, put Salesperson in Rows, Order Date in Columns grouped by quarter, and Sales in Values set to Average.