Use Excel's FILTER Function Like Never Before

[@GoodlyChandeep]

Download File - goodly.co.in/crazy-excel-filter-function-hacks-i-wish-i-knew-before/

[@Rice0987]

In choosecol func you may use list style of column numbers: {1,2,3}.

[@romulusmilea2747]

The formula to copy the header of raw data table is =Data[#Headers].

[@jimfitch]

Great tutorial. I use FILTER function extensively. It took me a while to figure out these hacks. Your tutorial provides fabulous explanations.

[@richardhay645]

Use CHOOSECOLS and wrap it arround the horizontal array of headers and select the same column numbers as in the CHOOSECOLS wrap around FILTER to line up headers with the chosen FILTER columns.

[@joffrimpong7720]

Thanks for the video, but what it you want to choose a range ?

[@sureshsagaram6365]

Chandeep you are superb in terms of content and clarity u are showing in your videos..Hats off

[@GoodlyChandeep]

Thanks Suresh :)

[@emilmubarakshin49]

Excellent tips and tricks. These are amazing for somebody who wants to transition from Excel to database thinking.

[@donniemcgee7523]

Fantastic tutorial. I can think of many uses for it. Thank you very much.

[@yogirajoo]

Hi Chandeep, many thanks for good informative video . Well, one query.... in this dynamic array of filter function, if I am referring a date field then in the filter output it just shows the number not in date format. How we can carry forward format? I mean on output side how many rows I shall keep date formatted is question. In the Filter formula OR alongwith is there way to format date ?

[@tinytoons2517]

simply brilliant

[@cshahed]

Excellent!!

[@chrism9037]

Thanks Chandeep, super helpful

[@gandhisunil3]

Hello Chandeep, Amazing examples of filter, these can be further automated using developer text box to get the most out of these... I too wished i had these before 🤗🤗

[@hi_vishy]

Wow. Only you showed how to filter on multiple values from a single field. Is it possible to do a SQL like grouping sum, count etc ?

[@EmuMan44]

Can you do approx search for multiple inputs? ie organizations that contain both b and c or even a longer list?

[@taizoondean689]

Thank you 😊

[@kebincui]

Great session👍

[@ManiKandan-bt6uj]

Approximate search is awesome bro

[@igreen4460]

Thank you. Really helpful. Can you kindly assist with the following? I have a table of transactions by customer, payment date, payment amount. I want to extract the customer name, the last payment date and last payment amount. Ignore all other transactions.

[@teoxengineer]

I wanna choose not all columns, two of them ore mixed ones. What will we do?

[@abhisheknandiable]

Amazing Chandeep! Hats off to your knowledge, attitude and the ease with which you explain the concepts. One question for you. How do I generate the column name dynamically? User selects one of the columns from 5 options (cols) and based on that selection the filter function selects that column dynamically

[@Luciano_mp]

Wow, nice tips. Great! Thank you!👍

[@tnssajivasudevan1601]

Great really helpful.😊

[@hemalshahorigamilove]

Nice trick with countif..👍 Do you have choosecol video?

[@pmaguiar]

Excelente vídeo!

[@ddp2049]

This is well created. I going to use it.

[@RiteshSinha0807]

Loved it!!

[@topherl9110]

I enjoy your videos. What approach would you use with Filter to group by columns and sum the results?

[@GoodlyChandeep]

Haven't tried grouping rows using FILTER. I'd rather use Power Query for that. See this might help kzbin.info/www/bejne/oH3TmKdjrNWnfpY

[@topherl9110]

@@GoodlyChandeep Thank you, I've used your Power Query videos before and M-Code. In this instance I had a report to publish based on a DAX Qry with manual edits inserted, and was curious if a sum and group by could be achieved without Pwr Qry. I used a nested LET,UNIQUE,FILTER, HSTACK,SUMIFS,LARGE, INDEX, FILTER, SORT, & FILTER function to come get there. Seems like there is no easy way to do this in Excel. It would be nice if MS would add a GroupBy function that is not in PowerQry or DAX.

[@ShubhamSharma-ls6hj]

Extremely good hacks

[@sujit3375]

Hi Goodly, i am not able to enroll in your course through card please make any other way to buy course

[@GoodlyChandeep]

Can you please send me an email at [e-mail]

[@emilmubarakshin49]

Just tried doing this with text column and the formula wouldn't work - turns out Excel does not like dashes (minuses) (and I would suspect "+/*" either in text values). I took out dashes and it did work. Probably could have put quotations around the text to but went the easy way about it.

[@vijetasaralaya2697]

It takes curly brackets automatically when I use filter formula on excel file....I saved file and changed the data...the formula doesn't work....any suggestions please

[@Scoofnie]

the =filter function doesn't even work on my app, I don't understand why the online version of excel doesn't work the same as the downloaded app version.

[@mohitchaturvedi8931]

Countif was new for me

[@edcooper1471]

Here's my issue with the filter function. It makes the excel spreadsheet so large that I can't email or share it. You can't even upload it to a Google Drive to share because it's so large, not to mention that it takes a much longer time to open up. I have a spreadsheet that only has 419 rows in it, and 13 columns. And the initial size of it, before I used only one "Filter" command, was 262kb; but after I used only one "Filter" command in it, it increased to a size of 37,837KB. So my question is, is it typical for an Excel spreadsheet to increase dramatically when we use these new functions, because I usually create spreadsheets (especially at work) to share with others and colleagues, but if these new functions are making these Excel spreadsheets so large that you can't do that, then wouldn't it make more sense to use the old methods of massaging the data instead of using these new functions?

[@GoodlyChandeep]

It is possible that something else is going on in your spreadsheet. I doubt the file is getting large because of the filter function

[@Milhouse77BS]

The syntax for AND and OR seem unintuitive.

[@kedarhatkar]

The function is great but I don't understand the use case in real life, any examples from anyone will be great

[@CharudattaAmbekar]

I am facing a challenge. I am using the Filter function and using the Filter condition on one of the columns in the source data. However, I do not want to include that filtered column in my output. How do I do that? This is very easy and intuitive in Google Sheets with Query formula but in Excel, it doesn't seem that easy.

[@gandhisunil3]

There can be two ways.. First one choose columns and you mention all column numbers except that which you don't want. Second wrap this in another filter and use xmatch as filter condition

[@martyc5674]

Easiest way is to wrap your original filter function in another filter function, and put for example {1,0,1} as the include argument in the second function- this would exclude column 2 if there were 3 columns. Eg =filter( filter(Data, Data[Name]=“Chandeep”), {1,0,1} )

[@CharudattaAmbekar]

@@martyc5674 Yes, thanks. It works that way but I was looking for a formula that other users will understand. 0,1 parameters may not be so intuitive. Again, what happens if we insert a new column in source data, later.

[@priyeshsanghvi8424]

Hi @martyc5674 , something is wrong........I tried exactly as above but it does not work. Can you please check ? I guess, you have to use CHOOSECOLS only instead of the first FILTER functions

[@martyc5674]

@@priyeshsanghvi8424 no need for choose cols- build your filter function returning all columns, the wrap it in another filter function, the include argument should be in the format {1,0,1,0,} where 1s represent columns you want returned and Zeros you don’t want returned. It works- it’s easy- yes there are better dynamic solutions but this is a great starting point.

[@parimalpatel4827]

Wow....