Advanced Charts and Tables (PivotTables, PivotCharts, and Chart Types)
Summarising and visualising
This domain covers PivotTables, PivotCharts, and the advanced chart features that separate an Expert candidate from an Associate.
PivotTable mechanics. Fields placed in Rows and Columns form the layout; Values aggregate (Sum, Count, Average, Min, Max). Show Values As converts a value field into % of Grand Total, % of Column Total, Running Total, Rank, and similar. Date fields can be grouped by Month, Quarter, or Year. Calculated fields create a new field whose formula references other fields (e.g., =Revenue*0.1); calculated items create a new member inside an existing field (e.g., a combined "East + West" item). Confusing the two is the single most common trap.
Filtering and interactivity. Slicers and Timelines filter pivots visually; Report Connections lets one slicer drive several PivotTables at once. A PivotChart stays linked to its PivotTable, so filtering either filters both.
Data Model. Choosing Add this data to the Data Model enables relationships between tables and unlocks Distinct Count - a summarization option unavailable to a plain pivot cache.
Charts. Combo charts place one series on a secondary axis when the scales differ wildly. Trendlines, error bars, and data labels are per-series settings. Chart templates are saved as .crtx and applied via Change Chart Type > Templates. Sparklines (Line, Column, Win/Loss) live inside a single cell. Structured references such as Table1[Sales] auto-expand, giving charts dynamic source ranges without OFFSET.
Rule of thumb: if the new calculation uses whole fields, it is a calculated field; if it combines specific items within one field, it is a calculated item.