How to calculate a moving average in Excel

How to calculate a moving average in Excel

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.

Data analysis on the Data tab

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.

Search for add-ins 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-ins” and click “Go”.

Go to File, Options, Add-ons

After the add-ons window appears, check the box next to Analysis Tools and click “OK”.

Select Analysis tools

Now, when you go to the Data tab, you should see the Data Analysis button in the ribbon.

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 the moving average

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.

Enter the input 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.

Enter the output range

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.

Get the results and moving average chart in Excel

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.

Similar Posts

Leave a Reply

Your email address will not be published.