Exponential Smoothing in Excel – Step by Step Guide

Exponential Smoothing is a way of smoothing out time series data in order to more easily see trends. On top of this it then allows us to forecast future values as well due to the overall trends appearing less messy.

This guide will run through how to perform Exponential Smoothing in Excel, and visualise our results.

How to use the Exponential Smoothing Function

The first thing we will need to do is see if the Data Analysis Toolpak is installed.

If you head up to the data tab in the ribbon, and see the following Data Analysis option on the right hand side then we are good to go.

If you do not have this option then you can find our guide on enabling this by clicking here.

Now let’s move on to our dataset. For this guide we have monthly sales over time:

When visualized on a line chart it looks like this:

We can see an upwards trend, but it does seem to jump up and down a fair bit. This is where Exponential Smoothing will make things a lot more clear.

To perform this, head over to the Data Analysis section in the Data ribbon, and then in the pop up box that appears select Exponential Smoothing:

Excel Data Analysis Options

Next, in the options that come up, enter the range of the data we are working with under input range.

Next we need to select a Damping factor to determine the alpha value which is used to indicate how much we are smoothing the data by. A damping factor of 0.9 means an alpha of 0.1, while 0.8 means 0.2 and so on.

If you are interested in learning more about how this all works in the background this article sums up the formulas and theory that goes into exponential smoothing.

For the output range enter the cell next to the first value that we are working with – in our case this will be D3. That way we can more easily visualise this side by side.

Exponential Smoothing Function in Excel

Press OK, and we will see the following values appear:

What this function has also done is filled in the second value and onwards with the actual formula that goes into this:

Now if we were to re-create our line chart using both columns of data we can really see just how much smoother these values are:

Exponential Smoothing with Varying Alpha Values

Our first example took quite an extreme smoothing approach by using the 0.1 Alpha value. Let’s have a quick look at how our values would differ if we used 0.4, 0.7 and 0.9 as well:

You can easily do this by re-running the function a few more times and selcting new damping factors and output locations, or since we have the formulas from our first example you can just change the numbers in there.

If we visualize this now we can easily see that each alpha value has a different level of smoothness to the data:

Exponential Smoothing results Visualized

Being able to choose any alpha value we want is really handy as sometimes running with 0.1 may remove too much of what is really going on and something a bit smaller might help us see a trend without having too much of an effect on the underlying data.

From here we can either leave things as is if our goal was to simply see trends, or we can perform further analysis such as forecasting and other what if style analysis.

This sums up our step by step guide on how to perform Exponential Smoothing in Excel. For more tutorials head over to our Excel page here.

Similar Posts