Excel Advanced - Charting Pivoted Data

0 of 8 activities completed

Microsoft Excel Advanced · Lesson 09

Charting Pivoted Data

Turn connected PivotTable summaries into clear, interactive PivotCharts and assemble a compact management view that responds to filters and slicers.

120-minute lesson120 sales records8 guided activitiesMixed Excel versions

Learning outcomes

Create PivotChartsBuild charts that remain connected to their PivotTables and source data.

Match chart to questionUse column, line and bar charts for comparisons, trends and rankings.

Interact with dataFilter charts through field buttons, report filters and connected slicers.

Build a management viewArrange a PivotTable, PivotChart, slicer and written conclusion on one page.

Download the practice dataset

This lesson continues with the same 120 fictional transactions used in Lesson 8. The generated workbook contains Sales Data, PivotChart Tasks and Controls sheets.

120records

RM146,236revenue

RM56,646profit

12months

Recommended workflow: open the workbook in desktop Excel, convert Sales Data to a Table named tblSales, and place each practice PivotTable or dashboard on a new worksheet.

1. Create revenue by region

ACTIVITY 1

Build the supporting PivotTable

1. Select a cell in Sales Data and choose Insert → PivotTable.
2. Place the report on a new sheet named PC Revenue Region.
3. Drag Region to Rows and Revenue (RM) to Values.
4. Confirm Value Field Settings uses Sum and apply an RM number format.
5. Reconcile the Grand Total to RM146,236.

Region	Revenue
Central	RM30,090
East	RM12,682
North	RM59,679
South	RM43,785

My supporting PivotTable totals RM146,236.

2. Revenue by region PivotChart

ACTIVITY 2

Create a clustered column chart

1. Click inside the regional PivotTable.
2. Choose PivotTable Analyze → PivotChart, then select Clustered Column.
3. Change the title to Revenue by Region.
4. Remove the legend because the chart contains only one series.
5. Apply an RM number format to the vertical axis and add data labels.
6. Test a field filter and confirm that both PivotTable and PivotChart update.

Why column? The regions are discrete categories, and their vertical columns make differences in magnitude easy to compare.

My column chart is connected, titled and readable.

3. Monthly revenue trend

ACTIVITY 3

Use a line PivotChart with a Region filter

1. Create a PivotTable with Date in Rows and Revenue in Values.
2. Group Date by Months; add Years if the data may later span multiple years.
3. Place Region in Filters.
4. Insert a Line with Markers PivotChart.
5. Title it Monthly Revenue Trend and format the value axis as RM.
6. Choose each Region from the filter and observe the changing trend.

If dates will not group: remove blanks, text-formatted dates or invalid entries from the source Date column, then refresh.

My line chart shows 12 months and responds to Region.

4. Profit by category

ACTIVITY 4

Create a ranked bar PivotChart

1. Create a PivotTable with Category in Rows and Profit (RM) in Values.
2. Sort the profit values Largest to Smallest.
3. Insert a Clustered Bar PivotChart.
4. Title it Profit by Category.
5. Format the horizontal axis and labels as RM.

Category	Profit
Furniture	RM34,668
Accessories	RM21,978

A bar chart gives category names more horizontal room and works well for ranked comparisons.

My category bars are sorted from highest to lowest profit.

5. Work with PivotChart fields

ACTIVITY 5

Change the analytical question

1. Select the Revenue by Region PivotChart and open its field list.
2. Drag Channel to Legend (Series) to compare channel composition.
3. Move Channel to Filters and note how the chart changes.
4. Return Channel to Legend (Series).
5. Use Chart Design → Change Chart Type to compare Clustered and Stacked Column.

Axis (Categories)
Region

Legend (Series)
Channel

Values
Sum of Revenue

Filters
Optional

Channel totals: Corporate RM56,728; Online RM50,154; Retail RM39,354.

I changed field positions and compared clustered with stacked columns.

6. Connect a Channel slicer

ACTIVITY 6

Control several reports from one filter

1. Select a compatible PivotTable and choose PivotTable Analyze → Insert Slicer.
2. Select Channel and apply a clear slicer style.
3. Right-click the slicer and open Report Connections or PivotTable Connections.
4. Select the regional and monthly PivotTables that share the same source.
5. Test Online, Corporate, Retail, multi-select and Clear Filter.

Connection unavailable? PivotTables created from different ranges or PivotCaches may not share a slicer. Recreate them from the same Excel Table.

One Channel slicer filters both connected PivotCharts.

7. Format for business communication

ACTIVITY 7

Reduce clutter and strengthen the message

1. Use an informative title that states the measure and dimension.
2. Format financial axes with RM and sensible display units.
3. Keep only necessary legends, labels and gridlines.
4. Use one restrained colour family and one highlight colour.
5. Hide field buttons for presentation: PivotChart Analyze → Field Buttons → Hide All.
6. Add Alt Text through the chart formatting pane where supported.

Field buttons: keep or hide?

Keep them while learners explore the chart. Hide them in a finished dashboard when a slicer or clearly labelled report filter already provides interaction.

My final chart has a clear title, RM axis and no unnecessary clutter.

8. Final challenge: one-page management view

ACTIVITY 8

Assemble and test a compact dashboard

1. Create a worksheet named Management View.
2. Position one PivotTable, one PivotChart and the Channel slicer without overlaps.
3. Add the heading Sales Performance Dashboard and a short reporting-period subtitle.
4. Align objects and make spacing consistent.
5. Test every slicer selection and refresh the source.
6. Write a one- or two-sentence conclusion beneath the visual.

Example conclusion: North produces the highest overall revenue. Furniture generates more profit than Accessories, so management should examine whether the category’s performance is consistent across channels.

Completion standard: the chart remains connected to its PivotTable; field and slicer changes update the visual; titles, legends and axes remain readable.

My one-page management view works and includes a written conclusion.

Troubleshooting

PivotChart option is unavailable
Click inside a PivotTable first, then choose PivotTable Analyze → PivotChart.

Chart shows Count of Revenue
Convert source values to numbers, refresh and choose Sum in Value Field Settings.

Slicer does not affect a chart
Connect it to the supporting PivotTable through Report Connections.

Chart misses new records
Expand the source or use an Excel Table, then Refresh All.

Knowledge check

1. What makes a PivotChart different from a normal chart?

A PivotChart is connected to a PivotTable and responds to pivot field changes, filters and compatible slicers.

2. Which chart best communicates a monthly trend?

A line chart, because the connected points emphasise movement through ordered time periods.

3. Why might one slicer fail to connect to another PivotTable?

The PivotTables may not share the same source or compatible PivotCache.

4. Should a finished chart keep every label and field button?

No. Keep only elements that help the reader interpret or interact with the result.

Completion checklist

• Clustered column chart: Revenue by Region
• Line chart: Monthly Revenue Trend
• Bar chart: Profit by Category
• Interactive control: Channel slicer
• Financial axes: RM number format
• Final output: one-page management view with a written conclusion

Course complete: you have progressed from dependable formulas and organised source data to advanced analysis, PivotTables and interactive PivotCharts.

Lesson 09 · Charting Pivoted Data · Excel Advanced