How to Generate Moving Average in Excel Chart (4 Methods)

Moving average is a common term in time-series analysis to review previous data and predict the future. In this article, I’ll show the fundamental concept of moving average, its types, calculation methods, and especially the process of creating moving average in an Excel chart.


Download Practice Workbook


Basics of Moving Average in Excel

What is Moving Average in Excel

Theoretically, the definition of moving average may differ a little bit as its usage includes in the field of statistics, finance, marketing, and so on.

Whatever, the tool can be used by investors to analyze the direction of trends and traders to monitor the price.

Basically, there are three types of moving averages.

1. Simple Moving Average 

The average of the latest number of periods of a data series can be calculated using a simple moving average.

2. Weighted Moving Average (WMA)

The weighted moving average calculates the average of the previous n periods. Each data point from the prior time period decreases the weighting.

3. Exponential Moving Average (EMA)

While a simple moving average determines the average of a set of data, an exponential moving average gives the current data more weight.


How to Calculate Moving Average in Excel

We can easily calculate the moving average in Excel by using two simple functions.

If you want to utilize the AVERAGE function, the formula will be like the following-

=AVERAGE(C5:C6)

Here, C5 is the precipitation in January, C6 is the precipitation in February.

On the other hand, if you want to use the SUM function, the formula will be-

=SUM(C5:C6)/2

The two calculation methods are given in the following figure. Just have a glimpse of it.

Moving Average Calculation Methods


How to Generate Moving Average in Excel Chart (4 Productive Methods)

This is the turning point of our article. Now, we’ll see the process of making charts of different types of moving averages in Excel. Before that, let’s be introduced with the dataset. This is the monthly precipitation data of Washington D.C. which is adapted from the US Climate Data.

Dataset


1. How to Generate Moving Average in Excel Chart

Do you know the process of creating charts in Excel?

If not, don’t be worried as it is quite simple.

⇒Firstly, select data

⇒Click Insert tabs>Charts ribbon

How to generate charts in Excel

⇒Click Recommended Charts or any chart option

After that, you’ll see the following figure.

How to generate charts in Excel

I have chosen the Scatter chart option, but you can pick another one depending on your purpose.

Finally, a chart is created and this is like the following.

How to generate charts in Excel

Then, follow the steps to create a moving average line.

⇒Click on the Plus (+) icon in the upper-right corner of the chart.

⇒Move the cursor on the Right arrow of the Trendline element.

⇒Choose the Two Period Moving Average option.

How to Generate Moving Average in Excel Chart

Thereafter, you’ll see the following moving average line for a 2-months period.

How to Generate Moving Average in Excel Chart

It is an interesting fact that we can generate a simple moving average line in the chart without the calculation process using a formula.

Read More: How to Calculate Average in Excel (Including All Criteria)


2. Producing Simple Moving Average in Excel Chart

Also, the previous one is an example of a simple moving average, but in this section, you’ll learn an interesting thing: adding a second moving average line.

Before that, let’s see the process of adding a 3-months moving average line.


2.1. Producing The Simple Moving Average for 3-months

As you know, you can calculate the moving average for different periods. In the earlier example, you saw the moving average line for 2 months. But you can find the line for different months.

For making a moving average line for 3-months, follow the steps below.

⇒Pick More options from the Trendline element by moving your cursor on the element.

Generating Simple Moving Average in Excel Chart

⇒You’ll see a Format Trendline toolbar on the right side of Excel.

⇒Click on Trendline Options.

⇒Choose the Moving Average option.

⇒Fix the period as 3.

Format Trendline for 3-months

Or if you want you may click on the 2-months moving average line, you’ll discover the above Format Trendline toolbar in the same place. Then fix the period as 3.

Ultimately, you’ll see the following output.

Generating Simple Moving Average for 3-months


2.2. Adding a Second Simple Moving Average Line in Excel Chart

You have to follow a similar process to add a second simple moving average line like the before indeed. Simply choose the period as 5.

Eventually, the output will look like this.

Adding a second simple moving average line in Excel Chart


3. Calculating Weighted Moving Average in Excel Chart

Since you are aware that the weightage factor is compulsory in creating the weighted moving average line in the Excel chart.

The factor may be 0.5, 0.3, and 0.2 sequentially, also you can modify this factor.

So the formula will be-

=0.5*C7+0.3*C6+0.2*C5

Here, C7 is the precipitation in March, C6 is the precipitation in February and C5 is for the precipitation in January.

Calculating Weighted Moving Average in Excel Chart

If you select “Month”, “Precipitation in mm”, and “WMA” fields, and then insert line chart. As you are fully conscious that the process of generating a chart has been discussed at the beginning of this article.

Lastly, the output will be as follows.

Calculating Weighted Moving Average in Excel Chart 2

The above picture clearly reveals that the weightage moving average provides a more smooth moving average line than a simple moving average line.


4. Creating Exponential Moving Average in Excel Chart

Not to mention that is our last example. Though the method of generating exponential moving average line is a little bit complex, it will be easier if you understand some concepts and the following formula.

St=α.Yt-1+(1- α)St-1

Formula Explanation:

Yt-1 means actual observation is made in the t-1th period

St-1 refers to the simple moving average (SMA) in the t-1th period

α (Alpha) denotes the smoothing factor which can be calculated using the formula below.

α=2/(N+1)

Here, N is the value of the period.

I would like to mention that if the value of α  is higher, the line will be less smooth.

Now, input the following formula in Excel.

=$H$6*C6+(1-$H$6)*D7

Formula Breakdown:

Here, H6 is the value of α where $ (dollar sign) is used as it is fixed

C6 is is the precipitation of February (actual observation)

D7 is the simple moving average for 3-months.

Generating Exponential Moving Average in Excel Chart

At this moment, if you select the precipitation, SMA, and EMA fields and insert the line chart, you’ll see the following output.

Generating Exponential Moving Average in Excel Chart

It is clear that the weighted moving average (WMA) line is smoother than the exponential moving average (EMA) line. If the α is lower, the EMA line will be smoother.

Read More: How to Calculate Exponential Moving Average in Excel


Things to Remember

1. Be careful when selecting the cell for getting output as the period is related to this. For example, if you want to calculate a simple moving average line for 3-months, you have to blank above two cells.

2. Weighted moving average (WMA) and exponential moving average (EMA) can be used for forecasting.


Conclusion

This is how you may calculate the moving average in an Excel chart. I hope that this article will accelerate your Excel learning. If you have any queries or suggestions, please let me know in the comments section below.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo