Use Excel's FILTER Function Like Never Before
Vložit
- čas přidán 4. 07. 2024
- 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! - Věda a technologie
Download File - goodly.co.in/crazy-excel-filter-function-hacks-i-wish-i-knew-before/
In choosecol func you may use list style of column numbers: {1,2,3}.
The formula to copy the header of raw data table is =Data[#Headers].
Great tutorial. I use FILTER function extensively. It took me a while to figure out these hacks. Your tutorial provides fabulous explanations.
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.
simply brilliant
Wow, nice tips. Great! Thank you!👍
Loved it!!
Fantastic tutorial. I can think of many uses for it. Thank you very much.
Excellent!!
Excelente vídeo!
Thanks Chandeep, super helpful
Thanks for the video, but what it you want to choose a range ?
Great session👍
Excellent tips and tricks. These are amazing for somebody who wants to transition from Excel to database thinking.
Great really helpful.😊
Thank you 😊
This is well created. I going to use it.
Extremely good hacks
Approximate search is awesome bro
Chandeep you are superb in terms of content and clarity u are showing in your videos..Hats off
Thanks Suresh :)
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 🤗🤗
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.
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
Nice trick with countif..👍
Do you have choosecol video?
Countif was new for me
Can you do approx search for multiple inputs? ie organizations that contain both b and c or even a longer list?
Wow....
I wanna choose not all columns, two of them ore mixed ones. What will we do?
I enjoy your videos. What approach would you use with Filter to group by columns and sum the results?
Haven't tried grouping rows using FILTER. I'd rather use Power Query for that. See this might help czcams.com/video/jLpgt-wptH4/video.html
@@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.
The function is great but I don't understand the use case in real life, any examples from anyone will be great
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.
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
Hi Goodly, i am not able to enroll in your course through card please make any other way to buy course
Can you please send me an email at goodly.wordpress@gmail.com
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.
The syntax for AND and OR seem unintuitive.
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?
It is possible that something else is going on in your spreadsheet. I doubt the file is getting large because of the filter function
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.
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
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}
)
@@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.
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
@@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.