Level 2 · Build the monthly review in Excel

What the sheet holds

Priya’s export has one row per month from January 2024 to December 2025:

column	holds
A	the month as text, 2025-11
B, C	completed orders and their net revenue
D	orders still marked pending
E, F	web sessions and the sessions that converted

Row 6 of the KPI block already names the three months to compare: November 2025, October 2025 and November 2024.

Step 1: is the month closed?

Before reporting a month, check that its orders have settled. The largest pending count and the month it belongs to take two formulas:

I2  =MAX(D2:D25)
I3  =INDEX(A2:A25,MATCH(I2,D2:D25,0))

MATCH with 0 returns the position of an exact match; INDEX returns the month at that position. XLOOKUP does both in one step when you know the key, as in I4 for November.

Step 2: one formula per KPI row

The KPI block reads the month from its header, so one formula fills all three columns:

I7  =XLOOKUP(I$6,$A$2:$A$25,$C$2:$C$25)

I$6 keeps row 6 but lets the column move to J and K; the data ranges are locked completely. Average order value is a division of two cells already in the block, and conversion divides two lookups. The change columns compare November with each comparison month:

L7  =I7/J7-1
M7  =I7/K7-1

Reading the result

KPI	2025-11	vs last month	vs last year
Net revenue	292,364.40	+67.4%	−8.4%
Orders	651	+95.5%	+15.4%
Avg order value	449.10	−14.3%	−20.7%
Web conversion	4.45%	+2.12 pts	+0.94 pts

The month-over-month column is mostly the season: November always beats October. The year-over-year column is the one to read, and it says more orders at a lower value. The SQL and pandas versions go one step further and find why: seven bulk business orders last November, one this November.

Pitfalls

• Typing 2025-11 into a formula cell as a number or date breaks the match: the months in column A are text, so the header must be text too.
• A change in a rate is reported in points.
• Pending orders are not in column B: B counts completed orders only, which is why December’s B will still grow.