Today in Episode #1302 Bill shows us how to create a chart for each customer in our database. Using a Pivot Table to manipulate the data, some formatting and the "Show Report Filter Pages" option our task becomes simplified.
From Chapter 8...This episode is the video podcast companion to the book, "Charts And Graphs: Microsoft Excel 2010", by Bill Jelen a.k.a. MrExcel.
For all of your Microsoft Excel needs visit MrExcel.com - Your One Stop for Excel Tips and Solutions.
From Chapter 8...This episode is the video podcast companion to the book, "Charts And Graphs: Microsoft Excel 2010", by Bill Jelen a.k.a. MrExcel.
For all of your Microsoft Excel needs visit MrExcel.com - Your One Stop for Excel Tips and Solutions.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel Charts, Chapter Eight: Creating A Chart For Every Customer.
Oh hey, welcome back to the MrExcel netcast. I am Bill Jelen.
This podcast is going to 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 Pivot Table. So, insert Pivot Table, click ok. Let's see I want to create a chart showing Revenue.
Definitely, buy product and 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 Pivot Table to look good. Grand totals, we'll 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 a 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 Options under Calculations will say show values as a percentage of the column total.
Now, we can see everyone's percentage, 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're going to do that. I'll show you a great trick here we're going to go to the Pivot Table > Tools > Options, 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" All right 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, be really great of sure Report Filter Pages work 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, each of these items and here's how I'm going to do that. I'm going to choose the first item and then Shift-click on the last item.
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, okay, right click and choose Line Group Sheets All right 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. It's the default chart on this particular computer. Let's change that. Design > Change Chart Type. We'll go with a Pie Chart.
Click OK. Maybe, customize things a little bit more. We'll get rid of that... Legend off, Data Labels > More Data Label Options, will show Category Name and percentage Click close and all right! So, now that chart is a pretty close to what I want. I actually, don't here want the field buttons, 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.
Go find that template. Let's see right there, Chapter8. You can even 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, to go to the next sheet it's 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 favorite chart type would be.
Well Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Excel Charts, Chapter Eight: Creating A Chart For Every Customer.
Oh hey, welcome back to the MrExcel netcast. I am Bill Jelen.
This podcast is going to 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 Pivot Table. So, insert Pivot Table, click ok. Let's see I want to create a chart showing Revenue.
Definitely, buy product and 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 Pivot Table to look good. Grand totals, we'll 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 a 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 Options under Calculations will say show values as a percentage of the column total.
Now, we can see everyone's percentage, 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're going to do that. I'll show you a great trick here we're going to go to the Pivot Table > Tools > Options, 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" All right 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, be really great of sure Report Filter Pages work 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, each of these items and here's how I'm going to do that. I'm going to choose the first item and then Shift-click on the last item.
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, okay, right click and choose Line Group Sheets All right 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. It's the default chart on this particular computer. Let's change that. Design > Change Chart Type. We'll go with a Pie Chart.
Click OK. Maybe, customize things a little bit more. We'll get rid of that... Legend off, Data Labels > More Data Label Options, will show Category Name and percentage Click close and all right! So, now that chart is a pretty close to what I want. I actually, don't here want the field buttons, 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.
Go find that template. Let's see right there, Chapter8. You can even 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, to go to the next sheet it's 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 favorite chart type would be.
Well Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
