How to Smooth Data in Excel (6 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.


Smooth Data in Excel: 6 Methods

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.

how to smooth data in excel

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.

📌 Steps:

  • 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.

Using Smoothed Line Option

Immediately, a Line Chart appears on the worksheet.

Inserting Line Chart in Excel

  • 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.

Working on Format Data Series

At this moment, the line of the chart gets smoothed and looks like that.

Using Smoothed Line Option to Smooth Data in Excel

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.

Formatting the Chart

  • Again, click on the Add Chart Element dropdown.
  • Hereafter, click on Legend.
  • Then, select Top from the options.

Adding Legend on Top of the Chart

Therefore, the final output of the chart looks like the following image.

Using Smoothed Line Option to Smooth Data in Excel


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.

Adding Trendline

  • 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.

Adding Trendline on Chart

Suddenly, it added a new Trendline to the chart.

Adding Trendline to Smooth Data in Excel

  • Now, right-click on the new series.
  • From the context menu, click on the Outline drop-down.
  • Then, select Automatic as Theme Color.

Changing Theme Color of Series

Presently, the line looks like the following one.

Adding Trendline to Smooth Data in Excel


3. Applying Exponential Smoothing in Excel

In this section, we 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.

📌 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.

Applying Exponential Smoothing in Excel

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.

Creating New Column

  • Therefore, move to the Data tab.
  • Hence, select Data Analysis on the Analysis group.

Selecting Data Analysis Tool

At this time, the Data Analysis toolbox pops up.

  • Here, select Exponential Smoothing from the Analysis Tools section.
  • Lastly, click OK.

Using Data Analysis Toolbox

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.

Utilizing Exponential Smoothing Wizard to Smooth Data in Excel

Thus, it gives the result in the newly created column.

  • Then, insert a chart based on the above table like Method 1.

Applying Exponential Smoothing in Excel to Smooth Data in Excel


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.

Implementing Trend-Adjusted Exponential Smoothing

  • 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.

Using Fill Handle Tool

Thus, you’ll get the remaining results in cells in the E7:E14 range.

Getting Values after Exponential Smoothing

  • 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.

Finding Trend Values

  • Thenceforward, write down 0 in cell F5 to fill up the whole column.

Implementing Trend-Adjusted Exponential Smoothing to to Smooth Data in Excel

=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.

Implementing Trend-Adjusted Exponential Smoothing to to Smooth Data in Excel


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:

  • First, construct a new column named Moving Average.

Utilizing 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.

Using Moving Average Dialog Box

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.

Utilizing Moving Average to Smooth Data in Excel


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.

Inserting TREND Function

  • Then, using the table, insert a Line Chart like Method 1.

Inserting TREND Function to Smooth Data in Excel

Read More: Perform Holt-Winters Exponential Smoothing in Excel


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.

How to Remove Noise from Data in Excel

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.

Performing Data Analysis

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.

Getting Noise-Free Data

  • Finally, insert a chart just like Method 1.

Removing Noise to Smooth Data in Excel


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.

Practice Section

 


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice 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.


<< Go Back to Exponential Smoothing in Excel | Solver in Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

2 Comments
  1. 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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo