#Insert a linein chart how to
If you’d like to know more, for example how to add a trendline or how to work with more chart elements, watch separate tutorials by Eas圜lick Academy! Links to the tutorials are in the list below.ĭon’t miss out a great opportunity to learn: So, in case there’s a change in the input data, you don’t have to redo the whole thing again manually.
#Insert a linein chart update
If you change the November Sales value from 123 to 22, Excel will immediately recalculate the average value and update the display in the chart, too. The rest will stay as is, and Excel will show the average value as a line.Īgain, a huge benefit in adding an average line this way is that the whole graph is dynamic.
Thanks to this option, we can select only the data from Average to display as a line graph. That’s why we need to combine two types of graphs, so we’ll select the option ‘Combo’. If we actually clicked on ‘Line’, Excel would turn into a line graph not only the average value, but also the Sales data, so both graphs would be line graphs, and we want the Sales data to appear as they were in the original chart. It seems pretty straightforward here – we want a line, so we’ll click on ‘Line’, but watch out! Let’s click on the tab Chart Design here at the top and choose the option ‘Change Chart Type’. All values have now been marked with these little blue circles. To change the way the Average data is displayed, click on any value from Average in the chart. How to Change the Way the Average Data Is Displayed The average value now shows as a bar next to the bar for every month, but we want it to be displayed as a horizontal line running through the graph. You can see that Excel included the new data from the Average column in the chart.
The range of data already displayed in the chart has been highlighted in the table.Ĭlick and drag the bottom right corner of the selection to expand it to the Average column. The easiest way to include the average value as a line into the chart is to click anywhere near the chart. The result is now correct in each row, so we can carry on with adding the average line into our chart.īut, how to do that? The Easiest Way How to Add an Average line in an Excel Graph Now when the values have been fixed, we can press Enter and copy the function to the rest of the rows in the column. This is called an ‘absolute’ reference as opposed to the so-called ‘relative’ reference we had in our formula just a moment ago. Some of you might not need to press the Function key – this depends on what type of keyboard you’ve got on your computer.Ī dollar sign appeared right next to the letter and the number, which means that this reference has been fixed and it won’t change as we copy the formula in the rows below. This can easily be done by clicking into the reference itself and then pressing the Function key and F4. Let’s click into the cell D3 and ‘pin’ the reference to the first point of the range – C3. So, we need to anchor the references to the cells.
To ensure the function calculates the correct value in each row, we need to make sure that the formula refers to the same range in every instance. This, of course, is something that needs to be fixed. This is because as we copied the formula, the cell references of the range moved with each row.įor example, here in row 3, we’ve got the specified cell range C3 to C14, but in row 4, the range shifted down by one, so we’ve got C4 to C15. However, if we copy the function as it is, every month shows a different number, not the correct average value. Now we need to copy the function to every row in this column, so that the average could appear as a line through every month in the chart. Here the range will be all entries in the column Sales.Īnd we’ll close the brackets to see the result. Click on the suggested function and select all the data from which we’re going to calculate the average. We’re also going to adjust the formatting of the column to make it look like the rest of the table.Ĭlick into the first row of the column Average and calculate the average value by entering the equal sign and typing in AVERAGE. Let’s insert a new column right next to the column with sales and name it ‘Average’. If we need to show the average value in a chart, we need to calculate it first. 4 How to Change the Way the Average Data Is Displayed How to Calculate Average
0 kommentar(er)
