How to Create Dynamic Variance Charts with Arrows | Show Change to Previous Year / Budget

In the last two lectures, I showed you how to first create a dynamic variance chart using a column chart, and then using a scatterplot chart with error bars. The bars colors automatically changed when numbers shifted from the positive to the negative side and vice versa. Now, in this lecture, I want to show you two more ways to get this. When I was designing the last chart, the one with the error bars, I ended up playing around with them and got them to look like this, and then like this. Now, in this lecture, I'm going to show you how to get these two effects. So let's get started. In this video, I'm going to start off at the point where I finished in the last lecture. So my starting point is here. If you missed that video, make sure you check it out. I put the link in the descriptions below. I've also put a link where you can download this workbook and practice along with me. As a first step, I'm going to give this a makeover and make it look like this. These arrows are also dynamic, so we'll test that at the end. Now, these were the error bars, and error bars are actually not really bars. They are lines. And because they're lines, you can have a begin arrow and an end arrow for them. So this is my positive error bars. Begin means where your point is. That's the beginning. You could say for the begin arrow type, let's go with this one. That's the error bars associated to the negative series. So I'm actually going to create now selected series. I want to click on the error bars themselves to go to the error bar options, and that's my point at the bottom. So that's also the begin arrow type, and I want it to look like this. Now, all I have to do is to bring it up and to make sure that I don't shift it around, I'm going to hold down the Shift key and bring it up. Put it right here. Now, I'm just going to hold down Control, just move both of them down. And for the chart description of the previous chart, just going to remove that because I can add it now to the top chart. Now, again, let's move this a bit higher. Okay, so I don't need to show the axis. I'm just going to remove that. Now, this looks good. It's just the data labels are too close together, so I can move the data labels of this series directly inside the bars. So I want them inside end, and let's make them really stand out by making them white. This looks okay, but I think it can be improved if we change the alignment of these arrows because now they're both aligning to the x-axis, but I don't want them to have this look. It just looks like they're being misplaced. So what I want them to do is to align together both on top of the axis. So even if the values are minus, I want them to be up here. Now, how can I do that? Well, I could change the data preparation table here. So this is basically this series. These are the negative points. I could change it that if the value is negative, don't show me negative. Show me a positive number. This way, they get shifted up there, but I have a problem because the arrows are showing up and it looks like it's a positive 35%, but it's a negative 35%. Now, this I can easily change because I have created an entirely separate series for the negative variance. That means I have full control over this series. So what do I have to do here to make it look right? I can switch the arrow. Instead of showing it at the beginning, I can show it at the end. Let's switch that. Okay, that looks good. Now, my values are also not correct. I need them to be negative. Since this series will always only show negative values, I can easily change that in number formatting. All I need to say is if the values are positive, show them as negative. So the first argument in the custom format is to show how you want positive numbers to be formatted. So I'm going to say, well, show my positive numbers as negative numbers. And I'm going to add that. Now, you see that this worked, this worked. It added all the zeros in there because nothing in the Excel formulas, when I say return nothing, it's a zero for Excel. Before, I used custom formatting to hide the zeros, but another way you can hide the zeros is to write a special error here. And this special error is the NA error. And when Excel encounters this error, it just doesn't plot that point. And because it doesn't plot it, it doesn't have a data label. Let's check that out. You see, they just disappeared. Just to be consistent, let me also do it here. Okay, so it's starting to look really nice. Now, I just have to do some formatting. I want these numbers to always sit on top. So this is the label options for the negative series. Instead of below, I wanted above. But for the other one, it's already above. So now everything looks really consistent. What I can do is add the chart title here. And I'm just going to copy and paste from this one. And just adjust my plot area or my chart area to make it as big as I want. Now, everything looks aligned. The numbers are correct. I can group this one together with this. So I'm holding down Control, click, click, right mouse click, and group. Now you have one chart that you can move around and adjust as you see fit. Just test this quickly. I am going to change this number to a positive variance. So this is this one. It has minus 9%. Let's switch it around. It has plus 6%. What I also like about this technique is that the arrow sizes change. So you can actually tell what is a bigger number, what is a smaller number. So if this gets even bigger, let's go to 146. The arrow gets bigger. Let's go to 156. Then it starts to go up. So you can quickly tell which company was far below previous year values and which company was far above previous year values. So now let's look at the other way. I want to put it on the bottom instead of putting it on top. Just have to follow the same thing. Let's go and add the arrows to these. And how can I align them that they are together? Which one should I switch around now? Well, let's switch our positive axis. So now for the positive axis, I want to show a negative number. Okay, now they're both aligned like this. The negative one looks good. I have to activate the end arrow type instead of the begin arrow type. So the begin I can take away. And again, with the number formatting, all I have to do is to say if these numbers are negative, show them always as positive. So for the positive numbers, I'm never going to have positive numbers here because of this formula, but I'll just leave that as 0% and say negative numbers should also look like a positive number. So that's just going to be 0%. And I can hide zeros if I don't use the NA technique here. Okay, that looks good. Now let's move these data labels to the bottom side below. Okay, and I don't need to show the axis. Let's adjust. And again, I can group. So click, click, right mouse click, group. You have one graph that you can move around and use as you see fit. Okay, again, let's just quickly test this. This is minus 9%. Let's go to minus 12% just to see that this gets bigger. Now let's even increase it and the arrows go more down or they would switch over if you have the other effect. I prefer this version and then this one, then this, and last one is this one. Which version do you prefer? Let me know in the comments below. So if you did enjoy this video, make sure that you give it a thumbs up. And if you like to learn more about Excel in terms of data analysis, data manipulation, and visualization, make sure that you subscribe to this channel to get notifications when new videos like this one come out.