Level 6 · The KPI block and a one-line summary with TEXT

A KPI block is the top of every monthly report: four or five numbers, each with one clear definition, in cells that anyone can click to see how they were made. Under it sits the sentence that most readers stop at. This level builds both for November 2025.

The sheet is Aaron’s daily export for the whole month: 30 rows, one per day, with completed orders, order lines, net revenue and the lines that were later returned. A day table is small enough to paste in full, so these are the real November totals, not a sample.

The KPI block

H2   =SUM(D2:D31)
H3   =SUM(B2:B31)
H4   =H2/H3
H5   =SUM(E2:E31)/SUM(C2:C31)

H2 shows $292,364.40 and H3 651. Adding daily order counts is safe, because each order falls on one day. H4 divides the totals, $449.10, for the reason the quiz covered: an average of the daily AOVs would weight a quiet day like Black Friday. H5 is the same idea for returns: 59 returned lines out of 1,600, 3.69%.

Those are the numbers the SQL holiday case returns for November 2025: 651 orders, 292,364.40 of net revenue, AOV 449.10. The Monday you built in level 2 is in this table too: November 17, 10,937.42, the same total your order block added up to.

Formats change the look, not the value

H2 is formatted $#,##0.00, so it shows $292,364.40, but the cell holds 292364.3965. H5 shows 3.69% and holds 0.036875. When you join a cell into text with &, Excel takes the value and drops the format:

="Revenue: "&H2        → Revenue: 292364.3965

TEXT: a number formatted as text

TEXT(value, format) applies a number format and returns the result as text, ready to join:

formula	result
TEXT(H1,"mmmm yyyy")	November 2025
TEXT(H2,"$#,##0")	$292,364
TEXT(H4,"$#,##0.00")	$449.10
TEXT(H5,"0.0%")	3.7%

TEXT rounds for display only, the way a format does: $#,##0 shows the revenue to the dollar, 0.0% the rate to one decimal. H3 is a whole number below 1,000, so it can join the sentence as it is; above 999 it would need TEXT(H3,"#,##0") to get its comma.

Building the sentence

Text pieces go in double quotes, including their spaces and punctuation; numbers go through TEXT; & joins them:

G8   =TEXT(H1,"mmmm yyyy")&": "&TEXT(H2,"$#,##0")&" net revenue from "&H3&" orders, AOV "&TEXT(H4,"$#,##0.00")&", return rate "&TEXT(H5,"0.0%")&"."

Result: November 2025: $292,364 net revenue from 651 orders, AOV $449.10, return rate 3.7%.

The format codes here work the same in Excel and Google Sheets. Check the result against the KPI cells once: if the sentence says $292,364 and H2 shows $292,364.40, the pieces point at the right cells.

This sentence is the first line of the executive summary from level 1’s report plan. The rest of the summary is words a formula can’t write: the finding that matters (Black Friday week, the app ahead of web), the recommendation, and the limit worth knowing (November’s return rate will still rise as late returns arrive).

Common mistakes

• No TEXT around a number. The sentence shows 292364.3965 and 0.036875.
• Missing spaces. "from"&H3&"orders" gives “from651orders”. The space belongs inside the quotes.
• Rounding the KPI cell itself with ROUND to make the sentence look right. Keep the KPI precise and round only in the text.
• Averaging the daily columns for AOV or return rate. The total row divides totals.