Excel Optimization and Data Analysis Practice Quiz (17 questions)

All questions and answers

1. You need to build a PivotTable from a new dataset and place it on a separate sheet. Which setup sequence should you use?

Convert the data to an Excel Table with headers, then choose Insert > PivotTable > From Table/Range > Existing Worksheet
Choose Insert > PivotTable > From Table/Range > New Worksheet, then convert the source range to an Excel Table with headers
Convert the data to an Excel Table without headers, then choose Insert > PivotTable > From Table/Range > New Worksheet
Convert the data to an Excel Table with headers, then choose Insert > PivotTable > From Table/Range > New Worksheet

Convert the data to an Excel Table with headers, then choose Insert > PivotTable > From Table/Range > New Worksheet

Explanation

The setup begins by converting the source data to an Excel Table with headers enabled, followed by inserting a PivotTable from that table onto a new worksheet. Creating the pivot first reverses the sequence, Existing Worksheet changes the destination, and omitting headers fails to meet the setup requirement.

2. You want a PivotTable to show one row per region, one column per product, and total sales for each combination. Where should each field go?

Region in Rows, Product in Columns, and Sales in Values
Region in Rows, Sales in Columns, and Product in Values
Region in Rows, Product in Columns, and Sales in Filters
Product in Rows, Region in Columns, and Sales in Values

Region in Rows, Product in Columns, and Sales in Values

Explanation

Rows creates one row per region, Columns creates one column per product, and Values aggregates the sales numbers. Swapping Region and Product reverses the intended layout, Filters selects subsets rather than calculating totals, and placing Product in Values does not aggregate sales.

3. A PivotTable lists many regions, and you need to display just the five regions with the largest sales totals. Which action should you take?

Apply a Top 5 value filter to the region labels
Display sales as a percentage of the grand total
Sort the sales totals from largest to smallest
Apply the default Top 10 value filter to the region labels

Apply a Top 5 value filter to the region labels

Explanation

A Top 5 value filter limits the PivotTable to the five largest values. Sorting changes the order but does not reduce the list, the default Top 10 keeps ten items, and showing percentages changes the display rather than selecting the top five.

4. You add records to the source data of a PivotTable, but the displayed totals remain unchanged. The source is an Excel Table. What should you do?

Refresh the PivotTable
Move the PivotTable to a new worksheet
Change the Values summary from Sum to Count
Convert the Excel Table back to a range

Refresh the PivotTable

Explanation

A PivotTable does not automatically update when source data changes, so you must refresh it; an Excel Table ensures new rows are included in the source. Converting the table to a range does not refresh the pivot, changing Sum to Count changes the aggregation, and moving the pivot does not update its data.

5. Two orders have sales of $100 and $900, with profits of $10 and $270 respectively. Which calculation gives the overall profit margin?

Average the order margins of 10% and 30% to get 20%
Use the larger order's 30% margin as the overall result
Divide total sales of $1,000 by total profit of $280 to get about 357%
Divide total profit of $280 by total sales of $1,000 to get 28%

Divide total profit of $280 by total sales of $1,000 to get 28%

Explanation

The overall margin is total profit divided by total sales: 10+270100+900=2801000=28%\frac{10+270}{100+900}=\frac{280}{1000}=28\%. Averaging the individual margins gives 20%, dividing sales by profit inverts the ratio, and using the larger order's margin ignores the other order.

6. A spreadsheet contains a formula for projected monthly revenue. You know the revenue target and want to find the single sales-volume input that would produce it. Which tool fits this task?

Solver
Scenario Manager
Goal Seek
A chart

Goal Seek

Explanation

Goal Seek works backward from a desired formula result to find one input value. Solver is for optimization or limits, while Scenario Manager compares cases and a chart displays results.

7. A formula in cell B9 calculates a catapult's range from the launch angle in B8. To make the range equal 5 meters, how should Goal Seek be configured?

Set cell B9; To value 5; By changing cell B8
Set cell B9; To value B8; By changing cell 5
Set cell 5; To value B9; By changing cell B8
Set cell B8; To value 5; By changing cell B9

Set cell B9; To value 5; By changing cell B8

Explanation

The Set cell is the formula output, the To value is the typed target, and the By changing cell is the input used by the formula. Reversing the output and input roles or treating a cell reference as the target misconfigures Goal Seek.

8. A manager wants a spreadsheet to maximize profit by changing several prices while keeping production within capacity limits. Which tool should the manager use?

A chart
Goal Seek
Scenario Manager
Solver

Solver

Explanation

Solver can optimize a result while changing multiple inputs and enforcing limits. Goal Seek changes one input to hit one target; a chart and Scenario Manager do not enforce the requested optimization constraints.

9. A designer wants Excel Solver to minimize the surface area of a can. Which cell should be selected as the objective?

The constraint cell for required volume
The formula cell calculating surface area
The input cell containing the can's radius
A cell containing the target volume

The formula cell calculating surface area

Explanation

The objective is the single formula cell whose value Solver minimizes or maximizes. Radius is a variable, while a required-volume condition is a constraint rather than the objective.

