Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Trend Lines and Error Bars in a chart can add additional information to what's already displayed. We'll begin with trend lines, which can help you to see trends in your data. Now, these lines are calculated and drawn to fit your data according to the type of equation you prefer. Let's use the Utility Cost chart in our Energy Saving Plan spreadsheet. The first step is to select the chart. Now, we'll go to the Chart Inspector, click the Inspector button on the Toolbar, make sure that Chart Inspector is selected, the Series button, and now we'll open up the Advanced section by clicking the disclosure triangle next to Advanced.
Here's where we'll find the Trendline button, and now to show a trend line say for the year 2008, we simply select any of the bars for 2008. This selects the entire series. We'll go back to the Chart Inspector now, and you'll notice under trend line that None is the default. But when we click the button, we see a list of trend line types to choose from. Linear produces a best-fit straight line and it's most useful for simple linear data series like what we have here on this chart.
The other types like Logarithmic produce a best-fit curved line, which is really useful when values increase or decrease quickly and then level out. Polynomial is a little bit different. It also produces a curved line, but it has hills and valleys to show where values rise or fall. Then there is Power, which produces a curved line for use when you want to compare measurements that increase at a specific rate. Exponential produces a curved line again for use though when values rise or fall at increasingly higher rates.
Then Moving Average is going to produce a trend line using the number of data points you specify in the Period field, and then it will average them out and display a trend line using that average. Let's select Linear. Right away we see the straight line that is produced when we choose Linear, displaying the trend in our data for 2008. Now, we also have some additional options down below. Here, you'll see we have got a check box next to Label. Select that if you want to add a label. In the Label field, you can type whatever you like. In this case, let's type in Trend for 2008.
When you press Return on the keyboard, it's locked in and you will notice we have got a new entry in our Legend at the bottom of our chart showing the trend for 2008. If you want to show the equation as well which describes the trend line we selected, we can select Show Equation. The equation pops up in our chart and we can move this around. Let's click once to select the equation, and let's move it where we can read it a little bit better. If you don't want to display the label, no problem, select the trend line, and then again in the Chart Inspector, deselect Show Equation.
You can even show the R2 value that was used to calculate our trend line by selecting the check-box next to Show R2 value but we'll leave that out for this particular example. Now, let's close our Chart Inspector and deselect the chart to see the end result. If we wanted to change the actual line color maybe the thickness for example, so we can see it better, we can go back to our chart, click once to select it, click the trend line. So it's selected. You will see handles at each end, and now we can use our Format Bar.
In the top left corner, you can see a solid line is being used. You want to choose a different line style, such as Dots for example, you can do that. We can change the thickness. Let's choose 4 points, and so it really stands out. Let's change the color, click the Color Fill button, and let's choose Black. Now we can really see that trend. Again, we'll deselect by clicking the Canvas to see the end result better. All right, if your data also contains a potential margin for error, you can display that in the chart using Error Bars. Let's scroll down a little bit where we've got a second chart here, Costs by Type. Click once to select that chart, we'll go back to our Inspector and in the Chart Inspector, under the Advanced section, we'll select Error Bars. Notice we don't have any Error Bars at this time.
Now, let's say we wanted to display a margin of error for plus or minus 10%. Well, in that case, we click the button that currently displays None and to show both positive and negative numbers, we choose Positive and Negative. Now we can type in a Fixed Value or if we click the button displaying Fixed Value, change it to Percent or Percentage where you've got other options here as well, Standard Deviation, Standard Error, and even Custom. But with Percentage selected, we can choose the percentage we want to display.
We can type in 10, we can use the Arrow buttons to change the value. We'll leave it at 10% and when we look at our chart, you will see the Error Bars going above and below the actual data in our chart displaying a positive and negative value, which represents that 10% margin of error. Let's deselect our chart and close up the Chart Inspector, and there we have it. So even though the trends and margin for error don't appear as data in our tables, we can still display the results. Thanks to these Trend Lines and Error Bars in the Chart Inspector.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97711 Viewers
80 Video lessons · 141125 Viewers
59 Video lessons · 59479 Viewers
52 Video lessons · 72781 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.