Are you using filters in Excel?
FREE: YOUR EXCEL CHEATSHEET DOWNLOAD
https://www.tigerspreadsheetsolutions...
There is no doubt filters can be useful for data analysis in Excel, and they are often one of the first techniques beginners learn. But, they can be ‘onerous’, to say the least!
DOWNLOAD FILES LINK
https://tinyurl.com/yrk3h9nv
What about the excessive number of clicks required to get them working? Clicks to select the dataset, to apply the filter, then to configure whatever criteria might be required. Oh, and to change the criteria too!
All this left me thinking, ‘There must be a better way …’
My Excel ‘Secret Weapons’ are tools that few seem to talk about, but that deliver ‘next level’ analytical power. To this day, I still don’t understand why more people don’t use them. Perhaps they are simply not fashionable. I have used them dozens of times on real world projects to help people get the analyses they need ‘at the click of the button’. Time and again I have seen my customers’ jaw drop when they see what is possible …
What if I told you that one of my ‘Secret Weapons’ allows you to filter data … using an Excel formula? Rather than clicking through the filter menus, we might be able to enter a value in a cell to get the answer we need … and quickly change that cell for a different analysis. Sound good?
Don’t get me wrong, this formula is not easy to set up. A degree of preparation is required. We have to set up a ‘criteria’ area where we designate the columns by which we want to filter, and the values we want to filter by. And there are other considerations, too.
FREE: YOUR EXCEL CHEATSHEET DOWNLOAD
https://www.tigerspreadsheetsolutions...
Perhaps this is why many people give up with DSUM. It takes some precision to set up it up. I point out a few of the pitfalls in the video – column header names must be entered with 100% precision, for example, with a single spelling mistake messing everything up. Oh, Excel!
So, are you up for the challenge? I take you through it stepbystep in this video, so go ahead, download the file and work along with me.
Not only will DSUM (and its friends DCOUNT and DAVERAGE) allow you to stop using filters for good, but it might just open up a world of possibilities for your Excel data analyses.
Let me know how you get on.