In this lecture, I'd like to show you how you can create a non-standard Column chart or an info chart in excel like this one
some time ago
Udemy published a very informative and interesting report that had this chart in it which grabbed my attention
And I was like wow, I really want to make this in excel
That's what I'm going to show you how to do in this lecture. The thing I like about this chart is that it's informative,
It's also visually appealing and it's different to what people are used to seeing - so they pay more attention
Now this chart....
This is something that people are used to seeing. Some of you see this every single day
It is visually effective because it brings attention to the key chart elements that need emphasis, but if I put this side-by-side
Which one grabs your attention more?
For me personally - this one definitely grabbed my attention.
I wanted to create it in excel in a way that is also dynamic
So it saves you time whenever new data comes in.
Now the highlighted part
the part that's conditionally formatted, is something that I also wanted to make dynamic depending on the condition.  In this example,
I have some random data and the number of users per weekday that are logged into the system
And I want to highlight the weekday that has the maximum number of users logged in
So if I get new Data and Tuesday happens to be 600 everything will update automatically
First I'll show you how to get this dynamic conditional formatting effect
And then I'm going to show you how you can bring shapes in charts
And finally how to tweak them for optimal effect because we don't want to have stretched out shapes
So let's start with the conditional formatting part
This is the data table which we're going to use to create our amazing chart
We have the number of users per weekday, [and] we're going to start off by inserting a very basic
Column chart
Now let's just make [some] minor adjustments. So remove gridlines remove axis, add data labels
Let's Decrease the Gap width so that they're closer together. Let's go with
70%
I'm going to update the title so that basically equals this
Take away the border. So I have this in [my] quick access toolbar
[and] that's the outline if you don't have it you can get to it by
Going to format and removing the shape outline from here, so while I'm here. Let's also change the shape fill
To grey. That's the basic chart now
I want to conditionally format this so that the weekday that has the most users
becomes automatically another color
if I were to do this manually so here Thursday has 500 right I could click again on it and
Change the shape fill of only this one
Category to green but it's not dynamic because if Tuesday is 600
This is going to remain green and that's not so what I want to happen. Is that this one becomes green and this one
Thursday turns back to grey
Now here's the key that whenever you want to
Conditionally format anything in your charts you need to add a new data series
What I'm going to do is add a data series that basically consists of only one
Bar and that bar is only going to pop up
whenever I have the maximum so basically everything else is going to be zero but only
There where the maximum number is I'm going to have let's say a green bar. I
need a new series
The series is going to have zeros and that
maximum number, but that maximum number has to pop up
adjacent to the Category, so
[my] formula is going to be if
this
equals the Max of this data set I'm going to press F4 to fix it because I want to copy it down if
That's the case then give me back this number, so if that's a maximum
otherwise give me zero
And let's copy this down
so that's my max series, so I'm going to add this to my chart by going to select Data add
That's the name of the series always always include the series name in
your chart otherwise you're going to mix them up and
That's that so it's plotting it beside it
But that's something I can [easily] [change]
So I'm going to go here to series option make sure that I select [the] whole series and I'm going to change the fill color
first to a green and
Now we're going to go here and for series overlap you see there are minus 27
That's why there's a bit of space between them. I'm going to change it to a hundred percent
So now this one is on top of the other series
The order of it is basically defined by this
[see] if I push this one down
See the gray is covering the green if I push it back up
The Green is covering the gray but for the other [datasets] is all zero
That's why this gray is the only one that's being covered by the green
This is fully dynamic because if I change Tuesday to 600 - it jumps.
So that's the first step now
How do I bring shapes to the chart, so how do I change the shape of this to something else?
So let me just take a copy of this and paste it here
From insert shapes I can pick a shape [that] I want I
Want to have the one that is a bit rounded at the edges
That's this one
Just draw it out here. You can turn it around and change the color to my grey
The Shape Outline to nothing - just take it away
That's the shape let's say that I want to use all I have to do to replace these bars with this one is to go
Control C. On [my] shape click on the bar
Make sure the entire series is selected so also the one behind this green one and go control V
To get the green one [I] have to add that
[to] the second series because remember I have two series right so if I want my second series to be green and this shape I
Have to add that [to] the series as well
okay, so I'm going to change this to [the] green and
[CTRl] C
Click on the maximum series. That's the one that's highlighted and go control V
so now I've added shape to chart, and it should be dynamic, [so] let's go and
change Tuesday back to 300
[ok] jumps back as well, but you see what the problem is here for the data sets that are bigger
So here for 500 the shape is really Pointy and here, it's not
So basically it gets stretched out like this
For bigger data sets and becomes like this for smaller data sets
That's something that I want to avoid to do that. I need to [use] a trick and the trick is to
Basically to split each of these data sets so the 400 to two data sets
The top Data set is going to be identical for [all] of them
And that's going to be let's say this shape and the bottom data set is
going to be a shape that doesn't mind being stretched out, and that shape is a rectangle to
[add] 2 data series to this I could [use] a stacked Column [chart]
That means I just have to change my data set a little bit. I'm going to copy this part
Paste it here
Let's not make you too complex for the beginning
I'm just going to do the gray series first and then at the last that we're going to add the green series to it
Okay, so I need to have two sacks
Let's call them stack one
and
Stack two
That too is going to be a number
That's common to all of them right [so] that can [be] the minimum of this data set
I'm going to fix it [and] now depending on my data set I could adjust this even more I could go
-
If I find that this doesn't really represent my shape quite. Well, it makes it squash that I can change this [number]
that's the size of my top stack now my bottom stack is basically going to be my
Original value [minus] my top stack I'm going to copy that down
Okay, so let's insert this chart. I'm going to highlight this hold down control highlight this and
Insert a column chart, but now a stacked column chart
So this is our top stack they all have the same size so we don't have the problem of having our
getting stretched out
You're just adding the title taking these away and let's reduce the [gap] [flip] again
I'm going to reduce it even more here. Let's go to [fifty]
Okay, so I need two shades. That's my shape number one
My shape number two is going to be the rectangle down here
Let me copy this first and click on this paste
That looks good [so] [I] can either bring in a rectangle [shape] here
But since this already seems in sync with this one, I can just change the color of this to the same color
Now what about the data labels?
Well, I have two stacks here right in this top stack
I'm going to highlight them right mouse click and add data labels to it
Let's make them stand out make them white
Bold and let's get them to be bigger
But the data label is not showing what I want to show right is showing the size of this stack
So that's not what we want. [I] want to show
basically the value from cells and the value that I want to show is this
So if you have excel 2013 or excel 2016 you can do what I'm doing right now
If you have an older version of excel like excel
2010 and seven what you have to do is click on each of these data labels and connect them with a
formula to your data
Okay, but it's doable it. Just takes a bit longer
Take away the border of this one
want to do is bring in the
Axis labels [on] top of each of these bars so I can remove the axis completely
How do I do that because that's not a part of the axis options?
You always have to leave the x-Axis on the bottom you can't bring the labels up there
But you can use a trick and the trick is
remember we had a stack right and the bottom stack can also have data labels so I can activate them and
I can adjust their positioning so that they're on the bottom inside base
And now all I have to do is do the same trick and use value from cells, but now instead of showing actual numbers
I'm going to show my axis labels and
Remove these two so again let's adjust the formatting
That looks really nice. It's very harmonized so as a next step. I'm going to get my conditional formatting in there
What I have to do is exactly what we did in the first step here where we added a max series
We need to have another stacked
Column chart in here
That's going to cover our gray snack if that stack happens to be the maximum
So let's call it stack one Max
and
back to Max
I'm just going to move this a little bit out of the [way]
Okay
formula is the same we need to check if
this number equals the Max of
this series
press F4
Then now not show the number but show the value of Stack one otherwise show zero
That's for Thursday now. I'm going to just copy this press escape
Paste this here, and just move this one to stack two because my east condition is basically the same
So now I bet I have my series. I need to bring it in my chart right mouse Click select Data and
Don't forget to series name
series values of my first [pack] is this
Add again or you can see it popped up right on top
So that's my second stack for the max, and that's the series values so press [enter]
Like that so that looks a bit funny, but basically it's a stacked chart right so whatever I'm adding to it
It's going to add it as a separate snack on top. I don't want it to add on top
I actually want it to cover it
So now layering this by 100% is not going to work in a facts Column chart
Right because the series overlap is 100% what I can do instead is to
Change them to only these two stacks to the secondary axis
So just came back to the change chart type because I can do them quickly here
So that's the fact one [max] and stack two max see now it covered it
And the good thing about this is that this is a mac series that I'm putting on the secondary axis
[I] have that same max on the primary axis
I don't have the problem [of] my access my secondary and my primary isis not being in sync
They are going to be in sync. [so] [now] I can remove this and
For this well the green that's the green that I had I think
Is just double check should be this one now all I need is
To change the tip of that I'm going to cTRl C this
to
this shape
So that's not too much the whole stack to Mac series is highlighted and I'm going to paste it there that looks nice
So let's test this I'm going to change
Monday to 500 as well
Let's see what happens well now I have two maximums and now let's change Tuesday to 600
that one becomes green
That does take some steps
But you just do [the] [setup] once and you can use it on different data sets
Let me just bring this up, and we can do a quick direct comparison with our first chart
This compare to this is in conspicuous trade this one really stands out the first time I saw it
I was really attracted to it, and then I was like wow. It's actually easy to read as well
So that's how you can set it up in excel
Now remember you can use this on any set of data that you want
I suggest that you don't overuse this type of chart use them with care in special reports and in dashboards
if you like this type of excel
Tutorials make sure to subscribe so you don't miss out a more advanced excel technique that I'm going to share with you
This technique is a part of my upcoming - book course that's coming out on [unity] in about two months time
So do check [it] out because it's going to have a lot of neat techniques in there
