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.
What can the Values area do with numbers?
Sum, count, or average them.
What does the Filters area let you display?
Selected data.
Which value-sorting order can PivotTable analysis apply?
Largest to smallest.
Which value filters can PivotTable analysis apply?
Top 10 or Top 5.
What must users do after PivotTable source data changes?
Refresh the PivotTable.
How are new rows included when PivotTable source data is an Excel Table?
They are included automatically.
What overall margin results from $280 profit on $1,000 sales?
28%.
What does Goal Seek find when running a model backwards?
One input value that makes a formula cell reach a specified target.
In Goal Seek, what must the Set cell contain?
A formula.
In Goal Seek, what does the To value specify?
The typed target number.
What does Goal Seek change to reach one target?
One input value.
Which Excel tool optimizes a best value or enforces limits?
Solver.
What is a Solver objective?
The single formula cell whose value is minimized or maximized.
Which cells are Solver variables?
Input cells that Solver may change to optimize the objective.
What do Solver constraints specify?
Rules that changing cells or model results must satisfy.
What distinguishes a binding constraint from a constraint with slack?
A binding constraint controls the design; a constraint with slack does not limit it.
What does a PivotTable do to rows and their values?
It groups rows and aggregates their values.
What can a PivotTable calculated field define using Profit and Sales?
A Margin ratio: Profit divided by Sales.
What does double-clicking a PivotTable value open?
The source rows behind it.
How do you fix a PivotTable showing Count instead of Sum for a numeric total?
Fix the blank or text source cell, then select Sum in Value Field Settings.
For the ball model, which valid time is nearest a starting guess of 1 second?
0.99 seconds, going up.
For the ball model, what is the other valid time for reaching 15 m?
3.09 seconds, coming down.
What maximum height does the ball model reach?
20.4 meters.
Which Solver method handles formulas with powers, products, or ratios?
GRG Nonlinear.
Which Solver method applies when all model relationships are linear?
Simplex LP.
When should integer constraints be added to a Solver model?
When a decision variable represents a whole item that cannot be produced fractionally.
Why should Solver models include minimum-size constraints for design dimensions?
Without them, Solver may choose zero or negative dimensions.
What should you compare when nonlinear Solver results vary with starting points?
Compare the objective values of candidate solutions.
Why can a feasible tall, thin beam still fail in real life?
Solver checks only entered limits, so the beam may buckle sideways.
What happens to a relative reference such as A1 when filled?
It adjusts.
What happens to an absolute reference such as $A$1 when filled?
It stays locked.
What does a mixed reference such as $A1 lock?
Only the column.
How can IF, MIN, and MAX be used in spreadsheet pricing models?
They can model tiered pricing.
In the tiered pricing example, how many initial hours are free?
10 hours.
What formula calculates the charge after 10 free hours?
Which chart type should be used for measured data?
An XY Scatter chart.
Where should bar-chart axes begin?
At zero.
What should chart axis labels specify?
The quantity and its unit.
What can a fitted trendline display?
Its equation and R².
Why is predicting beyond measured data risky?
It extrapolates beyond the measured range.
Which match modes can XLOOKUP use?
Exact match or next-larger match mode 1.
What can cause an XLOOKUP #N/A error?
Typos, spaces, or numbers stored as text.
Answer 17 questions on Excel Optimization and Data Analysis.
1. You need to build a PivotTable from a new dataset and place it on a separate sheet. Which setup sequence should you use?
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?
Go over the full study guide for Excel Optimization and Data Analysis.
Read the study guide →Upload your class notes and Revizly writes your flashcards in about a minute.
Flashcard maker