Lesson 8 of 8 · 8 min · Putting it together

Capstone: build a monthly sales report from messy data

Covers: Microsoft 365 Excel. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • A repeatable workflow from raw export to finished report
  • Where each earlier skill fits: cleaning, lookups, SUMIFS, pivots and charts
  • The checks that stop wrong numbers reaching your manager
  • How to document and hand over a workbook

In simple words

You now know the individual tools. Real work is using them in order on a messy file somebody emailed you at five o'clock. This lesson walks through one complete job from start to finish, so you see how the pieces fit and what to check at each step.

Words to know

  • Raw data: the original export, which you never edit directly.
  • Reconciliation: checking that your totals match a trusted source.
  • Assumption: a decision you made that the reader should know about.
  • Handover: giving someone a workbook they can understand and refresh.

The task

Your manager sends orders_october.csv and says: "I need sales by city and product for October, with last month's comparison, as a one-page report by tomorrow."

Step 1: keep the raw data safe

Open the file and Save As sales_report_2025-10.xlsx. Create sheets in this order: README, Report, Pivots, Data, Lookups. Paste the raw export into Data and never type over it. All cleaning is done by adding columns beside it.

Step 2: understand and profile the data

  • Convert to a Table (Ctrl+T) and name it Orders.
  • Scan each column: COUNT, COUNTA, MIN, MAX and filters. Look for blanks, duplicates (Data > Remove Duplicates on a copy, or =COUNTIFS on the order id), odd dates, negative amounts and test orders.
  • Note what you find in the README: "12 rows have no city", "orders 1050 and 1051 appear twice".

Step 3: clean with helper columns

Add columns to the Table:

City_clean     =PROPER(TRIM([@City]))
Amount_num     =VALUE(SUBSTITUTE([@Amount], ",", ""))        (if amounts came as text)
OrderDate_real =DATE(LEFT([@Date],4), MID([@Date],6,2), RIGHT([@Date],2))   (if dates came as text)
Month          =TEXT([@OrderDate_real], "yyyy-mm")

Then add a Region column with a lookup against a small Lookups table of cities and regions:

=XLOOKUP([@City_clean], CityMap[City], CityMap[Region], "Unmapped")

Filter the Region column for Unmapped to find spelling variants, add them to the lookup table, and check again until none is left.

Step 4: fix the business rules

Decide, with the manager if needed, what counts as sales. For example only Status = "Paid". Put this as a column so it is visible and reusable:

CountsAsSales  =IF([@Status]="Paid", 1, 0)
SalesAmount    =[@Amount_num]*[@CountsAsSales]

Write the rule in the README. Hidden rules are the main reason two people produce different numbers from the same data.

Step 5: summarise with a pivot

Insert a pivot from Orders on the Pivots sheet: City_clean in Rows, Month in Columns, Sum of SalesAmount in Values. Add Count of Order ID for orders. Use Show Values As > % Difference From (previous month) or place Sum of SalesAmount twice with one as percentage of column total. Sort cities from largest to smallest and check the grand total.

Compare with a formula for one cell to confirm the pivot is right:

=SUMIFS(Orders[SalesAmount], Orders[City_clean], "Pune", Orders[Month], "2025-10")

Step 6: reconcile

Reconciliation is what makes a report trustworthy:

  • Total check: =SUM(Orders[SalesAmount]) equals the pivot's grand total.
  • Row count check: number of rows in Orders equals rows in the original CSV.
  • Control total: if finance gave a figure for October, your total matches it, or the difference is explained and written down.
  • Spot check: pick three random orders and trace them from the raw export to the report.

Add a small "Checks" block to the README with the formula and a pass or fail result.

Step 7: present

On the Report sheet:

  • A title and the date range, for example "Sales by city, October 2025 (paid orders only)".
  • Three headline numbers: total sales, number of orders, average order value, with change versus September.
  • One pivot chart: top cities by sales as a sorted bar chart, with a title that states the finding ("Hyderabad is 38% of sales").
  • One small table: sales by product with percentage of total.
  • A line of notes: assumptions and caveats, such as "12 orders with no city are shown as Unknown".

Hide gridlines (View menu) and set the print area to one page. Share as a PDF unless the reader needs to interact; if they do, keep slicers and connect them.

Step 8: hand over

The README sheet should say what the file is for, where the data came from and the date, the rules (which statuses count), the steps to refresh next month (paste new data into Data, Refresh All, check the "Checks" block), the known data issues and your name. A report that only you can update is a risk; make it repeatable. Later, Power Query (Get & Transform, in the Power BI course too) can automate the cleaning steps.

Common mistakes

  • Editing the raw data so you cannot repeat or audit the work.
  • Hidden business rules that live only in your head.
  • No reconciliation, so a duplicated or dropped row goes unnoticed.
  • Hard-coded numbers typed into the report instead of linked to the source.
  • A chart without a message, or a report that needs your voice to understand.

Try it yourself

  1. Download any sales CSV, or make one with 500 random rows, and follow the steps above.
  2. Insert some deliberate problems (a duplicate order, a city spelt two ways, a text date) and write how you caught each.
  3. Add a Checks block with three formulas that return OK or CHECK.
  4. Give the workbook to a friend and ask them to refresh it using only your README.

Check yourself

Why keep the raw data on its own sheet and unedited?

So the cleaning can be audited and repeated; if you overwrite it you cannot prove or redo what you did.

What is reconciliation?

Checking that your totals and counts agree with a trusted source, such as the original file or a finance figure.

Where should business rules like "only paid orders count" be recorded?

In a visible helper column and in the README, so everyone applies and understands the same rule.

Why use a lookup table for regions instead of typing them?

You can fix spelling variants in one place and reuse the mapping for every future report.

Interview questions

How do you make sure your numbers are correct before sharing a report?

Reconcile totals and row counts with the source, spot-check individual records, test edge cases such as blanks and duplicates, and document the rules and assumptions.

You are asked to produce the same report every month. How do you make it easy?

Keep raw data, cleaning columns and the report separate, use Tables so formulas and pivots expand, add a checks block and a README with refresh steps, and consider Power Query to automate imports.

Two people get different totals from the same data. How would you investigate?

Compare the rules applied (filters, statuses, date ranges, duplicates), the row counts, and the data version; reconcile step by step until the gap is explained.

Summary

  • Keep raw data untouched; clean with helper columns in a Table.
  • Write down business rules and make them visible in columns.
  • Summarise with pivots, then reconcile totals, row counts and control figures.
  • Present a clear message, document assumptions, and make the workbook repeatable.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on Excel for analysts for Data Analyst and scores every answer, with what to fix.