Excel Pivot Charts: Effortlessly Summarize Your Data Visually

I’ve been diving into the world of data visualization in Excel, and let me tell you, it’s a game-changer. Visuals can turn rows upon rows of data into a clear and compelling story, which is exactly why pivot charts have caught my attention. These nifty charts are perfect for anyone looking to step up their data game and deliver insights that make an impact.

What sets pivot charts apart is their dynamic nature. Imagine you’re sitting on a mountain of sales data – a pivot chart lets you sift through those numbers to present trends, patterns, and anomalies that could otherwise be lost in the sea of information. Whether you’re focusing on total sales by region or the performance of a specific product, a pivot chart will help you tailor your data story to your audience, all while rocking that report like a boss.

Difference Between a Pivot Chart and a Normal Chart

Data Source:
- Normal Chart: Uses a range of cells.
- Pivot Chart: Based on pivot table summary.
Dynamics:
- Normal Chart: Convert to dynamic manually.
- Pivot Chart: Dynamic by default.
Chart Types:
- Most types accessible except xy (scatter), stock, or bubble charts for Pivot Charts.

Steps to Create a Pivot Chart in Excel

1. Create a Pivot Chart from Scratch

I find that creating a pivot chart from scratch is just as straightforward as setting up a pivot table. First, I make sure I have my datasheet ready. Then, here are the steps I usually take, which work in Excel versions from 2007 through 2016:

I click on any cell within my data sheet, head over to the Insert Tab, move to Charts, and click on Pivot Chart.¹
Next, a window pops up that automatically selects the entire data range for me. It’s pretty handy since I can then decide whether to place my pivot chart in a new worksheet or with my existing data.²
After I hit OK, I get a new worksheet served up with a blank pivot table and a corresponding pivot chart.³

Note: When I insert a pivot chart, Excel is kind enough to throw in a pivot table alongside it. And if I just need a pivot chart, I can always add my data straight into the Power Pivot Data Model.

In terms of components, the pivot chart fields mirror the pivot table, consisting of:

Axis (Categories): These correspond to the rows in the pivot table.
Legend (Series): This is similar to the columns in the pivot table.
Values: This is where I plot the actual data points, like quantities.
Report Filter: I use this to filter specific data in the pivot chart.

And just like that, I’ve got myself a fully dynamic pivot chart that I can tweak to my heart’s content.⁴

2. Create a Pivot Chart from the Existing Pivot Table

When I already have a pivot table in my worksheet and I need to add a pivot chart, I just follow a couple of simple steps:

I select any cell within my existing pivot table.⁵
Then, I go to the Insert Tab, pick Charts, and choose the specific type of pivot chart I want to use.⁶
Clicking OK gets me a shiny new pivot chart right there in the same worksheet, using the pivot table configurations for the axis and legends.

One cool trick I’ve learned is that if I’m looking for speed, I can just hit the F11 shortcut key after selecting any cell in my pivot table, and boom—a new pivot chart appears instantly.

More Information about Pivot Charts

1. Change Chart Type

I often enjoy tweaking my pivot charts to better represent my data. To switch up the chart type, I follow these steps:

I select the pivot chart and head over to the Design Tab, find the Type group, and click on Change Chart Type.
Then I pick the new style that best fits my summary data. Excel offers a range of chart types including column, line, pie, bar, area, and more.

By changing the chart type, I can emphasize different aspects of my data, like trends or comparisons.

2. Refresh a Pivot Chart

Keeping my pivot chart updated with the latest data is crucial. Here’s how I make sure it reflects the most current information:

If my underlying pivot table auto-refreshes, my pivot chart does too. They’re connected.
To manually refresh, I go to the PivotTable Tools Options tab and click on Refresh, or I can use the following VBA code to refresh all pivot tables and charts:

Sub RefreshAllPivotItems()
    Dim PC As PivotCache
    For Each PC In ActiveWorkbook.PivotCaches
        PC.Refresh
    Next PC
End Sub

This ensures my pivot chart is always showcasing the latest data.

3. Filter a Pivot Chart

Filtering a pivot chart brings focus to the exact data I’m interested in. Here’s how I apply filters:

I begin by right-clicking on the pivot chart and selecting “Show Field List.”
In the field list, I drag the fields I want to filter into the filter area.
Since pivot tables and pivot charts are linked, applying a filter to one automatically updates the other.

Filtering helps me highlight specific information without altering the source data.

4. Show Running Total in a Pivot Chart

Sometimes, I like to display a running total in my pivot charts to analyze progress over time. Here’s what I do:

First, I add my value field to the values area twice.
Then, in the second value’s settings, I swap to the “Show Value As” tab and pick “Running Total.”
After I’ve got my running total, I might need to tweak the primary axis to align with the secondary axis if I’m using one.

This running total provides a dynamic view of accumulating values.

5. Move a Pivot Chart to New Sheet

I like to give my pivot charts their own space sometimes. Here’s how I move them:

I click on the chart, right-click, and select “Move Chart.”
In the dialog, I choose to place my chart in a new chart sheet or another existing worksheet.
After confirming my selection, the chart relocates, but I can always move it back if needed.

This gives me flexibility in how I present my pivot charts within the workbook.

Extra Tips on Pivot Charts

1. Using a Slicer with a Pivot Chart to Filter

I love how slicers give a visual way to filter pivot chart data. Here’s how I do it:

First up, I click on my pivot chart and hit the Analyze Tab. Then I catch the Filter group and click on Insert Slicer.
Next, I pick the fields I want to use for my slicer, making sure to pick those that’ll help me slice the data just right.
Finally, I click OK, and there it is, my very own filter control right on my worksheet.

And the cool thing is, I can sync a single slicer with multiple pivot charts and tables — keeping everything consistent.

2. Insert a Timeline to Filter Dates in a Pivot Charts

Now, when I have dates to filter, I go for a timeline instead of a standard slicer. It’s this nifty slider that lets me zoom in on data by days, months, quarters, you name it.

So, I grab my pivot chart and dive into the Analyze Tab. From there, I select Filter and go for Insert Timeline.
Then, I choose the date field I want to filter on, check the box, and hit OK.

It’s a breeze and looks super slick on my worksheet.

3. Present Months in a Pivot Chart by Grouping Dates

Here’s a neat trick. If my data only has dates but I want a pivot chart by month, I don’t even need to add a month column in my data table.

I jump right into my pivot table, select a cell with a date, and give it a right-click. Then I choose the group option.
A window pops up, and I select ‘Months‘. Once I click OK, my dates are neatly bunched into months.

This way, my pivot chart gets all the monthly insights without extra columns in my data.