Advanced Excel Modeling - Pivot Charts

Post date: Aug 24, 2018 1:24:09 PM

For the professional modeler Excel's advanced features improve transparency and efficiency while reducing complexity and risk. They also provide greater flexibility, scalability and connectivity. These benefits grow in value as models become more complex and connected to other models, data sources and people.

In the previous section we discussed creating slicers to help us see how various scenarios, items, products or other model element impacts results. In this section we create visuals to help make those impacts obvious at a glance with Pivot Charts.

Input Tables

Process Tables

Data Model

Slicers

PivotTables

Pivot Charts

Lesson Objectives

At the end of this lesson you will be able to:

    • Describe the purpose of charts

    • Describe how charts make "Understanding at=a-Glance" possible.

    • Describe the difference between normal charts and Pivot Charts

    • Create a Pivot Chart

This is under construction.

Click this link for the course's start

What is Meant by "Understanding at-a-Glance"?

A Chart's purpose is to make relationships between various elements obvious and to convey important information at a glance.

Numerical results can require a bit of mental deciphering to understand. We often want to know things like which item is larger so we sort our numbers in descending order. And then we want to know by how much so we add a percentage column. And then we want to see if there is a relationship between one column and another or maybe two. So we move columns closer together. And then there are all those other numbers. Are they equally important?

Charts reveal such relationships more readily. They are effective in:

    • Comparing one or more items to other items with bar, column, line, or radar charts.

    • Displaying distributions with histograms, scatter or map charts.

    • Revealing relationships with scatter or bubble charts.

    • Showing composition with pie and waterfall charts.

    • Projecting trends with trend lines.

Charts can display a lot of information in an easy to understand format. They can help our customer grasp what is impactful and ignore what is just noise.

Modeling creates data sets representing one or more real world scenarios that may or may not exist currently. This data can be analyzed in exactly the same way as data analysts analyze current or historical data.

How to Create Pivot Charts

Excel can produce charts based on ranges or PivotTables.

Range based charts require filters, sorts, and calculations be made by changing the range formulas or entries. Changes to the chart do not flow back to the range.

PivotTable based Charts (Pivot Charts) are linked to their PivotTables bidirectionally. Filters, sorts, and data rearrangements applied to Pivot Charts are also applied to their associated PivotTables and vice versa. Slicers can also be applied to Pivot Charts and a single slicer can be applied to multiple PivotTables/Pivot Charts if the PivotTables/Pivot Charts are based on the same data providing us multiple view points over a single data set.

The disadvantage of Pivot Charts is only a limited number of Excel chart types are available. They include: Column, Line, Pie, Bar, Area, Surface, Radar, and combos of those.

    1. To create a PivotChart we start by selecting any cell in our PivotTable. This activates the PivotTable Tools tab.

  1. Click Analyze > PivotChart. This displays the Insert Chart Dialog.

    1. Type: Choose the chart type.

    2. Name: Enter the chart's name in PivotChart Tool's Analyze tab in the Chart Name box on the far left

    3. Placement: Place the chart where you would like it by either click and drag or cut and paste to location.

  1. Resize: Clicking on the chart then click and drag one of the tiny resize circles located onchart edges and corners.

    1. Add Elements: Click the + box next to the chart's upper right to add Axis Titles, Chart Title, Legend and other chart elements.

    2. Apply Styles and Colors: Click the brush box below the + box to apply styles and colors to your chart. Be consistent with both styles and colors.

A monochromatic gray color scheme will work with any corporate pallet and, if conditional formatting for KPIs is applied (using a BXL Add-in) green (good results) and red (poor results) will show up and not clash with the rest of the chart.

