Stephen wants a bar of pie chart and a slicer. If you choose the west region, the column chart will show the states that make up that region, and the remaining regions will appear as regions in the main pie. This episode uses many rarities, including GetPivotData, Remove Duplicates, Slicers, ISNA, Bar of Pie Charts, a tiny bit of VBA, and an example where Pivot Table Compact Layout is actually useful!
For more of a write-up, see the corresponding blog post at http://www.mrexcel.com/learnexcel/2014/02/11/using-slicers-to-zoom-in/
For more of a write-up, see the corresponding blog post at http://www.mrexcel.com/learnexcel/2014/02/11/using-slicers-to-zoom-in/
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL Learn Excel from MrExcel Podcast Episode 1857: Slicer controls the bar of Pie charts-second chart.
Hey, welcome back to the Mr.Excel netcast.
I'm Bill Jelen.
We have a doozy today with rarities Rarities, a pivot table that's not so rare.
but remove duplicates.
A disconnected slicer ISNA.
A bar of pie chart and is the Crazy one.
I'm going to embrace the pivot table compact layout.
That's a sentence. I've never said before in my life.
Alright, so today's question sending by Steven p.
I have a data set and he thinks the best way to display it is in using bar of pie.
The Data is by State and region.
What he wants to do is have the exploded view of the pie.
This piece over here be dynamic based on slicer selection.
So, in the slicer we're going to have the region and whichever region is selected will be exploded in the states and the other regions will stay as regions.
Isn't that crazy when originally I told Stephen I'd be able to do this, I had misread his question.
Then who said a question. That was a lot simpler than what it actually is.
But see it's working.
So, I choose West we get California and Washington, and then all the other regions are showing up as regions.
We choose central.
We get the states in Central, Illinois and Michigan and the other regions are showing up this way.
All right!
So let's walk through this.
It's a lot of steps. I'm going to apologize in Advance Insert Here's our original data has region and state.
Insert > Pivot Table and we're going to build that Pivot Table and an existing location.
I'm going to put it right here to the right of my Data, Click OK.
Alright, from the Pivot Table field list I'll choose Region, State and quantity.
So, there is all the data that will eventually drive our pivot table.
And look they put Region and State in the same column.
Usually I hate that.
But in this particular case, no worries.
I'm going to let it go over here in Column.
Oh!
I have a list of all the possible states and all of the possible regions.
And we're going to use get Pivot Data.
Usually Get Pivot Data drives as crazy.
But in this case, It's going to be a good thing.
Let's make sure you haven't turned it off.
You have to be inside the pivot table.
Go to Pivot Table Tools>options.
Open the options drop-down and make sure that Generate GetPivotData is checked.
Alright! So, it is in this case.
Come out here, and we're just going to type equal sign.
I'm going to go find the number for Illinois And we get a Get Pivot Data Formula.
Copy that formula down double click.
We're going to get the wrong answer all the way.
That's because get pivot data, as I've discussed before in the podcast is hard-coded to Illinois.
Well I'm going to change that.
We don't have to have region Central.
In these particular cases, I just need to say state, Illinois and instead of hard-coding Illinois, we're going to parameterize it.
So here I'm going to click on 02.
Alright, and so then as I copy that down to the other states.
I'm going to get the actual answer for each of the states.
Great right!
Now down here for the regions that form is going to be a little bit different.
I don't need to specify any particular states.
So, I'm going to get rid of everything after region central.
And again, just like I did with the state.
I'm going to take the hard-coded central and replace it with a cell value.
And copy that down.
Okay! so now, I have a pivot.
So, the original Data.
A pivot table summarizing that data.
And then a range that is eventually going to become the source data for the pie chart.
Ah! But I need to build the slicer.
And the slicer cannot be applied to this table here because if I chose central, it's going to hide all the other regions.
I need to keep all of this data in the pivot table.
So, I'm going to take region then state [ CTRL+SHIFT+down arrow ] [ CTRL+right arrow ] copy and we're going to move that over here.
So, it columns U and V.
[ CTRL+V ] and then on the data Tab, say remove duplicates.
Click ok.
So now, I have just one unique Occurrence of every region and state.
I'm going to build another pivot table from this data, So Insert > Pivot Table.
Where is going to go.
Existing worksheet, over here to the right.
Click ok.
And we're going to put region and state in the pivot table.
See it.
Just gives you a list of all the regions in state.
Seems kind of silly.
But that's going to be very helpful.
Now from this pivot table, we're going to insert a slicer.
And choose region.
Click ok Alright! So there are our Slicers for region and you'll see when I choose central, here from this slicer.
I see just the states and regions involved in Central.
And the Compact view which I usually hate, I usually say how horrible it is.
It is actually good because it puts all of those values right here in this column.
Alright! A couple of cleanup things.
I'm going to take the Slicer.
Wanna make it be four columns.
Make it be wide enough to hold those four columns.
And change the slicer height.
A little bit larger.
All right.
So here we go central West, North East, South east.
Now, All we have to do is figure out based on what is showing over here in this Pivot Table.
We have to figure out which of these items we want to show in the chart.
So, what Stephen had said was if we select Northeast, Any of the states that are in Northeast want to be in that separate pie chart.
So, here where I say equal MATCH.
Look for Illinois over here in Column X comma 0 because we want an exact match.
And when I copy that down to the States, we're going to see New Jersey and New York.
Actually come up with a value and everything else comes up with is error comes up with Na.
So, what I need to do is if we're getting a value here, I want to show one.
If I'm getting an NA, I want to show a zero.
So, we'll come back to this Formula.
equal if open parenthesis ISNA.
If we get an NA from there, then I want to zero.
Otherwise, I want to one And copy that down just far enough through the States.
Alright, so in this case, Northeast of selects selected we're going to show New Jersey at New York.
If I choose West, and we're going to show California and Washington.
Now, for the Region's it's actually kind of reversed.
We want to show the region in the opposite fashion, So, here I want to show Central North East and South East.
So, West is found and we're going to make that one and zero be reversed all right.
Finally, this column called where.
This is an important Column.
And for all of the states, we're going to hard-code a value of one.
And for all of the reasons, we're on a hard code a value of zero All right now, let's create our bar of pie chart.
So, we select the data area in quantity.
Inserts> open the pie, and there's either a bar of pie or a pie apply this trick would work with either 1.
I'm going to choose bar of pie bar apply.
This is a rare chart.
It's rare that I ever work with these.
So, let's do a couple of different things.
First, we're going to rid of the legend delete Layout data labels.
More data label options.
And I want to see the category name and percentage Click ok.
All right! So, now we have this is actually well It's it's really a mess when you get right down to it, And I think because we're zooming out to show these states, we probably want to make this part of the chart larger than the pie, So, I'm going to Right-Click there, Format data series.
And I'm going to say that the second plot size instead of being 75% is about one hundred fifty percent.
Which will make it larger than the pie okay.
And then the same trick we use yesterday with the Morpheus lander.
We are going to hide items in the chart by using the filter.
So, here in this little area here will select these cells.
I'll go to Data filter and in the show column, I only want to see the one.
So, only the things that are once all right.
So, now when we choose West, the California and Washington are showing up to States.
Everything else Central northeast and southeast is showing up as region but it's not showing up correctly here.
Now, we're going to use a macro for this but I want to show you how this is actually done.
I'm going to right click.
Format Data Series and say that we're to split the series not by position, but by Custom.
Custom and it says selected Data point to move between.
So, this is Northeast.
We want to have back here in the main part of the chart.
Select that data point and then I can change from the first plot to the second plot.
A little bit of UI there that I had never seen before.
I actually tried to solve Stephen's trick.
Alright! So, Here's what we have.
Any time that we update the pivot table, I want to have some code run and that code will go through reapply the filter.
And then look at each of the series that are visible here and depending on whether there's a one or a zero here, move the series of the correct place.
Now, it turns out in VBA.
In order to have it appear in the original chart it needs to be a value of zero.
That's why we hard-coded zeros for all the regions and to have it appear in the secondary chart.
It needs to have a one.
All right!
Let's do [ ALT+F11 ] and take a look at that code.
Over here in the project explorer, we need to find the sheet that we're on.
So that is the demo sheet.
From the top left drop-down there's only one choice.
Choose worksheet from the top right drop-down.
Choose Pivot table update and then inside of there, we're going to have this code.
Alright! So, the first one active sheet dot auto filter dot apply filter.
Reapplies the one that we've previously set and then I have a counter here.
I have to use a counter for each cell in R2 to R13, and yes that's hard coded to match where my where columns are.
but I take special cells.
Excel cell type visible.
That make sure that I only see the visible ones.
And we're going to do active sheet dark dot chart objects one dot chart dot series collection one.
All of that's gonna be the same.
But the points the points, and it's funny.
So, we see California, Washington, in Central Northeast and southeast.
In this chart, this is considered point one, point two, point three, point four, point five.
It sure would be nice if I could use the row number.
But I can't because Central which is 0 point three is actually over here in row 10.
So, I have to use the counter to keep track of which counter I'm setting.
And we do that using the secondary plot property.
and we just set that equal to cell dot value.
o either a 0 or a 1, something their plot and then increment the counter and next cell.
Alright! So, now that that is all working.
It's on the worksheet code pane.
Let's come back here and try it out.
We will choose South East.
All right and we get the states in Southeast, Florida and Georgia and then all of the other regions are showing up back in the main pie.
North East West and Central.
I had automatically updates.
So, when Steven asked for a pivot chart that would do this well it's not really a pivot chart at all.
It's just a regular chart and while we do have to pivot tables and a slicer.
The slicer is just over here It's what Rob Kali calls a disconnected slicer.
Where it's not based on the original data set at all because we need to have all of the answers showing up correctly in this real pivot table.
And Not being sliced by the slicers.
Now in this particular case, some of these rows are hidden because of the filter but the slicer is operating on this pivot table.
And then the macro is applying the filter to get us just the results of the show.
What a crazy set of steps.
But it does work.
I want to give credit to Steven for this idea.
I think it's a great idea.
Interesting way to go Too bad that because of the VBa we can't put it up on the Excel web app.
That would have been a cool use but unfortunately, It's just going to have to run here on the flat client.
Not on the in the web app.
I hey I want to thank Steven for sending that Crazy question in.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
These rarities that pivot table.
That's not so rare, but remove duplicates.
A disconnected slicer, ISNA A Bar of pie chart and the crazy one I'm going to embrace the pivot table Com
Hey, welcome back to the Mr.Excel netcast.
I'm Bill Jelen.
We have a doozy today with rarities Rarities, a pivot table that's not so rare.
but remove duplicates.
A disconnected slicer ISNA.
A bar of pie chart and is the Crazy one.
I'm going to embrace the pivot table compact layout.
That's a sentence. I've never said before in my life.
Alright, so today's question sending by Steven p.
I have a data set and he thinks the best way to display it is in using bar of pie.
The Data is by State and region.
What he wants to do is have the exploded view of the pie.
This piece over here be dynamic based on slicer selection.
So, in the slicer we're going to have the region and whichever region is selected will be exploded in the states and the other regions will stay as regions.
Isn't that crazy when originally I told Stephen I'd be able to do this, I had misread his question.
Then who said a question. That was a lot simpler than what it actually is.
But see it's working.
So, I choose West we get California and Washington, and then all the other regions are showing up as regions.
We choose central.
We get the states in Central, Illinois and Michigan and the other regions are showing up this way.
All right!
So let's walk through this.
It's a lot of steps. I'm going to apologize in Advance Insert Here's our original data has region and state.
Insert > Pivot Table and we're going to build that Pivot Table and an existing location.
I'm going to put it right here to the right of my Data, Click OK.
Alright, from the Pivot Table field list I'll choose Region, State and quantity.
So, there is all the data that will eventually drive our pivot table.
And look they put Region and State in the same column.
Usually I hate that.
But in this particular case, no worries.
I'm going to let it go over here in Column.
Oh!
I have a list of all the possible states and all of the possible regions.
And we're going to use get Pivot Data.
Usually Get Pivot Data drives as crazy.
But in this case, It's going to be a good thing.
Let's make sure you haven't turned it off.
You have to be inside the pivot table.
Go to Pivot Table Tools>options.
Open the options drop-down and make sure that Generate GetPivotData is checked.
Alright! So, it is in this case.
Come out here, and we're just going to type equal sign.
I'm going to go find the number for Illinois And we get a Get Pivot Data Formula.
Copy that formula down double click.
We're going to get the wrong answer all the way.
That's because get pivot data, as I've discussed before in the podcast is hard-coded to Illinois.
Well I'm going to change that.
We don't have to have region Central.
In these particular cases, I just need to say state, Illinois and instead of hard-coding Illinois, we're going to parameterize it.
So here I'm going to click on 02.
Alright, and so then as I copy that down to the other states.
I'm going to get the actual answer for each of the states.
Great right!
Now down here for the regions that form is going to be a little bit different.
I don't need to specify any particular states.
So, I'm going to get rid of everything after region central.
And again, just like I did with the state.
I'm going to take the hard-coded central and replace it with a cell value.
And copy that down.
Okay! so now, I have a pivot.
So, the original Data.
A pivot table summarizing that data.
And then a range that is eventually going to become the source data for the pie chart.
Ah! But I need to build the slicer.
And the slicer cannot be applied to this table here because if I chose central, it's going to hide all the other regions.
I need to keep all of this data in the pivot table.
So, I'm going to take region then state [ CTRL+SHIFT+down arrow ] [ CTRL+right arrow ] copy and we're going to move that over here.
So, it columns U and V.
[ CTRL+V ] and then on the data Tab, say remove duplicates.
Click ok.
So now, I have just one unique Occurrence of every region and state.
I'm going to build another pivot table from this data, So Insert > Pivot Table.
Where is going to go.
Existing worksheet, over here to the right.
Click ok.
And we're going to put region and state in the pivot table.
See it.
Just gives you a list of all the regions in state.
Seems kind of silly.
But that's going to be very helpful.
Now from this pivot table, we're going to insert a slicer.
And choose region.
Click ok Alright! So there are our Slicers for region and you'll see when I choose central, here from this slicer.
I see just the states and regions involved in Central.
And the Compact view which I usually hate, I usually say how horrible it is.
It is actually good because it puts all of those values right here in this column.
Alright! A couple of cleanup things.
I'm going to take the Slicer.
Wanna make it be four columns.
Make it be wide enough to hold those four columns.
And change the slicer height.
A little bit larger.
All right.
So here we go central West, North East, South east.
Now, All we have to do is figure out based on what is showing over here in this Pivot Table.
We have to figure out which of these items we want to show in the chart.
So, what Stephen had said was if we select Northeast, Any of the states that are in Northeast want to be in that separate pie chart.
So, here where I say equal MATCH.
Look for Illinois over here in Column X comma 0 because we want an exact match.
And when I copy that down to the States, we're going to see New Jersey and New York.
Actually come up with a value and everything else comes up with is error comes up with Na.
So, what I need to do is if we're getting a value here, I want to show one.
If I'm getting an NA, I want to show a zero.
So, we'll come back to this Formula.
equal if open parenthesis ISNA.
If we get an NA from there, then I want to zero.
Otherwise, I want to one And copy that down just far enough through the States.
Alright, so in this case, Northeast of selects selected we're going to show New Jersey at New York.
If I choose West, and we're going to show California and Washington.
Now, for the Region's it's actually kind of reversed.
We want to show the region in the opposite fashion, So, here I want to show Central North East and South East.
So, West is found and we're going to make that one and zero be reversed all right.
Finally, this column called where.
This is an important Column.
And for all of the states, we're going to hard-code a value of one.
And for all of the reasons, we're on a hard code a value of zero All right now, let's create our bar of pie chart.
So, we select the data area in quantity.
Inserts> open the pie, and there's either a bar of pie or a pie apply this trick would work with either 1.
I'm going to choose bar of pie bar apply.
This is a rare chart.
It's rare that I ever work with these.
So, let's do a couple of different things.
First, we're going to rid of the legend delete Layout data labels.
More data label options.
And I want to see the category name and percentage Click ok.
All right! So, now we have this is actually well It's it's really a mess when you get right down to it, And I think because we're zooming out to show these states, we probably want to make this part of the chart larger than the pie, So, I'm going to Right-Click there, Format data series.
And I'm going to say that the second plot size instead of being 75% is about one hundred fifty percent.
Which will make it larger than the pie okay.
And then the same trick we use yesterday with the Morpheus lander.
We are going to hide items in the chart by using the filter.
So, here in this little area here will select these cells.
I'll go to Data filter and in the show column, I only want to see the one.
So, only the things that are once all right.
So, now when we choose West, the California and Washington are showing up to States.
Everything else Central northeast and southeast is showing up as region but it's not showing up correctly here.
Now, we're going to use a macro for this but I want to show you how this is actually done.
I'm going to right click.
Format Data Series and say that we're to split the series not by position, but by Custom.
Custom and it says selected Data point to move between.
So, this is Northeast.
We want to have back here in the main part of the chart.
Select that data point and then I can change from the first plot to the second plot.
A little bit of UI there that I had never seen before.
I actually tried to solve Stephen's trick.
Alright! So, Here's what we have.
Any time that we update the pivot table, I want to have some code run and that code will go through reapply the filter.
And then look at each of the series that are visible here and depending on whether there's a one or a zero here, move the series of the correct place.
Now, it turns out in VBA.
In order to have it appear in the original chart it needs to be a value of zero.
That's why we hard-coded zeros for all the regions and to have it appear in the secondary chart.
It needs to have a one.
All right!
Let's do [ ALT+F11 ] and take a look at that code.
Over here in the project explorer, we need to find the sheet that we're on.
So that is the demo sheet.
From the top left drop-down there's only one choice.
Choose worksheet from the top right drop-down.
Choose Pivot table update and then inside of there, we're going to have this code.
Alright! So, the first one active sheet dot auto filter dot apply filter.
Reapplies the one that we've previously set and then I have a counter here.
I have to use a counter for each cell in R2 to R13, and yes that's hard coded to match where my where columns are.
but I take special cells.
Excel cell type visible.
That make sure that I only see the visible ones.
And we're going to do active sheet dark dot chart objects one dot chart dot series collection one.
All of that's gonna be the same.
But the points the points, and it's funny.
So, we see California, Washington, in Central Northeast and southeast.
In this chart, this is considered point one, point two, point three, point four, point five.
It sure would be nice if I could use the row number.
But I can't because Central which is 0 point three is actually over here in row 10.
So, I have to use the counter to keep track of which counter I'm setting.
And we do that using the secondary plot property.
and we just set that equal to cell dot value.
o either a 0 or a 1, something their plot and then increment the counter and next cell.
Alright! So, now that that is all working.
It's on the worksheet code pane.
Let's come back here and try it out.
We will choose South East.
All right and we get the states in Southeast, Florida and Georgia and then all of the other regions are showing up back in the main pie.
North East West and Central.
I had automatically updates.
So, when Steven asked for a pivot chart that would do this well it's not really a pivot chart at all.
It's just a regular chart and while we do have to pivot tables and a slicer.
The slicer is just over here It's what Rob Kali calls a disconnected slicer.
Where it's not based on the original data set at all because we need to have all of the answers showing up correctly in this real pivot table.
And Not being sliced by the slicers.
Now in this particular case, some of these rows are hidden because of the filter but the slicer is operating on this pivot table.
And then the macro is applying the filter to get us just the results of the show.
What a crazy set of steps.
But it does work.
I want to give credit to Steven for this idea.
I think it's a great idea.
Interesting way to go Too bad that because of the VBa we can't put it up on the Excel web app.
That would have been a cool use but unfortunately, It's just going to have to run here on the flat client.
Not on the in the web app.
I hey I want to thank Steven for sending that Crazy question in.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
These rarities that pivot table.
That's not so rare, but remove duplicates.
A disconnected slicer, ISNA A Bar of pie chart and the crazy one I'm going to embrace the pivot table Com
