Building a pivot chart to summarize a data set
I start with a pivot table.
If you want years to go across the chart, make them go down the Rows area
Change Field Settings to Show Values as a Percentage of Row
Format the pivot table as % with no decimal places
Create the pivot chart
Immediately Cut, Ctrl+Home, Paste
Formatting the chart:
Adding a Title at the top
Triple click the title to edit in place
Select second series. Ctrl+1. Narrow the Gap Width.
Add Data Labels to only the second series
I start with a pivot table.
If you want years to go across the chart, make them go down the Rows area
Change Field Settings to Show Values as a Percentage of Row
Format the pivot table as % with no decimal places
Create the pivot chart
Immediately Cut, Ctrl+Home, Paste
Formatting the chart:
Adding a Title at the top
Triple click the title to edit in place
Select second series. Ctrl+1. Narrow the Gap Width.
Add Data Labels to only the second series
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2008 -- Pivot Charts Hey, I'm going to be podcasting this whole book, go ahead and subscribe to the MrExcel Excel playlist.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
This is one where the videos are going to go into a lot more detail than the book.
The book glossed over how to do something.
We're on the way to creating a dashboard in these next several podcasts but first let's talk about doing a Pivot Chart.
I have to admit I hardly ever use Pivot Charts.
They're great.
I don't know what my problem is.
So let's talk about building a Pivot Chart in detail and I'm still old school here.
I create the Pivot Table first and then from the Pivot Table create the chart.
So I'm going to insert a Pivot Table.
It's going to go to an existing worksheet.
I've already built a dashboard sheet here but I need it out of view on the dashboard sheet.
So here for the location I'm going to go to the dashboard and then down to P54 because everything above this and to the left is going to be the actual dashboard.
I need this stuff to be out of view when we look at our dashboard.
Alright, now, what I'm envisioning here is a chart, with years going across and I want to compare ebooks to print books.
So, because I want the years to go across in the Pivot Table they have to go down.
It's completely backwards.
So I take the years and move it down this area and then the product line is going to go across and we'll put value here and this is showing absolute dollars but what I want to do is I want to show how the ebooks are growing as a percentage of the total.
So I'm going to choose one of these cells and go into field settings, some count average, max, min.
There's stuff here on the first tab but the good stuff is on the second tab.
Show Values As and I want it as a percentage of the Row Total, alright?
That way 80 plus 20 is going to be a hundred percent going across.
Most of the time I'm doing Percentage of Column Total because I want to show you know how things add up to a hundred percent here but again it's kind of backwards because I'm building the Pivot Table a little bit backwards.
Alright, now I don't want all those percentages.
I want to change this to a percentage with zero decimal places.
Perfect.
I don't really need the Grand Total, and it I don't really need to remove it, but I'm going to just that way in case someone happens to see the Pivot Table down here to look good and in the same vein report layout show in tabular form to make it look better.
Alright now from here we're going to create a Pivot Chart.
So I make sure I'm inside the Pivot Table, click Pivot Chart and I want it to be a stacked column chart, that adds up to one hundred percent, like that.
I click ok I'm going to put it right here in the middle of the screen, which is not in the middle of my dashboard, so I Ctrl x to get rid of it.
Ctrl home, Ctrl v to paste and now I have the chart up in the area where the dashboard is going to go.
Now from here, sort of, just some normal formatting.
Alright so we're going to take the legend and we remove the legend to the top.
I am going to add a chart title.
To edit the chart title, I triple click and this will be E Book Sales as a% of Total.
Alright and so I'm really focused here on just that blue.
The light blue, the light blue so I select that series and I want to add Data Labels to those series, but I know that I'm going to need more space for the data labels because I have two digits and a percent.
So the first thing I'm going to do is press Ctrl one, to go into format cells and there's a setting here called Gap Width and I'm going to make the Gap Width narrower, which makes my columns nice and big and fat.
Alright good, so now from here I have just that Second Series selected.
I'll add Data Labels.
See, now I get Data Labels just on the top series.
This looks good zero to one hundred percent.
Sometimes you end up with extra decimal places there and you have to double click to go in but in this case it all worked fine.
Now the only weird thing is we have some a value Line and Year and again because I'm not a Pivot Table, Pivot Chart connoisseur I don't use them all the time.
I, these just bother me.
I want to get them out of here.
So on the Analyze Tab, under Field Buttons I say hide to all.
I don't know, I'm sure in the YouTube comments I'm going to hear from someone who says oh no those are the greatest things.
You should feel free to let me know.
School me on this on why I should like them but right now I just simply don't like them.
I want to get rid of them.
Alright so, what we have now is we have a Pivot Chart and this Pivot Chart is tied to a Pivot Table that's down out of view.
Nice little summary report and where we're headed in this series of videos is we're going to create some more Pivot Tables and some more Pivot Charts and then tie them all to a set of Slicers.
Let's do that tomorrow.
Great way to impress your boss.
Buy this book.
Forty great tips plus some bonus tips.
Twenty five bucks.
Alright recap of this episode we're going to build a Pivot Chart to summarize the data set.
I always start with a Pivot Table when you want the years to go across the chart make them go down the rows area,it's backwards.
Change the field settings to show values as a percentage of the row instead of percentage of column.
Format as percentage with no decimal places.
Then choose one Pivot Table click Pivot Chart.
I immediately cut, Ctrl Home, paste, move that up into the dashboard.
We added a title at the top.
Triple click the title to edit in-place.
Select that second series, Ctrl one, to narrow the gap with, and then add data labels to only the second series.
Hey, I want to thank you for stopping by.
Oh we'll see you next time for another netcast from MrExcel.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
This is one where the videos are going to go into a lot more detail than the book.
The book glossed over how to do something.
We're on the way to creating a dashboard in these next several podcasts but first let's talk about doing a Pivot Chart.
I have to admit I hardly ever use Pivot Charts.
They're great.
I don't know what my problem is.
So let's talk about building a Pivot Chart in detail and I'm still old school here.
I create the Pivot Table first and then from the Pivot Table create the chart.
So I'm going to insert a Pivot Table.
It's going to go to an existing worksheet.
I've already built a dashboard sheet here but I need it out of view on the dashboard sheet.
So here for the location I'm going to go to the dashboard and then down to P54 because everything above this and to the left is going to be the actual dashboard.
I need this stuff to be out of view when we look at our dashboard.
Alright, now, what I'm envisioning here is a chart, with years going across and I want to compare ebooks to print books.
So, because I want the years to go across in the Pivot Table they have to go down.
It's completely backwards.
So I take the years and move it down this area and then the product line is going to go across and we'll put value here and this is showing absolute dollars but what I want to do is I want to show how the ebooks are growing as a percentage of the total.
So I'm going to choose one of these cells and go into field settings, some count average, max, min.
There's stuff here on the first tab but the good stuff is on the second tab.
Show Values As and I want it as a percentage of the Row Total, alright?
That way 80 plus 20 is going to be a hundred percent going across.
Most of the time I'm doing Percentage of Column Total because I want to show you know how things add up to a hundred percent here but again it's kind of backwards because I'm building the Pivot Table a little bit backwards.
Alright, now I don't want all those percentages.
I want to change this to a percentage with zero decimal places.
Perfect.
I don't really need the Grand Total, and it I don't really need to remove it, but I'm going to just that way in case someone happens to see the Pivot Table down here to look good and in the same vein report layout show in tabular form to make it look better.
Alright now from here we're going to create a Pivot Chart.
So I make sure I'm inside the Pivot Table, click Pivot Chart and I want it to be a stacked column chart, that adds up to one hundred percent, like that.
I click ok I'm going to put it right here in the middle of the screen, which is not in the middle of my dashboard, so I Ctrl x to get rid of it.
Ctrl home, Ctrl v to paste and now I have the chart up in the area where the dashboard is going to go.
Now from here, sort of, just some normal formatting.
Alright so we're going to take the legend and we remove the legend to the top.
I am going to add a chart title.
To edit the chart title, I triple click and this will be E Book Sales as a% of Total.
Alright and so I'm really focused here on just that blue.
The light blue, the light blue so I select that series and I want to add Data Labels to those series, but I know that I'm going to need more space for the data labels because I have two digits and a percent.
So the first thing I'm going to do is press Ctrl one, to go into format cells and there's a setting here called Gap Width and I'm going to make the Gap Width narrower, which makes my columns nice and big and fat.
Alright good, so now from here I have just that Second Series selected.
I'll add Data Labels.
See, now I get Data Labels just on the top series.
This looks good zero to one hundred percent.
Sometimes you end up with extra decimal places there and you have to double click to go in but in this case it all worked fine.
Now the only weird thing is we have some a value Line and Year and again because I'm not a Pivot Table, Pivot Chart connoisseur I don't use them all the time.
I, these just bother me.
I want to get them out of here.
So on the Analyze Tab, under Field Buttons I say hide to all.
I don't know, I'm sure in the YouTube comments I'm going to hear from someone who says oh no those are the greatest things.
You should feel free to let me know.
School me on this on why I should like them but right now I just simply don't like them.
I want to get rid of them.
Alright so, what we have now is we have a Pivot Chart and this Pivot Chart is tied to a Pivot Table that's down out of view.
Nice little summary report and where we're headed in this series of videos is we're going to create some more Pivot Tables and some more Pivot Charts and then tie them all to a set of Slicers.
Let's do that tomorrow.
Great way to impress your boss.
Buy this book.
Forty great tips plus some bonus tips.
Twenty five bucks.
Alright recap of this episode we're going to build a Pivot Chart to summarize the data set.
I always start with a Pivot Table when you want the years to go across the chart make them go down the rows area,it's backwards.
Change the field settings to show values as a percentage of the row instead of percentage of column.
Format as percentage with no decimal places.
Then choose one Pivot Table click Pivot Chart.
I immediately cut, Ctrl Home, paste, move that up into the dashboard.
We added a title at the top.
Triple click the title to edit in-place.
Select that second series, Ctrl one, to narrow the gap with, and then add data labels to only the second series.
Hey, I want to thank you for stopping by.
Oh we'll see you next time for another netcast from MrExcel.
