Building an analysis

01The 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.

02The concept

A standard layout. One sheet per job, in this order:

Checks catch mistakes. Examples:

=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:

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_2025

Add a region criterion to get the same by region. Format growth as a percentage with one decimal.

Growth, step by step. Growth is the change divided by where you started:

Divide by the old value, not the new one: dividing by 290.7m would give 16.0%, which understates the growth. If the old value could be zero (a brand-new region), wrap it: =IFERROR((new-old)/old, "new").

Percentage points versus per cent. When the thing that changes is already a percentage, say which you mean. Supermarkets went from 24.8% of revenue in 2025 to 26.3% in 2026. That's a rise of 1.5 percentage points, or 6% in relative terms (1.5 ÷ 24.8). "Up 1.5%" is ambiguous, and a careful reader will ask which.

Inputs in cells, not in formulas. Put the period dates in labelled cells on Calc (say B1 = start, B2 = end) and refer to them: ">="&$B$1. Next quarter, you change two cells instead of editing every formula. That's what makes a workbook reusable.

03Example

H1 2026 vs H1 2025 by region (₦ million):

Findings for the summary page:

1. First-half revenue grew 19.1%, helped by the January price rise.
2. Lagos and the South West delivered most of the growth.
3. North West nearly halved and South East slipped. These two need attention.

04Walkthrough

1. Create the sheets above and write the README.
2. 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.
3. Add a total row with SUM, and on Checks confirm the H1 2026 total equals a SUMIFS on dates alone.
4. 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.
5. Protect your work from accidental typing: Review → Protect Sheet on Calc and Summary.

05Practice

06More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

07Check your understanding