Structure clean data
Create a table that formulas, pivots, charts, and other people can trust.
Pivot Table Excel Tutorial
From Kevin Stratvert. Watch here or open it on YouTube .
Before following the pivot-table video, inspect why a rectangular source table is required for fields to group and aggregate correctly.What this lesson is really solving.
Build one header row, one field per column, one record per row, and one type per field. Do not encode meaning through fill color, merged cells, blank spacer rows, or repeated subtotals inside source data. Preserve original values before cleaning.
Understand the idea before touching the steps.
One row is one record, one column is one field, and one cell holds one value.
Follow these steps in order.
Take the action in each step; then use the deliverable below to prove the lesson is finished.
- 1
Define the unit of observation—transaction, person, day, order—before creating columns.
- 2
Use one header row, consistent data types, real dates, unique identifiers, and no merged cells inside the dataset.
- 3
Separate raw imports, cleaned data, calculations, and presentation into clearly named sheets.
- 4
Add data validation for controlled inputs and document units, definitions, owners, and refresh date.
See the standard in context.
A sales sheet becomes Order ID, Order Date, Customer ID, Region, Product, Units, Unit Price, and Status. Currency symbols are display formatting, dates are real date values, and each order line has its own row. A separate data dictionary defines status codes.
Inspect before you move on.
- Headers are unique, stable, and understandable.
- Dates and numbers behave as dates and numbers, not look-alike text.
- A unique key or documented compound key identifies records.
Your deliverable
A tidy source table with data dictionary, validation rules, and refresh timestamp.
Watch for this
Using color, blank rows, and merged headers as data structure that machines cannot interpret reliably.
Prove it—don’t just recognize it.
You can add a new record without changing formulas or the meaning of any column.
Quick knowledge check
Answer from the lesson—not from confidence alone. Score at least 2 of 3 to unlock completion.
This curriculum-aligned check is scored automatically and stored with your account when signed in. It is an objective learning signal, but it has not yet been independently validated as a standardized assessment.
Tools, templates, and references
Pass the knowledge check above first.
Completion unlocks after a score of 2 out of 3. Then confirm that you produced the lesson deliverable.
Go to the knowledge check