How to Add Mean to Excel Scatter Plots

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
Each data point can change a series' average.

Microsoft Excel's scatter plots offer trendlines that plot the data's moving average. The resultant line shows how each new point changes the data's mean. The chart doesn't directly track the data's singular mean, however. To add that mean to a scatter plot, create a separate data series that plots the mean against your data's x-axis values. This series can use Excel's "Average" function to find the mean of the other series' data.

Advertisement

Step 1

Click a cell adjacent to the first cell in your original data series.

Video of the Day

Step 2

Type "=AVERAGE(" into this cell.

Step 3

Click and drag your cursor over all the cells of your original series to select them. Their cell references will appear in the formula you have created.

Advertisement

Step 4

Insert "$" signs in the pasted cell references. This locks their values when you transfer the formula to other cells. For instance, if the cell reads "=AVERAGE(E8:E16)," add dollar signs to change it to "=AVERAGE($E$8:$E$16)."

Step 5

Press the "Enter" key. The cell now displays the data's mean.

Advertisement

Step 6

Move your cursor to the small black rectangle in the cell's lower-right corner. The cursor changes from a white cross to a black one.

Step 7

Click and drag the rectangle until you have highlighted a whole series of cells parallel to the original series. The cells all now display the original series' mean.

Advertisement

Step 8

Right-click the legend from the scatter plot chart. Click "Select Data" from the menu that opens. This opens the "Select Data Source" dialog box.

Advertisement

Step 9

Click the "Add" button, which opens the "Edit Series" dialog box.

Advertisement

Step 10

Type "Mean" into the "Series name" text box.

Step 11

Click the "Select Range" button next to the "Series X values" text box.

Step 12

Click and drag your cursor over the original series's x-values to select them.

Advertisement

Step 13

Click the "Select Range" button next to the "Series Y values" text box.

Step 14

Click and drag your cursor over the series of cells that you created in Step 7.

Step 15

Click "OK" in the "Edit Series" and "Select Data Source" dialog boxes. Points now appear on the scatter plot marking the data's mean.

Advertisement

Step 16

Right-click any of these new points. Click "Change Series Chart Type" from the menu that opens. This opens the "Change Chart Type" dialog box.

Step 17

Click the third icon under the "X Y (Scatter)" heading, which corresponds with "Scatter with Smooth Lines."

Step 18

Click "OK." A solid line labeled "Mean" now appears on the scatter plot, marking the mean of your original data.

Video of the Day

Advertisement

Advertisement

references & resources