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 okay. 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 okay, 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 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'll head over 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. 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 okay 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. Very first one that's offered I'll click okay.
I'll take this chart, move it over to the side so we're not blocking the chart. I mean 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 overlapping 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. Okay 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 affects the other. So that is working with pivot charts in Microsoft Excel.