Project›Core· 120 min read

Project: Analyse a Dataset & Build a One-Page Summary

Bring it all together: clean a real sales dataset, summarise it with a pivot table, and present it as a one-page report with a chart.

What you will learn

Clean and prepare a real dataset
Summarise it with formulas and a pivot table
Present findings on a single clear page

What you will build

You will take a messy sales dataset and turn it into a one-page summary that a manager could read in 30 seconds. It will have headline numbers, a pivot-table breakdown and a chart — using every skill from this course. By the end you will have a portfolio-worthy report.

Step 1 — The dataset

Create a sheet named Data with these columns and at least 12 rows. Type it in or paste your own sales export. Include a couple of messy bits on purpose (an extra space, a duplicate row) so you can practise cleaning.

Date	Region	Product	Sales
2026-01-05	North	Apples	120
2026-01-06	South	Apples	80
2026-01-08	North	Bananas	90
2026-01-09	South (with spaces)	Bananas	60
2026-01-09	North	Apples	100

Step 2 — Clean it

Tidy the data before you analyse, so your numbers are trustworthy:

Use TRIM to remove stray spaces, for example =TRIM(B2) on a region value that has spaces around it.
Use Data > Remove Duplicates to delete any accidental repeat rows (keep a backup copy first).
Make sure every Sales cell is a real number, not text, so totals work.

Clean the region name that had extra spaces around it

=TRIM(B5)

Note: Output: South The stray spaces are gone, so the value now reads exactly South and matches the other South rows. This matters because a pivot would otherwise treat them as two different regions.

Step 3 — Headline numbers (KPIs)

On a new sheet named Summary, add three live KPI cells that read from the Data sheet:

Three KPIs pointing at the Sales column on the Data sheet

Total Sales:   =SUM(Data!D2:D100)
Orders:        =COUNT(Data!D2:D100)
Average Order: =AVERAGE(Data!D2:D100)

Note: Output: Total Sales: 450 Orders: 5 Average Order: 90 (For the five sample rows above.) These three numbers give the whole story in a glance, and they update automatically as you add more orders to the Data sheet.

Step 4 — Summarise with a pivot table

Click in the Data sheet, choose Insert > PivotTable, and build a breakdown of sales by region:

Pivot the cleaned data into total sales per region

Rows:    Region
Values:  Sum of Sales

Note: Output: North 310 South 140 Grand Total 450 Because you cleaned the spaced-out region earlier, all the South rows group into one line. The pivot turns 5 rows into a clean two-region summary.

Step 5 — Add a chart

Select the pivot result and go to Insert > Column Chart (or use a PivotChart). Give it the title Sales by Region and clear labels.

Note: Output: A two-bar column chart appears: North is the taller bar (310) and South is shorter (140). Anyone can see at a glance that North outsells South.

Step 6 — Lay out the one page

On the Summary sheet, arrange everything so it reads top to bottom:

A clear title at the top, like Sales Summary — January 2026.
The three KPI cards in a row beneath it.
The pivot table showing sales by region.
The chart beside or below the pivot.
One sentence of insight, for example North drove most sales this month.

Your tasks

Build the Data sheet with at least 12 rows and clean it (TRIM and Remove Duplicates).
Add at least three KPI cells that read live from the Data sheet.
Create a pivot table of sales by region (then try sales by product too).
Add a chart with a title and labels.
Write one sentence stating the main finding.

Tip: Build it in small steps and check each one before moving on: clean first, confirm the totals look right, then pivot, then chart. A finished, tidy one-pager beats a half-built complicated one.

Watch out: Remember to Refresh the pivot and pivot chart after you add or change rows in the Data sheet — they do not update on their own like the SUM formulas do.

Note: When this works you have done a complete analyst workflow end to end: raw data in, a clean and clear one-page report out. Save it as your first analytics portfolio piece!

Q. Why do you clean the spaced-out region value with TRIM before building the pivot table?

Answer: Stray spaces make the value look different from South, so a pivot would list them separately. TRIM makes them identical so they group into one region.

✍️ Practice

Complete all six steps and produce a one-page Summary sheet with KPIs, a pivot and a chart.
Add five more rows to the Data sheet, refresh the pivot and chart, and confirm the KPIs and visuals all update.

🏠 Homework

Swap in a different dataset (your own expenses, a sports table, or a public CSV — a CSV is a plain text file of comma-separated values that opens straight into spreadsheet rows and columns) and repeat the whole workflow. Write a short note on the single most useful insight your one-pager revealed.