40 6.3 PivotCharts
Emese Felvegi; Noreen Brown; Barbara Lave; Julie Romey; Mary Schatz; Diane Shingledecker; and Robert McCarn
Learning Objectives
- Create a PivotChart.
- Sort and filter data for a PivotChart.
- Use slicers.
- Interpret an output.
CREATe A PIVOTCHART
Larger data sets make it impossible for Excel to create a basic chart or graph for you. If your data source or selection has too many cells, the recommended chart types will have no items to show, nor will you be able to select any items from the All Charts tab.
Even though we can use PivotTables to summarize large data sets, it is often helpful to use data visualization tools to give us a quick and high impact visual of our results. PivotCharts are a great way of getting an overview of general trends, comparisons, or proportions of your data in a visual format.
INSERT A PIVOTCHART
- Select any ONE cell in your range of data. (Note: Do NOT select the entire sheet, allow Excel to select the source automatically, otherwise, you may select too much data for the application to handle causing it to slow down or freeze.)
- Go to the Insert tab on the ribbon, select the ‘PivotCharts’ icon (Figure 6.3.1).
- A dialogue box titled ‘Create PivotCharts’ will pop-up, the Table/Range automatically populates with a selection of your data (Figure 6.3.2).
- Confirm that your PivotTable will be inserted into a New Worksheet.
- Select and click ‘OK’.
- A new worksheet will be created in your workbook. Now you have space on the left for your PivotTable, your PivotChart in the middle and your PivotChart Field including the field names and areas are in the pane on the right.
Once you start adding fields to your PivotChart field selector into one or more of the four possible areas (Filters, Columns, Rows, Values), your sheet will start filling in your currently blank PivotTable range and PivotChart object with summary data. Building your PivotChart works just as building your PivotTable did. Review the “Manipulating Pivot Table Fields” section in Chapter 6.1 if you need a refresher about how to add fields.
Exercises
Create a PivotChart using State Abbreviations as Row Labels, the Count of Institution Names as values in a corresponding column. Your output should match Figure 6.3.4 below.
Answer the following questions:
- Is the chart easier to read than the data table?
- Is it clear from the chart which column corresponds with which state? Adjust the width of the chart to see the state names under each column.
- What type of chart is this? Click PivotChart Tools > Design > Change chart type that you have a clustered column chart.
- Are there any other chart types that would allow you to visualize this data better?
Sort and Filter a PivotChart
It can be hard to pick a good chart type even if you have summarized records for over seven thousand institutions by fifty categories in our case with the College Scorecard Data and the State Abbreviations. However, we can always sort our data to show items in a diff