Skip to content
Reading settings
ExcelIntermediate~30 min practiceLesson 21 of 34

Connect a PivotChart to a focused sales question

Chart monthly totals and show which region is selected.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Build a chart from a PivotTable and compare all-region, West and East views.

Example

A monthly chart with a region filter

Enter Month, Region and Sales in A1:C7: Jan West 300, Jan East 200, Feb West 450, Feb East 250, Mar West 500 and Mar East 300. These six records total 2000. Use this sample rather than the earlier eight-row sales list.

How it works

A PivotChart shows its PivotTable’s summary and filters. Give readers a title or nearby note that identifies the selected region.

A slicer changes the view. Clearing it should restore the all-region totals; test one known selection before using it in a report.

Steps

  1. Enter the six records as a table and check total 2000. Create a PivotTable with Month in Rows, Region in Filters and Sum of Sales in Values.

  2. Check Jan 500, Feb 700 and Mar 800. Put the text month labels in calendar order if Excel sorts them alphabetically.

  3. On Windows, select the PivotTable and choose Insert > PivotChart. On Mac, create the PivotTable first and insert a supported column or line chart. In web Excel, insert a chart from the summary.

  4. Use the title Monthly sales, all regions and include the amount unit. Check that the chart plots 500, 700 and 800 without treating the grand total as another month.

  5. Select West in the Region report filter. If Insert Slicer is available for this source, add a Region slicer and compare it with the report filter; otherwise continue with the filter.

  6. Read Jan 300, Feb 450 and Mar 500. Clear the selection and check that 500, 700 and 800 return. Add a note beside the chart stating which region view is selected.

Try it yourself

  1. Select East and record its three monthly values and total. Explain whether the chart title needs changing or a dynamic filter note is sufficient for a screenshot handover.
  2. Append Apr East 100 to the source table, refresh and confirm that April appears under the East selection. Clear all filters and record the new full total.
Show expected result
  • West totals 1250 from 300, 450 and 500. East totals 750 from 200, 250 and 300; together the regions match the full 2000.
  • After the April East record, East totals 850 and the full source totals 2100. Filtering changes scope but does not delete the West records.

If something goes wrong

The chart includes 2000 as another category.
A standard chart may include the grand-total row in its source. Select the monthly rows only, or use a PivotChart linked to the intended PivotTable.
The slicer filters a different report.
Check Report Connections where available. Test West against the known 300, 450 and 500 values to identify the connected PivotTable.

Quick check

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

1. What is the West total in this six-row example?
2. What should happen when a Region slicer is cleared?
3. What is the new full total after adding April East 100?

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 monthly chart with the six source records. Write a short filter note and show how the West and East totals add to 2000.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…