Skip to main content
Garfield Stinvil/4 min read

Creating Dynamic Visuals with Pivot Charts in Excel

Pivot Chart Workflow

0/5
Master Excel at Noble Desktop

Noble Desktop's Excel Bootcamp covers formulas, pivot tables, data analysis, and VBA.

This lesson is a preview from our Excel Bootcamp Online (includes software) and Excel Expert Certification Online (includes software & exam). Enroll in a course for detailed lessons, live instructor support, and project-based training.

Pivot Charts. Pivot Charts are dynamic graphical representations of Pivot Table data that work in tandem with pivot tables.

Pivot Charts. Pivot Charts are dynamic graphical representations of Pivot Table data that work in tandem with pivot tables. We're going to create a Pivot Chart, but before we do, we're going to create something that looks like a Pivot Chart using conditional formatting.

So this is our table of information that we would like to convert to a Pivot Table. We recommend that you convert the data to a table before creating a Pivot Table. A simple shortcut to do that is CTRL + T. I’ll press ENTER, and now I’ve created the table.

I have a new Table Design tab. It might be worded differently depending on the version of Excel you have. Mine is called Table Design.

I’ll move over to the Tools group and click Summarize with Pivot Table. I’m going to create that Pivot Table in this existing worksheet. So I’m going to choose that second option from choose where you want the Pivot Table report to be placed.

I’ll click in the Location input box and then click on the gray cell. This adds the location of that cell into the location area, and then I can click OK. Now the Pivot Table is in the shaded cell in my worksheet, and now I can start building the Pivot Table.

I’d like to bring in region first and then city, and then I’ll bring in revenue twice. So I can click the check box and revenue will go there once, and then I can drag it and bring it in there a second time. Now I can add some formatting to this if I’d like.

Maybe I’ll right-click here and choose Number Format Currency. I’ll remove the decimals, click OK, then I’ll call this Total Sales, and I’ll call this Data Bars because we are going to use data bars to visually represent the values we have in a Total Sales column. So what we’ll do next is apply conditional formatting by highlighting the amount, the total for each region first.

So I’ll start with East. I’ll now press the Control key and click on the Midwest amount, the South amount, and the West amount. Then I’ll head over to the Home tab, choose Conditional Formatting, Data Bars, and I’ll choose Solid Fill.

For my particular conditional formatting, I’ll choose Red as the initial color. Now I’ll go and select the values for the cities. I’ll select the first three, press Control, select the next three, next three, and finally the last three.

I’ll head over to Conditional Formatting, Data Bars, I’ll choose a Contrasting Color. This time I’ll choose Green. So far, looking good.

I can see the data has been charted sort of using data bars and we’re in a way creating a bar chart inside of the Pivot Table. The one thing that we could do to improve this is remove the dollar amounts because we already have that in the Total Sales column. We can do that by selecting the data.

We’ll go to Conditional Formatting, choose Manage Rules, and I’m going to select each Data Bar and choose the option to Show Bar Only. I’ll click the final OK and there we have something that looks like a chart, but it’s not really a chart. Now it’s actually much easier to create a Pivot Chart.

All you need to do is click anywhere within the Pivot Table, go to the Pivot Table Analyze tab, and over in the Tools group, you’ll see Pivot Chart. I’ll click on it and I’m going to create a Clustered Column Chart. The very first one that’s offered, I’ll click OK.

I’ll take this chart, move it over to the side so we’re not blocking the Pivot Table, and now we’ll make some adjustments. Now you’re getting this two-tone color because we used data bars and we applied two different types of data bars, so we can make this all one color by right-clicking and then heading over to Format Data Series.

I’ll go to Series Overlap and I’ll drag that over to a hundred percent so the series overlap each other. If I want to change the color, I can click on the Paint Bucket, go to Fill, and see the orange color that’s there and then change it to something else, like maybe Blue. OK. I’ll close the Format Series dialog box, and there we have our Pivot Chart.

Now if I make any changes to the Pivot Table, it will automatically reflect in the chart, and then if I make any changes in the Pivot Chart, it’ll automatically reflect in the Pivot Table, so they are connected together, and any changes you make on one affect the other. So that is working with Pivot Charts in Microsoft Excel.