โ Must-know
๐ Create a PivotTable by following these steps:
The four PivotTable areas are:
Further detail
โ Must-know
๐ PivotTables do not update automatically when source data changes, so refresh the PivotTable; new rows are included automatically when the source is an Excel Table.
๐ Averaging individual ratios does not generally give the overall ratio: two orders with margins of 10% and 30% average to 20%, whereas total profit of $280 divided by total sales of $1,000 gives an overall margin of 28%.
Further detail
๐ If a PivotTable displays Count instead of Sum for a numeric total, the source column may contain a blank or text cell; fix the source cell and select Sum in Value Field Settings.
Sum answers total money; Average answers a typical value.
โ Must-know
๐ Goal Seek changes only one input to hit one target; it cannot optimize a best value or enforce limits, which are tasks for Solver.
Further detail
When a model has multiple valid answers, Goal Seek finds the answer nearest its starting guess; for a ball reaching 15 m under h = 20t โ 4.9tยฒ, the two valid times are 0.99 s going up and 3.09 s coming down.
A Goal Seek target may be impossible: the ball model h = 20t โ 4.9tยฒ reaches a maximum height of 20.4 m, so a target of 25 m has no solution.
Goal Seek hits one target with one input; Solver optimizes with limits.
๐ Use GRG Nonlinear for formulas with powers, products, or ratios, such as can and beam models, and use Simplex LP when all relationships are linear.
๐ Add integer constraints when a decision variable represents a whole item that cannot be produced fractionally.
โ Must-know
Further detail
Missing lower limits can allow Solver to choose zero or negative dimensions, so include appropriate minimum-size constraints.
Different starting points can produce different answers in nonlinear Solver problems, so compare the objective values of candidate solutions.
Solver checks only the limits entered in the model, so a mathematically feasible tall, thin beam may still buckle sideways in real life.
Binding constraints control the design; constraints with slack do not.
๐ Relative references such as A1 adjust when filled, absolute references such as 1 stay locked, and mixed references such as $A1 lock only the column.
Relative references move; absolute references stay locked.
๐ Use an XY Scatter chart for measured data, start bar-chart axes at zero, and label chart axes with the quantity and its unit.
A fitted trendline can display its equation and Rยฒ, but predicting beyond the range of the measured data is risky.
XLOOKUP can use exact match or next-larger match mode 1, and #N/A errors may be caused by typos, spaces, or numbers stored as text.
Take a 17-question practice quiz on Excel Optimization and Data Analysis, with every answer explained.
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?
Memorize the key concepts of Excel Optimization and Data Analysis with 45 flashcards.
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.
Upload your class notes and Revizly writes a study guide, a quiz and flashcards in about a minute.
Study guide maker