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

Get FREE Advanced Excel Exercises with Solutions!

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 averages, their types, calculation methods, and especially the process of creating a moving average in an Excel chart.


What is Moving Average in Excel?

Theoretically, the definition of moving average may differ a little bit as its usage includes the fields 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.


Moving Average in Excel Chart: 4 Methods

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, and 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

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 introduce the dataset. This is the monthly precipitation data of Washington D.C. which is adapted from the US Climate Data.

Dataset


1. Generating 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-month 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 Add Average Line to Excel Chart


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-month moving average line.


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

To make 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-month moving average line, and 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

Read More: How to Determine Triple Exponential Moving Average in Excel


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 before. 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 the precipitation in January.

Calculating Weighted Moving Average in Excel Chart

If you select “Month”, “Precipitation in mm”, and “WMA” fields, and then insert a line chart. As you are fully conscious 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 smoother 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 an 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 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 averages (WMA) and exponential moving averages (EMA) can be used for forecasting.


Download Practice Workbook


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


<< Go Back to Moving Average | Calculate Average in Excel | How to Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo