How to Perform Exponential Smoothing in Excel

Exponential smoothing is a technique for “smoothing” out time series data and is often used for short-term forecasting.

The basic idea is that time series data often has “random noise” associated with it, which leads to peaks and valleys in the data, but by applying exponential smoothing we can smooth out these peaks and valleys to see the true underlying trend of the data.

The basic formula for applying exponential smoothing is as follows:

F_t = αy_t-1 + (1 – α) F_t-1

where:

F_t = Forecasted value for current time period t

α = The value for the smoothing constant, between 0 and 1

y_t-1 = The actual data value for the previous time period 

F_t-1 = Forecasted value for previous time period t-1

The smaller the alpha value, the more the time series data is smoothed out.

In this tutorial, we show how to perform exponential smoothing for time series data using a built-in function in Excel.

Example: Exponential Smoothing in Excel

Suppose we have the following dataset that shows the sales for a particular company for 10 sales periods:

Perform the following steps to apply exponential smoothing to this time series data.

Step 1: Click on the “Data Analysis” button.

Go to the “Data” tab along the top ribbon and click the “Data Analysis” button. If you don’t see this button, you need to first load the Excel Analysis ToolPak, which is completely free to use.

Step 2: Choose the “Exponential Smoothing” option and click OK.

Step 3: Fill in the necessary values.

• Fill in the data values for Input Range.
• Select the value you’d like to use for Damping Factor, which is 1-α. If you’d like to use α = 0.2, then your damping factor will be 1-0.2 = 0.8.
• Select the Output Range where you’d like the forecasted values to appear. It’s a good idea to choose this output range right next to your actual data values so you can easily compare the actual values and the forecasted values side by side.
• If you would like to see a chart displayed with the actual and the forecasted values, select the box that says Chart Output.

Then, click OK.

A list of forecasted values and a chart will automatically appear:

Note that the first time period has a value of #N/A because there is no previous time period to use to calculate the forecasted value.

Experimenting with Smoothing Factors

You can experiment with different values for the smoothing factor α and see how it impacts the forecasted values. You’ll notice that the smaller the value for α (larger value for Damping Factor), the more smoothed out the forecasted values will be:

For more tutorials in Excel, be sure to check out our complete list of Excel Guides.