How to Remove Noise from Data in Excel (2 Easy Ways)

In the real world, users deal with raw data that contains noise or deviation. The representation of this noisy data may ill-depict the trend, so it becomes essential to remove noise from data in Excel. Moving Average and Exponential Smoothing flatten the deviation by removing the noise within it.

Let’s say we have velocity data against time and want to remove noise from the velocity data.

Download Excel Workbook

2 Easy Ways to Remove Noise from Data in Excel

Smoothing data and removing noise from data are similar things. Smoothing raw data removes existing noise. This smoothed data is crucial to understanding prevailing trends and better forecasts.

Go through the below sections to be able to remove noise from data.

Method 1: Using Moving Average to Remove Noise from Data

From the dataset, we get the existing average velocity values for a certain interval. Now, we need to find the Moving Average values to smoothen the Velocity-Time curve.

• Type the following formula into the cells of the D Column.

• Highlight all three columns, then go to the Insert tab.
• Choose a Scatter Chart type.

• Instantly, Excel inserts a Scatter Chart as shown in the below picture.

• Furnish the chart according to your taste, as depicted below.

The above picture shows the smoothing of noisy data. As the moving average takes the following couple of entries (i.e., 3, 5, or 7) to come up with a smooth entry, the Moving Average curve doesn’t have the last couple of values. This shortcoming can be overcome using Trendline insertion or Data Analysis Tools.

Trendline Insertion

What if we don’t want to use the AVERAGE function or want automatic smoothing of an inserted curve?

• Right-click on the raw data scatters curve. The Context Menu appears.
• Select Add Trendline.

• Excel pops up the Format Trendline side window. Mark Moving Average under Trendline Options and enter 5 in the Period command box.

• The Moving Average Trendline gets inserted.

Moving Average – Data Analysis Tools

If users want all the smoothen data, they can use the Moving Average Tools in Data Analysis.

• Move to the Data tab.
• Click Data Analysis.

• The Data Analysis dialog box appears. Select Moving Average under Analysis Tools.
• Click OK.

• Clicking OK fetches the Moving Average dialog box. Assign Input (i.e., C4:C20) and Output (i.e., D4) Ranges.
• Finally, click OK.

• Excel inserts all the moving average values, removing noise.

Use the smoothed data to draw charts or any kind of representation to solidify your stance to some extent.

Method 2: Fetching Smoothed Data Using Exponential Smoothing

As an alternative to method 1, users can exercise Exponential Smoothing Tools from Data Analysis.

• Go to Data > Data Analysis. Excel displays the Data Analysis.
• Choose Exponential Smoothing under Analysis Tools.
• Click OK.

• Upon Excel fetching the Exponential Smoothing dialog box, assign the Input (i.e., C4:C20), Output (i.e., D4) Ranges and Damping Factor (i.e., 0.91).
• Click OK.

• Excel stacks the smoothed data in the D column. Highlight all three columns.
• Move to the Insert tab.
• Select a Scatter Chart.

• In a second, Excel inserts a scatter chart containing noisy and smoothed data.

• Modify the Chart and you’ll get a depiction like a picture below.

Things to Keep in Mind

• Moving Average method has alternatives as smoothed data can be achieved using Formula or Data Analysis Tools.
• Signal processing or smoothing signals is similar to removing noise from data if signals contain numeric values.
• The Trendline of Exponential Smoothing returns the same curve to Data Analysis- Exponential Smoothing in the chart.

Conclusion

This article demonstrates the ways to remove noise from data in Excel. Though the moving average method has multiple alternatives for its execution depending on the outcome, exponential smoothing has the same outcome irrespective of ways.

Do check out our awesome website, Exceldemy, to find interesting articles on Excel.