How to Smooth Data in Excel (6 Methods)

To make data trends more visually clear, we’ll demonstrate how to smooth a Product Demand chart in Excel. Our dataset includes columns for “Period,” “Month,” and “Demand,” located in cells B4:D14 consecutively.

Method 1 – Using Smoothed Line Option

• Select the Range:
  • First, highlight cells in the B4:D14 range.

• Insert a Line Chart:
  • Go to the Insert tab.
  • Click on the Insert Line or Area Chart dropdown in the Charts group.
  • Choose 2-D Line from the available options.

• • A Line Chart will appear on the worksheet.

• Format Data Series:
  • Right-click anywhere on the series in the chart to open the context menu.
  • Select Format Data Series.

  

• • The Format Data Series task pane will appear on the right side of the display.

• Adjust Line Style:
  • Click on the Fill & Line icon (depicted as a spilling color can).
  • Choose Line (as shown in the image below).

• Enable Smoothed Line:
  • Check the box next to Smoothed line.

• • The chart’s line will now appear smoothed.

• Additional Formatting:
  • Move to the Chart Design tab.
  • Click on the Add Chart Element dropdown in the Chart Layouts ribbon group.
  • Open the sub-menu and select Chart Title. Choose None to remove the chart title.

• • Click the Add Chart Element dropdown again.
  • Select Legend and position it at the top.

The final output of the chart will resemble the image below:

Method 2 – Adding a Trendline

In this approach, we’ll introduce a new Trendline to our chart. The trendline will represent a smoother version of our data. Follow the steps below to achieve this:

• Insert the Chart:
  • Begin by inserting a chart based on the data table in the B4:D14 range, similar to Method 1.

• Navigate to the Chart Design Tab:
  • Go to the Chart Design tab.

• Add a Chart Element:
  • Click on the Add Chart Element dropdown within the Chart Layouts group.

• Select Trendline:
  • Choose Trendline.

• Choose Moving Average:
  • From the available options, select Moving Average

• Customize the Trendline:
  • Right-click on the newly added trendline series.
  • In the context menu, click on the Outline dropdown.
  • Select Automatic as the theme color.

The resulting trendline will appear as shown below:

Method 3 – Applying Exponential Smoothing in Excel

In this section, we’ll demonstrate the quick steps to perform exponential smoothing in Excel on the Windows operating system. You’ll find detailed explanations of the methods and formulas here.

Steps:

• Enable Data Analysis Tool:
  • Before proceeding, we need to enable the Data Analysis tool. To do this:
    • Go to the Developer tab.
    • Select Excel Add-ins in the Add-ins group.
    • The Add-ins dialog box will appear.

• • • Check the box next to Analysis Toolpak.
    • Click OK.

• • Now that we’ve installed this add-in, we can utilize it in Excel.

• Create a New Column for Exponential Smoothing:
  • Add a new column called Exponential Smoothing under Column E.

• Access Data Analysis Toolbox:
  • Move to the Data tab.
  • Select Data Analysis in the Analysis group.

• • The Data Analysis toolbox will appear.

• Choose Exponential Smoothing:
  • In the toolbox, select Exponential Smoothing from the Analysis Tools section.
  • Click OK.

• Configure Exponential Smoothing:
  • The Exponential Smoothing dialog box will open.
  • Specify the input range by giving the cell reference of D5:D14 in the Input Range box.
  • Set the damping factor to 0.9.
  • Specify the output range by using E5:E14 as the cell reference in the Output Range box.
  • Click OK.

• View the Result:
  • The result will appear in the newly created Exponential Smoothing column.

• Create a Chart:
  • Finally, insert a chart based on the updated table, similar to Method 1.

Method 4 – Implementing Trend-Adjusted Exponential Smoothing

In this method, we’ll calculate trend-adjusted exponential smoothing to smooth our data. Let’s dive in!

Steps:

• Initial Setup:
  • Select cell E5 and enter the following formula:

• • Press ENTER.

• Calculate Exponential Smoothing:
  • Go to cell E6 and enter the formula below:

• • • Here, D5 represents the demand for January, E5 represents the exponential smoothing for January, and F16 serves as the smoothing factor (0.2 in this case).

• • Press ENTER.

• Fill Down the Formula:
  • Position the cursor at the right-bottom corner of cell E6 (it will look like a plus sign, known as the Fill Handle tool).
  • Double-click on it.

• • This will populate the remaining results in cells E7:E14.

• Create a New Column for Trend:
  • Add a new column named Trend.

• Calculate Trend:
  • In cell F6, paste the following formula:

• • Write down 0 in cell F5 to fill the entire column.

• Trend-Adjusted Exponential Smoothing:
  • To calculate trend-adjusted exponential smoothing, select cell G6 and enter the formula below:

• Press ENTER.

• Insert a Chart:
  • Finally, create a chart using columns Period, Month, Demand, and Trend-Adjusted Value, similar to what we did in Method 1.

Method 5 – Utilizing Moving Average

The Moving Average is also referred to as the Rolling Average. Let’s use this feature to smooth our data. Follow the steps below:

• Create a New Column for Moving Average:
  • Add a new column named Moving Average.

• Access the Data Analysis Tool:
  • Go to the Data tab.
  • Click on the Data Analysis tool in the Analysis group on the ribbon.
  • The Data Analysis toolbox will open.

• Choose Moving Average:
  • From the Analysis Tools list, select Moving Average.
  • Click OK.

• Configure Moving Average:
  • In the Moving Average dialog box:
    • Specify the input range by giving the reference of D5:D14 in the Input Range box.
    • Change the Interval to 2.
    • In the Output Range box, write down E5:E14.
    • Click OK.

• View the Result:
  • The moving average result will appear in cells E5:E14.

• Insert a Chart:
  • Similarly, create a chart using the data range from B4:E14.

Method 6 – Inserting the TREND Function

• Open your Excel spreadsheet.
• Navigate to cell E5.
• Enter the following formula:

• • This formula uses the TREND function to predict values based on historical data points. The range D5:D14 represents the known y-values (dependent variable), and B5:B14 represents the known x-values (independent variable).
• Press ENTER to calculate the predicted value.

• To visualize the trend, create a Line Chart using the data from your table, similar to Method 1.

Remember that the TREND function uses the least-squares method to find the best-fitting linear trend for your data points.  It’s a powerful tool for predicting values based on existing data.

Read More: Perform Holt-Winters Exponential Smoothing in Excel

Removing Noise from Data

Let’s talk about how we can remove noise from data in Excel. We’ll use 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.

Using Exponential Smoothing

• Go to the Data tab.
• Click Data Analysis.
• Choose Exponential Smoothing under Analysis Tools.

• In the Exponential Smoothing dialog box:
  • Assign the input range (C4:C20).
  • Set the output range (D4).
  • Specify the damping factor (e.g., 0.9).
• Click OK.

• Excel will provide smoothed data in column D, which represents the noise-free data.

• Finally, create a chart similar to Method 1 to visualize the trend.

Exponential Smoothing is an effective technique for removing noise and improving data clarity.

Practice Section

To practice on your own, you’ll find a “Practice” section on the right side of each sheet. Take some time to work through the exercises there.

Download Practice Workbook

You can download the practice workbook from here:

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