Sign in or start a free trial to avail of this feature.
11. Build Charts from a Pivot Table
Charts can be extracted directly from a Pivot Table, but a better option is to create Pivot Chart directly from the dataset.
Creating a Chart from a Pivot Table (00:04)
It’s possible to create a chart directly from a Pivot Table. To do this, you select a cell in the Pivot Table, then insert the chart you want using the Insert tab. Doing this inserts a chart that is based on the row and column labels in the Pivot Table, which might not be what you want.
If you change the fields in the chart, then the Pivot Table will automatically adjust. Here, we modify our chart to show revenue by subscription type and quarter.
Creating a Pivot Chart (01:02)
If you have a complex Pivot Table, you may not want to disrupt it to create a chart. In this case, you should use a Pivot Chart. This accesses fields directly from the data set, so you can create a chart without disrupting a Pivot Table.
As with Pivot Tables, Pivot Charts should be created from a data set that is formatted as an Excel table. To create the Pivot Chart, select a cell in the data set, and press Alt, N, S, Z, or select Pivot Chart from the Insert tab of the ribbon.
You can then build up the Pivot Chart by dragging fields from the field list to the various areas, like with a Pivot Table. For example, to create our chart of revenue by subscription type and quarter, we add quarters to the axis field, subscription type to the legend field, and revenue to the values field.
Pivot Charts provide many formatting options. For example, you can sort and filter data by clicking the field buttons on the chart, or you can add slicers to the chart.
In the previous lesson, We learned how to use slicers to filter a pivot table. In this lesson, we'll learn how to build charts from a pivot table. There are two ways of creating charts related to a pivot table. The first way is to simply select a cell on the pivot table, go to the ribbon and insert a chart.
In this case, we'll select a column chart.
This command automatically creates a chart based on the values we currently have as row and column labels.
Let's say we'd like to create a chart that shows how much revenue was earned in each quarter last year by subscription type.
To do this, we'll have to move and delete a number of fields from our pivot table. Let's start off by moving quarters to the access field and removing both sales person and subscription type.
We'll also remove payment date from the legend field and put subscription type here to split the revenue by large, medium, and small. Now after some manipulation, we have the chart we want. If you have a complex pivot table arrangement with various numbers, formats, fields, and grouping, it's unlikely you'll want to disrupt this structure every time you want to create a chart. Thankfully, Excel provides us with a remedy to this problem called a pivot chart. Pivot charts access fields directly from the data set and allow you to pivot fields to create charts without changing your existing pivot table.
If your data set is formatted as a table, you can simply go to insert and select pivot chart.
This will automatically select the data in your table. We'll put our pivot chart on a new worksheet and press OK.
This creates a new Excel worksheet where we can create our pivot chart.
As we did previously, we'll add quarters to the axis field, subscription type to the legend field, and revenue as values.
As we can see, we've recreated the chart we made earlier on the pivot table, but done it much more quickly and without altering the existing pivot table.
Pivot charts provide you with quite a lot of formatting and analysis options to choose from. For example, you can sort and filter plotted data by simply clicking on these field buttons on the chart. You can even apply slicers to this data by going to the analysis tab and clicking on insert slicer. Unfortunately, pivot charts do come with some limitations. The XY scatter, bubble, and stock charts can not be created in this way. This is also the case with charts created directly from pivot tables, like the one we made earlier. If you're interested in building charts from pivot tables, I recommend checking out pivot charts first, as they normally take less time to build and leave your initial pivot table format in place.