馃攳
Build Impressive Charts: It's NOT your usual Bar Chart (Infographics in Excel) - YouTube
Channel: Leila Gharani
[0]
Sometime last year, I created a video
[3]
that showed how to create
nonstandard charts in Excel.
[6]
So like info charts.
[8]
But some people actually
called them dome charts.
[11]
If you missed that one, I'm
going to put the link to it
[13]
in the descriptions.
[15]
Now a few weeks ago,
[16]
I got this question from
one of my Udemy students.
[20]
Dyego asked, is there any way
to create this chart in Excel?
[25]
So basically a chart that has symbols,
[28]
which are aligned on the right hand side
[30]
and the label on the left hand side.
[33]
What do you think?
[34]
Can we set this up in Excel?
[36]
Sure we can.
[37]
Let's get to work.
[39]
(upbeat music)
[43]
That's our simple data set.
[45]
For female, I just input the 43% here.
[48]
Male, I put a formula, one
minus the female amount.
[52]
Now let's just assume that's
the percentage of female
[56]
and male that buy our product.
[59]
And we want to create
an info chart on this.
[62]
So let's just take a
second look at the diagram
[64]
that we have here.
[66]
The 43% represents that blue box here.
[70]
The one that's behind
it, that's in light gray
[73]
is the full amount, which is the 100%.
[77]
So we could set this one up
first, once we're done with it,
[80]
we could replicate it and
update the data source
[84]
and also our symbol here.
[86]
Okay, so let's get to work.
[88]
Now, let's also add the
value for the full amount.
[93]
So I'm going to call it
full and put in 100% here.
[97]
Let's create a bar chart based on this.
[99]
So just highlight the dataset,
[101]
go to insert
[103]
and insert a bar chart.
[106]
Okay, just a normal clustered bar chart.
[110]
We get them beside each
other, but we don't want that.
[113]
We need to switch things around.
[115]
Right mouse click, go to select data
[118]
and switch the row and the column.
[121]
Okay, so this gives us the
female percentage right here
[125]
and that's our full percentage amount.
[128]
So we're fine with this,
[129]
but what we need to do is to overlap them.
[132]
We need to bring this on top of this.
[135]
So just click on any of
these series, press Ctrl+1
[139]
or you can also double click
to activate the format options
[143]
or right mouse click
and format data series.
[147]
So where is the series overlap?
[149]
Well, it's right here.
[150]
It's on 0% now, but we're going to take it
[153]
all the way up to 100%.
[155]
And this way, we get one of
them overlapping the other.
[159]
Now in this case, the wrong one
[162]
has gone on top of the other one.
[164]
Right, so we need to change that order.
[166]
I'm going to right mouse
click, go to select data,
[170]
and I'm going to push down the female.
[172]
Because the last series that we added,
[175]
that's the one that comes on top,
[177]
but I don't want that one to be on top.
[179]
So I'm going to click on
this series and push it down.
[183]
Okay, and that's fine.
[184]
So now it looks good.
[186]
One thing I'm going to do, is
to go back to series options.
[191]
Okay, so click on one of these series.
[192]
It doesn't matter which
one, go to series options
[196]
and decrease the gap width to 0%.
[200]
That's going to fill up our
entire plot area with our series.
[204]
Then let's just remove the title.
[207]
Let's remove our axis.
[208]
I'm just pressing the
delete button on these
[211]
and let's fix our X axis to
be between zero and 100%.
[217]
Okay, so go to axis options.
[220]
For minimum, I'm actually going to fix it,
[223]
just put in a zero, press enter
[225]
so that you see the reset button.
[227]
That means you've really fixed it.
[229]
The same thing for the
maximum, one, press enter.
[232]
Now we fixed it.
[233]
Okay, now let's make this smaller.
[238]
And let's also remove the axis here.
[242]
Okay, I'm just going to go to format
[244]
and remove the outline of my chart.
[248]
Okay, so that looks better.
[250]
Okay.
[250]
Now we have the rough shape, correct.
[254]
But it would look nicer
to have that rounded edges
[258]
instead of the sharp edges here.
[260]
To get that look, let's
go and insert a shape
[265]
that has the shape that we like.
[268]
So I'm going to click on that
square with the rounded edges,
[271]
just drag it here.
[273]
Okay, so that's the shape
that I want this one to have.
[278]
And it's the same shape that
I want the other one to have,
[280]
but I just want it to
be in a different color.
[283]
So let me fix the color of my shape first.
[286]
So under format, let's go to shape fill.
[289]
I'm going to go with this
blue and for shape outline.
[292]
I don't want an outline, so
let's just do no outline.
[296]
Okay, so that looks nice.
[297]
Now, here is the important part.
[300]
Click on your shape, press Ctrl+C to copy,
[305]
click on this series, press Ctrl+V.
[309]
Okay, and we get it in there.
[312]
Now let's do the same for the full range,
[315]
but first we need to
have the right colors.
[317]
I'm going to copy this.
[319]
I'm going to paste this
[322]
and I'm going to change
the color to a light gray.
[325]
Under format, under shape
fill, let's pick this gray.
[331]
Okay, so again, Ctrl+C,
click on the series, Ctrl+V.
[337]
Okay, so this part is
starting to look good.
[340]
Now there is a tiny
little line here and here.
[344]
That seems to be the grid lines.
[346]
Let's just remove them.
[349]
Okay, great.
[351]
So now what we need,
[353]
let's go back to our image
is to add this label here
[357]
and then let's try and
add the symbol to this.
[360]
First, let's do the easy part, the label.
[363]
For that, we can just activate
[365]
the data label for this series.
[367]
So just right mouse click, add labels.
[371]
Now let's just push the label to the base.
[374]
So click on the label
first, go to options here,
[379]
and for label position,
let's select inside base.
[384]
The rest is just formatting.
[386]
You just need to make this quite big.
[388]
I'm going to go with 20
and we can also choose
[391]
a different font since
this is an infographic.
[395]
I'm going to go with
[397]
Latto.
[399]
It's right here,
[401]
Black.
[402]
So Latto Black is a free
font that you can download.
[406]
I find it looks quite nice on PowerPoint
[408]
and on infographics.
[411]
So let's make this white as well.
[414]
Okay, so now comes our symbol.
[417]
First, just get a symbol of your choice.
[420]
I'm going to use one of
Microsoft's inbuilt symbols
[424]
that are relatively new in office 365.
[428]
Under icons, you have a
lot of different icons
[432]
and the icon that we need in
this case is just right on top.
[436]
I want this one and this one.
[438]
So I'm going to click on them
and insert them right here.
[442]
Okay, so let's just
bring them to the side.
[447]
Now I'm just going to reduce their size
[449]
to the size that I would
like to have in the chart.
[453]
So something like that.
[455]
I want to make the second one
identical to the first one.
[457]
So let me just copy this,
[460]
go to the second one
and paste it in there.
[465]
So by default, that should adjust.
[467]
Yeah, both are the same.
[469]
Now I'm going to make
them in a light color.
[473]
So for the graphics fill,
let's go with a light gray.
[478]
Just so that it stands
out on top of the blue.
[481]
Now let's just make this
a little bit thinner too.
[485]
Okay, that looks good.
[487]
Now the aim is to put
this graphic right here.
[492]
The challenge is to put
it in a dynamic way.
[495]
So whenever this is getting
more, this is moving with it.
[498]
And it always stays in the same place.
[501]
How do we do that?
[502]
Whenever it comes to manipulating charts,
[505]
the answer is usually
you need a new series
[509]
and we need a new series for this.
[511]
And basically the series is super simple.
[513]
All we need the series to have,
is to plot one single point
[518]
right here at the end.
[520]
And that point of course
has to be dynamic.
[523]
So it has to somehow
be related to this 43%.
[527]
And we're going to use our
symbol as our charts series.
[532]
Okay, so let me show you what I mean.
[533]
First, let's add our series.
[535]
I'm going to just call it icon here.
[538]
In the first round, let's just make this
[540]
equal to this number.
[543]
We can see later if we need to adjust it.
[545]
So now we need to add
this 43% in here as a dot.
[551]
What chart type is that?
[553]
A scatterplot, right?
[554]
That gives us dots.
[556]
But at this moment in
time, Excel doesn't know
[558]
that we want to add a dot, right?
[561]
Anything we add to this,
it's going to think
[563]
we want to add a column chart.
[565]
So let's add our column chart.
[567]
I'm going to right mouse click,
[569]
go to select data, click on, add.
[572]
The series name is icon
and the series values,
[576]
where right now I just have this 43%.
[579]
Let's just click on this
and say, okay and okay.
[583]
Now that's the last series that we added.
[586]
So it's put it on top of our blue series.
[590]
We want it to be a scatterplot.
[592]
So the second thing I need to do
[594]
is to change the series chart type.
[597]
So right mouse click,
change series chart type.
[600]
And from here for the icon series only,
[604]
let's select a scatter plot.
[608]
Okay, and right away, Excel
plots it on this secondary axis.
[613]
That's the only place it can plot it
[615]
because this side has already been taken.
[618]
So it needs to plot it on the other side.
[621]
That's fine for us.
[622]
We're going to go with, okay.
[623]
But now we can see that
our dot is right here.
[628]
It's too high.
[629]
We want our dot to always
sit in the middle here.
[632]
The great thing is that we're
working with a scatterplot
[635]
and the advantage of scatterplot series
[638]
is that you can give it
numbers for the x-axis
[641]
as well as the y-axis.
[643]
What's our y-axis number?
[646]
It should be somewhere in the middle here.
[649]
And our y-axis number is
always going to be fixed
[652]
because we always want our dot
[654]
to be in the middle of our series here.
[657]
So we can fix that by first
fixing our secondary axis.
[662]
So let's just activate
it, go to options here.
[665]
Under axis options, let's
really fix the minimum to zero.
[669]
So just put in a zero, press enter
[672]
and I'm going to fix the maximum
to a 100% and press enter.
[676]
Okay, and this has moved here,
[678]
but it's not exactly under 50% line
[680]
because right now it thinks it
should plot it on 43%, right?
[685]
That's the Y value we've given it.
[687]
So let's go and change this dataset.
[690]
So I'm going to right mouse
click anywhere on this chart,
[693]
go back to select data for my icon.
[696]
Now I get something in addition.
[699]
I get series X values,
[701]
which I didn't have before,
[703]
because now I'm working
with a scatterplot.
[706]
But check this out, my Y value is 43,
[710]
but my Y value should
actually be 50%, right?
[714]
Because I wanted to
always sit in the middle
[717]
and the X value should actually be 43
[720]
because 43 is somewhere here.
[722]
Remember this goes from zero to a 100%.
[726]
So 43 is going to sit right here actually.
[729]
Right at the end of this bar.
[731]
Okay, so let's switch things around.
[734]
I'm going to just cut this one out.
[737]
And for the Y value, I'm
going to fix it to 0.5.
[742]
So I'm just going to type it in, 0.5.
[745]
And for the X value, I'm going
to paste what I had under Y,
[749]
which is basically
referencing this number here.
[752]
Okay, and I'm going to say, okay,
[754]
and this symbol just shifted right there.
[757]
Right, so it sits about right.
[760]
Let's just first remove this
one, that we don't see it.
[764]
And second, let's replace
this circle with our symbol.
[768]
The same trick, just click on your symbol
[771]
Ctrl+C, click on this series
in the chart and Ctrl+V.
[777]
Okay, now we have our symbol in there.
[779]
The only thing is that, it's
sitting exactly on the end,
[784]
and we want to adjust it a little bit.
[786]
So let's just update this formula
[789]
by bringing it a little bit down.
[791]
So I'm just going to bring it down by,
[793]
let's say 6% and it puts it right here.
[796]
Right, so depending on
how big your symbol is,
[799]
you just need to update this adjustment.
[803]
Okay, so that looks pretty good.
[804]
Let's just double check with our original.
[807]
That was this.
[808]
And our version is this.
[810]
Let's just make sure that this is dynamic.
[813]
So I'm going to change this to 67.
[815]
Everything updates, let's
even go to a hundred percent.
[819]
Looks good.
[820]
Let's go to 28%.
[824]
Looks good.
[826]
Now we can replicate this for male.
[828]
We just have to add this part in.
[832]
And then we can copy
and paste our graph here
[837]
and update the series.
[838]
The only thing is, when
you use shapes in charts
[841]
and you change the original
series to something else,
[845]
it resets your shapes.
[847]
So you just have to copy and
paste these in there again.
[850]
Let's say if I just click on this,
[852]
I'm going to drag this for male
[856]
and update this as well.
[861]
Okay, you see that it got reset here.
[864]
So we just have to copy and paste
[866]
these shapes in there again.
[868]
You can also right mouse
click, go to select data
[871]
and do the update here.
[872]
So for the icon, I'm just going
to click on this, go to edit.
[876]
The title is fine.
[878]
The X value should be down here.
[881]
So I need to make a calculation here.
[884]
Let's just delete that,
[886]
replace it with this and the
Y value, that stays the same.
[890]
That's going to be fixed.
[892]
So let's go with, okay, that's that.
[896]
And let's just copy this
formula and paste it here.
[899]
So the relative referencing
works to our advantage here.
[904]
Now all we have to do is
Ctrl+C, Ctrl+V and that's that.
[909]
And let's change our symbol
[913]
and let's add the data label.
[918]
Now there is a quick way to copy and paste
[920]
formatting for data labels.
[922]
Just click on this data
label, do Ctrl+shift+C.
[926]
So not Ctrl+C, but Ctrl+shift+C,
[928]
click on this one and do Ctrl+shift+V.
[932]
And this way we paste the formatting.
[935]
Okay, so now let's just
do some testing here.
[938]
(keyboard clicking)
[941]
So that's how you can create info charts
[944]
that incorporate symbols in this way.
[947]
And you don't have to just work
[948]
with the male and female symbol.
[950]
You can also add in your products.
[953]
Let's say you sell office
supplies and phones.
[956]
You could put a symbol of the
product that you're selling
[960]
in there and create
this nice visualization.
[964]
If you liked this video, I'd appreciate it
[966]
if you gave it a thumbs
up and while you're here,
[969]
why not subscribe to this
channel if you're interested
[973]
in the topics that you see on the channel?
[975]
This way, you're going to get
updates when new videos come out.
Most Recent Videos:
You can go back to the homepage right here: Homepage