Excel Variance Charts: Actual to Previous Year or Budget Comparisons

Data Translates
219 followers
In this video I'm going to show you how you can create a dynamic column chart
that's going to show variances. So what we want is that if you have a positive change
that that data point is in a different color so let's say green. If you have a negative change
that data point is in let's say red. But we don't want to fix the data point so we don't want to
click on the data point and color it in manually. We want to make it dynamic so Excel figures it out
for you. That means that if your data set changes then the color coding is going to automatically
update. So it's like conditionally formatting the chart. And not just that but I'm also going to
change the color of the data labels so that when they're on the positive side of the axis
the data labels are let's say in green and when they're on the negative side of the axis
the data labels are red. In this example what I have is the sales data that I want to show using
a column chart for the actual year and below it I want to show the change to previous year using
this technique that we just talked about. So these two charts are going to be in sync. So let's see
how we can set that up in Excel. So let's go through the steps of getting this done from scratch.
I'm going to completely remove this and remove the chart. This one is showing the actual sales data.
It's a very simple column chart. I've removed everything I don't need here, activated the data
labels to make it very simple and easily readable. What we're going to concentrate on now is to add
the change to previous year and show this in the different colors. So first off we need the
calculation. Second step is to insert the column graph. I'm just going to highlight this, hold down
the ctrl key, highlight this and insert a column chart. Now we can remove the object elements that
we don't need. So I don't need this, I don't need this, I don't need the grid lines, I am not going
to need the y-axis because I'm going to activate the data labels here. The axis labels I don't need
either because I'm going to be placing this right below this and if someone looks at this they know
that company A, that's the value and that's its variance. I don't necessarily need to show company A
here. But don't go and press the delete button because that also deletes your line. So I'm just
going to reverse this. What we need to do is go to format axis and let's also remove the tick marks
from the line and for axis labels instead of next to axis we're going to select none because we don't
want to see any labels here. While we're here let's just change the line color as well to a darker color
and make the line a little bit thicker. Then what I'm going to do is place this right below this but
I need to be in sync. I don't want them to be this long so I can reduce my chart area even a bit more.
I need to play around a little bit with the formatting to get them in line and what helps is
to remove the white color of your plot area and your chart area and then you can really
put one on top of the other and then move it down. So let's go and remove
the fill and any border. Well the plot area doesn't have the border but the chart area does.
Now let's place this on top. Okay so that looks quite good. Now I'm going to press ctrl and click
on the border to get this little circle so I can move this down with the arrow key. So just make
sure that your plot area ends here and there's enough space for this label to show which is
fine in this case. Now let's take a look at changing the color of the bars first.
What I would like to have is that they're green if they're on top and red if they're
on the bottom. So let's go and take a look at the fill options here. We have solid fill so that's
the color we have now and I can change this to green. Okay it's now for everything but I have
the option here to invert if negative. So this option came from Excel 2010. If you have Excel
2007 it doesn't exist. So from Excel 2010 you can use invert if negative and then select the color
that you want to have if your values are going to be negative. So that really makes it simple
because if you have Excel 2007 and you want to get this effect what you need to do is something
similar that we did in the previous lectures that you need to add a series for plus and only put in
the values if they're positive and a separate series for minus and only put in the values if
they're minus overlap them 100% and format the positive series in green and the negative series
in red and this option helps us skip all of these steps. You just have to click this and select our
color. For the data labels how do I do that? If I change the color of this font if I use let's say
conditional formatting and change the color of my minus values to red they don't reflect in here
because the font color is independent to this but if I expand on the decimal places here
it shows in here. So the font color doesn't show but changing the formatting of this cell
is showing in here which means that my formatting here is taken from the cell formatting here.
We can see that if we go to data label options we can see under number that it says it's linked
to source and that's why the formatting is reflected in here. If I take it away I can define
my own formatting here so let's say I don't want any decimal places I can add that and that's the
formatting I see. I activate this that's the formatting that I see which means that if I can change
the actual cell formatting to show me a different color that could get reflected in here. So let me
just reverse out my font color. Let's have a look at the cell formatting of these. So I'm just going
to press ctrl 1 or right mouse click and format cells and let's take a look at the formatting
options we have here. For percentage we can say okay show us two decimal places and that would
show two decimal places here or we can go to custom and make our own formatting in a way
that we can control the color. With custom formatting you can control how you want your
numbers to be shown. The good thing is you have some control over the color. What you can define
is that the color or the formatting you want if your number is positive, the color you want if
your number is negative, and how you want your values to be shown if you have zeros in your cell
and text can be done too. So if you're not familiar with custom formatting I'm just going to take you
quickly through this but I cover it in much more detail in my advanced excel class. I have a full
lecture on this so if you're enrolled in that make sure you watch that to really understand the power
of this. If I would put let's say 0.0 percentage now it's formatting my negative number as well
because I haven't told it how to format negative values separately. I'm just telling it format all
cells like this but the moment I add the semicolon here it means that I want to provide it with a
different formatting if my numbers should be negative. So I could say show my negative numbers
in brackets and show them as again 0.0 percentage. That's how negative numbers will be formatted.
Next is zero. How should zero values be formatted? Now if I don't mention anything for zeros it will
take the first one to format zeros and if I want zeros to be formatted in a special way so let's
say I just have this one this line here I can type that in. Last is how you want text to be formatted
but that doesn't apply to us so we can leave it out. You're going to see positive ones are like
this negative ones are in brackets and if I happen to have a zero somewhere it's like that. If I don't
want zeros to be shown at all I can just delete that and leave that empty and that's gone. Okay so
that's the power of custom formatting. How could I bring now my red and green in there? So let's go
back. You can see here that there's so much you can do with formatting but don't get put off by
this because the idea is very simple and you might be confused with the with the hashtag sign and the
zero sign. These are just placeholders for number. The difference is that zero is a fixed placeholder
and the hashtag sign that's a variable placeholder so depending on the length of your numbers. So if
I would for example put zero zero point this I have to put it here in the negative zero zero you
can see my number changes to zero one because that's a fixed placeholder for numbers. So let's
just remove that. I don't want to in brackets let's say I want to show the minus sign but I want to
have red and green and that's an option that you have in custom formatting to put in inside square
bracket green after the semicolon put in red. So this is language dependent. So now I have an
English Excel I put in green and red. Obviously if you send this to someone who has Spanish Excel
they're going to change automatically. They don't have to do anything but if you're typing this
yourself you need to type it in in your language. That changes to green and red but it's a very ugly
green. Probably need to put on your sunglasses for that. You have some options here. There are
different colors you can use. A nicer green is color 10. That's the one I generally use. You can
see it's a bit darker. To see the full list of colors I put the link in here. It's from the
Microsoft site and you get the list of all the color indexes that can be used here. Let's scroll
down. You can see color 10. That's the one that I use and if you even want a darker one you could
use 51 for example. Let's go back. Let me change this to 51. Okay so that got darker. Now remember
that these data labels they were connected to the cell. Let's go back to format them.
We're linked to source. If you don't want to show these colors in your original table,
you just want to show them on the graph, uncheck this and add this as the custom. We're just going
to add that and we're going to use that. We're going to be independent to the cell formatting
we have here and what we can do is just remove the formatting and use a normal percentage format.
But we still have the effect in our chart. That's how you can conditionally control the color of
your data labels when they're on the positive side of the axis to a different color than when
they're on the negative side of the axis. Not just the data labels but also your bars. I hope you
enjoyed the video and if you found it useful don't forget to give it a thumbs up. Also if you enjoy
this type of videos and you want to become more advanced in Excel don't forget to subscribe to
this channel to get notifications when new videos like this one come out.
