Excel Practice Exercises: A Sales Dataset with Worked Answers
You do not need a large workbook to practise useful spreadsheet skills. This invented sales dataset gives you exact answers for formulas, conditional totals and a PivotTable. It connects to the formulas, conditional functions and data-summarisation modules in Excel & Spreadsheet Mastery.
Type the headings in row 1 and the six records in rows 2–7. Use columns A–D for Region, Product, Units and UnitPrice. Enter prices as numbers, not strings with currency symbols. Formula examples use English function names and comma separators; your locale may use semicolons.
Key points
- •Worksheet revenue should total 100 before the deliberate error.
- •Reconcile region totals to the grand total.
- •Check source data, not only formulas.
- •Refresh and recheck the PivotTable after changes.
Enter the practice dataset
Keep one transaction per row. This is a deliberately simplified model with no taxes, returns or discounts. Leave column E available for Revenue.
Region,Product,Units,UnitPrice
North,Notebook,3,4
South,Notebook,5,4
North,Pen,10,2
West,Pen,4,2
South,Folder,2,5
West,Folder,6,5Exercise 1: Calculate revenue for every row
In E1 enter Revenue, then in E2 enter =C2*D2. Fill down to E7. The results are 12, 20, 20, 8, 10 and 30. In E9 enter =SUM(E2:E7); the answer is 100.
Check one result manually before filling down. Relative references change from C2/D2 to C3/D3 as you move to the next row. If every row produces the first transaction's result, inspect whether you accidentally used fixed references.
=C2*D2
=SUM(E2:E7)Exercise 2: Total one region with SUMIFS
Calculate North's revenue using E2:E7 as the sum range and A2:A7 as the criterion range. The answer is 32. South totals 30 and West totals 38. All three must sum to 100.
Put the region name in G2 and use a cell reference as the criterion. Fix the ranges with dollar signs before copying the formula down. SUMIFS ranges must have matching dimensions.
=SUMIFS($E$2:$E$7,$A$2:$A$7,G2)Exercise 3: Combine region and product conditions
Find South Notebook revenue. The row must meet both conditions: South in column A and Notebook in column B. The answer is 20. A North Folder query returns zero because no row matches both conditions; it does not mean folders have a zero selling price.
=SUMIFS($E$2:$E$7,$A$2:$A$7,"South",$B$2:$B$7,"Notebook")Exercise 4: Build and reconcile a PivotTable
Select A1:E7, insert a PivotTable, put Region in Rows and Revenue in Values. Check that Values uses Sum rather than Count. Expect North 32, South 30, West 38 and grand total 100.
Add Product beneath Region to see the contributing transactions. If the PivotTable total disagrees with the worksheet, inspect its source range, filters and refresh state. A total that matches is a useful check, but it does not prove every input is correct.
Exercise 5: Audit a deliberate error
Change the last row's units from 6 to 60. Its revenue becomes 300; West becomes 308 and the overall total becomes 370. Refresh the PivotTable and verify both totals. This illustrates why a plausible formula can still report an incorrect business input.
Restore 6. Add a check cell that compares the worksheet total to the expected 100. Document whether the check represents this exercise's fixed dataset or a real business reconciliation. In a real workbook, do not hardcode 100 as a universal expected total.
=IF(SUM(E2:E7)=100,"Matches practice total","Review inputs")Turn the example into an independent exercise
Add a new region and a new transaction, predict its totals and expand the data range. Converting the range to an Excel Table can help manage growth, but check the resulting formula references and PivotTable source.
A clean handover includes clear column definitions, protected or clearly marked formula cells, and a short record of your checks. Continue with the linked course for lookups, data cleaning and dashboards; the free paper below is a separate knowledge check, not a substitute for building the workbook.
Frequently asked questions
- Does this require XLOOKUP or dynamic arrays?
- No. These exercises use multiplication, SUM, SUMIFS, IF and a PivotTable. More advanced functions are covered separately in the course.
- Why does my PivotTable count instead of sum?
- Check the Values setting and whether the source amounts are numbers. Text or mixed data can lead to a Count default; inspect and correct the source before changing the aggregation.
Study it properly: Excel & Spreadsheet Mastery
From first formula to PivotTables, Power Query, dashboards and financial models.
- Excel & Spreadsheet Mastery Study Guide: Skills, Practice and a Realistic Learning Plan
- Excel & Spreadsheet Mastery Certificate of Completion: What It Shows and What to Verify
- Excel & Spreadsheet Mastery Course Costs: How to Compare Access Support and Assessment
- Excel & Spreadsheet Mastery Online: What to Look for Before Choosing a Course