How to Create Trends and Average lines on a Chart in Excel

Charts

A chart is a way to visually represent data.  Best example is a stock chart that plots the price of a stock against time. Note that the description below uses Excel 2013. Your version of Excel may be slightly different.

The ability to read a chart and make a trading decision is called technical analysis. A good usage of Excel is to teach you whether technical analysis actually works.

But back to charts.

The first thing is to create a basic chart. Open Excel and enter in cell A1 the formula

=randbetween(1,100)

This will randomly put in a number from 1 to 100 in the cell.

Copy this formula around 10 times to cells A2 thru A10 commonly written as A2:A10.

From the Ribbon select Insert - Line Chart - Line.

The following type chart will appear on your sheet.

Your chart should look the same but with different values.

Trends

A trendline can show you where you data is going usually over a time frame or range of dates. I use a trendline in the Excel Diet to help you loss weight and keep it off.  Some people use trendlines to predict future stock prices.

A benefit of a trendline is that slight changes doesn't affect the trend.

To create a trend-line, right-click the line in the chart and select Add Trendline from the popup menu.

If you don't like the ..... line you can left-click the trendline and then select Format Trendline. Near the top of the section there is a bucket spilling paint. In Dash Type select the Solid Line.




Now trends of random data is useless but trends can be used to show where you're going. It can be used to show the trend of:

• Your weight
• Miles driven in a car
• Price of a stock
• Number of tasks in your to-do list
• Change in productive tasks

Average

The most common usage of an average was in school or in baseball.  In school your average is the sum of your grades divided by the number of grades.

To get an average line, assuming you have 10 values in A1:A10, in cell B1 enter =Average(A1:A10)

In cells B2:B10 enter the formula =$B$1.

If you keep pressing the F9 key (note that you might have to press Fn-F9 on some computers) the chart will continually change.

To add an Average line select the chart and delete it, then select the data range then Insert -Line Chart and you should see the following:

Summary

Don't be afraid to experiment with trendlines and averages.You have nothing to lose except for ignorance.

If you liked or disliked this post please provide me with some feedback in the comment section below or tweet on twitter at @Alecberg