Beginner level · Module 1: Clean Data for Business Analysis · Lesson 2 of 3
Structuring Data for Analysis
Lay data out so Excel, PivotTables and Power BI can analyse it, and design entry sheets that stay clean.
In this lesson you will
- Structure data as tidy tables
- Design data entry sheets that prevent errors
Key concepts
one row per recordone column per fieldno merged cells or subtotalsdata validation
The lesson
Analysable data has one row per record (one sale), one column per field (date, branch, product, amount), a single header row and no merged cells, blank rows or subtotals inside the data.
Reports that look nice for printing — merged headings, totals between groups — are hard to analyse. Keep raw data in a plain table and build reports from it.
Prevent errors at entry with Excel tables, drop-down lists for branches and products, and validation for dates and amounts.
Step by step
- Identify structure problems in a sample report.
- Rebuild it as a tidy table.
- Create an entry sheet with drop-down lists.
- Add validation for dates and amounts.
Practice activity
Design a clean entry sheet for the business's daily sales.
Expected result: A validated entry table that produces analysable data.
Check your understanding
-
Why should raw data contain no subtotals?
Show answer
They mix summaries with records and break analysis.
-
How can data entry errors be prevented?
Show answer
With drop-down lists and data validation.
Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.