Trendline in Excel

Trendlines are chart features that help illustrate patterns in a set of data. They offer directional insight and assist in visualizing trends. They’re easy to add inside spreadsheets. Let’s learn how to add a trendline in Microsoft Excel.

How to Add a Trendline in Excel

Trendlines in Excel are the perfect way to instantly visualize correlation between, or general trends within, your data. They work with an assortment of chart types in Excel, giving you plenty of versatility.

Trendlines are most often found on scatter plots. Scatter plots are simple but powerful graphs. They track a pair of variables across the X and Y axis, representing individual data points with dots.

Consider a real-world example. Imagine that you’re a teacher, and you want to track test scores relative to hours students spend studying for the test. You have a hypothesis that, as study time increases, test scores increase as well. This is a logical guess, but you need to gather data to prove it. A scatter plot with a trendline will serve as an easy visual.

Begin by inputting your data into a pair of columns somewhere on your spreadsheet. Test Score can be listed in column A, and Time Studied can be listed in column B. This is the core data for your project. Excel will construct a scatter plot, and then a trendline, around this data.

Then, click and drag to select your range. Here, that’s cells A2:B6. Then, go to the Insert tab on the ribbon, and open Recommended Charts. Excel’s first suggestion is Scatter, which we’re planning to use. But you can also construct trendlines for bar charts, column charts, line charts, and more. The process is the same.

Click Scatter, and you’ll see Excel add a basic scatter plot outlining your data. As you can see, there’s an obvious relationship between the variables, as expected. But a trendline will help you smoothly map up the specific trend. Trendlines don’t touch the specific data points. They’re directional indicators, and represent best-fit lines, rather than exact-fit lines.

Now, Excel has inserted a scatter plot, but you may want to make a few adjustments. Notice that the test scores begin at 70, but Excel starts the chart at 0. This leaves too much empty space.

To fix it, right-click on the horizontal axis, then choose Format Axis. On the Format Axis sidebar on the right side of your screen, you can easily adjust the minimum and maximum bounds of the chart. 60 is a good starting point, with 100 as the upper limit. Make the changes, and the chart will update itself.

You can also add Axis Titles to ensure your chart is easier to understand. Find the Chart Design tab on Excel’s ribbon, then click on the Add Chart Element dropdown on the far left. Hover over Axis Title, and choose to add Primary Horizontal and Primary Vertical titles. These appear on your chart, and you can fill them in like any other text box in Excel.

Now, it’s time to add your trendline. With your chart still selected, go back to the Add Chart Element dropdown. Hover over the Trendline option. You’ll see several options, including:

• Linear
• Exponential
• Linear Forecast
• Moving Average

The latter three let you perform complex analyses with advanced datasets. But in most basic instances, Linear works best. Go ahead and click on it.

You’ll see the trendline appear as a dotted line on your chart. Notice that Excel has attempted to find the line of best fit. This demonstrates a clear upward trajectory in both variables, indicating a strong correlation. In other words, as study time increases, test scores increase.

As you can see, trendlines are a powerful, yet easy way, to illustrate correlations in Microsoft Excel.