You might think of a moving average to predict stocks or investments. But you can also use it to see trends for inventory, sales, or similar data. Without equations or functions, you can easily calculate a moving average in Excel.
Add the data analysis tool in Excel
Although you may perform calculations and create a line chart to view your moving average in Excel, there is an easier way.
Microsoft offers a free Analysis ToolPak that you can add to Excel. The tool allows you to develop statistical or engineering analyzes with a variety of functions. One of these options is a moving average.
To see if you already have Analysis Tools, go to the Data tab and look for the Data Analysis button in the Analysis section of the ribbon.
If you have the button, you’re good to go and can move on to calculating a moving average below. If not, here’s how to quickly add it.
Go to the search box at the top of Excel and enter “Add-ins”. When you see Add-ons in the list, select it.
Alternatively, click File> Options and select “Add-ons” on the left. At the bottom of the window, next to Manage, select “Excel Add-ins” and click “Go”.
After the add-ons window appears, check the box next to Analysis Tools and click “OK”.
Now, when you go to the Data tab, you should see the Data Analysis button in the ribbon.
When you are ready to calculate the moving average, click the Data Analysis button on the Data tab. Select “Moving Average” from the list and click “OK”.
At the top of the Moving Average window, enter the input range in the corresponding box. You can also click inside the box and then drag the data range. Optionally, you can check the “Labels in first row” box and include a range.
Then, enter the output range by entering a cell reference or clicking inside the box and selecting a cell on the sheet. Optionally, you can check the Standard Errors box. And to create a moving average graph and receive the results, check the Output Graph box.
When you are done setting the moving average, click “OK” and you will see the output. Values begin in the cell selected for output. If you have the graph output selected, you will have a handy line graph that also shows the data.
Tip: You can customize the moving average chart like any other chart you create in Excel. Select the chart and use the Graphic Design tab to adjust the layout, colors, legend, and more.
Be sure to check out the other options available with the Analysis ToolPak. You can do things like create a histogram, find a correlation, or use the random number generator.