How to Add a Target Line to an Excel chart
Today, we want to learn how to add a Target Line to our chart and control it.
Sometimes, we want to lower the line, sometimes we want to raise it. But, firs things first.
Let’s start with writing the target. Let’s write 700. Then, we have to create a simple formula without any dollars in the first cell below (Fig. 1)
=C2
Now, we can copy the formula down. And what’s happened? Each cell is referring to the cell above it. At the end, they are going to the first cell (Fig. 2)
It means that when we change the value in the first cell, the values in the cells below also change (Fig. 3)
Now, when our data is properly prepared, we can select the whole data, go to the Insert tab (1), then go to Charts and choose the column chart option (2) (Fig. 4)
We have a chart but no line. To create one, we have to select the Target series, go to the Insert tab (1), and choose a line chart from the Charts area (2) (Fig. 5)
When we have the line, we can right-click it and choose the Change Series Chart Type option from the pop-up menu (Fig. 6)
In the window that has appeared, we can change the chart type to any we like (Fig. 7)
However, we have already chosen the type we wanted. Let’s change the value in the first cell in the Target column. What we can see is that the line has changed its position (Fig. 8)
We can also calculate the average of our data using the AVERAGE function (Fig. 9)
=AVERAGE(B2:B13)
Now, we have an average line for our data (Fig. 10)
