Manage advanced charts and tables
What Domain 4 tests
This domain tests charts that compare mismatched scales and PivotTables/PivotCharts that summarize large data sets. Expect questions on combo charts, secondary axes, trendlines, calculated fields and items, grouping, slicers, timelines, and GETPIVOTDATA.
Core rules
- Combo chart: Change Chart Type > Combo, then tick Secondary Axis for the series measured on a different scale (revenue versus margin %). Format the secondary axis separately, including number format and bounds.
- Trendline: add from Chart Elements or Format Trendline; types are linear, exponential, logarithmic, polynomial, power, and moving average. Options include forecast forward/backward, display equation, and R-squared. Trendlines are unavailable on stacked, 3-D, pie, doughnut, radar, and surface charts.
- Chart templates (.crtx) save formatting only, not data; apply via Change Chart Type > Templates.
- PivotTable calculations: a calculated field uses other fields and aggregates at the data-field level; a calculated item uses items within one field. Show Values As offers % of Grand Total, running total, difference from, and rank.
- Grouping dates by year/quarter/month or numbers by intervals; slicers and timelines filter one or many connected PivotTables.
- GETPIVOTDATA extracts a value by field and item names and survives layout changes better than a direct cell reference.
Worked example
A PivotTable shows Sales by Region. To display each region as a share of total sales, add Sales to Values a second time, then Show Values As > % of Grand Total. A calculated field is the wrong tool: calculated fields aggregate other fields and cannot reference the PivotTable's own grand total, so =Sales/SUM(Sales) does not produce a share of the grand total. Show Values As computes the ratio against the PivotTable total directly.
Traps
- PivotTables do not expand automatically when rows are added to a plain range; base them on an Excel Table or refresh after resizing.
- A PivotChart reflects the PivotTable only after refresh.
- Clicking a PivotTable cell while typing a formula inserts GETPIVOTDATA; turn it off under PivotTable Analyze > Options if you need a normal reference.
- Calculated fields cannot use cell references, arrays, or self-reference; they operate on sums of underlying data.