How to Create Variance Charts in Excel with Percentage Change (simple & uncommon technique)

In a previous lecture, I showed you how you can create a dynamic column chart to show variances. Now it was dynamic in the sense that if your bars were on the positive side of the axis, they had a different color to if they were on the negative side of the axis. And this was automatically controlled by Excel. It wasn't something that was done manually. And not only that, but the color of the data labels was also changing if they were on the In the example, we had the sales information for the actual year and I had created a normal column chart on that. Then I was showing the change to previous year with this specific chart underneath it and they were in sync. So you could easily tell this is the value for the actual year and that's the change to previous year. Now what I don't like about the variance chart is that the bars are too big. They kind of distract from the actual data. It will look much better if the variance bars are much thinner. Now the first logical approach is to increase the gap width of the second chart. If I go all the way, I can't go anymore after this. It's 500%. I get it look like this. And honestly, I do prefer this to the previous version, but I still like this one more because this I think has a good balance. It directs the attention to actual numbers, but it also provides the information on the change to previous year. And it does this in a more subtle, elegant way. It's not too bulky and it's not too distracting. So this chart that you see here is actually not a column chart. What chart type do you think it is? Well, let me give you a hint. It starts with an S. It's a scatter plot. So stay tuned if you want to see how you can disguise a scatter plot to look like a column chart. This is the actual information that we have and this is our table. To show the variance, first we got to calculate it. So let's write the formula for that. It's the actual sales minus previous year sales divided by previous year. It's a percentage and we copy down. And I'm going to insert a scatter plot series. Okay, I'm just going to bring it a bit down. Delete all the chart elements that I don't need. I'm going to remove this because ultimately I want to show the data label of each data point. Delete the grid lines. Delete the title. Notice now for the x-axis, I didn't give it anything. I just highlighted my variance and I inserted a chart. But by default, because the scatter plot needs numbers on the x-axis as well as the y-axis, it already allocated 1, 2, 3, 4 and so on to each of these data points. So this data point is the number one data point. Then that's the number two data point. That's the 17% and so on. Now I don't want to see these numbers here. I just want to see my line. So I'm going to go in and hide my axis labels. From labels, select none. Now how do I get a line out of these data points? Well, that's the error bar trick. All I have to do is activate the error bars for each point. Error bars is a feature that's often used in statistical analysis that allocates a range of error to each data point. And data points like the scatter plot series can have a range of error on the x-side as well as the y-side. And you have different options on selecting this error bar range. To activate the error bars, just have to go to the plus. If you're using Excel 2010, you can activate error bars from the chart tools menu up here. So I'm going to click it and I can see there's an x error bar that's associated with this and a y error bar. So let's go to more options. And now I can see I'm in the x error bar. I can also see it here. So I get x and y. I don't need the x error bar, so I'm just going to press delete. For the y error bars, I want a line that goes from the data point all the way to the axis. So let's see which option we can use for that. Well, we don't want both sides. We just want the point to the axis. So let's pick minus and that should work for these. If this is my point and that's my line, this makes sense, right? For the percentage, we're going to go with a hundred percent because we want it to be the full size of this data point here. And I don't want a cap. Now look what happened. It also worked for the negative error bars. So even though I have a point here and actually it looks like the plus would work here, the minus is working for these. This is something you have to keep in mind when you work with error bars is that they expect positive numbers. So even if you have a minus, it expects you to give it positive numbers. I know it can be confusing, but think of it like an expense account where you're always inputting positive numbers and the system behind it when it's calculating and adding them up, it knows that it should deduct from the account because the characteristic of the account is an expense account. And this is the same logic. If you have positive data points and you pick minus, the line that you get is like this. But if you have negative data points and you pick a minus, it's going to be minus and minus. The result that you get is going to look like the plus. Now the next step, I'm going to change the thickness of this to just a little bit thicker. Let's go to 2.5. Okay, that's starting to look good. I don't want to see this data point. So I'm going to go to format and take away the shape fill and the shape outline. So basically I'm making them invisible. That's okay. I'm getting where I want to get, but I want to color them differently. So I want to have a green color for the lines that are on the positive side and a red color for those that are on the negative side of the axis. To do that, I need to use two series. So generally in charts, whenever you want to use conditional formatting, you need to add a new series. This means that I need a separate series for the data points that are going to be above the axis and a different series for the data points that are going to be below the axis, which means I need to split this. One is going to be percentage change if it's positive and another series, the percentage change for the negative data points. And all I have to do here is add an if. If this, I'm just going to copy it, is greater, let's do equal to zero, then give us this, otherwise give us nothing, copy down. Now copy this, press escape, I'm going to paste it here and just change the greater than and equal sign to less than zero and copy down. Let's change the formatting also to a percentage. So let's just go and format this to a green color that we like. I like this one. Right mouse click, select data, add, series name is this, the series y values is this. I don't necessarily need to give it any x values because by default x is taken as 1, 2, 3, 4, 5 and so on. Now for my other series, I'm just going to go and edit it because I don't like not having a series name in there, especially if I have more than one series. So I'm going to change that to this and press okay. So now I have my red series here as well. For this one, I'm going to go back and activate the arrow bars as well. For the x arrow bar, I'm going to do the same thing. I don't need them so press the delete button. For the y arrow bar, let's go and select it from here. So series, this is the minus series, the y arrow bar. What should we pick here? Minus, 100%, no cap and let's go and change the formatting to a red that we like and the same thickness that we have for the green series. So that looks good. Let's remove these markers, no shape fill and no shape outline. Now what I'm going to do is to activate the data labels for both of these series. So data labels and let's go to more options and see if we like the placement above. That's good. Now you can see the zero percentages there. These are the ones where I said return nothing and nothing in a formula is still a zero in Excel. So I can go here and adjust the number formatting to a formatting that hides the zeros and that follows the formatting rules. So the first thing that comes is how positive numbers should be formatted. Okay so I just want the percentage without decimal places then it's how negative numbers should be formatted and then it's how zero should be formatted. So for that I'm just going to leave it empty. Basically don't show zeros. I'm going to add that and you can see the zero values for the green series just disappear. Now for the data labels of the red series. I want to see them below and I'm going to apply the same formatting. Now I'm just going to make some adjustments. Let's take away the shape fill color and the shape outline of the chart and also of the plot area so that I can position it perfectly under my original chart. I'm just going to put it on there, drag this along and just get them right. And remember you have two things here that you can control. You have the chart area and the plot area. That looks quite good. Now I'm going to click on it, hold down control, click on the border of the chart so I can drag it down with the arrow keys. It's still a little too tall. I'm just going to reduce the height and move it up. Now one thing you need to adjust is the plot area. You can reduce that so that you leave enough space for the data labels on both sides and that's the chart. That's another way how you can create a variance chart that's actually based on a scatter plot but it looks like really thin bars and it's not too distracting and it's not too bulky. 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.