How to calculate a moving average in Microsoft Excel

Moving Average Chart in Excel

You may be thinking of a moving average for stock or investment forecasts. But you can also use it to see trends for stock, sales or similar data. Without equations or functions, you can easily calculate a moving average in Excel.

RELATED: How to use the built-in stock feature in Microsoft Excel

Add the data analysis tool in Excel

While you able to perform calculations and create a line graph to show your moving average in Excel, which is an easier way.

Microsoft offers a free Analysis ToolPak that you can add to Excel. The tool allows you to develop statistical or technical analyzes with a number of functions. One such option is a moving average.

To see if you already have the Analysis ToolPak, go to the Data tab and look for the Data Analysis button in the Analysis section of the tape.

Data analysis on the Data tab

If you have the button, you are good to go and can go on to calculate a moving average below. If not, here you can add it quickly.

Go to the search box at the top of Excel and enter “Add-ons”. When you see add-ons in the list, select it.

Search for additions in Excel

Alternatively, click File> Options and select “Add-ons” on the left. At the bottom of the window, next to Manage, select “Excel Add-ons” and click “Go.”

Go to Files, Settings, Add-ons

When the add-on window appears, select the check box next to Analysis ToolPak and click “OK.”

Select Analysis ToolPak

When you now go to the Data tab, you should see the Data Analysis button on the ribbon.

RELATED: How to get Microsoft Excel to calculate uncertainty

Calculate a moving average

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.”

Choose moving average

At the top of the Moving Averages window, enter the input area in the corresponding field. You can also click inside the box and then drag through your data area. You may want to select the “First row labels” check box and include a range.

Enter the input area

Then enter the output range by entering a cell reference or clicking inside the box and selecting a cell on your sheet. You may want to select the Default Error check box. And to create a graph of the moving average and receive the results, select the chart output check box.

Enter the output range

When you have finished setting up the moving average, click “OK” and you will see the output. You have the values ‚Äč‚Äčthat begin in the cell you selected for output. If you selected chart output, you will also have a handy line graph that shows the data.

Receive the moving average results and chart in Excel

Tip: You can customize the moving average graph like any other chart you create in Excel. Select the graph and use the Chart Design tab to adjust layout, colors, explanation, and more.

Be sure to check out the other options available with Analysis ToolPak. You can do things like create a histogram, find a correlation, or use the random number generator.

Leave a Comment