Summarising with pivot tables — quiz

Which pivot feature gives % of grand total without a helper column?

1. Grouping
2. Show Values As
3. Slicers
4. GETPIVOTDATA

Answer: Show Values As — Show Values As offers % of total, running total, rank and difference calculations.

How do you make one slicer filter several pivot tables?

1. Copy the slicer
2. Use Report Connections
3. Group the pivots
4. Use GETPIVOTDATA

Answer: Use Report Connections — Report Connections links a slicer to every pivot sharing a cache.

You receive a new CSV monthly and rebuild a report by hand. Best fix?

1. A macro
2. A Power Query From Folder connection
3. More VLOOKUPs
4. A pivot table

Answer: A Power Query From Folder connection — From Folder plus Refresh replaces the manual rebuild entirely.

Data has one column per month. Which transformation makes it analysable?

1. Pivot
2. Unpivot
3. Merge
4. Fill down

Answer: Unpivot — Unpivot converts month columns into attribute/value rows suitable for pivots.

You need a correct distinct count in a pivot. What enables it?

1. Show Values As
2. Loading to the Data Model
3. Grouping
4. A slicer

Answer: Loading to the Data Model — Distinct Count as a summarisation requires the Data Model / Power Pivot engine.

The Data Model helps most when:

1. One flat table of 500 rows
2. Several related tables and large volumes
3. Formatting a report
4. Printing

Answer: Several related tables and large volumes — Relationships, scale, distinct counts and time intelligence are its purpose.

A pivot grouped by Months only shows 12 rows across 3 years because:

1. A bug
2. Months alone pools every year — add Years too
3. The data is wrong
4. Wrong aggregation

Answer: Months alone pools every year — add Years too — Select Years and Months for a time series.

A pivot calculated field for Revenue/Units computes:

1. Row by row then averaged
2. Total revenue divided by total units
3. The median
4. An error

Answer: Total revenue divided by total units — It operates on the aggregated values.

How do you make one slicer control three pivot tables?

1. Copy the slicer
2. Report Connections
3. A macro
4. Group the pivots

Answer: Report Connections — They must share a data source.

For filtering by date, prefer:

1. A slicer on the date field
2. A timeline
3. A filter dropdown
4. Conditional formatting

Answer: A timeline — A date slicer would list every individual date.