If you’d like to know more about other interesting chart elements, to learn how you can add and format them, have a look at more tutorials by Eas圜lick Academy. So, if you use this way to add the target line, Excel will do all the work of keeping the whole chart up to date for you. Here we can choose Line only for the Target data, and Excel will show the target value as a horizontal line.Ī huge advantage of all this is that the whole system is dynamic, which means that if you need to change the value 60 to 80, Excel will refresh the whole table and the data display in the graph, too. We want to combine two types of graphs, so we’ll go for Combo down here. If we select Line here, we’ll get both the Target as well as the Sales data shown as lines, as you can see in this preview.īut that’s not what we want.
#Combine bar and line graph excel how to#
All target values get marked with these small blue circles, which means we can now make changes.įind the tab Chart Design and select the option Change Chart Type.īut watch out now! How to Combine Two Types of Graphs
So let’s fix this together!įirst, click on any bar displaying the target value in the graph. The best way to show the target value across all months would be a horizontal line. There’s a catch though! The target data are displayed as bars, which is not very convenient. Hover over the bottom right corner and click and drag the highlighted area of the table to extend the selection to the column Target.Įxcel immediately includes this new set of data in the graph. The easiest way to get these new data in the chart is to double-click into the chart area to highlight all the data already shown in the graph. We need to show the target value as a horizontal line in the graph. This has been solved, so let’s carry on now! How to Show the Target Value as a Horizontal Line in the Graph If we change the number in D3 to – let’s say – 60 and press Enter, the change in the value will be reflected through the whole column. The target value in each row will now always copy the value entered in the cell D3. Now you see that the cell coordinates D3 have been marked with dollar signs, which means that the cell reference in the formula is fixed and we can simply copy it by dragging the right bottom corner of the cell to the rest of the rows in the column. Some of you might not have to press the Function key – this depends on the type of keyboard you’re using. Now we need to make sure that cell coordinates will not change when this simple formula is copied into the rest of the rows.Ĭlick on the part indicating the cell reference (D3) in the formula, press the Function key and F4. How to Fix the Cell Reference and Copy the FormulaĬlick into the cell below, which is D4, type in the equal sign and click again on D3. It’s quite handy to be able to change this value whenever you need, so we’ll make sure that the rest of the rows will take the target value from this cell.
We also adjust the formatting of the table to make it consistent and move on.Ĭlick into the cell in the first row of the column Target and type in a value, for example 70. Let’s insert another column next to the column Sales and name it Target.
If you need to show a target value in a graph, the first step is to define it. 4 How to Combine Two Types of Graphs How to Define a Target Value in a Graph
0 kommentar(er)
