Pivot Charts in Excel

Pivot Tables and Pivot Charts are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or online Excel classes.

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. 

Create a Pivot Table

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 Control 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 PivotTable. 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 PivotTable 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. 

So now that pivot table is in the shaded cell in my worksheet, 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 checkbox 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 the 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 the East, and then I'll press on 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'd go to Conditional Formatting, Data Bars. I'll choose a contrasting color. This time I'll choose green. 

So far, looking pretty 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. So 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. 

Create a Pivot 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 PivotTable 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, very first one that's offered. I'll click OK. I'll take this chart and 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 100 percent. So the series have overlapped 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 pivot 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 affects the other. 

Recap 

So that is working with pivot charts in Microsoft Excel.