← Excel from Scratch: Formulas, Functions, and Data Analysis
Lesson
Final practice: a mini report from data to conclusions
Bring together what you've learned in one task: calculate, apply a condition, pull in data with a lookup, and fix an error.
How the final task works
A mini report: step by step
In practice, work in Excel usually starts with a raw table and ends with conclusions. A typical route: enter or import the data → calculate totals (SUM, AVERAGE) → apply a condition (IF, SUMIFS) → pull in extra data with a lookup (VLOOKUP or INDEX+MATCH) → fix the errors that inevitably appear with real data.
In this lesson, we'll go through the whole route on a small sales table. A reminder of the key details: in VLOOKUP, a fourth argument of “0” or “FALSE” means an exact match — don't skip it. Locking the range with $ (for example, $D$2:$E$6) keeps the reference from shifting when you copy the formula down. If VLOOKUP can't find the value, you get #N/A — fix it with IFERROR or TRIM.
SUMIFS takes the sum range as its first argument: =SUMIFS(C2:C6,B2:B6,"North"). SUMIF, by contrast, puts the sum range third, and it's optional. Don't mix up the order — it's a common mistake.
After the number crunching, it helps to describe the conclusion in words: “sales in the North were X, Y% above the average for all regions.” Turning numbers into words is the final step of any report.
Lesson notes
A mini report: step by step
In practice, work in Excel usually starts with a raw table and ends with conclusions. A typical route: enter or import the data → calculate totals (SUM, AVERAGE) → apply a condition (IF, SUMIFS) → pull in extra data with a lookup (VLOOKUP or INDEX+MATCH) → fix the errors that inevitably appear with real data.
In this lesson, we'll go through the whole route on a small sales table. A reminder of the key details: in VLOOKUP, a fourth argument of “0” or “FALSE” means an exact match — don't skip it. Locking the range with $ (for example, $D$2:$E$6) keeps the reference from shifting when you copy the formula down. If VLOOKUP can't find the value, you get #N/A — fix it with IFERROR or TRIM.
SUMIFS takes the sum range as its first argument: =SUMIFS(C2:C6,B2:B6,"North"). SUMIF, by contrast, puts the sum range third, and it's optional. Don't mix up the order — it's a common mistake.
After the number crunching, it helps to describe the conclusion in words: “sales in the North were X, Y% above the average for all regions.” Turning numbers into words is the final step of any report.