a contains the actual values that are plotted on the chart This is a topic that many people are looking for. star-trek-voyager.net is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, star-trek-voyager.net would like to introduce to you Excel Variance Charts: Actual to Previous Year or Budget Comparisons. Following along are instructions in the video below:
“This video. I m going to show you how you can create a dynamic column column chart that s going to show variances. So what we want is that if have a positive change that that data point is in a different color. So let s see 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. It s like conditionally formatting. The chart and not just that but i m also going to change the color of the data label so that when they re on the positive side of the axis.
The data labels are that same 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 isolated. 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 calculations second step is to insert the column graph.
I m just going to climb like 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 arocs is labeled. 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 his 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. Access and let s also remove the tick marks on the line and for access labels instead of next to access. 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 bigger 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 if 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 house is to remove the white color of your plots 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 my 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 keys. So just make sure that your plot area ends here.
And there is 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 in 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.
It s 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 re not excel. 2007. And you want to get dis effect.
What you need to do is something similar that we did in the previous lecture is that you need to add a series for plus and only put in the values. If they are positive and a separate series for and only put in the values. If they re overlap them 100 and format the positive series in green and the negative series in red and this option helps us skip all of the steps 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 this a conditional formatting and change.
The color of my values to red. They don t reflection here because the font color is independent to this. But if i expand on the decimal places here it shows in here right 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 on their 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 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 a 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 yourself 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 with say zero point zero percentage. Now it s formatting way 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 moments 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 zero point zero percentage. That s how negative numbers will be formatted next is 0.
How should the values before matted 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 here. 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 place holder and the hashtag sign that s a variable phrase holder so depending on the length of your numbers. So if i would for example because 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. Okay..
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 brackets ring sure semicolon put in red. So this is language dependent. So now i have an english excels.
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 and 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. If i scroll down you can see color. 10. That s the one that i used and if you even want a darker.
One you could use 51 for example. So let s go back let me change this to 51 here s a back got darker. Now remember that these data labels they were connected to the cell. So let s go back to format them we re link 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 career just going to add that and we re going to use that so 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 different color than when they re on the negative side of the axis and 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 ” .
Thank you for watching all the articles on the topic Excel Variance Charts: Actual to Previous Year or Budget Comparisons. All shares of star-trek-voyager.net are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.