PivotTables & Data Manipulation Flashcards

Which 'Show Values As' option displays each value as a percentage of the total for its column?

Answer: % of Column Total

% of Column Total divides each value by the total of the column it sits in.

You want users to filter a PivotTable by date ranges using a sliding visual control. What should you insert?

Answer: Timeline

A Timeline is a date-specific filter that lets users select days, months, quarters, or years by dragging.

After editing values in a PivotTable's source range, what must you do to see the changes in the PivotTable?

Answer: Refresh the PivotTable

PivotTables cache source data and update only when refreshed (Alt+F5 or Refresh).

When a numeric field containing no blanks or text is added to the Values area, which summary function does Excel use by default?

Answer: Sum

Excel defaults to Sum for all-numeric fields and to Count when the field contains blanks or text.

Where do you create a calculated field such as Profit = Revenue - Cost in a PivotTable?

Answer: PivotTable Analyze > Fields, Items & Sets

Calculated fields are created under PivotTable Analyze > Fields, Items & Sets > Calculated Field.

A PivotTable lists individual dates in the Rows area. How can you summarize them by month and quarter?

Answer: Right-click a date and choose Group

The Group command lets you combine dates into months, quarters, years, and other intervals.

While typing a formula, you click a cell inside a PivotTable and Excel inserts a long function automatically. Which function is it?

Answer: GETPIVOTDATA

By default Excel generates GETPIVOTDATA when you reference a PivotTable value cell in a formula.