Create a chart for every customer.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Excel Charts Chapter 8: Creating a chart for every Customer.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
This podcast is going be a little bit longer than usual but it's a cool, cool trick.
We want to create a chart for every single customer in this database.
We're going to do that using a PivotTable, so insert PivotTable and click OK.
Let's see, I want to create a chart showing revenue, definitely, by product and I want to see that for every single customer.
So I'm going to take the customer field up into the report filter.
Now, you'll see why later--we're going to do that.
Let's do a little bit of formatting here to get the PivotTable to look good.
Grand Totals.
We're going to turn those off.
Report layout; we're going to go back to tabular form that gets rid of the word row labels and replaces it with the word product.
Here for revenue we're going to do space ‘Revenue’ instead of ‘Sum of revenue’.
I think that just sounds better.
Notice you can't type Revenue you have to type space Revenue, it has to be different than it feels in the table and then here I want to format these revenue amounts.
Actually you know what; let's show them as percentages.
So we'll come here to one of the revenue amounts and click Options then under Calculations we will say ‘Show Values As’ a percentage of the column total.
Now we can see everyone's percentage of the total.
All right, so now we have this using the filter we could very slowly go through and show each customer one at a time but that’s not what we are going to do.
I'll show you is a great trick here we're going to go to the PivotTable tools and choose Options and on the left hand side there's a button called Options.
I don't want you to choose that button I want you to choose the drop down next to the button something called ‘Show Report Filter Pages’ and because I have customer up in the Report Filter field it offers it here in this dialog now watch this sheet 2 when I click OK.
Watch what happens, All right did you see that?
They just inserted several new worksheets; one for every customer.
So we can scroll through here.
So for every customer in the database they created a brand new worksheet.
Now it would be really great if Show Report Filter Pages worked with pivot charts but unfortunately it doesn't.
So what we have to do is now add a pivot chart for each of these items.
Here's how I'm going to do that.
I'm going to choose the first item and then Shift-click on the last time.
So I've chosen all of those items and I want to make sure to choose a single cell on each of those sheets.
Now that I've done that I can right click and choose Line Group Sheets and then we want to create our chart.
Now if I just press Alt+F1 I'm going to get-- it looks like a column chart is the default chart on this particular computer.
Let's change that; click Design, change chart type and we'll go with a pie chart, click OK.
Maybe customize things a little bit more, we'll get rid of the legend—off.
Data labels, more data label options will show category name and percentage, close.
All right, so now that chart is a pretty close to what I want.
I actually don't even want those field buttons so we will turn those off.
Hide All and there we go, okay.
Now that I have this chart watch this next step.
We're going to go into Save As Template and we'll just call it “Chapter8.” Good.
Change chart type and go find that template; let's see right there Chapter8.
You can hover to see the name so Chapter8 and then set as default chart All right, so now I've created my own custom chart type.
I've set that as the default and now it's easy.
I just go to each sheet and press Alt+ F1 next sheet, Alt+ F1 next sheet, Alt+ F1 next sheet.
To go to the next sheet is Ctrl+Page Down.
So I'm just going to go through and create these charts one at a time using Alt+F1.
The trick there though is we set up a template and taught Excel what our favourite chart type would be.
Hey I want to thank you for stopping by see you next time for another netcast from MrExcel.
Excel Charts Chapter 8: Creating a chart for every Customer.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
This podcast is going be a little bit longer than usual but it's a cool, cool trick.
We want to create a chart for every single customer in this database.
We're going to do that using a PivotTable, so insert PivotTable and click OK.
Let's see, I want to create a chart showing revenue, definitely, by product and I want to see that for every single customer.
So I'm going to take the customer field up into the report filter.
Now, you'll see why later--we're going to do that.
Let's do a little bit of formatting here to get the PivotTable to look good.
Grand Totals.
We're going to turn those off.
Report layout; we're going to go back to tabular form that gets rid of the word row labels and replaces it with the word product.
Here for revenue we're going to do space ‘Revenue’ instead of ‘Sum of revenue’.
I think that just sounds better.
Notice you can't type Revenue you have to type space Revenue, it has to be different than it feels in the table and then here I want to format these revenue amounts.
Actually you know what; let's show them as percentages.
So we'll come here to one of the revenue amounts and click Options then under Calculations we will say ‘Show Values As’ a percentage of the column total.
Now we can see everyone's percentage of the total.
All right, so now we have this using the filter we could very slowly go through and show each customer one at a time but that’s not what we are going to do.
I'll show you is a great trick here we're going to go to the PivotTable tools and choose Options and on the left hand side there's a button called Options.
I don't want you to choose that button I want you to choose the drop down next to the button something called ‘Show Report Filter Pages’ and because I have customer up in the Report Filter field it offers it here in this dialog now watch this sheet 2 when I click OK.
Watch what happens, All right did you see that?
They just inserted several new worksheets; one for every customer.
So we can scroll through here.
So for every customer in the database they created a brand new worksheet.
Now it would be really great if Show Report Filter Pages worked with pivot charts but unfortunately it doesn't.
So what we have to do is now add a pivot chart for each of these items.
Here's how I'm going to do that.
I'm going to choose the first item and then Shift-click on the last time.
So I've chosen all of those items and I want to make sure to choose a single cell on each of those sheets.
Now that I've done that I can right click and choose Line Group Sheets and then we want to create our chart.
Now if I just press Alt+F1 I'm going to get-- it looks like a column chart is the default chart on this particular computer.
Let's change that; click Design, change chart type and we'll go with a pie chart, click OK.
Maybe customize things a little bit more, we'll get rid of the legend—off.
Data labels, more data label options will show category name and percentage, close.
All right, so now that chart is a pretty close to what I want.
I actually don't even want those field buttons so we will turn those off.
Hide All and there we go, okay.
Now that I have this chart watch this next step.
We're going to go into Save As Template and we'll just call it “Chapter8.” Good.
Change chart type and go find that template; let's see right there Chapter8.
You can hover to see the name so Chapter8 and then set as default chart All right, so now I've created my own custom chart type.
I've set that as the default and now it's easy.
I just go to each sheet and press Alt+ F1 next sheet, Alt+ F1 next sheet, Alt+ F1 next sheet.
To go to the next sheet is Ctrl+Page Down.
So I'm just going to go through and create these charts one at a time using Alt+F1.
The trick there though is we set up a template and taught Excel what our favourite chart type would be.
Hey I want to thank you for stopping by see you next time for another netcast from MrExcel.
