Purpose
By the end of this lesson, you will be able to build a PivotChart linked to a PivotTable and choose an appropriate chart type for business data.
Lesson Explanation
A PivotChart is a chart built directly from a PivotTable (Insert → PivotChart, or selecting a PivotTable and inserting a chart from it). It stays linked to the PivotTable’s data: changing which fields are in Rows, Columns, or Values updates the chart automatically.
Choosing the right chart type matters for clarity. A bar or column chart compares discrete categories (revenue by region). A line chart shows a trend over time (monthly revenue across a year). A pie chart shows parts of a whole, but works well only with a small number of categories – a pie chart with 15 slices is hard to read and is generally discouraged for that reason.
A PivotChart includes interactive field buttons by default, letting a report viewer filter the chart directly (e.g., clicking “Region” on the chart to filter to just one region), though these buttons are often removed before sharing a polished report, since they can look cluttered.
Practice Questions
1. A PivotTable shows total revenue by region. What type of chart would most clearly compare these regions against each other?
View Answer
A bar or column chart, since it compares discrete categories effectively.
2. A PivotTable shows monthly revenue across a full year. What type of chart would best show the trend over time?
View Answer
A line chart.
3. A manager wants to show the proportion of total sales coming from each of 4 major product lines. Would a pie chart be appropriate here? Why or why not?
View Answer
Yes, reasonably – a pie chart works well for showing parts of a whole with a small number of categories, and 4 is a manageable number.
4. A different manager wants to show the proportion of sales across 20 individual product SKUs using a pie chart. What problem does this lesson identify with this choice?
View Answer
A pie chart with that many slices becomes hard to read, since it works well only with a small number of categories – a bar chart would likely communicate this better.
5. A PivotChart is built from a PivotTable with “Region” in Rows and “Revenue” in Values. If the PivotTable’s Rows field is later changed from “Region” to “Product Category,” what happens to the linked PivotChart?
View Answer
The PivotChart automatically updates to reflect the new grouping, since it stays linked to the underlying PivotTable.
6. A polished report is being prepared for external stakeholders, and the underlying PivotChart still has interactive filter buttons showing on it. What does this lesson suggest doing before sharing it?
View Answer
Removing the interactive field buttons, since they can look cluttered in a finished, shared report.
7. A report needs to compare this year’s revenue against last year’s revenue, month by month, for the same 12 months. What chart type would most clearly show this comparison?
View Answer
A line chart (potentially with two lines, one per year) or a clustered column chart comparing month-by-month.
8. A PivotChart’s interactive field buttons allow a viewer to click and filter the chart to show only one region at a time. Is this feature more useful for an internal working file, or a final polished report for external distribution?
View Answer
An internal working file, where interactivity for further exploration is valuable; a final external report generally benefits from a cleaner, non-interactive presentation instead.
9. A dataset with 8 product categories needs a chart showing each category’s exact revenue figure clearly labeled. Would a pie chart or a bar chart make these exact figures easier to read and compare?
View Answer
A bar chart, generally – individual bar lengths and labels are typically easier to compare precisely than pie slice sizes, especially as the category count grows.
10. A PivotChart is set up, and later the underlying dataset gets 200 new rows of transactions added. What two things need to happen for the PivotChart to reflect this new data?
View Answer
The PivotTable itself needs to be refreshed with the new data, and since the PivotChart is linked to it, it will then update automatically once the PivotTable refreshes.
11. Why does this lesson specifically caution against using a pie chart for a large number of categories, rather than banning pie charts altogether?
View Answer
Pie charts can work well for a small number of categories, where each slice is large enough to distinguish; the problem specifically arises when there are too many slices to visually compare or label clearly.
12. A report needs to show both the total revenue trend over 12 months AND a breakdown by 4 regions within that trend. What might this suggest about chart type or structure, beyond a single simple bar or pie chart?
View Answer
A line chart with four separate lines (one per region) plotted over the 12 months, or a stacked/clustered column chart showing regional breakdown within each month – something that can represent both the time trend and the categorical breakdown together.