Here’s how to add a trendline to your charts in Excel. It’s a great way to make your charts easily interpreted at a glance, as the line will indicate to viewers where the general trend of your data is pointing.
It can also be very helpful to extend the line beyond existing data to communicate a forecast of future trends. Trend lines can be added to a variety of charts, including bar charts, line charts, scatter plots and more, but they’re not an option for 3-D charts, pie charts or other similar chart configurations.
- In Excel, once you have a chart with data in it, click on the green “+” icon in the upper right-hand corner of the chart and check off the Trendline box.
To use different types of trendlines:
- You will notice that there is a sideways arrow next to the Trendline box where you can choose different types of trendlines: exponential, linear forecast, or two period moving average. These are the most commonly used options, which is why they are here, but you can also click into More Options to get more ways to configure your trendline, including a logarithmic or power line.
- Now, if you want to use a moving average instead of a linear trendline, you can go into More Options and select Moving Average and indicate how many periods the moving average should use.
- You’ll also see an option to display an R-squared value. To give you the short version of the statistics involved, an R-squared value will land somewhere between 0 and 1. The closer it is to one, the closer your actual data is to the data indicated by the trendline match. So, if you’re extending a trendline as a prediction into the future, checking this would help people see how closely your current data matches the trend, with a closer match giving better evidence that the trend may continue with all factors being equal.