Microsoft Excel Excel Pivot Charts 2 — Questions and Answers
Question 1: 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 2: 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 3: 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 4: 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 5: 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 6: 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.
Microsoft Excel Excel Pivot Charts 2
Which tool lets you add clickable buttons that filter both a PivotTable and its PivotChart at the same time?