Target Chart with Unique targets 

by | Jul 13, 2023 | Excel

Today, we want to learn how to cre­ate a chart on which we have unique tar­gets for each month, group, shop, etc. 

Tar­get Chart with Unique targets

The most impor­tant task for us is to cre­ate prop­er data. When we do it in the right way, the rest will be much sim­pler. Let’s start with cre­at­ing a Base series, which will be the foun­da­tion of our chart. We have to check if the income is larg­er than the tar­get. It means that we have to make a log­i­cal test with the IF func­tion. If it’s cor­rect, we want the small­er val­ue, which is the tar­get val­ue. In the oth­er case, we want the income (Fig. 1)

=IF(B2>C2,C2,B2)

IF function for the Base series
Fig. 1 IF func­tion for the Base series

And just like that, we have our foun­da­tion. Now, we can move on to the next step. If the income is larg­er than the tar­get, we want to know how much larg­er it is. We will cre­ate it in the Upper col­umn. We start with the IF func­tion again, and we are check­ing if the income is larg­er than the tar­get. If so, we want to deduct the tar­get from the income. In the oppo­site case, we want noth­ing, i.e. we want our chart to show noth­ing. It means that we can­not write just an emp­ty text string in the for­mu­la in two dou­ble quotes. We have to return an error. The sim­plest way I know is using the NA func­tion (Fig. 2)

IF function for the Upper series
Fig. 2 IF func­tion for the Upper series

And just like that, we have our Upper series. The last thing to do is the Low­er series. It is very sim­i­lar to the pre­vi­ous for­mu­la, All we need to change is the ‘larg­er than’ sign into ‘small­er than’ sign, and deduct the income from the tar­get (Fig. 3)

IF function for the Lower series
Fig. 3 IF func­tion for the Low­er series

Just like that, we have the sec­ond series. 

Now, when we have a val­ue in one of the Upper cells, we will have an error in the same row in the Low­er cell, and vice ver­sa. When our data looks OK, we can move on to insert­ing a chart. We have to select the Months col­umn, as well and the Base, Upper and Low­er series. Then, we go to the Insert tab (1), then to the bar chart, where we select the Stacked Column option (2) (Fig. 4)

Creating a stacked column
Fig. 4 Cre­at­ing a stacked column

Now, we have all we need. We have our Base. When our income is larg­er than the tar­get, we have a blue part, and when we did­n’t reach the tar­get, we have green. Let’s change the col­ors, so that the dif­fer­ences are more vis­i­ble. I also don’t like the hor­i­zon­tal lines, so let’s delete them by select­ing them and click­ing the Delete key. I will also widen the columns by select­ing them and press­ing Ctrl + 1. In the For­mat Data Series win­dow on the right we can change the Gap Width to 70% (Fig. 5)

Gap Width option
Fig. 5 Gap Width option

Now, I want to add our tar­get. Since we have only col­umn charts here, we can copy our tar­get col­umn, select the chart and press Ctrl + V. Now, we have our tar­get, how­ev­er, we don’t want to have anoth­er col­umn. We can change it into a line chart with marks. We can do it by right-click­ing on the tar­get series in the chart. In the pop-up menu, we have a Change Series Chart Type option (Fig. 6)

Change Series Chart Type option
Fig. 6 Change Series Chart Type option

In the Change Chart Type win­dow, we can change our tar­get by click­ing on prop­er options (Fig. 7)

Line with Markers option
Fig. 7 Line with Mark­ers option

How­ev­er, there is one more option. We can go to the Insert tab, and when the series is select­ed, we choose a prop­er chart. The result is the same (Fig. 8)

Line with Markers option
Fig. 8 Line with Mark­ers option

Now, we have to select our line with mark­ers, then press Ctrl + 1 to open the For­mat Data Series win­dow. When our line chart is select­ed, we have to go to the buck­et part, and select the No line option in the Line part (Fig. 9)

No line option
Fig. 9 No line option

In the Marker part, we can use some built-in mark­ers, like a line. How­ev­er, when I’m using the line, it’s too thick for me, espe­cial­ly in a big­ger size (Fig. 10)

Bigger size
Fig. 10 Big­ger size 

In such a case, we can do anoth­er trick. Let’s go to the Insert tab (1), then the Shape part (2), and select a sim­ple Line (3) (Fig. 11)

