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

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

What is Meant by "Understanding 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.

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.

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.

• 3. 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.

• 4. 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

• 5. 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.

• 6. 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

• • Read Data Visualization - How to Pick the Right Chart Type?

  • Read How to Choose the Right Chart Type for your Data

  • Read Create a PivotChart

  • Complete the Lab

  • Complete the Quiz

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

• 3. Which Chart type is not supported by Pivot Charts.

• • 1. Bar

    2. Column

    3. Pie

    4. Line

    5. Area

    6. Scatter

    7. Radar

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

• • 1. 1

    2. Many

• 5. 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.