Lab

    1. Create Fees and Interest Rate chart

      1. Click any cell in pvtFI (from last lesson)

      2. Click menu path PivotTable Tools > Analyze > PivotChart

      3. On the Insert Chart dialog, All Charts tab, click Combo (at bottom)

      4. For Fees select chart type Clustered Column

      5. For Interest Rate select chart type Line with Markers and check Secondary Axis.

      6. Click OK.

      7. Use menu path PivotChart Tools >Analyze and in the Chart Name: box enter chtFI

    2. Format chtFI

      1. Right click on any of the field buttons (Term, Fees, Interest rate, Description, or Values) and click Hide all field buttons on chart.

      2. Click + box and make sure Axes, Axis Titles, Chart Title, Gridlines, and Legend are checked.

      3. While still in the Chart Elements dialog, hover mouse pointer over Axes. A triangle appears at right. Click it and make sure Primary Horizontal, Primary Vertical and Secondary Vertical are checked.

      4. While still in the Chart Elements dialog, hover mouse pointer over Axis Titles. Click triangle and make sure Primary Horizontal, Primary Vertical and Secondary Vertical are checked.

      5. Click on the chart's left axis title then type Total Fees.

      6. Click on the chart's right axis title then type Interest Rate.

      7. Use menu path PivotTable Tools >Design > Quick Layout and select Layout 3 (top right image)

      8. Click the Brush box, click Style tab and select Style 8 (last style).

      9. Click the Brush box, click Color tab and select the first gray scale (if any) pallet.

      10. Click the chart's Chart Title box then type = then click pvtFI's title.

    1. Place and resize chtFI

      1. Click and drag (or cut and paste) the chart to its home location.

      2. Use the chart's resize handles to size appropriately.

    1. Create Payment and Costs Chart

      1. Click any cell in pvtPC (from last lesson)

      2. Click menu path PivotTable Tools > Analyze > PivotChart

      3. On the Insert Chart dialog, All Charts tab, click Combo

      4. For Payment select chart type Clustered Column

      5. For Costs select chart type Line with Markers and check Secondary Axis.

      6. Click OK.

      7. Use menu path PivotChart Tools >Analyze and in the Chart Name: box enter chtPC

    1. Format chtPC

      1. Right click on any of the field buttons and click Hide all field buttons on chart.

      2. Click + box and make sure Axes, Axis Titles, Chart Title, Gridlines, and Legend are checked.

      3. While still in the Chart Elements dialog, hover mouse pointer over Axes. Click triangle and make sure Primary Horizontal, Primary Vertical and Secondary Vertical are checked.

      4. While still in the Chart Elements dialog, hover mouse pointer over Axis Titles. Click triangle and make sure Primary Horizontal, Primary Vertical and Secondary Vertical are checked.

      5. Click on chart's left axis title then type Payment.

      6. Click on chart's right axis title then type Costs.

      7. Use menu path PivotTable Tools >Design > Quick Layout and select Layout 3.

      8. Click the Brush box, click Style tab and select Style 8.

      9. Click the Brush box, click Color tab and select the first gray scale (if any) pallet.

      10. Click the chart's Chart Title box then type = then click pvtPC's title.

    1. Place and resize chtPC

      1. Click and drag (or cut and paste) the chart to its home location.

      2. Use the chart's resize handles to size appropriately.

Assignments

Quiz

    1. The advantage of charts is:

      1. Relationships are easier to see

      2. Trends are easier to spot

      3. Noise (unimportant information) can be omitted

      4. All of the above

      5. None of the above

    2. The advantage of Pivot Charts over normal charts is:

      1. Changes to Pivot Charts are reflected in their PivotTables

      2. Slicers can be applied to Pivot Charts

      3. Multiple Pivot Charts can be applied to the same data set

      4. All of the above

      5. None of the above

    1. Which Chart type is not supported by Pivot Charts.

      1. Bar

      2. Column

      3. Pie

      4. Line

      5. Area

      6. Scatter

      7. Radar

    1. How many PivotTables/Pivot Charts can we create over a single data set?

      1. 1

      2. Many

    1. What is the best reason to create charts using monochromatic gray scale?

      1. Gray goes with any color scheme.

      2. We want to apply red to chart elements showing something bad and green for something good.

Summary

Good charts make important information instantly recognizable.