Shahriar Abrar Rafid
Are you a stock market investor? Or want to perform any kind of economic analysis? Then, obviously, you have come across the term Data Smoothing. It helps us to understand the pattern of data. Also, we can predict the trend and do forecasting based on this. In this article, we will take you through six easy and convenient methods on how to smooth data in Excel.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
6 Methods to Smooth Data in Excel
For ease of understanding, we’re using a Product Demand chart for a certain product. This dataset includes Period, Month, and Demand in columns B, C, and D consecutively.
Now, we’ll show how we can smooth the data in Excel using the dataset above.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience. If any methods won’t work in your version, then leave us a comment.
1. Using Smoothed Line Option
In our first method, we’ll use the Smoothed line option in the chart to smooth data in Excel. It’s simple & easy, just follow along.
- First of all, select cells in the B4:D14 range.
- After that, go to the Insert tab.
- Then, click on the Insert Line or Area Chart drop-down on the Charts group.
- Following this, select 2-D Line from the available options.
Immediately, a Line Chart appears on the worksheet.
- Now, right-click anywhere on the series on the chart to get the context menu.
- Then, select Format Data Series from the menu.
Instantly, the Format Data Series task pane opens on the right side of the display.
- Here, click on the Fill & Line icon (Spilling Color Can).
- After that, select Line like in the image below.
- In the end, check the box before Smoothed line.
At this moment, the line of the chart gets smoothed and looks like that.
Now, we’ll do some formatting on the chart.
- Initially, move to the Chart Design tab.
- Secondly, click on the Add Chart Element drop-down on the Chart Layouts ribbon group.
- Then, click on the Chart Title to open the sub-menu.
- After that, select None.
- Again, click on the Add Chart Element dropdown.
- Hereafter, click on Legend.
- Then, select Top from the options.
Therefore, the final output of the chart looks like the following image.
2. Adding Trendline
In the second approach, we’ll add a new Trendline to our chart. It will represent a smoother version of our data. To do this using the second method, you may follow the steps below.
📌 Steps:
- At first, insert a chart from the table in the B4:D14 range just like Method 1.
- Then, jump to the Chart Design tab.
- After that, click on the Add Chart Element drop-down from the Chart Layouts group.
- Following this, click on Trendline.
- Successively, select the Moving Average option from the available choices.
Suddenly, it added a new Trendline to the chart.
- Now, right-click on the new series.
- From the context menu, click on the Outline drop-down.
- Then, select Automatic as Theme Color.
Presently, the line looks like the following one.
3. Applying Exponential Smoothing in Excel
In this section, we will show you the quick steps to do Exponential Smoothing in Excel on Windows operating system. You will find detailed explanations of methods and formulas here.
📌 Steps:
Before going any further, we need to enable the Data Analysis tool. For this, we need to install the Analysis Toolpak add-in. Let’s see the process in detail.
- Firstly, go to the Developer tab.
- Secondly, select Excel Add-ins on the Add-ins group.
Instantly, the Add-ins dialog box appears.
- Here, check the box of Analysis Toolpak.
- Lastly, click OK.
Now, we’ve installed this add-in in our Excel. So, we can utilize this tool from now on.
- Forthwith, create a new column Exponential Smoothing under Column E.
At this time, the Data Analysis toolbox pops up.
- Here, select Exponential Smoothing from the Analysis Tools section.
- Lastly, click OK.
Ultimately, it opens the Exponential Smoothing dialog box.
- Here, give the cell reference of D5:D14 in the Input Range box.
- Then, give the Damping factor as 0.9.
- Later, put E5:E14 as the cell reference in the Output Range box.
- After all, click OK.
Thus, it gives the result in the newly created column.
- Then, insert a chart based on the above table like Method 1.
4. Implementing Trend-Adjusted Exponential Smoothing
In this method, we’ll calculate Trend-adjusted Exponential Smoothing to smooth our data. So, without further delay, let’s dive in!
📌 Steps:
- Initially, select cell E5 and write down the following formula.
=D5
- After that, press ENTER.
- Then, go to cell E6 and enter the formula below.
=D5*$F$16+(1-$F$16)*E5
Here, D5 represents the Demand for January. E5 represents the Exponential Smoothing for January and F16 serves as the Smoothing Factor of 0.2.
- Later, press ENTER.
- Now, get the cursor at the right-bottom corner of cell E6 and it’ll look like a plus (+) sign. It’s the Fill Handle tool. Hence, double-click on it.
Thus, you’ll get the remaining results in cells in the E7:E14 range.
- Thereupon, create a new column named Trend.
- Here, select cell F6 and paste the following formula.
=$F$17*(E6-E5)+(1-$F$17)*F5
- After that, press ENTER.
- Thenceforward, write down 0 in cell F5 to fill up the whole column.
- Consequently, to get the Trend Adjusted Value, select cell G6 and enter the formula below.
=E6+F6
- As always, hit the ENTER key.
- Finally, insert a chart using columns Period, Month, Demand, and Trend Adjusted Value as we did in Method 1.
5. Utilizing Moving Average
The Moving Average is also known as the Rolling Average. Here, we’ll use this feature to solve our problem. Allow me to demonstrate the process below.
📌 Steps:
- At first, construct a new column named Moving Average.
- Thereafter, jump to the Data tab.
- Also, click on the Data Analysis tool on the Analysis group on the ribbon.
- Immediately, the Data Analysis toolbox opens.
- From that, choose Moving Average from the Analysis Tools list.
- As usual, click on OK.
- Thenceforth, in the Moving Average dialog box, give the reference of D5:D14 in the Input Range box.
- After that, change the Interval to 2.
- In the Output Range box, write down E5:E14.
- Finally, click OK.
As a result, the result comes into sight in cells in the E5:E14 range.
- Similarly, insert a chart from the above data range of B4:E14.
6. Inserting TREND Function
In this case, we’ll use a statistical function named the TREND function. So, allow me to demonstrate the process step-by-step.
📌 Steps:
- Firstly, go to cell E5 and write down the following formula.
=TREND(D5:D14,B5:B14)
- Ultimately, press ENTER.
- Then, using the table, insert a Line Chart like Method 1.
How to Remove Noise from Data in Excel
In this place, we’ll talk about how we can remove noise from data in Excel. Actually, we want to minimize the random apex and trough of kine in a chart.
Here, we are using a list of Prices of Eggs (Per Dozen). This dataset contains 512 rows of information. There are prices from 1 Jan 1980 to 1 Aug 2022.
Now, we’ll use this dataset to show how we can remove noise from data in Excel. So, let’s have a look at the procedure below.
📌 Steps:
- Primarily, call the Exponential Smoothing tool just like Method 3. For a better understanding, see the image below.
Immediately, it opens the Exponential Smoothing dialog box.
- In the Input Range box, give the reference of C5:C516.
- After that, select the Damping factor as 0.9.
- In the Output Range box, write down D5:D516 as a cell reference.
- Lastly, click OK.
Thus, we got the Noise-Free Data in Column D.
- Finally, insert a chart just like Method 1.
Practice Section
For doing practice by yourself we have provided a Practice section like the one below in each sheet on the right side. Please do it by yourself.
Conclusion
This article provides easy and brief solutions to the question of how to smooth data in Excel. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.
I believe the exponential smoothing in Excel uses the simple (Brown’s) exponential method. There are other methods that can be more appropriate if the data exhibits trend or seasonality.
For the damping parameter (0-1), I suggest you calibrate this value by minimizing the sum of squared errors between the data and the smoothed values, to get the best result.
Hello MOHAMAD,
Thanks for your valuable comment. We always expect such positive feedback from our users.
As you said, here we’ve shown the simple exponential method only. The main reason for this is that we didn’t only want to emphasize exponential smoothing; rather we wanted to cover all conceivable methods of data smoothing. If so happened the article would then be extremely lengthy.
But you can find triple exponential smoothing widely called the Holt-Winters Exponential Smoothing on our website also. This method is very much adaptable for data with seasonal patterns.
Thanks Again.