11. Build Charts from a Pivot Table

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

Charts can be extracted directly from a Pivot Table, but a better option is to create Pivot Chart directly from the dataset.

Lesson Notes

Create chart directly from Pivot Table

1 Select a cell in the Pivot Table and insert chart
2 Change legend and axis fields to create desired output
3 Format the chart to suit your needs

Create Pivot Chart

- Often the better option because original Pivot Table field settings are not changed
1 Select a cell on the original dataset and insert Pivot Table -> Pivot Chart
2 Change legend and axis fields to create desired output
3 Format the chart to suit your needs

Transcript

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 numbers 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 simply 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 XY Scatter, Bubble and Stock charts cannot be created in this way.

This is also the case with 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.