Pivot Tables, Pivot Charts, and Solver – Comprehensive Study Notes

Pivot Tables, Pivot Charts, and Solver – Comprehensive Study Notes

• Purpose of charts and tables

  • Charts communicate a specific message clearly and simply; tell a story with your chart.
  • Pivot tables serve a similar purpose for tabular data: summarize data in a way you determine, revealing relationships not obvious in the raw dataset.
  • Pivot tables present data in tabular form but are still designed to communicate a message efficiently.

• Quick recap of when to use each

  • Charts: communicate trends and messages visually; build by inserting a chart and then adding data series manually if needed.
  • Pivot tables: summarize large datasets; customize layout to reveal specific relationships; great for answering targeted questions.

Creating a Pivot Table in Excel (step-by-step)

• Start: select any cell in the dataset, then Insert → PivotTable.
• Excel auto-detects the data range; the PivotTable will expand to cover all data.
• Choose location:
  • New Worksheet: creates a new sheet with the pivot table in the top-left corner.
  • Existing Worksheet: specify a top-left cell where the pivot table begins.
• After clicking OK, a blank pivot table appears and the PivotTable Fields pane (the Field List) shows up.
• If the Field List pane is hidden, go to PivotTable Analyze → Field List under Show to bring it back.
• The Field List contains the dataset’s fields (columns from the original table) that can be dragged into four areas:
  • Filters: filter the data by a field.
  • Columns: define column categories.
  • Rows: define row categories.
  • Values: the data to summarize (sum, count, average, etc.).
• Field types and defaults:
  • The fields themselves are the dataset’s columns.
  • Values area shows a summary of the data (e.g., count, sum, average) depending on the field and chosen aggregation.

Example Dataset and Key Questions (Vehicles in a Warehouse)

• Example question 1: Which car brand has the highest number of cars in the warehouse?
  • Put Brand in Rows.
  • Put Brand in Values to count occurrences; Excel defaults to counting when the field is dragged to Values.
  • Result: counts by brand; grand total shows total number of vehicles (e.g., 24).
  • Observation: When you place a field in Rows vs Values, you control both the granularity and the metric displayed.
• Understanding totals:
  • If you place brand in Rows and also in Values, you get a per-brand count (e.g., Ford, Honda tied for the highest).
  • If you place Color into Columns, you get a two-dimensional pivot table with intersections (e.g., red Ford, etc.).
• Part two of the exercise (question 2): how many white Chevrolet cars and how many silver cars in total?
  • You can add Color to either Columns or Rows.
  • Color in Columns yields a 2D table with color as a separate dimension across brands.
  • Color in Rows makes Color a subcategory of Brand (e.g., Chevrolet → White, Silver within Chevrolet).
  • Pros/cons:
  • Columns: easier at-a-glance totals per color across brands (vertical totals on the right, horizontal totals at the bottom).
  • Rows: easier to see a specific brand with its colors (but less straightforward to see cross-brand totals at a glance).
  • When Color is above Brand in Rows (hierarchy: Color → Brand), you can collapse/expand higher-level categories using the +/- controls.
  • To see both brand-level totals and color breakdowns at a glance, Brand in Rows and Color in Columns (or vice versa) is often best.
• Important guideline: be intentional with fields; don’t dump all fields into one pivot table. The goal is to communicate a clear message quickly.

Practical Pivot Table Layouts and Best Practices

• Layout decisions:
  • If you want a 2D grid that shows intersections (e.g., color by brand), place one field in Rows and one in Columns.
  • If you want hierarchical drill-down (e.g., Color within Brand), place Color in Rows under Brand.
  • For straightforward counts by category, place the category in Rows and the same category in Values (which defaults to a Count).
• Readability and storytelling:
  • Use only what's necessary to communicate your message.
  • Collapse/expand levels to avoid overwhelming users with data.
• Pivot tables as an interactive storytelling tool:
  • They allow you to explore relationships quickly and adjust layouts to reveal insights without altering the underlying data.

Pivot Table Fields and Operations

• Field List and areas:
  • Filters: filter by a field.
  • Columns: layout by columns.
  • Rows: layout by rows.
  • Values: displayed data (summary statistics).
