Butterworth Filter and Moving Average in Excel
You’ve got noisy data.
You can see an underlying trend but Excel is not enabling you to get to it.
You have tried plotting a trend-line. You may even have tried to implement an average by coping an equation down you column of numbers.
Neither are really working out as they are proving slow and cumbersome to “tune” to values that look correct.
You’re familiar with moving averages, likely used them before, but what is the right number of points to average over?
It takes a while to iterate through this and involves lots of copy and pasting in cells.
If you’ve a lot of data, Excel struggles and it takes a long time.
You also know that moving averages offset the data by the number of points you’re averaging over.
If your data is time dependant then this can create a big issue – even if you’re not always aware of it.
A “Proper” Filter
You may also know that moving averages are not really proper filters at all.
They don’t actually filter anything, they just fold all the data – signal and noise – together.
You know that there are potentially better data filters (like the butterworth filter) but you’ve not been able to implement them easily within Excel.
If you have tried then it’s also likely taken an age to do, they’re unlikely to be dynamic, nor easy to change, nor easily to port to other data sources.
I’ve been there too.
I’ve faced these same challenges, time and again.
You will get there eventually but honestly when? And are you sure you haven’t made any mistakes? And what about when you get the next set of data, what do you do then?
What could be great is a simple, proven tool that can do the filtering for you.
One that takes in a simple column of numbers and instantly displays both the original data AND the filtered results overlaid.
A tool that enables you to quickly tune the filter parameters to instantly evaluate the effect of different settings.
This is a problem I was always coming across so I developed both a moving average AND a butterworth filter tool to help me, and now help you.
Watch this short video below to see it in action. If they will offer value to you then instantly grab you own copy below.
- Advanced version of the tool.
- Auto-load data
- Apply both the filter and average.
- Adjust settings with a live slider.
- Instantly compare results.
- Unlocked VBA code.
- Detailed video tour of the code.
- Tips for your own projects.
- Test data.
- The Butterworth Filtering LITE tool, and
- The Moving Average LITE tool.
Butterworth Filter Only
- Butterworth Filtering LITE tool
Moving Average Only
- Moving Average LITE tool
Tools & Lessons You Can Apply Immediately
These tools will enable you too:
- rapidly apply a moving average to your data
- rapidly iterate through moving average settings to find the best result
- apply a butterworth filter to your data, a true filter and we’ve set it up with no time offset, so the time will line up exactly with your underlying noisy data.
- iterate through the butterworth filter settings to see the best result
- compare the results of these filters all together to choose the best one
- have access to the code to use in your own projects
- have access to a dedicated code walk through video, to explain how it works.
- have access to sample data to see the format and experiment with the settings
Tools We Use All The Time
Measured sensor data is so often noisy.
Having the ability to quickly run ‘look see’ evaluations, find features and trends is invaluable.
Perhaps Excel is not the ideal tool. Perhaps Excel should have this feature baked in?
It does not though so I’ve done it for you.