Line option
Fig. 11 Line option

Now, while hold­ing the Shift key, we can draw a line with our mouse (1), and mod­i­fy it (2) (Fig. 12)

Line modification
Fig. 12 Line modification

Now, we can copy the line, then select mark­ers 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)

Longer line
Fig. 13 Longer line

Now, let’s insert data labels. In the Chart Ele­ments win­dow we have to select the Data Labels option. Excel,then will give me data labels for each series (Fig. 14)

Chart elements window
Fig. 14 Chart ele­ments window

How­ev­er, I don’t want data labels for the tar­get series, so I have to select it and just delete it. Now, It’s impor­tant 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 low­er parts. Errors won’t show any­thing on the chart. Let’s change low­er data label col­or by going to the Home tab and select­ing white (Fig. 15)

Color change
Fig. 15 Col­or change

Let’s add the chart title. Let it be the con­tend of cell B1. We write = in the for­mu­la bar, then click the cell and press Enter (Fig. 16)

=‘E061’!$B$1

Chart title
Fig. 16 Chart title

There is one more thing. I some­times don’t want to show the tar­get series in the leg­end. To remove it, we have to click on the leg­end, then once more on the tar­get series in the leg­end, then press Delete (Fig. 17)

Final chart
Fig. 17 Final chart

Now, I’m hap­py with my chart with unique tar­get for months, groups, shops, etc. 

https://www.youtube.com/watch?v=_UmfRhNT9v4&t

Chart Secondary Axis

by | May 1, 2023 | Excel

Today, we are going to talk about putting two dif­fer­ent series into one chart.

Sec­ondary Axis on Excel Chart Tem­per­a­ture and rainfall

If you have two series that dif­fer from each oth­er, e.g. they have dif­fer­ent units or one of them is much big­ger than the oth­er, then you should use the Sec­ondary Axis on you chart. How can you do it? From Excel 2013 the task is sim­ple as Microsoft insert­ed the Com­bo chart. Our exam­ple has sim­ple data, so we just have to click on one cell, then go to the Insert tab, where we can find a Com­bo chart with the Sec­ondary Axis (Fig. 1)

Combo chart with secondary axis
Fig. 1 Com­bo chart with sec­ondary axis

I can see that not every­thing is as I want­ed, that’s why I’m going to make some changes. I just select the chart and go to the Chart Design tab, where I can find the Change Chart Type option. In the win­dow that appeared, we change the type of each series. The rain­fall is on the sec­ondary axis, which is good, how­ev­er, I pre­fer the rain­fall to be pre­sent­ed as a col­umn chart, and I will put the tem­per­a­ture into a line chart with mark­ers (Fig. 2)

Column chart and line chart with markers
Fig. 2 Col­umn chart and line chart with markers

After press­ing OK, we can see a fin­ished chart with two val­ues (Fig. 3)

A finished chart with two values
Fig. 3 A fin­ished chart with two values

But, how can we do it in Excel from before 2013? Let’s insert a sim­ple col­umn chart by going to the Insert tab, then choos­ing the prop­er col­umn chart (Fig. 4)

Column chart
Fig. 4 Col­umn chart

As our val­ues dif­fer much in size, where the rain­fall is sig­nif­i­cant­ly big­ger than the tem­per­a­ture, I would like to have the rain­fall series on a sec­ondary axis. I have to select the hole series by click­ing once on the series ele­ment, then press Ctrl + 1, find the series option, go to Plot Series On, and choose Sec­ondary Axis (Fig. 5)

Series options
Fig. 5 Series options

And, just like that we have a dif­fer­ent axis for rain­fall, and a dif­fer­ent axis for tem­per­a­ture. There is still one thing we need to change, which is the type of tem­per­a­ture series, because now one series is behind the oth­er and we don’t know how high some columns are. Let’s click one time on the series and go to the Insert tab, where we can choose a new chart type. Let’s choose the Line with Mark­ers option (Fig. 6)

Line chart with markers
Fig. 6 Line chart with markers

Just like that, I have a chart with a sec­ondary axis and dif­fer­ent chart types. Let’s change the name of the chart into Tem­per­a­ture and rain. The chart is fin­ished (Fig. 7)

Finished chart
Fig. 7 Fin­ished chart

https://www.youtube.com/watch?v=L7K2_jejIHg