Learn how to quickly transform your Excel data into insightful visualizations using pivot tables and dynamic pivot charts. Enhance your data analysis skills by mastering conditional formatting and interactive chart features.
Key Insights
- Create pivot charts in Excel by first converting data into a table (shortcut: Ctrl T), placing your pivot table into an existing worksheet, and then selecting specific fields such as region, city, and revenue.
- Use conditional formatting with data bars to visually represent values within a pivot table, enhancing readability and highlighting important data trends.
- Pivot charts and pivot tables in Excel remain dynamically connected; any updates made to one automatically reflect in the other, enabling seamless data analysis.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
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.