• Examples of common operations:
  • Count: when a field is placed in Values, it often defaults to Count if the field is textual.
  • Sum: used for numeric fields when you want the total across groups.
  • Average: use Value Field Settings to switch from Sum to Average.
  • Max: use Value Field Settings to show the maximum value in a group.
• Charting with Pivot Tables:
  • Pivot Chart is a dynamic chart linked to the pivot table; it updates when the pivot table changes and vice versa.
  • To add a Pivot Chart: Insert → PivotChart (or PivotTable Analyze → Pivot Chart from an existing pivot).
  • Chart types: you can use clustered column, line with markers, etc.
  • To combine different measures with different scales, you can use a Combo/Combination chart and place one measure on a secondary axis.
  • Order matters: rearranging fields (e.g., putting Profit in Values vs Mileage in Values) changes how data is presented visually.
  • Filtering the chart (via the Pivot Table row dropdown) updates both table and chart.
• Practical tips:
  • Alt-drag to snap graphs to cell borders when moving/resizing.
  • If a pivot chart looks sparse, reconsider the layout; some combinations create many empty intersections.
  • Use the Value Field Settings to switch between Sum, Average, and Max to suit the question.

Common Pivot Table Scenarios Demonstrated

• Scenario A: Highest number of cars by brand with color breakdown
  • Setup: Brand in Rows; Color in Columns (or vice versa depending on readability); Color in Rows adds nested subcategories under Brand.
  • Tip: If you want to see overall brand totals and color breakdown simultaneously, place Brand in Rows and Color in Columns.
• Scenario B: Include color and brand with subtotals
  • Add Color above Brand in Rows to create a top-level color category with brands underneath; useful for seeing color distribution first.
  • You can expand/collapse to view only white cars or only silver cars by color.
• Scenario C: Build a chart to visualize mileage and profit simultaneously
  • Create a Pivot Table with Mileage as a Values statistic (Sum, Average, or Max) and add Profit as another Values item to compare scales.
  • If Profit values are much smaller, use a Combo Chart with a secondary axis for Profit to visualize both measures together.
• Practical note on chart types and scaling:
  • If you add Profit to the pivot chart and it’s too small on the same axis as Mileage, switch to a combo chart and assign Profit to the secondary axis.
  • You can change the chart type via Design → Change Chart Type → Combo; configure one series on the primary axis (Mileage) and another on the secondary axis (Profit).
• Reminder about data integrity:
  • The pivot table/chart reflect the data and the chosen layout; misplacement of fields can mislead or obscure the message.
  • Always align your layout with the question you’re answering.

Solver Add-in: Enablement and Core Concepts

• What is Solver?
  • Solver is an add-in (not enabled by default) used to solve optimization problems and linear programming tasks.
  • It can solve systems of equations and optimization problems with constraints.
• Enabling Solver in Excel:
  • File → Options → Add-ins → Excel Add-ins (at bottom) → Go…
  • Check the Solver Add-in box and click OK; Solver appears under the Data tab on the right.
• Core terminology:
  • Objective function: the equation you want to maximize or minimize.
  • Variable cells: the cells containing decision variables (the values Solver will change).
  • Constraints: extra equations/inequalities restricting feasible solutions.
• Important rule: Solver only references cells; it does not perform calculations inside Solver itself.
  • Set Objective: provide a cell with a formula that computes the objective value (e.g., a cell containing a computed profit).
  • By Changing Variable Cells: select the cells that will hold the final values (the decision variables).
  • Constraints: add references to cells that define the left-hand side of constraints and the right-hand side (the bound).
