Microsoft Excel Excel Pivot Charts — Questions and Answers
Question 1: What is the main difference between a PivotChart and a standard Excel chart?
- A PivotChart cannot display a legend
- A PivotChart is linked to a PivotTable and updates as fields change (Correct answer)
- A PivotChart only supports pie charts
- A PivotChart cannot be formatted
Correct answer: A PivotChart is linked to a PivotTable and updates as fields change
PivotCharts are bound to PivotTable data and include interactive field buttons.
A PivotChart visualizes a PivotTable and stays synchronized with it. Changing fields, filters, or sorting in either the table or the chart updates both. Standard charts reference fixed ranges and lack the interactive field buttons that PivotCharts provide.
Question 2: Which ribbon tab contains the command to insert a PivotChart?
- Insert (Correct answer)
- Data
- Home
- Page Layout
Correct answer: Insert
PivotChart is in the Charts group on the Insert tab.
The PivotChart button appears in the Charts group on the Insert tab. From an existing PivotTable you can also use the PivotChart button on the PivotTable Analyze tab. Either path creates a chart linked to PivotTable data.
Question 3: In the PivotChart Fields pane, fields placed in the Axis (Categories) area correspond to which PivotTable area?
- Values
- Columns
- Rows (Correct answer)
- Filters
Correct answer: Rows
Axis categories map to Row fields in the underlying PivotTable.
In a PivotChart, the Axis (Categories) area is the equivalent of the Rows area in a PivotTable. The Legend (Series) area corresponds to Columns. Values and Filters map directly to their PivotTable counterparts.
Question 4: Which chart type is NOT available for a PivotChart?
- Column
- Line
- Pie
- XY Scatter (Correct answer)
Correct answer: XY Scatter
Scatter, bubble, and stock charts cannot be created as PivotCharts.
PivotCharts support most chart types including column, bar, line, pie, and area. However, XY Scatter, Bubble, and Stock charts are unsupported because they require paired numeric values rather than category-based data. To use these types you must chart the PivotTable range with a regular chart.
Question 5: What do the gray buttons displayed on a PivotChart allow you to do?
- Change the chart's color theme
- Filter, sort, and expand fields directly on the chart (Correct answer)
- Print the chart
- Convert the chart to an image
Correct answer: Filter, sort, and expand fields directly on the chart
Field buttons provide interactive filtering and sorting without touching the PivotTable.
PivotChart field buttons appear for Filter, Legend, Axis, and Value fields. Clicking them opens menus to filter or sort the categories shown, and they update the linked PivotTable. You can hide them for presentation through PivotChart Analyze > Field Buttons.
Question 6: What happens to a PivotChart when the underlying PivotTable is deleted?
- The chart is deleted automatically
- The chart becomes a static regular chart with the last data (Correct answer)
- The chart shows an error and cannot be opened
- The chart is unaffected and still updates
Correct answer: The chart becomes a static regular chart with the last data
Without its PivotTable, the chart converts to a standard chart with fixed data.
Deleting the PivotTable behind a PivotChart breaks the connection. Excel keeps the chart but converts it into a regular chart holding the values it last displayed. It will no longer refresh or offer field buttons.
Question 7: Which tool lets you add clickable buttons that filter both a PivotTable and its PivotChart at the same time?
- Data Validation list
- Slicer (Correct answer)
- Conditional Formatting
- Sparklines
Correct answer: Slicer
Slicers provide visual filters connected to one or more PivotTables and their charts.
A slicer is a set of buttons representing the unique values of a field. Clicking a button filters every PivotTable and PivotChart connected to that slicer. Slicers are ideal for building interactive dashboards.
Question 8: To display Sales by Region as separate lines over Month on a PivotChart, where should the Region field be placed?
- Values
- Filters
- Legend (Series) (Correct answer)
- Axis (Categories)
Correct answer: Legend (Series)
Each series in a chart comes from the Legend area, which maps to Columns.
Fields in the Legend (Series) area become separate series, so each Region gets its own line. Month in the Axis (Categories) area provides the horizontal axis. Sales in Values supplies the plotted amounts.
Question 9: Which feature adds interactive date-based filtering with a draggable time range to a PivotChart?
- Timeline (Correct answer)
- Scroll bar
- Form control spinner
- Watch Window
Correct answer: Timeline
A Timeline slicer filters PivotTables and PivotCharts by date periods.
Insert > Timeline creates a horizontal control for filtering by years, quarters, months, or days. It requires a date field in the source data. Like slicers, it can be connected to multiple PivotTables and their charts.
Question 10: New records were added to the source table of a PivotChart, but the chart has not changed. What should you do?
- Create a new PivotChart
- Click Refresh on the PivotChart Analyze tab (Correct answer)
- Change the chart type
- Reapply the chart style
Correct answer: Click Refresh on the PivotChart Analyze tab
PivotCharts do not update until the PivotTable cache is refreshed.
PivotTables and PivotCharts read from a cache that is not updated automatically when source data changes. Clicking Refresh (or Refresh All) reloads the cache and updates the chart. If the source is a formatted Excel Table, newly added rows are picked up on refresh without changing the data source range.
Question 11: Which option in PivotTable Options makes a PivotChart automatically reflect changes when the workbook is opened?
- Enable show details
- Refresh data when opening the file (Correct answer)
- Preserve cell formatting on update
- Autofit column widths on update
Correct answer: Refresh data when opening the file
This setting on the Data tab of PivotTable Options refreshes the cache at file open.
In PivotTable Options, the Data tab contains Refresh data when opening the file. Enabling it forces Excel to update the PivotTable cache, and therefore any linked PivotChart, each time the workbook opens. This is helpful when the source is external or edited by others.
Question 12: You want to show only the five best-selling products on a PivotChart. Which filter should you apply to the Product field?
- Label Filter > Begins With
- Value Filter > Top 10 set to 5 items (Correct answer)
- Date Filter > This Year
- Clear Filter
Correct answer: Value Filter > Top 10 set to 5 items
Top 10 value filters can be adjusted to any number of items by sum of a value field.
Value Filters include a Top 10 option that lets you specify Top or Bottom, the number of items, and the value field to rank by. Setting it to Top 5 by Sum of Sales limits the chart to the five highest-selling products. The filter applies through the field button on the chart or the PivotTable's row dropdown.
