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 we can 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, I'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 I'd like to create a chart that shows how much revenue was earned each quarter last year by subscription type. To do this, I'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 sales person and subscription type.
I'll also remove payment date from the legend field and put subscription type here to split the revenue by large, medium, and small. And now after some manipulation, we have the chart we want. If you have quite a complex pivot table arrangement, with various number of formats, fields, and grouping, it's unlikely that 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 you have your data set formatted as a table, you can simple go to insert and select pivot chart.
This will automatically select the data in your table. I'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 access fields, we'll add subscription type to the legend fields, and we'll add revenue as the values.
And as you can see, we've recreated the chart we did earlier on the pivot table, but we've 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 X Y scatter bubble and stock charts cannot be created in this way. This is also the case for charts created directly from pivot tables which we did earlier. If you're keen to build charts from pivot tables, I recommend checking out pivot charts first, because they normally take less time to build, and leave your initial pivot table format in place.