Concept
1. The four rules
- Each row is one observation — one sale, one invoice line, one attendance entry.
- Each column is one variable — Date, Region, Product, Qty, Amount.
- Each cell holds one value — not "Laptop, Mouse" or "5 pcs".
- Each table holds one kind of thing — sales in one table, product details in another.
Data that follows these rules is called tidy. Pivot tables, Power Query, charts, FILTER and XLOOKUP all expect it.
2. Report layout vs tidy layout
A typical monthly report (easy to read, hard to analyse):
| Region | Apr | May | Jun | Total |
|---|---|---|---|---|
| North | 1,46,000 | 1,62,000 | 2,01,000 | 5,09,000 |
| South | 1,45,000 | 60,000 | 54,000 | 2,59,000 |
The months are values (data), but they're sitting in the header as column names. To ask "which month was best?" you'd need a formula per column.
The tidy version:
| Region | Month | Amount |
|---|---|---|
| North | Apr | 146000 |
| North | May | 162000 |
| North | Jun | 201000 |
| South | Apr | 145000 |
| … | … | … |
Longer, but now one pivot answers any question — by month, by region, or both. (In Power Query this conversion is one click: Unpivot, Module 3.)
3. Common untidy patterns and fixes
| Problem | Why it hurts | Fix |
|---|---|---|
| Merged cells | sorting/filtering breaks, blanks under merged area | unmerge, fill down |
| Subtotal rows inside data | totals get counted twice | delete them; let the pivot total |
| Blank rows/columns as spacers | pivots and Ctrl+T stop at the gap | remove them |
| Two header rows | Excel sees one header + data | combine into one header row |
| "5 pcs", "₹1,200" as text | can't sum | separate number and unit; number format for ₹ |
| Colour as information (red = unpaid) | formulas can't read colour | add a Status column |
| Several values in one cell | can't filter or count | Text to Columns / split into rows |
| Dates typed as text | can't group by month | convert to real dates (Foundations M5) |
4. Make it an Excel Table
Click inside the data → Ctrl + T → My table has headers → OK. Rename it in Table Design (e.g. tblSales).
Benefits: grows automatically when you add rows, pivots and formulas pick up new data, headers stay visible, structured references like tblSales[Amount].
5. Keep raw data and reports separate
Raw data sheet: tidy, no formatting tricks, no totals. Report sheets: pivots, charts, formulas that read from the raw data. Never type report numbers back into the raw data.
Common mistakes
"Cleaning" data by hand every month instead of fixing the source layout. Adding totals inside the data. Using months or years as column headers in raw data.