While Excel can calculate a range of descriptive and logical statistics for you, it is often better to show a visual representation of the data when presenting information to a group.
Using the built-in trendline function in Excel, you can add a linear regression trendline to any Excel scatter plot.
Insert a scatter chart into Excel
Suppose you have two columns of data in Excel and you want to insert a scatter chart to check the relationship between two variables.
Start by selecting data in two columns. Then click the Insert tab on the Ribbon and locate the Charts section. Click the button labeled Scatter and then select the button from the Scatter with Only Markers menu.
In newer versions of Excel, scatter charts will appear as a small button with a graph and dots, as shown below. Also, select Scatter only from the dropdown.
You should now have a scatter chart with the data represented in the chart.
Add trendline in Excel
Add a trendline to Excel
Now that you have a scatter chart in your Excel sheet, you can add a trend line. First, click once on any data point in the scatter plot. This can be tricky because there are many chart elements that you can click and edit.
You know you have selected a data point when all data points are selected. After selecting the data points, right-click any data point and choose Add Trend Line from the menu.
You should now see the Format Trend Line window. This window contains many options for adding a trend line to an Excel scatter chart.
Note that you can add an exponential, linear, logarithmic, polynomial, power, or moving average trend / regression line.
Leave the default Linear selected for now. Click the Close button and your chart should now display a linear regression trendline.
As with everything in Microsoft Office, you can format the trendline to look exactly the way you want it. In the next section, we’ll discuss some of the more popular changes you can make to your trendline to highlight it.
Excel Trendline Formatting
Excel trendline format
To format the newly created trendline, start by right-clicking the line and choosing Format Trendline from the menu. Excel will reopen the Format Trend Line panel.
One of the more popular options for adding a trendline in Excel is to display the line equation and R-square value directly on the chart. You can find and select these options at the bottom of the window. For now, choose both of these options.
Let’s say we want our trend line to appear more clearly on the chart. After all, a trendline is only one pixel wide by default and can sometimes disappear among colors and other elements in the graph. On the left side of the Format Trend Line window, click the Fill and Line icon.
In this window, change the Width from 0.75 points to about 3 points and change the Dash to Square Point (third item in the drop-down menu). To demonstrate that this is possible, change the End Type parameter to an arrow.
When you’re done, click the X button in the Format Trend Line panel and notice the changes in the scatter plot. Notice that the line equation and R-squared values ??are now displayed in the chart and that the trendline is more prominent in the chart.
Like many other features in Excel, there are almost limitless possibilities when displaying a trend line in a scatter chart.
You can change the color and thickness of the line, and even add 3D elements to it, such as a shading effect (click the Effects icon).
Which you choose depends on how brightly you want the trend line to stand out on the chart. Experiment with the options and you can easily create a professional looking trendline in Excel. Enjoy!
–