There are two ways of adding trend/best fit line - Statistics and Regression analyses. First one is designed for typical Time charts, second for Scatter charts.
Note, it is also possible to omit both those analyses and just use calculations recreating what Regression does and present outcome on Category scatter chart.
Line from Statistics
In the Statistics analysis, add a 'Trend' on the series of interest. If needed, set the data range on which the trend line should be calculated, and untick 'Extrapolate' if you want the trend line only to be drawn on the defined range of calculation.
This will yield a trend line calculated using linear regression.
Line from Regression
This is the only way to add best fit line in a Scatter chart.
- Check boxes for 'Output the dependent series' and 'Output the explanatory series'.
- Check 'Include' for at least two series and mark one as 'Is dependent'.
- Add Scatter chart. Go there and open Graph layout (Ctrl+L).
- Pair series to generate a regression line. Make sure to set the right order of the series in Graph layout window (Note that when series is lagged there won't be a straight line as outcome.):
a) Pair #1: the explanatory and the dependent series
b) Pair #2: the explanatory and the predicted series
Line A Line B series 1 series 1 series 2 series 2 [predicted] - Click on one of the lines, go to Presentation properties > Appearance, change Graph style to Custom. Then set Line to None and select Marker style.
Example file: Regression line
Line through calculations
We recommend using Regression analysis, as it is shorter way, but it is also possible to recreate all calculations without Regression and represent best fit line on Category Scatter chart.
Example file: Best fit line for last values of group of series with and WITHOUT Regression analysis
For more information: