Skip to content
Reading settings
ExcelIntermediate~60 min practiceLesson 32 of 34

Build a checked sales dashboard from source to handover

Build a dashboard and check every summary against its source.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Create regional and monthly views, test an added sale and prepare a useful workbook handover.

Practice material

Compare the worked answer

Start with the practice file. Open the worked answer after trying the exercise. CSV and text briefs are also available. All resources and import notes →

Example

A quarterly sales dashboard

Download the sales dashboard starter workbook. Raw!A1:E9 contains the eight records from sales-csv, with the headers Month, Region, Rep, Product and Sales. Clean, Summary and Dashboard are labeled for you to complete. The solution workbook uses SUMIFS summaries and a native chart. It has no Power Query connection or PivotTable.

How it works

Link dashboard figures to the source or summary cells. A headline number should have a named unit and scope, and readers should be able to follow it back to its calculation.

Test the update path with a sale whose effect you can predict. Recalculation updates formulas; PivotTables need refreshing. Check both before handing over the revised dashboard.

Steps

  1. Open the starter workbook and save a copy as Sales-Dashboard-Practice.xlsx before editing. Check the eight records in Raw!A2:E9. Copy that range and paste values into Clean!A2:E9 below its existing headers. Select Clean!A1:E9, choose Insert > Table and confirm My table has headers. Name the table DashboardSales. Keep Raw as the original source.

  2. On Summary, enter =SUM(DashboardSales[Sales]) in B2 and =ROWS(DashboardSales[Sales]) in B3. Their labels are in A2 and A3. Check total 9450 and record count 8. Amounts in this sample use practice units; no currency is specified.

  3. Use the regional labels in Summary!A7:A9: West, East and North. In B7 enter =SUMIFS(DashboardSales[Sales],DashboardSales[Region],A7) and fill through B9. For Jan, Feb and Mar in A13:A15, enter =SUMIFS(DashboardSales[Sales],DashboardSales[Month],A13) in B13 and fill through B15. Check West 4500, East 3350, North 1600 and Jan 3950, Feb 3300, Mar 2200. As a separate practice method, you can build PivotTables from the same table using Region or Month in Rows and Sales in Values.

  4. Select Summary!A12:B15 and insert a clustered column chart. Move the chart to Dashboard and place it in the empty area beginning at D6. Keep Jan, Feb and Mar in order and use the title Sales by month (practice units). The solution chart links to this monthly summary; it does not contain a pasted picture.

  5. On Dashboard, enter =Summary!B2 in B2 and =Summary!B3 in B3. Use the existing labels in A2:A3 rather than typing the totals. If you add a filtered regional view, label its subtotal separately from the all-region result.

  6. Save a second copy before the update test. Add Mar, West, Maya, Laptops, 300 to Raw!A10:E10. Then copy that new row and paste values into Clean!A10:E10, directly below DashboardSales. Check that the table expands to include it. There is no automatic Raw-to-Clean connection. The formulas should show count 9, sales 9750, West 4800 and March 2500; January and February stay unchanged. If you built separate PivotTables, refresh them as well.

  7. Review the Dashboard chart, accessibility and Print Preview with your edition’s tools. Write a short handover note beside the file explaining the practice source, field meanings, and the two-part update: add the record to Raw, then copy it into the Clean table. Include the table name and Summary/Dashboard result cells so the next reader can trace the report.

Try it yourself

  1. Deliver the original eight-row workbook and a separate revised copy with the added sale. Write a comparison note showing which KPIs changed and which monthly totals stayed constant.
  2. Explain how you would detect a duplicated imported sale before refreshing. Include a proposed transaction-key requirement rather than treating matching amounts as definitive duplicates.
Show expected result
  • Original all-region sales are 9450 across eight records. Regional and monthly summaries each reconcile to that same total, and the chart matches the monthly values.
  • The revised copy has nine records and sales 9750, with West 4800 and March 2500. January remains 3950, February 3300, East 3350 and North 1600.

If something goes wrong

A source update is missing from the chart.
Check that the new Raw row was also copied into DashboardSales on Clean. Confirm Summary!B13:B15 changed and the chart still uses those cells. SUMIFS updates through calculation; any separately built PivotTable needs Refresh.
North is missing from the dashboard.
Check the source file and filters. This project uses the eight-row sales-csv; monthly-sales-csv is a separate six-row West/East sample.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. What is the original all-region source total?
2. Which month changes after the added sale?
3. What should happen to the original East total after adding a West sale?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Compare this dashboard with the eight source records. Reconcile the regional and monthly totals and check how formulas, PivotTables and charts update after the added sale.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…