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.
Remove Noise from Data in Excel: 2 Easy Ways
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.
=AVERAGE(C4:C8)
- 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 smoothened 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 Formulas 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.
Download Excel Workbook
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.
hi,
what is the consideration to put 0.91 as Damping Factor?
Hi BAGUS,
In the context of Exponential Smoothing in Excel’s Data Analysis Toolpak, the Damping Factor refers to a parameter used to control the impact of older observations on the forecasted values.
The damping factor has a value between 0 and 1. It determines the weight assigned to the most recent observation when calculating the forecast. A higher value (closer to 1) gives more weight to the most recent data point, making the forecast more responsive to recent changes in the data. On the other hand, a lower value (closer to 0) gives less weight to the most recent data point, making the forecast more stable and less responsive to short-term fluctuations.
The value of Damping Factor being 0.91 suggests relatively high importance to the most recent data while still considering some historical data. The choice of 0.91 is somewhat arbitrary and depends on the specific characteristics of the data and the desired balance between responsiveness and stability in the forecast. Different values of the damping factor may be chosen based on the analyst’s judgment and the nature of the data being analyzed.
To determine the most appropriate value for the Damping Factor, it’s often a good practice to experiment with different values and evaluate the forecast accuracy using techniques like Mean Absolute Error (MAE) or Mean Squared Error (MSE). The choice of Damping Factor should ideally be data-driven and selected based on how well it performs in forecasting historical data or predicting future values.
Regards
Rafiul Hasan
Team ExcelDemy