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
