Creating and formatting the overlay

So now we have these up down arrows. We can create our chart. So I’m going to select our date and I’m going to select our up and down arrows. And then I’m going to insert a scatter chart. So, we now have a scatter chart. And I’m going to fully cover in the chart we were working on earlier, but don’t worry it will still be there in the background. So we see now, we have our negative points. We’ve got minus eleven. We have our up series. Now, I’m going to go back in and I’m going to change this formula here. So we said, if we put in an error, an NA, it’s not plotted.

But if we put in a zero, it is plotted. So I’m going to change that to zero. You see the way this new plot, this new point has appeared on our chart. But when it is an NA, there is no value that’s actually plotted in the chart. So that point will now disappear on the chart because it’s not plotted. So we have to start now of our up down arrow bars. And we need to do some formatting now to create the look that we see in our original chart. So what we’re going to do is select chart elements and we are going to add arrow bars.

So the arrow bars will add both a side, and a horizontal and a vertical bar. We’re going to select the ones sticking out the side and we are going to delete them. We’ve got two separate series of data that were working on here. We’ve got the up and the down. So I selected the up that time. So I will still need to delete these from the down. So now I’m going to select all of the ups. And I am going to format our arrow bars to bring up our format arow bar box. I’m going to take a direction into a minus. We are going to add no cap.

And then we’re going to do is on the arrow amount, we’re going to change the arrow amount to 100%. Now, by doing this, you’re going to create a line that goes from the zero up to the percentage that is shown at data point. So we’ll say okay to this and we see now these lines have appeared for us. So now, if we go into our fill. And in our fill, we can make sure we have a solid line. We could turn that line into a green color. We can make it a little bit bigger so we can change it to 2.75. And if we come down to the end arrows, we can put an end arrow on this.

So, now we have some end arrows on this. Now, what’s left? We have these data points left on these. We need to get these actual data points. Go to our marker, marker option and say none. And that removes those data points for us. So now we need to go through the same steps with our negative values. The values that are below the zero. We also need to add some data labels to this. So, let’s add in our data labels. So now we have our data labels added to our up arrows. We can add in our data labels as well to our down arrows. And we can go ahead and we can start formatting our down arrows.

So I’m going to open, format my arrow bars. Remember the steps now. We’re going to make it the direction of minus. Make it no cap. We’re going to go to our percentage and increase the percentage from 5 to100. And that should give us our lines going all the way up. Then we’re going to go into our fill for our lines. We’re going to make it a solid line. This line, we are going to make a red color. We’re going to increase the width of the line to 2.75. And if we come down to the arrow type, we are going to add the arrow to the chart. Now, we need to take our labels.

And in our data labels, we are going to place these below. And finally, we need to take our data points. Go to our markers, marker option and say none. So now we have created all of these up down arrows. We can go ahead now and we can remove the chart title. We can remove the up, down arrows. And we can remove these values on the side as well. We can remove the grid lines. We can make the chart transparent. So I’m going to go back into format chart area. For the whole chart, I’m going to give it no fill to make it transparent. And now we can see one chart already overlaid against the other.

Now, I’m going to make this a little bit smaller. And we can come down. And we can start to try and overlay this chart. Now what we don’t need is the axis on the chart. So I’m going to delete the axis. And now we can start trying to line up our Chart 1. So each pair is on top of the right ones. Now, we have the chart area and we also have the plot area. And we also have the chart area. So if we select in here, we have the plot area. And we can change the size of the plot area by dragging the inner box. So we see there is an inner and an outer box.

Let me just move this up a little bit. So we see here, there is an inner and an outer box. This inner box is the plot area whereas the outer box is your chart area. So we can make, we can use both of these to make the chart fit the way we needed to fit. So I’m just lining these up a little bit. Now, I’m going to go to our original chart. And I’m also going to remove the grid lines from our original chart from our scatter chart. We’re going to go back into format area and I want no Line. I don’t want to any border around this chart.

And then what I’m going to do is select both of these charts. So I’ll select the scatter chart and I will select our other chart. I’m going to right-click and I am going to group. Now, by grouping these together, we get one big chart that we can then move around. And both items move around in line with it. [END]