If you are searching for a solution or some special tricks to do Exponential Smoothing in Excel. Then you have landed in the right place. There is a quick way to do Exponential Smoothing in Excel. This article will show you each and every step with proper illustrations, so you can easily apply them for your purpose. Letâ€™s get into the main part of the article.

Have a look at the overview image of this article.

## What Is Exponential Smoothing?

Exponential smoothing is a time series forecasting method where newer data is given higher priority and old data is given low priority. Priority is determined by a weight factor. The past observations are weighted by an exponential decrease. There are three types of exponential smoothing:

**Single Exponential Smoothing:**There is no trend or seasonality. And only one parameter which is the smoothing factor (Î±).**Double Exponential Smoothing**: It includes trends in the time series forecasting.**Triple Exponential Smoothing:**It includes both trends and seasonality. It is also named as Holt-Winters Exponential Smoothing.

## What Is Damping Factor?

You have to use a smoothing coefficient between 0 and 1 which is known as a smoothing coefficient (Î±). And damping coefficient is the value of 1 minus alpha (Î±). So, the smaller the damping factor is, the higher the alpha level. A lower alpha level smooths the peak and valley points of the graph and a lower damping factor smooths the values close to the actual points.

Suppose, you have a dataset containing the actual sales for months and you want to forecast sales for the next months using the exponential smoothing method. In this section, I will show you the quick steps to do Exponential Smoothing in Excel on the Windows operating system. You will find detailed explanations of methods and formulas here. I have used the *Microsoft 365 version* here. But you can use any other versions as of your availability. If any methods wonâ€™t work in your version then leave us a comment.

## 1. Using Data Analysis Tool to Do Exponential Smoothing in Excel

### Step 1: Enabling Data Analysis ToolPak Add-In

In Excel, **Data Analysis ToolPak** is not enabled by default so you have to enable it manually. Follow the steps below to enable the Data Analysis ToolPak.

- At first, open any workbook in Excel and Go to
**File**Tab >>**Options**.

- Then, you will see a window named
**Excel Options**will open. - Go to the
**Add-ins**options. - Here, select Excel
**Add-ins**in the**Manage**tools and press**Go.**

- Now, mark the
**Analysis ToolPak**box and press**OK**.

### Step 2: Selecting Exponential Smoothing in the Data Analysis Dialog

- Now, you have to go to the
**Data**Tab. - Select the
**Data Analysis**option in the**Analyze**Â portion.

- After clicking on the
**Data Analysis**option, a window named**Data Analysis**will appear. - Then, select
**Exponential Smoothing**from the Analysis tools. - And, press
**OK**.

### Step 3: Inserting Proper Inputs in Exponential Smoothing Window and Apply

- After selecting Exponential Smoothing, a window will appear.
- Select the cells in the
*Actual Sales*column as the**Input Range**. - Insert the
**Damping Factor**as**0.9**if the Alpha value is**0.1**. - Then, select the first cell of Forecast column
**F5**as**Output Range**. - Finally, press
**OK**.

- As a result, after clicking the
**OK,**you will see the Forecast column will be filled.

- You can follow a similar way to create Time Series Forecasting with an Alpha value = 0.3. For this, just insert (1-0.3)= 0.7 in the
**Damping factor**box.

### Step 4: Inserting a Graph to Compare

You can make charts to compare the actual and forecasted data with different smoothing factors. For this,

- Select range
**D4:D16Â**and**F4:G16**simultaneously >> go to**Insert**tab >> Select**Line or Area Chart**.

- Tap on the
**Line**chart in**2-D Line**.

- Thus, we obtain the chart showing the differences.

## 2. Using Excel Solver Add-in to Do Exponential Smoothing

Initially, the first forecast value will be equal to the actual value. So, type the following formula in **D6**,

`=C5`

Afterward, input the following formula in **D7**,

`=$C$14*C6+$C$15`

Write **the ABS function**Â in **E6**,

`=ABS(C6-D6)`

Later, type the basic formula in **F6**,

`=E6^2`

Finally, type the following formula in **G6** to find the **Absolute Percent Errors**.

`=E6/C6*100`

Lastly, in **E14**, enter the following formula >> copy the cell by dragging it to **G14**.

`=AVERAGE(E6:E13)`

Hence, we obtain the Mean Absolute Deviation, Mean Squared Error, and Mean Absolute Percent Error in **E14**, **F14**, and **G14** respectively.

**Mean Absolute Deviation**is the sum of Absolute Forecast Errors per n. Mean Square Error is the sum of the Square of absolute forecast per (n-1).- The
**Mean Absolute Percentage Error**(**MAPE**) is a statistical metric used to assess the accuracy of a forecasting method in terms of predictions.

Go to **File** >> **Options**.

Click on **Add-ins** >> **Go**.

Tap on **Solver Add-in** in **Add-ins** dialog >> **OK**.

Thus, we add **Solver Add-in**. Go to** Data** tab >> **Solver**.

In **Solver Parameters**, put **E14** as the value of **MAD** in **Set Objective** >> Check **Min** option >> put **C14** in **By Changing Variable Cells** >> add **C14 <=1** in **Add** box >> **Solve**.

Thus, the value of alpha changes to 1 in **C14**. That means, that to get the smallest **MAD** possible, we should set the alpha to 1.

Next, put **F14** as the value of **MSE** in **Set Objective** >> Check **Min** option >> put **C14** in **By Changing Variable Cells** >> add **C14 <=1** in **Add** box >> **Solve**.

Hence, we will find the changes in **C14**. Therefore, we should set the alpha value to **0.94136709** to get the smallest value possible for **MSE**.

Finally, click the **Solver** tool again, and in the **Solver Parameters**, put **G14** in **Set Objective** >> check **Min** box >> **C14** in **By Changing Variable Cells** >> tap **Add** and write **C14 <= 1** >> **Solve**.

Thus, the value of alpha changes to **1**. This shows, that to obtain the minimum value for **MAPE**, set the alpha value to 1.

**Note:****Mean Absolute Percent Error** gives all errors equal weightage while **Mean Square Error** weights errors according to their squares.

## How to Forecast with Exponential Smoothing in Excel

### 1. Applying FORECAST Function

To predict the sales value, type the following **the FORECAST function** in **D10**,

`=FORECAST.ETS(C10,$D$5:$D$9,$C$5:$C$9,1,1)`

Later, drag the cell down to **D16 **to get the other forecasts.

### 2. Using Forecast Sheet Feature

To use the** Forecast Sheet** feature, select the range of data table >> go to **Data** tab >> **Forecast Sheet**.

Put **12** in **Forecast End** >> **Create**.

Thus, the predicted value appears with a forecast chart.

## Things to Remember

- The damping factor is the subtraction of the Alpha value from 1.
- With the higher value of alpha, the curve fluctuates highly with data.
- When you take input data with headings
**,**you have to tick the LabelÂ in the Exponential Smoothing.

## Frequently Asked Questions

**In Simple Exponential Smoothing can we take alpha > 1? If not then why?**

If alpha > 1 then 1-alpha < 0 and so the predicted value at time t has a negative impact on the predicted value at time t+1, which is counter-intuitive.

**Can I get a negative result in the forecast by using Holts linear method?**

It depends on the data values, but yes. You can obtain a negative result.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

In this article, you have learned how to do Exponential smoothing in Excel. I hope you found this article helpful.Â Please, drop comments, suggestions, or queries if you have any in the comment section below.