10. A Solver model calculates a beam's cross-section area from its width and height. Which cells should be designated as variables if Solver may adjust the design dimensions?

The cells containing stress limits
The input cells for width and height
The formula cell for cross-section area
The cell displaying the final report

The input cells for width and height

Explanation

Solver variables are the input cells it may change, so the beam's width and height are the appropriate cells. The calculated area is an objective or model result, and stress limits belong to constraints.

11. A product-design model includes formulas with powers, products, and ratios. Which Solver method is appropriate for this nonlinear model?

Simplex LP
Goal Seek
GRG Nonlinear
Integer constraint

GRG Nonlinear

Explanation

GRG Nonlinear is suited to formulas involving powers, products, or ratios. Simplex LP applies when relationships are linear, while an integer constraint restricts variable values rather than selecting a solving method.

12. A Solver report shows that the stress limit is reached exactly, while the deflection remains below its limit. Which constraint is binding?

The stress constraint
The variable lower bound
The objective function
The deflection constraint

The stress constraint

Explanation

A binding constraint is reached at its limit and therefore controls the design; stress is at its limit. Deflection has slack, the objective is not a constraint, and a lower bound is not identified as active here.

13. Two runs of a nonlinear Solver model, started from different initial values, produce feasible designs with different objective values. What should you do to select the better candidate?

Average the two designs
Keep the result from the first run
Compare their objective values
Choose the design with more binding constraints

Compare their objective values

Explanation

Different starting points can lead to different answers in nonlinear Solver problems, so compare the objective values of candidate solutions. The first result is not guaranteed to be best, averaging is not the stated selection rule, and the number of binding constraints does not determine the better objective.

14. Cell B1 contains a rate, and a formula in C2 should keep referring to that rate when filled down while allowing the row reference in another part of the formula to change. Which reference locks the rate cell?

B1
$B$1
B$1
$B1

$B$1

Explanation

An absolute reference such as $B$1 locks both the column and row when copied. B1 is relative, $B1 locks only the column, and B$1 locks only the row.

15. A service charges nothing for the first 10 hours and charges a fixed rate for each additional hour. Which expression calculates the bill for a given number of hours?

=MIN(hours, 10) * rate
=MAX(0, hours − 10) * rate
=hours * rate
=(hours − 10) * rate

=MAX(0, hours − 10) * rate

Explanation

MAX(0, hours − 10) makes the billable hours zero for usage at or below 10, then charges the rate for hours beyond that threshold. Multiplying all hours charges the free tier, omitting MAX can yield a negative amount, and MIN selects the free-tier hours instead of the excess.

16. A researcher plots measured temperature against time and then compares totals across categories with a bar chart. Which charting approach follows the appropriate conventions?

Use an XY Scatter chart for temperature and time, start the bar-chart axis at zero, and label axes with quantities but omit units.
Use an XY Scatter chart for temperature and time, start the bar-chart axis at zero, and label axes with quantities and units.
Use an XY Scatter chart for temperature and time, start the bar-chart axis above zero, and label axes with quantities and units.
Use a bar chart for temperature and time, start the bar-chart axis at zero, and label axes with quantities and units.

Use an XY Scatter chart for temperature and time, start the bar-chart axis at zero, and label axes with quantities and units.

Explanation

Measured paired data belong in an XY Scatter chart; bar-chart axes should start at zero, and chart axes should identify both quantity and unit. A bar chart for paired measurements confuses chart purposes, an axis above zero violates the bar-chart convention, and omitted units leave axis labels incomplete.

17. An XLOOKUP using exact match returns #N/A even though the desired entry appears in the table, and another lookup needs the next larger match. Which response best addresses both tasks?

Check for typos, extra spaces, or numbers stored as text; use match mode 1 for the next larger match.
Check for typos, extra spaces, or numbers stored as text; use match mode -1 for the next larger match.
Assume the table lacks the desired entry; use match mode 1 for the next larger match.
Switch the failing exact lookup to approximate matching; use match mode 1 for the next larger match.

Check for typos, extra spaces, or numbers stored as text; use match mode 1 for the next larger match.

Explanation

An #N/A result can stem from a typo, spaces, or a number stored as text, while XLOOKUP match mode 1 requests the next larger match. Using mode -1 requests the next smaller match, switching the exact lookup to approximate matching changes its intended behavior, and #N/A does not by itself prove the entry is absent.

Study with flashcards

Lock in the answers with 45 flashcards on Excel Optimization and Data Analysis.

How do you open the PivotTable creation dialog after converting source data to an Excel Table?

Choose Insert > PivotTable > From Table/Range > New Worksheet.

What does a PivotTable field placed in Rows display?

One row per item.

What does a PivotTable field placed in Columns display?

One column per item.

See the flashcards →

Read the study guide

Go over the full study guide for Excel Optimization and Data Analysis.

Read the study guide →

Related classes

Make your own practice quizzes

Upload your class notes and Revizly writes a practice quiz with explained answers in about a minute.

Quiz maker