Use Excel's FILTER Function Like Never Before

Рет қаралды 13,801

Күн бұрын

In this video, we will learn how to use advanced Excel filtering with the Filter function in Excel. Discover cool tricks like filtering by multiple criteria, dynamic filtering, and tricks like filtering by a list of values and conducting wildcard searches. Improve your data analysis skills and become an Excel pro!
#filterfunction #excelfunction #exceltricks
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨‍🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/crazy-excel-filt...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!

Пікірлер: 47

@GoodlyChandeep 9 ай бұрын

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

@romulusmilea2747 9 ай бұрын

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

@Rice8520 9 ай бұрын

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

@jimfitch 9 ай бұрын

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

@richardhay645 9 ай бұрын

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.

@cshahed 9 ай бұрын

Excellent!!

@pmaguiar 9 ай бұрын

Excelente vídeo!

@RiteshSinha0807 9 ай бұрын

Loved it!!

@Luciano_mp 9 ай бұрын

Wow, nice tips. Great! Thank you!👍

@tnssajivasudevan1601 9 ай бұрын

Great really helpful.😊

@kebincui 9 ай бұрын

Great session👍

@taizoondean689 9 ай бұрын

Thank you 😊

@chrism9037 9 ай бұрын

Thanks Chandeep, super helpful

@donniemcgee7523 9 ай бұрын

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

@ManiKandan-bt6uj 9 ай бұрын

Approximate search is awesome bro

@igreen4460 21 күн бұрын

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.

@emilmubarakshin49 9 ай бұрын

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

@ShubhamSharma-ls6hj 9 ай бұрын

Extremely good hacks

@emilmubarakshin49 8 ай бұрын

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.

@sureshsagaram6365 8 ай бұрын

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

@GoodlyChandeep 8 ай бұрын

Thanks Suresh :)

@hemalshahorigamilove 9 ай бұрын

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

@gandhisunil3 9 ай бұрын

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 🤗🤗

@EmuMan44 9 ай бұрын

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

@ddp2049 3 ай бұрын

This is well created. I going to use it.

@vijetasaralaya2697 7 ай бұрын

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

@parimalpatel4827 9 ай бұрын

Wow....

@kedarhatkar 9 ай бұрын

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

@abhisheknandiable 8 ай бұрын

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

@mohitchaturvedi8931 9 ай бұрын

Countif was new for me

@teoxengineer 3 ай бұрын

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

@topherl9110 9 ай бұрын

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

@GoodlyChandeep 9 ай бұрын

Haven't tried grouping rows using FILTER. I'd rather use Power Query for that. See this might help kzfaq.info/get/bejne/oLKgmtde3NbYeGg.html

@topherl9110 9 ай бұрын

@@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.

@sujit3375 9 ай бұрын

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

@GoodlyChandeep 9 ай бұрын

Can you please send me an email at goodly.wordpress@gmail.com

@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.

@Milhouse77BS 9 ай бұрын

The syntax for AND and OR seem unintuitive.

@edcooper1471 2 ай бұрын

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 2 ай бұрын

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

@CharudattaAmbekar 9 ай бұрын

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 9 ай бұрын

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 9 ай бұрын

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 9 ай бұрын

@@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 6 ай бұрын

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 6 ай бұрын

@@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.