Project: a sales report
Combine controlled selections, conditional summaries and a defensible conclusion.
By the end, you’ll be able to:
- Use control cells in a reusable summary
- Reconcile selected totals and weighted margin
- Present a conclusion supported by the supplied data
Your brief: explain sales and gross margin
A small retailer supplied six fictional order records. Produce selected-channel revenue and profit, overall gross margin and a regional comparison. Keep Region selection in G2, Channel selection in G3 and the overall margin target in G4.
Gross profit means Revenue minus the supplied Cost. Gross margin means gross profit divided by Revenue. These sample costs are not a complete operating-cost statement; do not label the result net profit. The numeric target 0.30 means 30%.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Channel | Revenue | Cost | Control | |
| 2 | S101 | East | Online | 120 | 70 | East | |
| 3 | S102 | West | Store | 80 | 50 | Online | |
| 4 | S103 | East | Store | 200 | 140 | 0.3 | |
| 5 | S104 | West | Online | 150 | 90 | ||
| 6 | S105 | East | Online | 60 | 39 | ||
| 7 | S106 | North | Store | 40 | 25 |
Build a summary that follows the controls
For the selected segment, use =SUMIFS(D2:D7,B2:B7,G2,C2:C7,G3). The result is 180 for East Online. Replace the sum range with E2:E7 to get selected Cost of 109.
Subtract selected cost from selected revenue to find selected gross profit of 71. Use the same criteria for both amounts. If the numerator is filtered but the denominator is not, a margin calculation may look reasonable while describing no coherent segment.
Work through the whole-report result
Total Revenue is 650, total Cost is 414, and gross profit is 236. Overall gross margin is =(SUM(D2:D7)-SUM(E2:E7))/SUM(D2:D7), approximately 36.31%. It is above the 30% target.
Do not simply average the six order margin percentages: larger orders should carry more weight in the revenue-based overall measure. If total Revenue is zero, margin is undefined. Preserve that issue with an explicit message rather than presenting an invented percentage.
Add independent checks and a useful visual
Build a PivotTable with Region in Rows, Channel in Columns and Sum of Revenue in Values. Its grand total should match 650; East Online should match 180. Use a separate regional summary of East 380, West 230 and North 40 for the simple comparison chart.
Change S101 Revenue from 120 to 140. Selected Revenue should become 200 and selected Profit 91. Refresh the PivotTable before reconciling it. Restoring the original inputs lets you repeat the original worked results.
Finish with a conclusion and a scope note
A defensible conclusion is: “In the six-order sample, gross margin is 36.31%, above the 30% target. East contributes 380 of 650 revenue. East Online contributes 180 revenue and 71 gross profit.”
The records do not show customer count, returns, taxes, full operating expenses or a complete reporting period. State the scope so a reader does not mistake this exercise for a complete business assessment. Formula checks here test the calculations; the native PivotTable, validation and report layout are hands-on Excel tasks.
Review your source data, control labels, percentage formats, reconciliation and print preview before sharing. Completing this project prepares you for repeatable advanced workflows rather than making every reporting feature automatic.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 6 exercises complete
The bounded practice checker accepts equivalent supported formulas, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
A reusable report combines controlled selections, consistent measures, independent reconciliation and a conclusion that respects its scope.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Reviewed 2026-10-16.