Today, we want to learn how to create a chart on which we have unique targets for each month, group, shop, etc.
The most important task for us is to create proper data. When we do it in the right way, the rest will be much simpler. Let’s start with creating a Base series, which will be the foundation of our chart. We have to check if the income is larger than the target. It means that we have to make a logical test with the IF function. If it’s correct, we want the smaller value, which is the target value. In the other case, we want the income (Fig. 1)
=IF(B2>C2,C2,B2)
And just like that, we have our foundation. Now, we can move on to the next step. If the income is larger than the target, we want to know how much larger it is. We will create it in the Upper column. We start with the IF function again, and we are checking if the income is larger than the target. If so, we want to deduct the target from the income. In the opposite case, we want nothing, i.e. we want our chart to show nothing. It means that we cannot write just an empty text string in the formula in two double quotes. We have to return an error. The simplest way I know is using the NA function (Fig. 2)
And just like that, we have our Upper series. The last thing to do is the Lower series. It is very similar to the previous formula, All we need to change is the ‘larger than’ sign into ‘smaller than’ sign, and deduct the income from the target (Fig. 3)
Just like that, we have the second series.
Now, when we have a value in one of the Upper cells, we will have an error in the same row in the Lower cell, and vice versa. When our data looks OK, we can move on to inserting a chart. We have to select the Months column, as well and the Base, Upper and Lower series. Then, we go to the Insert tab (1), then to the bar chart, where we select the Stacked Column option (2) (Fig. 4)
Now, we have all we need. We have our Base. When our income is larger than the target, we have a blue part, and when we didn’t reach the target, we have green. Let’s change the colors, so that the differences are more visible. I also don’t like the horizontal lines, so let’s delete them by selecting them and clicking the Delete key. I will also widen the columns by selecting them and pressing Ctrl + 1. In the Format Data Series window on the right we can change the Gap Width to 70% (Fig. 5)
Now, I want to add our target. Since we have only column charts here, we can copy our target column, select the chart and press Ctrl + V. Now, we have our target, however, we don’t want to have another column. We can change it into a line chart with marks. We can do it by right-clicking on the target series in the chart. In the pop-up menu, we have a Change Series Chart Type option (Fig. 6)
In the Change Chart Type window, we can change our target by clicking on proper options (Fig. 7)
However, there is one more option. We can go to the Insert tab, and when the series is selected, we choose a proper chart. The result is the same (Fig. 8)
Now, we have to select our line with markers, then press Ctrl + 1 to open the Format Data Series window. When our line chart is selected, we have to go to the bucket part, and select the No line option in the Line part (Fig. 9)
In the Marker part, we can use some built-in markers, like a line. However, when I’m using the line, it’s too thick for me, especially in a bigger size (Fig. 10)
In such a case, we can do another trick. Let’s go to the Insert tab (1), then the Shape part (2), and select a simple Line (3) (Fig. 11)
Now, while holding the Shift key, we can draw a line with our mouse (1), and modify it (2) (Fig. 12)
Now, we can copy the line, then select markers and paste it. I can see that my line isn’t long enough, so i’ll just make it longer, then paste it once again (Fig. 13)
Now, let’s insert data labels. In the Chart Elements window we have to select the Data Labels option. Excel,then will give me data labels for each series (Fig. 14)
However, I don’t want data labels for the target series, so I have to select it and just delete it. Now, It’s important that we have data labels for our upper part only when there is an upper part. In places with errors, there won’t be data labels. The same is with lower parts. Errors won’t show anything on the chart. Let’s change lower data label color by going to the Home tab and selecting white (Fig. 15)
Let’s add the chart title. Let it be the contend of cell B1. We write = in the formula bar, then click the cell and press Enter (Fig. 16)
=‘E061’!$B$1
There is one more thing. I sometimes don’t want to show the target series in the legend. To remove it, we have to click on the legend, then once more on the target series in the legend, then press Delete (Fig. 17)
Now, I’m happy with my chart with unique target for months, groups, shops, etc.