Don't Use Excel Filters! Use This Incredible Excel Formula Instead ...

are you tired of using excel filters now don't get me wrong you can get a lot of good analysis with excel filters but it's so tiring it takes so long so many mouse clicks in today's session i'm going to show you the one formula that i use to do the jobs of those filters so you can get away from using filtering at all that's what we're going to do in the next 10 minutes but if we're meeting for the first time i'm chris mortimer i'm an excel consultant lecturer content creator and i love demystifying excel for people like you and get you using the powerful stuff before we get into it let me tell you about my excel cheat sheet now i've simplified excel for you in this cheat sheet i've highlighted the 21 formally and 13 techniques that you need to know it's going to simplify your excel learning you can get it for absolutely free the link is below this video so go ahead and get your excel cheat sheet let's get into this one so i've got a typical date set here now this is from the doggy football league and fans of the channel might know something about the doggy football league it's totally fictional but it's a typical date set that you might be working with and let's suppose we want to test some betting strategies i don't bet myself but a lot of people bet out there and a lot of people are using excel to analyze strategies so let's test this strategy we want to bet on one of the teams which is beagle uh when they're playing at home if the odds are more than 1.54 and if you know anything about sports betting i don't know much but i do know that one is a home win x is a draw and two is an away win so we've got all of the home mods down here so when beagle are playing and the odds are more than 1.54 we're going to put a pound on then over on the right we've got our returns column hair and you can see that when the home team doesn't win it says minus one we lose our money when the home team does win then you can see the winnings go in that so it's a typical kind of data situation how would you go about doing this first let's do it with filters yes i'm going to break my rule i'm going to do this with with filters so i'm going to teach you some keyboard shortcuts about filters i'm going to do these on windows you can find them on mac with a quick google search control shift rights control shift down is going to select the whole data set alt a t alt a t is going to apply the filters and then you can go to the column you can navigate there using the keyboard using the arrows go to the column where you want to apply the filter alt and down arrow is going to get you into the filter menu i'm going to tab down to the little text a little search box here i'm going to type in beagle and hit enter and you can see i've now put a filter on for beagle so you can do it all with the keyboard that may might make life a bit easier for you but i'm hoping you're going to use my formula anyway let's do the same thing here so alt down arrow in the home odds column this time we're going to go for number filters tap our number filter filters enter and then greater than and this is where we're going to put our 1.54 figure and then we hit okay now we want to see what that figure is so ctrl shift down again and i can see the bottom of excel here some 11.62 so this system is going to have a return of 11.62 we can prove that with filters but you don't want to do all of that clicking can you imagine if you wanted to test 10 systems 100 systems i build systems like this to test tens of thousands of systems of people you wouldn't want to be doing all that filtering so let's find a better way to do it ladies and gentlemen i give you that the sum formula are you using the dsun formula it requires a little bit of setup but if you can do the setup and get it working it is so powerful it is next level stuff so what is that set up go ahead and choose a couple of cells it's got to be two rows high and then for us two columns across is going to be fine but you can actually use any number of columns i'll tell you more about that in a second alt-h-b-a alt-h-b-a let's put some borders on there just to remind us um that this is where we're going to build our b-sum formula and then we've got to choose what columns do we want to filter by so previously we've gone through those columns and done it manually when you use d sum you type the columns that you want to use into this table in the top row so what columns are we interested in we're interested in the home column because that's where our team beagle is and then we're interested in the home odds so this is column one so the two columns i'm interested in i'm going to put the top of the table now i'm doing a two criteria analysis looking at the home team looking at the odds with dsum you can do any number of criteria you can do one criteria you can do i've done up to eight ten criteria super powerful stuff so make sure you give it a go yourself so home and now we've got to put in the criteria now here we've got to be accurate we've got to get our programmers mindset on we have got to get this spelling 100 right otherwise it's going to be problematic so the same thing here more than 1.54 we've got to know the notation normal mathematical notation again it's got to be 100 precise or that's going to be problematic this is how we say to excel more than 1.54 so we need this setup we do need to take the time to put this setup in but once you've got the setup you can then go ahead and build the formula you're going to love it it's going to change transform the way you're doing another data analysis i have no doubt so be sub open brackets then as always we take a pause what is excel asking for what is excel asking for here and with dsum it's reasonably intuitive or maybe not but some of it is intuitive so excel's asking for a database excel is saying where's this data set that you want me to do the analysis on that's fairly straightforward so again keyboard shortcuts i'm going to use in fact i'm going to go ahead first and take the filters off alt a t take the filters off that's just going to make sure that i include all of the rows i want in my analysis so equals d sub control shift right control shift down and i can see the whole area is selected for completeness if you want to be teacher's pet f4 key then we're going to have absolute references should we take the formula anywhere else and then d sub is asking for field field so it's saying which column do you want me to add up yeah remember we're adding up returns we've got this returns column over here and as we did with the filters at the beginning when we just selected all the values and looked at the bottom to get the sum we want these sum to do that for us so you can type in the name of the column you can also use a column reference number i've found yeah more recently i use the column name seems to work for me so returns home win again computer programmer head on that's got to be 100 accurate or we're going to have problems then finally and maybe least intuitively criteria criteria so dsum is asking for criteria now criteria is a data analysis word for which data do you want to filter out yeah and remember that little table that we set up those four cells they are our criteria and we've got to make sure we've got the whole table selected there so this is the setup we need for dsum got to select the data set we've got to designate the cell the column that contains the data we want to sum up that's what dsm's going to do it's going to sum data up and then finally we've got to specify where we've set up our criteria i'm going to put the absolute references in there using the f4 key because i'm a bit of a teacher's pet and then close the brackets there hit enter what do we got 11.62 rings about 11.62 rings about so let's go ahead do it again with filters ctrl shift right ctrl shift down alt a t over to the home column want to filter by that column alt and down arrow down to the search box and type in beagle here and then enter so that's our first filter on over to the home odds alt down number filter enter greater than enter uh 1.54 so we don't need the greater than sign here because it says in the box is greater than and then enter and let's just prove this again ctrl shift down i can see at the bottom here summing up 11.62 and this is the power of b sub i can just go ahead say i want to look at a different team now alt a t take the filters off say i want to look at great dane i just type it into our criteria area how about that and you can go ahead and combine this with other techniques with a bit of vba to do really cool optimizations it's one of my excel secret weapons so make sure you give these some a go and don't forget i'm simplifying excel for you with my excel cheat sheet i've picked out the 21xl formulae including dsum that are going to simplify your excel excel practice and push things forward for you go ahead and download it absolutely free just follow the link stick your email then i'm gonna send it to you take care i'll see you in the next one

Channel: Tiger Spreadsheet Solutions

Views: 654,349

Rating: 4.8201184 out of 5

Keywords: How To Do Filters In Excel, How To Apply A Filter In Excel, Alternatives To Filtering In Excel, Excel Filter Shortcut, Excel DSUM Formula Tutorial, Hwo To Do Data Analysis In Excel

Id: Qi1nfN5F8F4

Length: 9min 32sec (572 seconds)

Published: Fri Jan 22 2021