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.

## Exponential Smoothing in Excel: Knowledge Hub

- How to Calculate Trend Adjusted Exponential Smoothing in Excel
- Perform Holt-Winters Exponential Smoothing in Excel
- How to Smooth Data in Excel
- How to Remove Noise from Data in Excel

**<< Go Back to Solver in Excel | Learn Excel**