Module 10 · Excel for Data Analysis
Building an analysis
Organise a workbook someone else can trust - raw data, calculations, checks and a one-page summary - and compare periods properly.
About 40 minutes
The problem
A workbook full of correct formulas can still be useless if nobody else can follow it: numbers typed over formulas, pivots pointing at old ranges, totals that don't match. The managing director wants a first-half review of 2026 against 2025 that her finance team can check. This lesson is about building it properly.
The concept
A standard layout. One sheet per job, in this order:
| Sheet | Contains | Rule |
|---|---|---|
README | Question, sources, date, author, definitions | Written first |
Raw | Data exactly as received | Never edited |
Data | Cleaned Tables with helper columns (revenue, region, category) | Formulas only |
Calc | Pivots and summary formulas | No typed numbers |
Summary | The one page people read: KPIs, a chart or two, the findings | Refers to Calc |
Checks | Reconciliations: totals that must agree | All should say OK |
Checks catch mistakes. Examples:
Excel formula
=IF(ROUND(SUM(Data!Orders[revenue]) - GETPIVOTDATA("revenue", Calc!$A$3), 0) = 0, "OK", "MISMATCH")
=IF(COUNTIF(Orders[region], "Not found") = 0, "OK", "Unmatched customers")Period-over-period comparison. For January–June each year:
Excel formula
H1 2025: =SUMIFS(Orders[revenue], Orders[order_date], ">="&DATE(2025,1,1), Orders[order_date], "<="&DATE(2025,6,30))
H1 2026: =SUMIFS(Orders[revenue], Orders[order_date], ">="&DATE(2026,1,1), Orders[order_date], "<="&DATE(2026,6,30))
Growth %: =(H1_2026 - H1_2025) / H1_2025Add a region criterion to get the same by region. Format growth as a percentage with one decimal.
Example
H1 2026 vs H1 2025 by region (₦ million):
| Region | H1 2025 | H1 2026 | Growth |
|---|---|---|---|
| Lagos | 118.2 | 152.8 | +29.3% |
| South West | 28.9 | 51.7 | +78.6% |
| North Central | 25.3 | 27.2 | +7.3% |
| South South | 19.7 | 23.1 | +16.9% |
| South East | 20.9 | 19.4 | −7.1% |
| North West | 31.1 | 16.6 | −46.6% |
| Total | 244.2 | 290.7 | +19.1% |
Findings for the summary page:
- First-half revenue grew 19.1%, helped by the January price rise.
- Lagos and the South West delivered most of the growth.
- North West nearly halved and South East slipped. These two need attention.
Walkthrough
- Create the sheets above and write the
README. - On
Calc, list the six regions down column A. In B and C, write the SUMIFS for H1 2025 and H1 2026 with a region condition; in D, growth. - Add a total row with
SUM, and onChecksconfirm the H1 2026 total equals a SUMIFS on dates alone. - On
Summary: three KPI cells at the top (H1 2026 revenue, growth %, largest-falling region), a bar chart of growth by region with North West highlighted, and the three findings as sentences. - Protect your work from accidental typing: Review → Protect Sheet on
CalcandSummary.
Practice
Practice
What was Kolanut's revenue growth from H1 2025 to H1 2026, to one decimal place? Calculate it from the order data, not the rounded table.
Practice
Which region had the second-worst growth (the smallest growth after North West)?
Check your understanding
Answer every question to check.