Sorting, filtering, D functions, PivotTables, Power Query and Power Pivot only work properly when the raw data is stored in a proper data set.
Rules of a proper data set
- Field names (headers) in the first row, each one unique.
- Records in rows, one record per row.
- No blank rows or columns inside the data; empty cells around the table.
- One data type per column (numbers, dates, text).
- No totals and no merged cells inside the data set.
Cleaning data
Raw data often needs cleaning: remove unwanted characters, add needed ones, split and join values. Useful tools: TRIM, LEFT, RIGHT, MID, LEN, UPPER and the & operator.
The Excel Table feature (Ctrl+T)
Turns a data set into a dynamic table: formulas and formatting extend to new rows automatically, filter buttons and a total row appear, and PivotTables and charts based on it pick up new data.