Maruf Islam
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.
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.
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.
🔺 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.
Read More: How to Add Line to Scatter Plot in Excel (3 Practical Examples)
Similar Readings
- How to Add Text to Scatter Plot in Excel (2 Easy Ways)
- How to Connect Dots in Scatter Plot in Excel (with Easy Steps)
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.
Step 1: First, calculate the average of Total Sales and Total Revenue using the AVERAGE function.
Step 2: Right-click within the plot area. The Context Menu will appear. From the Context Menu, click on Select Data.
Step 3: Excel opens up the Select Data Source window. Click on Add under Legend Entries.
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.
Step 5: Excel adds a new Data Source named Average Values. Afterward, click on OK.
Step 6: Click within the Chart. The side options appear. Click on the Plus Icon > Arrow beside the Error Bars > More Options.
Step 7: Excel fetches the Add Error Bars. Click on Average Values, then OK.
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).
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.
🔺 Finally, Excel inserts an Average Line as depicted below.
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.
Step 2: Click on the Chart Plot area, and the side options appear. Click on the Plus Icon > Arrow beside the Trendline > More Options.
Step 3: The Add Trendline window appears. In the window, click on Average Line Values > OK.
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.
🔺 Excel inserts an Average Line as shown in the below image.
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.
