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.
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.
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.
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.
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.
Step 9
Click the "Add" button, which opens the "Edit Series" dialog box.
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.
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.
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