The Life Starter
0 of 4 complete0%
Week 4 · Lesson 4 of 4
Digital · Guided lesson

Structure clean data

About 27 minutes · 5 reading + 22 practice and checks
Work at your own pace. Optional videos take extra time.
Want more background?
Before you begin

The idea in everyday life

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.

The idea

One row is one record, one column is one field, and one cell holds one value.

Try it

One step at a time.

Use the sample or information you have permission to use. These steps help you build your weekly project.

  1. 1

    Decide what one row represents, such as a transaction, person, day, or order. This is the unit of observation. Then choose the columns.

  2. 2

    Use one header row, consistent data types, real dates, unique identifiers, and no merged cells inside the dataset.

  3. 3

    Separate raw imports, cleaned data, calculations, and presentation into clearly named sheets.

  4. 4

    Add data validation for controlled inputs and document units, definitions, owners, and refresh date.

Example

See how it works.

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.

Check your work

Did you cover these?

  • 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 work

What to finish

A tidy source table with data dictionary, validation rules, and refresh timestamp.

Common mistake

Watch for this

Using color, blank rows, and merged headers as data structure that machines cannot interpret reliably.

How can I tell I understand it?

You can add a new record without changing formulas or the meaning of any column.

Try 3 questions

Quick knowledge check

Answer all 3 questions. Get at least 2 right and the lesson completes automatically.

Not yet passed

You’ll get an explanation for each answer. Signed in? Your results save to your account.

1You define one tracker row as one job application. You apply to two roles at the same employer on the same day. How should these records be stored?
2A practice log should contain one row per expense. It contains $12 food, $8 travel, and a $20 “daily total” row inside the same amount column. What source-table repair preserves the two expenses and prevents double counting?
3A status field should use only Applied, Interview, Offer, or Closed. New rows include “interview,” “Intvw,” and a yellow fill with no text. Which repair makes future filtering dependable?
0 of 3 answeredApply the lesson to a short question, then review the explanation.
Now try it · small editable exercise

Repair a six-row application tracker

This import has six rows: five application records and one total. It contains three problems. Use the confirmation log and status dictionary to repair the working copy, then check your table.

All employers, roles, dates, confirmation references, and outcomes in this practice case are fictional. This optional exercise stays on this page; it does not change account progress.

Source evidence and status dictionary

Use these unchanged source records to verify row identities. Do not create a new ID just to silence a duplicate warning. The source export dictionary defines Intvw as Interview.

  • CFM-472 → APP-001 · Aster Community Centre, Front desk assistant
  • CFM-519 → APP-002 · Pinebridge Library, Library assistant
  • CFM-526 → APP-003 · Pinebridge Library, Events assistant
  • CFM-608 → APP-004 · Maple Workshop, Office assistant
  • CFM-731 → APP-005 · Harbor Learning Hub, Reception assistant

Allowed statuses: Applied, Interview, Offer, Closed. Keep both Pinebridge roles; they are separate applications.

Editable working copy · the original fictional import is preserved for Reset
Source / applicationApplication IDStatusCountTable placement
Aster Community Centre
Front desk assistant
CFM-472 · 2026-08-03		1
Pinebridge Library
Library assistant
CFM-519 · 2026-08-04		1
Pinebridge Library
Events assistant
CFM-526 · 2026-08-04		1
Maple Workshop
Office assistant
CFM-608 · 2026-08-05		1
Harbor Learning Hub
Reception assistant
CFM-731 · 2026-08-06		1
Total applications

Derived summary, no confirmation		5

Current source: 6 rows · Count-column sum: 10. Expected: five applications counted once.

4
Finish this lesson

Pass the check. We’ll save the progress.

Answer all 3 questions above and get at least 2 right. You can review the explanations and try again.

Go to the knowledge check
Optional course overview video
Kevin StratvertPivot Table Excel TutorialYouTube
Optional course overview

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.
Tools, templates, and lesson sources

Tools and templates

Tools open in a new tab so you can keep your place here.

Sources and further reading

Explore the references behind this lesson.

Microsoft — Excel help & learning
Google — Sheets training and help
Report a problem with this lesson

Tell us what went wrong. Please leave out personal information and private project contents.

No sign-in needed.