CES PivotTables & Data Manipulation 2

Question 1

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

Accepted Answer

% of Column Total

Answer

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

Question 2

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

Accepted Answer

Timeline

Answer

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

Question 3

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

Accepted Answer

Refresh the PivotTable

Answer

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

Question 4

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

Accepted Answer

Sum

Answer

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

Question 5

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

Accepted Answer

PivotTable Analyze > Fields, Items & Sets

Answer

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

CES - Certified Excel Specialists Practice Test