CES Strategic Planning & Analysis 5

Question 1

A dashboard has three PivotTables from the same data source. How can one slicer filter all three?

Accepted Answer

Use Report Connections on the slicer to link the PivotTables

Answer

Report Connections (Filter Connections) lets a single slicer control multiple PivotTables that share a cache.

Question 2

In a PivotTable showing sales by year, which Show Values As option displays year-over-year change as a percentage?

Accepted Answer

% Difference From

Answer

% Difference From compares each value to a base item, such as the previous year, and shows the percentage change.

Question 3

Actual revenue is in B2 and budgeted revenue is in C2. Which formula returns the variance as a percentage of budget?

Accepted Answer

=(B2-C2)/C2

Answer

Budget variance % divides the difference between actual and budget by the budget amount.

Question 4

Which sparkline type is best for showing only whether each month hit or missed a target, without showing magnitude?

Accepted Answer

Win/Loss

Answer

Win/Loss sparklines show only positive or negative outcomes as equal-height markers.

Question 5

You want KPI cells to show green, yellow, or red symbols based on performance thresholds. Which feature is most appropriate?

Accepted Answer

Conditional formatting icon sets

Answer

Icon sets in conditional formatting show traffic-light symbols based on the value thresholds you set.

CES - Certified Excel Specialists Practice Test