Excel Sales Report Project: Build, Check and Explain a Small Workbook
On this page
This project combines data structure, formulas, summaries and checking into one workbook. The goal is a report another person can inspect and update, not merely an attractive chart.
Practice setup: Start in a new workbook with fictional data. Formulas use English function names and commas; your regional settings may require semicolons. Interface labels vary between Excel versions. Keep the source data and check your results before reusing the method.
Create the source Table
Use Product, Category, Quantity and UnitPrice in A1:D5. Enter Notebook, Stationery, 3, 120; Pen, Stationery, 10, 25; Mug, Home, 2, 300; and Cable, Tech, 1, 800.
Convert the list to a Table named SalesProject. Add LineTotal in E1 and use =[@Quantity]*[@UnitPrice] in the first data row. Check the filled results: 360, 250, 600 and 800.
Keep quantities and prices numeric and label the fictional currency units in a note. Formatting can display currency; typing currency words inside numeric cells makes calculations harder.
Build a category summary
On another sheet, enter Stationery, Home and Tech in A2:A4. In B2 use =SUMIF(SalesProject[Category],A2,SalesProject[LineTotal]) and copy down. Results should be 610, 600 and 800.
Sum the summary with =SUM(B2:B4) to get 2010. Independently calculate =SUM(SalesProject[LineTotal]). Subtract the summary total from the source total in a check cell; the difference should be zero.
Check more than one total
A zero difference is useful, but two offsetting errors can cancel out. Count the four source records, inspect category labels and recalculate one line by hand. Every summary category should have an agreed meaning.
Do not include a precomputed total among the source records. It could be counted again. Keep the input list, calculations and summary areas visibly separate so someone can trace where a value came from.
Create a clear chart
Select the category summary without its grand-total row and insert a simple bar or column chart. Use Sales amount by category as the title and show the units. Start a column chart's value axis at zero.
The grand total is not another category. Including it as a bar compares the parts with their own combined total and distracts from the intended comparison. Keep the small summary table visible for exact values.
Test a new record and an error
Add Folder, Stationery, 4, 80 to the source Table. Its line total should be 320, Stationery should become 930 and the full total should become 2330. Check that the chart follows the summary.
In a practice copy, misspell Folder's category as Stationary. The original category summary omits its 320, and the reconciliation should reveal the gap. Correct the label afterwards. This tests whether your checks can detect a realistic error.
Document the limits
The data contains no dates, costs, returns or customer IDs. It supports sales amounts by category, not profit, monthly growth or distinct customers. Add a Read me note with fields, units, formulas, update steps and known limits.
Try it yourself
After correcting Folder, add Marker, Stationery, 2, 150. Predict the new Stationery and overall totals, and list the checks to repeat.
Check your answer
Marker adds 300. Stationery becomes 1230 and the full total becomes 2630. Check the new line formula, Table expansion, category summary, reconciliation and chart.
The difference check should remain zero. Inspect the record count and copied formulas too. A workbook is ready when its data, calculations and explanation agree.
Keep exploring: Excel learning path.