How to Add Average Line to Scatter Plot in Excel (3 Ways)

In some cases, acquired data resembles no correlation at all after inserting it into a Scatter Plot in Excel. Therefore, to find typical yardsticks, users need to add an average line to a Scatter Plot Excel. There are multiple ways, such as using Moving Average Trendlines, Error Bars, and Average Points Trendlines, to add an average line to Scatter Plot Excel.

Let’s say we have an inserted Scatter Plot of the highlighted data in an Excel Worksheet. And we want to add an average line to it.

Dataset-Add Average Line to Scatter Plot Excel

In this article, we demonstrate methods such as inserting Moving Average Lines, Error Bars, and Average Points Trendlines to add an average line to Scatter Plot Excel.


Download Excel Workbook


3 Easy Methods to Add Average Line to Scatter Plot in Excel

Use any of the below methods to add an average line to a Scatter Plot in Excel.


Method 1: Adding Moving Average Line to Scatter Plot in Excel

From the inserted Scatter Chart, we know that we have data with no correlation whatsoever. As a result, we want to add or insert an average line (Horizontal Line as our desired data is in the Y-Axis). We can use the Moving Average Trendline option to add one.

Step 1: Click inside the Chart area. The side options will appear. From the options, click on the Plus Icon > Arrow Sign beside Trendline > More Options.

Moving Average-Add Average Line to Scatter Plot Excel

Step 2: The Format Trendline side window appears. Select Moving Average as Trendline Options. You can change the Period times to best fit your data. As the dataset is comparatively small, a minimum period value (i.e., 2) is chosen.

Period-2

🔺 Excel adds or inserts a Moving Average Line maintaining the average value of each two consecutive values serially. The Moving Average Line is similar to the shown picture below.

Moving Average Trendline

Read More: How to Add Line to Scatter Plot in Excel (3 Practical Examples)


Similar Readings


Method 2: Using Error Bars to Insert Average Line to Scatter Plot

What if users want to add an Average Line to a Correlated Scatter Plot or Chart? Suppose we change the source data to a comparatively correlated data type, such as Total Sales. And we want to add a Horizontal Average Line to the Chart.

Correlated Data-Add Average Line to Scatter Plot Excel

Step 1: First, calculate the average of Total Sales and Total Revenue using the AVERAGE function.

Average Value

Step 2: Right-click within the plot area. The Context Menu will appear. From the Context Menu, click on Select Data.

Select Data

Step 3: Excel opens up the Select Data Source window. Click on Add under Legend Entries.

Add a Data Source

Step 4: Clicking on Add brings up the Edit Series dialog box. In that dialog box, assign the respective values as shown in the screenshot below.

Assigning Data Source

Step 5: Excel adds a new Data Source named Average Values. Afterward, click on OK.

New Data Source-Add Average Line to Scatter Plot Excel

Step 6: Click within the Chart. The side options appear. Click on the Plus Icon > Arrow beside the Error Bars > More Options.

Data Bars

Step 7: Excel fetches the Add Error Bars. Click on Average Values, then OK.

Selecting New Data Source

Step 8: The Format Error Bars side window appears. From the window, click Arrow beside Error Bar Options > Series “Average Values” X Error Bars (you can choose Series “Average Values” Y Error Bars if your desired values are in the X-Axis).

Inserting Horizontal Line

Step 9: In the Format Error Bars window, mark the Percentage under Error Amount, then insert 100 in the value box as depicted in the image. Also, you can choose other options such as Direction, End Style, etc., according to your choice.

Error Percentage

🔺 Finally, Excel inserts an Average Line as depicted below.

Average Error Line--Add Average Line to Scatter Plot Excel

Read More: How to Add Data Labels to Scatter Plot in Excel (2 Easy Ways)


Method 3: Adding Average Line to Scatter Plot Using Two Points

Also, users can insert a data source of two average points in the Chart and then connect them to make an average line. Finding the average points using the AVERAGE function is similar to the previous method. Just add an extra point by assigning the X-Axis values to 0 and the Y-Axis to the average.

Step 1: Repeat Steps 1 to 5 of Method 2 once. Afterward, you get the required two points to insert a line connecting them.

Average Line-Add Average Line to Scatter Plot Excel

Step 2: Click on the Chart Plot area, and the side options appear. Click on the Plus Icon > Arrow beside the Trendline > More Options.

Trendline

Step 3: The Add Trendline window appears. In the window, click on Average Line Values > OK.

Add Trendline

Step 4: Now, the Format Trendline side window appears. In the window, mark Linear as Trendline Options. For clarity purposes, the Linear Trendline option is selected from the Format Trendline side window; otherwise, you can select it from the Trendline options in the previous step.

Linear Trendline

🔺 Excel inserts an Average Line as shown in the below image.

Average Line Connecting Two Points

You can further extend the average line by inserting another point. But here we demonstrate the basics.

Read More: How to Make a Scatter Plot in Excel with Two Sets of Data (in Easy Steps)


Conclusion

In this article, we demonstrate Moving Average Trendline, Error Bars, and Average Points to add an average line to Scatter Plot Excel. You can use any of these methods depending on your convenience. We hope this article sheds light on the ways to insert an average line within a Scatter Chart. Comment if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo