I use Excel in my spare time and for work, I do not know why I just love working on excel and this video put a huge smile on my face. It is like a new toy to play with. Thank you so much for this video, I cannot wait to start using this information
This is exactly what I have been looking for to create a digitized Inventory log of items going out to projects/foreman. This will help me with creating an end of year usage statistics. Thank you!
Great solution in Office365. Most of the flexibility can be done/solved in earlier versions as well, in an easy way. - Use Data, Advanced filter on the list. - Create a condition area with all the column names (all column filter combinations then possible for output) at the top, and choice of filter values below them. - Create dropdown boxes to flexibly choose column values inside the condition area. - Specify an output area with all the column names at the top and empty space below, use that area for the Advanced filter output "Copy output to new area". - Create a macro button which automatically updates the Advanced filter, after the condition area has been updated via dropdown. No formulas needed.
Thank you for this Great Video! Question, and I have two questions... 1. Say you want to have both filters, then you want to deselect one over the other. Ex. You want to filter the table to just brands and not branch, without removing the branch drop down menu cell. How would you do this? 2. Let's say you only have the branch drop down cell. How would you add a total cell right below the branch drop down menu and the only reason it would be at the top is for convenience sake where you don't have to scroll all the way down to find the total (if there is such a thing) and it can continuously add the total at the top dependent of the table in which data is being added.
Hello Chester. This is a very helpful video. I am wondering if can we use spin button from the developer tab instead of drop down list. For example: the first spin button will spin branches and the second spin button will spin only those brands of a branch selected by first spin. Thanks
Thank you Chester! this is has solved a lot of my requirements. There are two queries that I have, (1) is there anyway we can get only certain columns instead of all (2) can we sort the filtered data based on one particular column (in my case there is date and time and I would like to have the latest date on top instead at the bottom)
for (1) You can use the =ChooseCols Function before the filter function for the final extract. After the Filter argument is close off, just add a comma and the column number you want, e.g if your final extract is showing 5 columns and you want the display on the 4th one use ")), 4)". Please see my command as an example: "=CHOOSECOLS(FILTER(TemplateCodes,(TemplateCodes[Company]=C2)*(TemplateCodes[Property]=C4)*(TemplateCodes[Type]=C6)), 4)"
Fantastic thank you so much. Very very easy to understand your explanations too. I'm curious though, how would you return just the total value of sales per item especially as the data can continually expand?
Hello, Congratulations, your videos are the best! Please help me with this... How can I include a "Select all" option in a drop down list?, because once an option is selected I can't look all the options again.
say, I have my two drop-down in my list already to go because I do. But I want the second list to be optional not that I have to select something it’s more of if I just want to see that specific data but if I want to see all the data in that drop-down how do I do that?
This is a really good trick, thanks for taking the time to share knowledge, I do have a question extent of this. 1) We have employee performance data with multiple details, 2) we get this data week basis 2) We select each employee's data from the validation list 3) We take this screenshot 4) Copy the screenshot in an email 5) Send that to employee - I see challenge to select 20 employees from data validation list and copying screenshot, is there any way we can work on this?
Very useful. If you wanted to show another table below, for instance lets say you had orders, is there any way to deal with the #SPILL error you get where data wants to overlap the below table?
great video. i have cells with long text on them. i can sort them out now with your great tutorial. however the results does not fit into the cells. so i went to properties and made the WRAP TEXT clicked and it sorted out the problem. but the wrapped cells remains in the same size and every new query showing in the same cell size and other cells with longer texts needs text wrapping. and it repeats. excel does not actively wrapping text on new query. any ideas?
Nice video... but sir how to filter All data in using data validation.. I mean to say Shown the report All Branches and any brand, otherwise All band and any one branches.... sir please create this type of video and please please please reply me...thank you
Is there a way to "filter all" or show all of the branches at once? I'd like to be able to have a drop down to select individual branches, and an option within that dropdown to show all branches (or in my case all departments.)
Thank you for the info. I have done this and it works well, but when I try to sort one of the columns (numerical) by oldest to newest it says cannot change part of an array. Is there way around this.. Thank you.
For me, the unique function also returned a value '0' as the last field in the list. To avoid it, I learnt that it can be modified e.g. as 'UNIQUE(FILTER(SalesData[Brand], SalesData[Brand]""), false)'.
thank you! very helpful. I am trying to find a way to do just this, only using multiple sheets. so the unique LIST would need to be compiled from multiple sheets as well. i have not found a tidy way to do create a unique list, without first dumping all of the separate sheets into a single master sheet first i tried: =SORT(UNIQUE({TABLE1[BUILDING];TABLE2[BUILDING]})) but excel did not like it ;) would really love your input, thankyou ( also tried a few others that did not work ;)
Is there a way, you can bring back the data for certain columns, rather than all ? This works for what i want it for, but in my instance i don't want to show the "branch" column in the data that comes back based of the dropdown list. Other than obviously hiding that column, is there another way to do this?
Is there a way to get links to work when they come up in the list? I have a links column that allows you to click the link to go to specific videos in the database. However, even though the link text goes through, it's no longer hyperlinked, making it impossible to click through to the video. And you can't even copy and paste because the data isn't actually in the cells.
Hi, at 3:48 what if the source array table has empty cells within? The FILTER will instead display 0 on empty cells or 00-Jan-1900 on cells formatted as dates. How should I maintain the corresponding cells to be empty instead of showing 0 ?
Is there a way to show only the data on exact table rows only. the problem is i have 10 rows but the data i filtered is 3 rows only, now i have 7 blank rows. i want to hide it automatically while doing the filter. Please help.
what would be the formula to list 2 collumns on another sheet and then have a drop down box that could filter through certains attributes ie employee role?
Absolute legend. Took me hours to find the right tutorial and this was exactly what i required. Very easy to follow
I use Excel in my spare time and for work, I do not know why I just love working on excel and this video put a huge smile on my face. It is like a new toy to play with. Thank you so much for this video, I cannot wait to start using this information
Saying this was useful is an understatement. You're a legend.
This is the present and future of Excel, focused on dynamic array formulas and A.I. Thank you Chester!!! Another great tutorial!!
This is exactly what I have been looking for to create a digitized Inventory log of items going out to projects/foreman. This will help me with creating an end of year usage statistics. Thank you!
Best video on drop down sorting. Highly recommended.
Great solution in Office365. Most of the flexibility can be done/solved in earlier versions as well, in an easy way.
- Use Data, Advanced filter on the list.
- Create a condition area with all the column names (all column filter combinations then possible for output) at the top, and choice of filter values below them.
- Create dropdown boxes to flexibly choose column values inside the condition area.
- Specify an output area with all the column names at the top and empty space below, use that area for the Advanced filter output "Copy output to new area".
- Create a macro button which automatically updates the Advanced filter, after the condition area has been updated via dropdown.
No formulas needed.
FINALLY! A solution that just makes sense! Thank you so much for this video!
Respected sir, am your excel tutorials lover because you give creativity to do something new and new, thanks a lot.
Extremely helpful you saved me hours of work, thank you!🎉
So amazing , i created my manual invoice list into a statement with this video with number as a unique identifier ... wooohoooo .... excellent !!!!
This is absolutely amazing! Needed so many formulae in the older versions
This is so great. You saved my life finding out how to do this in Macro. Thank youuuuu
Very useful one and working fine after follow your instructions. Thanks
This helped me immensely ! Now the limits are endless thank you!
Saved my life for a conditional report. Thank you!
thank you for the video, I am experimenting to apply this Filtering data in my Class record.
This is 100% what I was looking for! Amazing, 10/10!
Thank you for this, I’ve been looking for this solution for a couple days now!
Absolutely great, so helpful! Really great explanation as well. very happy with this, now to bed...
Thanks... Just opened up a whole path for me to expand my reporting!
Thank you for this Great Video! Question, and I have two questions...
1. Say you want to have both filters, then you want to deselect one over the other.
Ex. You want to filter the table to just brands and not branch, without removing the branch drop down menu cell.
How would you do this?
2. Let's say you only have the branch drop down cell. How would you add a total cell right below the branch drop down menu and the only reason it would be at the top is for convenience sake where you don't have to scroll all the way down to find the total (if there is such a thing) and it can continuously add the total at the top dependent of the table in which data is being added.
Thanks so much mate, was given a task at work and this is exactly what I needed!
MOST THANKS SIR, I HAVE WASTED LOT OF TIME BUT YOU SOLVE IT
THANKS
Super helpful, Thank you so much!
At last I have found what I needed. Thanks a lot.
Hello Chester. This is a very helpful video. I am wondering if can we use spin button from the developer tab instead of drop down list. For example: the first spin button will spin branches and the second spin button will spin only those brands of a branch selected by first spin. Thanks
Like the video, was easy to follow along but still returns 0 when using the filter function. really need to get this working ha
Thank you Chester! this is has solved a lot of my requirements. There are two queries that I have, (1) is there anyway we can get only certain columns instead of all (2) can we sort the filtered data based on one particular column (in my case there is date and time and I would like to have the latest date on top instead at the bottom)
for (1) You can use the =ChooseCols Function before the filter function for the final extract. After the Filter argument is close off, just add a comma and the column number you want, e.g if your final extract is showing 5 columns and you want the display on the 4th one use ")), 4)". Please see my command as an example: "=CHOOSECOLS(FILTER(TemplateCodes,(TemplateCodes[Company]=C2)*(TemplateCodes[Property]=C4)*(TemplateCodes[Type]=C6)), 4)"
Just what I was looking for!!!! Thanks you!
Great Video, you saved my life, Sir!
Great tutorial! Thanks! I have a question though, what if there are no data in the "brand" search box? It should be able to show all brands right?
Amazing! You just made my day!❤❤❤
Excellent and Thank you Very much...!
This is brilliant, but is there a way you can also sort the filtered data? Say in ascending order within a specific column?
Thanks Chester
Fantastic thank you so much. Very very easy to understand your explanations too. I'm curious though, how would you return just the total value of sales per item especially as the data can continually expand?
Extremely Helpful. this is great.
Awesome demo. Thanks heaps!!!!!
Thank you so much. This video has been so helpful.
Huge thank you!!
Thank you for this straightforward example!!! This is such a huuuge help :D
Thank you 🙏
Hello, Congratulations, your videos are the best!
Please help me with this... How can I include a "Select all" option in a drop down list?, because once an option is selected I can't look all the options again.
say, I have my two drop-down in my list already to go because I do. But I want the second list to be optional not that I have to select something it’s more of if I just want to see that specific data but if I want to see all the data in that drop-down how do I do that?
Simple and easy
WOW Awesome
GAHHH - our office only uses office 2019! is this possible with that? This would be a lifesaver!
Thanks
Many thanks Sir very helpful
Perfect
Great explanation! worked very well! Thank you
Hi Chester, Is there any chance you could do a Video of this using Excel 2019?
This is a really good trick, thanks for taking the time to share knowledge, I do have a question extent of this. 1) We have employee performance data with multiple details, 2) we get this data week basis 2) We select each employee's data from the validation list 3) We take this screenshot 4) Copy the screenshot in an email 5) Send that to employee - I see challenge to select 20 employees from data validation list and copying screenshot, is there any way we can work on this?
Thank you!! Super helpful!
this is super helpful! thank you!
Very useful. If you wanted to show another table below, for instance lets say you had orders, is there any way to deal with the #SPILL error you get where data wants to overlap the below table?
really you made very easy
Thank you so much!
Thank you so much
YOUR EXCEL VIDEOS ARE SIMPLE AND MORE CREATIVE......I SALUTE YOU....do u have any advanced level kind of training..not the basics...
Bravo!
great video. i have cells with long text on them. i can sort them out now with your great tutorial. however the results does not fit into the cells. so i went to properties and made the WRAP TEXT clicked and it sorted out the problem. but the wrapped cells remains in the same size and every new query showing in the same cell size and other cells with longer texts needs text wrapping. and it repeats. excel does not actively wrapping text on new query. any ideas?
Nice video... but sir how to filter All data in using data validation.. I mean to say Shown the report All Branches and any brand, otherwise All band and any one branches.... sir please create this type of video and please please please reply me...thank you
I have the same question. Did you ever find a solution?
Thanks!
Is there a way to "filter all" or show all of the branches at once? I'd like to be able to have a drop down to select individual branches, and an option within that dropdown to show all branches (or in my case all departments.)
Is it possible the filter function to bring spesific colums from the table?
Thank you for the info. I have done this and it works well, but when I try to sort one of the columns (numerical) by oldest to newest it says cannot change part of an array. Is there way around this.. Thank you.
For me, the unique function also returned a value '0' as the last field in the list. To avoid it, I learnt that it can be modified e.g. as 'UNIQUE(FILTER(SalesData[Brand], SalesData[Brand]""), false)'.
thank you! :D
That's "learned" ;)
This is great. Thank you.
Hi is it possible to extract only specific column instead of the whole table?
👍Superb Sir 👋
thank you! very helpful. I am trying to find a way to do just this, only using multiple sheets. so the unique LIST would need to be compiled from multiple sheets as well. i have not found a tidy way to do create a unique list, without first dumping all of the separate sheets into a single master sheet first i tried: =SORT(UNIQUE({TABLE1[BUILDING];TABLE2[BUILDING]})) but excel did not like it ;) would really love your input, thankyou ( also tried a few others that did not work ;)
Can we extract the data retaining the formating of the text from the respective cell?
Can you select branch and month? Do you use the same formula?
Awesome! thanks a lot!
You are amazing!
If the source column I am using has hyperlinks to web-based documents how would you get the hyperlink to come across using the filter equation?
Kindly share me if you have any links to perform the same with multiple sheets. Say we have Demo1, Demo2 and Demo3 Sheets
Can you now take the products with qty > 2 from each branch to a new list? From the new table (H:M)
How do you do this when the arrays you want to show are in different sheet tabs?
really amazing
Is there a way, you can bring back the data for certain columns, rather than all ? This works for what i want it for, but in my instance i don't want to show the "branch" column in the data that comes back based of the dropdown list.
Other than obviously hiding that column, is there another way to do this?
Thankyou sir
How to use this functions when I want a data between dates with an specific text . Thank you
Please make it for non 365 users Mr T 🙏
Does this work for google sheets?
Those function like filter, unique etc are not available on older version like excel 2016,2019. How do they do it?
great job
awesome
Is there a way to do this so that formatting from the original data table is also copied over to the cells in the drop down list table?
Hey... My Excel version is 2016 so the filter & unique formulas not working. Can you suggest me a same formula.
Is there a way to get links to work when they come up in the list? I have a links column that allows you to click the link to go to specific videos in the database. However, even though the link text goes through, it's no longer hyperlinked, making it impossible to click through to the video. And you can't even copy and paste because the data isn't actually in the cells.
I have one doubt if someone could answer that for me, what if don’t need all the columns of tables but only specific ones how can I get that
How would I do this without the filter function
In 2019 there isn't Filter formula. Can you demonstrate a solution about it?
Many thanks
Hi, at 3:48 what if the source array table has empty cells within? The FILTER will instead display 0 on empty cells or 00-Jan-1900 on cells formatted as dates. How should I maintain the corresponding cells to be empty instead of showing 0 ?
Is there a way to do this by using the Date field extracting the year to a list and the month to a list by which you would create your criteria?
Is there a way to show only the data on exact table rows only. the problem is i have 10 rows but the data i filtered is 3 rows only, now i have 7 blank rows. i want to hide it automatically while doing the filter. Please help.
what would be the formula to list 2 collumns on another sheet and then have a drop down box that could filter through certains attributes ie employee role?