Moving Average in Excel Charts

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Adding a moving average line to an Excel Chart is very easy, just a few clicks, with many options.  Any chart with a series of values over time will benefit from a trendline like a moving average.

Excel Moving Average opt - Moving Average in Excel Charts

What is a moving average?

An average / mean is a single value representing a list of numbers. Add all the values together and divide by the number of values.  In statistics it’s one of the measures of central tendency.

A moving average is great to see trends over time.  For each value, take an average of the last, say, 2 values then do that for all the values.  The result is a two period moving average.

Example:   a series   1, 2, 3, 4, 1  the moving averages are 3, 5,7 and 5.

Excel Moving averages

Calculating a moving average is quite simple in Excel.  Create a SUM() for the number of values you want (two in this case) and divide by the number of values then copy that formula down the column.  Or use the AVERAGE() function.

moving average in excel charts microsoft 365 37521 - Moving Average in Excel Charts

Excel will show an error warning ‘Formula Omits Adjacent Cells’ which you can clear via ‘Ignore Error’.

moving average in excel charts microsoft 365 37522 - Moving Average in Excel Charts

However you do NOT have to do that calculation for an Excel Chart.  Excel can add trendlines including Moving Averages into a chart without needing to fill any cells.

Make a Chart

Add a chart with the date/value pairs in the usual way.

moving average in excel charts microsoft 365 37523 - Moving Average in Excel Charts

Add a Trendline

Choose the Chart Elements “ + “ button and select ‘Trendline’.  That adds a default trendline which we’ve colored orange so it stands out.

moving average in excel charts microsoft 365 37524 - Moving Average in Excel Charts

Click on the Trendline then, from the Format Trendline side pane, choose Trendline Options.

moving average in excel charts microsoft 365 37525 - Moving Average in Excel Charts

You can see that Microsoft’s default is a simple linear trendline or ‘straight line’.  There are other trendline options: Exponential, Logarithmic, Polynomial, Power and the one we’re focusing on Moving Average.

Add Moving Average line in Excel Chart

Choose ‘Moving Average’ and the trendline changes a lot. The default is a 2 period moving average, the same as the column calculation we did earlier.

moving average in excel charts microsoft 365 37526 - Moving Average in Excel Charts

We’ve changed the daily results line to a faded shade to make the trendline / moving average stand out.

For a smoother trendline, increase the moving average period setting. Here’s the 6 period moving average, compare that with the more jagged 2 period line above.

moving average in excel charts microsoft 365 37527 - Moving Average in Excel Charts

 

Excel Charts – give them a try

Excel charts – It’s all in the data

Saving an Excel Chart as a Template

Excel 2016 – Jan 2016 update – new chart and functions

subs profile e1563205311409 - Moving Average in Excel Charts
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address