• How to add a constraint in Solver:
  • Click Add; cell reference points to the left-hand side calculation (the formula that computes LHS).
  • Choose the comparison operator (
  • Right-hand side is the bound value (constant or a cell reference).
• A useful technique for building constraints with many equations:
  • If multiple constraints share the same left-hand side structure, you can select a range for the left side coefficients and a corresponding range for the right-hand side bounds; Solver will pair them up.
• Practical tips for building an LP model in Solver:
  • Do not write equations directly in Solver; let Solver reference cells that compute those equations.
  • Use absolute references (press F4 to lock cell references) when copying formulas so the variable cells don’t shift unexpectedly.
  • Make unconstrained variables nonnegative only if you have domain knowledge supporting nonnegativity; otherwise leave unchecked.
• Numerical examples discussed in the session:
  • Example 1: Maximize $6x + 5y$ subject to constraints $x + y \,\leq \,5$ and $3x + 2y \,\leq \,11$.
  • Modeling notes:
  • Variable cells: where x and y live (the yellow-highlighted cells in the example).
  • Left-hand sides: computed via SUMPRODUCT to multiply coefficients by corresponding variables and sum them.
  • Coefficient layout: place coefficients for each equation in a table; when copying formulas, anchor variable cells with absolute references so formulas adjust correctly for subsequent equations.
  • SumProduct example for LHS:
  • If coefficients for an equation are in a row like [2, 5] and variables are [x, y], then the left-hand side is computed as: $ext{LHS} = ext{SUMPRODUCT}([2,5], [x,y])$
  • Copy the formula downward to apply to additional equations with corresponding coefficient rows.
  • Equality constraints example: set LHS equal to a right-hand side value, e.g., $ext{LHS}<em>{1} = 10$ and $ext{LHS}</em>{2} = 6$.
• Solver with an objective and multiple constraints:
  • Without an objective function, Solver can still be used to solve a system of equations by focusing on equality constraints.
  • With an objective, Solver can maximize or minimize a chosen objective cell value.
  • Example results: one solution might be x = 0, y = 2 for a given system; another scenario yields x = 3.2, y = -1.8 when continuous variables are allowed and nonnegativity is not enforced.
  • Integer constraint example: to enforce integer solutions, add a constraint with type Integer (int) for the variable cells; applying this may drastically change the solution (e.g., from 3.2, -1.8 to 2, 0).
  • Case when integers are necessary: turn on int constraint for relevant variables to ensure feasible, whole-number solutions.
• Practical caveat:
  • If results seem off (e.g., Solver returning x = 0, y = 0), re-check the setup: verify that variable cells and constraints reference the correct cells; ensure the objective function is properly defined; confirm the right-hand side bounds are correct.

Case Study Prompt (Capstone Practice)

• Scenario: Three types of needles; need to determine how to utilize available materials to maximize profit.
• Task: Determine how to set up variables, objective, and constraints to maximize profit given material limitations.
• Approach:
  • Identify variable cells (quantities of each needle type).
  • Define objective function (profit from producing needles) as a cell formula.
  • Build constraints reflecting material limitations (e.g., total material used for each needle type cannot exceed supply).
  • Use Solver to find the optimal production quantities that maximize profit while satisfying all constraints.
• Follow-up: After attempting, review the solver settings to ensure proper objective, variable cells, and constraints, then compare with an instructor demonstration.

Key Takeaways and Ethical/Practical Implications

• Pivot tables and charts are powerful for quickly communicating findings; design them with intent rather than filling them with everything.

• Pivot charts extend pivot tables with dynamic visualizations; they reflect changes in the underlying pivot table and can be filtered and formatted for clarity.

• Solver is a powerful optimization tool but requires careful setup: define objective, variables, and constraints accurately; use integer constraints when needed; understand the implications of nonnegativity on the solution.

• Real-world relevance:

  • Pivot tables help managers summarize and compare performance across categories, colors, brands, or time periods.
  • Solver supports resource allocation, production planning, and optimization decisions under constraints.

• Ethical/practical considerations:

  • Present data honestly; avoid misrepresenting totals or cherry-picking fields to support a bias.
  • Be transparent about constraints and assumptions used in optimization models.

• Quick formulas recap (LaTeX):

  • Objective (example): $ext{maximize } f(x,y) = 6x + 5y$
  • Constraints: $x + y \,\leq \,5$, $3x + 2y \,\leq \,11$
  • Alternative objective and constraints (scenario): $ext{maximize } g(x,y) = 6x + 3y$
  • Constraint example: $x - y \,\leq \,5$ and $3x + 2y \leq 6$
  • SumProduct example for LHS in Solver setup:
  • Coefficients: a row like $[2, 5]$; Variables: $[x, y]$; LHS = $ext{SUMPRODUCT}([2,5], [x,y])$
  • Integer constraint in Solver: select variables, then set constraint type to "int".

• Final reminder:

  • Practice building pivot tables with a clear question in mind.
  • Use Solver to understand feasible optimal solutions under given constraints, and adjust nonnegativity or integrality as needed to reflect real-world limits.