Study guide: Excel Optimization and Data Analysis

Course Outline

  1. Pivot Table Setup and Structure
  2. Pivot Analysis and Validation
  3. Goal Seek Setup and Limits
  4. Solver Model and Methods
  5. Solver Results and Constraints
  6. Spreadsheet References and Functions
  7. Charts, Fitting, and Lookup

1. Pivot Table Setup and Structure

โ˜… Must-know

  • ๐Ÿ”„ Create a PivotTable by following these steps:

    1. Convert the data to an Excel Table with Ctrl+T and enable headers.
    2. Choose Insert > PivotTable > From Table/Range.
    3. Choose New Worksheet.
  • The four PivotTable areas are:

    • Rows: one row per item
    • Columns: one column per item
    • Values: numbers to sum, count, or average
    • Filters: show selected data

Further detail

  • A PivotTable groups rows and aggregates their values, performing a task similar to SUMIFS automatically.

2. Pivot Analysis and Validation

โ˜… Must-know

  • PivotTable analysis supports sorting values largest to smallest, applying Top 10 or Top 5 value filters, choosing Sum, Count, or Average, and displaying values as a percentage of the grand total or row total.

๐Ÿ“Œ 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

  • A calculated field can define a ratio such as Margin = Profit รท Sales, and double-clicking a PivotTable value opens the source rows behind it.

๐Ÿ“Œ 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.

Memory Hook

Sum answers total money; Average answers a typical value.

3. Goal Seek Setup and Limits

Key Concepts & Definitions

  • Goal Seek: Runs a model backwards by finding one input value that makes a formula cell reach a specified target.

โ˜… Must-know

  • The three Goal Seek boxes require:
    • Set cell: a formula cell
    • To value: the target number
    • By changing cell: an input cell used by the formula

๐Ÿ“Œ 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.

Memory Hook

Goal Seek hits one target with one input; Solver optimizes with limits.

4. Solver Model and Methods

Key Concepts & Definitions

  • Solver objective: The single formula cell whose value is to be minimized or maximized.
  • Solver variables: The input cells that Solver may change to optimize the objective.
  • Solver constraints: Rules that the changing cells or model results must satisfy.

Essential Points

๐Ÿ“Œ 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.

5. Solver Results and Constraints

โ˜… Must-know

  • A binding constraint is one the design is pressed against and therefore controls it, while a constraint with slack is not limiting the design.

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.

Memory Hook

Binding constraints control the design; constraints with slack do not.

6. Spreadsheet References and Functions

Essential Points

๐Ÿ“Œ Relative references such as A1 adjust when filled, absolute references such as AA1 stay locked, and mixed references such as $A1 lock only the column.

  • IF, MIN, and MAX can model tiered pricing, including a free first 10 hours followed by a charge calculated as =MAX(0, hours โˆ’ 10) * rate.

Memory Hook

Relative references move; absolute references stay locked.

7. Charts, Fitting, and Lookup

Essential Points

๐Ÿ“Œ 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.

Test yourself

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?

Take the quiz โ†’

Study with flashcards

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.

See the flashcards โ†’

Related classes

Make your own study guides

Upload your class notes and Revizly writes a study guide, a quiz and flashcards in about a minute.

Study guide maker