Awesome tutorial on querying a dataset with multiple criteria. I now realize that slicing and filtering are interchangeable in Excel, subsetting the dataset based on a criteria. Slicing is more effective for categorical fields, while filtering is more flexible allowing more logic operators (less than, great than, not igual, and, or,..., contains, between). Here is the interesting fact with alternative data analysis tools. With R and Python, slicing and filtering are approached differently. Now back to Filters in Excel, and inspired by another Teach Excel tutorial on filtering dates based on period, how intricate or hard could be to have a helper column (periods) with categories (this month, last month, this year, last year and YTD) and then insert a slicer. I mean approching the selection of period based on slicer, instead of a drop down list selection, filter(), helper setting with start, ends and vlookup().
I wanted to hug you at the start, some areas I particularly struggle with you make look easy, little complicated for me in the middle, but great presentation at the end. Thank you.
Aw Kathy that's so nice to hear :) I'm glad you liked it! I would say that this feature becomes easy only after you use it a lot because, as you noticed, it does require the combination of a number of seprate topics, but just keep practicing this setup and I promise it will flow from your Excel worksheet when you need it. :)
Nice job! Is there any difference between using the text function for creating the helper column or using cell formatting for a column that contains the date by using a formula referring to the original date column? In the first case, the data type changes to TEXT, while in the second case it remains NUMBER. Does this have any significance? Anyway, if I want to sort the raw data by the day of the week, I have to change the sort order to the custom list in both cases.
is there a way to save space and make the slicer a interactive via a drop down list as apposed to either scrolliing or making it large enough to see all available options?
How we dynamically can extract the number of rows for: this month and last month, this quarter and last quarter, this year and last year from a table? Lets say there is column for thin 1, thing 2, thing 3 etc and each occur on daily basis and we want to know the number of those occurrences for the current month, quarter and year as well as the last ones? Could someone please help?
I suppose that I do not need to say that phrase so often, but, then again, I also do not need to make video tutorials online or repsond to comments, but I am happy when I can help someone and I am sorry for you if my speech patterns are too distracting. The funny thing is that I am more worried about saying "so" too often, haha.
I find your articulation melodic-didactic-methodical-cool/kewl. You're always understandable to me. Some of your peers are, too. I LIKE watching your tutorials for the lessons and engaging speeches. Thanks!
Very informative video, thank you so much, but can I ask you how I go on making it a start and end date like a 6-day week summary, can you please assist me with this problem
Awesome tutorial on querying a dataset with multiple criteria. I now realize that slicing and filtering are interchangeable in Excel, subsetting the dataset based on a criteria. Slicing is more effective for categorical fields, while filtering is more flexible allowing more logic operators (less than, great than, not igual, and, or,..., contains, between). Here is the interesting fact with alternative data analysis tools.
With R and Python, slicing and filtering are approached differently.
Now back to Filters in Excel, and inspired by another Teach Excel tutorial on filtering dates based on period, how intricate or hard could be to have a helper column (periods) with categories (this month, last month, this year, last year and YTD) and then insert a slicer. I mean approching the selection of period based on slicer, instead of a drop down list selection, filter(), helper setting with start, ends and vlookup().
I wanted to hug you at the start, some areas I particularly struggle with you make look easy, little complicated for me in the middle, but great presentation at the end. Thank you.
Aw Kathy that's so nice to hear :) I'm glad you liked it! I would say that this feature becomes easy only after you use it a lot because, as you noticed, it does require the combination of a number of seprate topics, but just keep practicing this setup and I promise it will flow from your Excel worksheet when you need it. :)
I've needed this for years. How many hours have I wasted? Good work, thank you.
Thanks Dave! I'm really glad I could help!
Great Stuff,Surprising What A Few Slicers And Some Formatting Can Do To Make Your Worksheet Look Professional...Thank You Sir :)
Thanks Darryl! Honestly, sometimes I'm surprise even by how much the simple Slicer can improve a spreadsheet!
Awesome video. You made my day, and I mean it. Literally!
Great tutorial! Thanks :-)
You are very welcome Alex! I'm glad you liked it :)
Nice, thanks!
I forgot to say Thank You!
Well done
Thank you! :)
Am looking for a report which query by a date range + other tow fields also in excel
Are you able to share this to use by multiple users
Thanks.
You're very welcome Sonia! :)
Nice job!
Is there any difference between using the text function for creating the helper column or using cell formatting for a column that contains the date by using a formula referring to the original date column? In the first case, the data type changes to TEXT, while in the second case it remains NUMBER. Does this have any significance?
Anyway, if I want to sort the raw data by the day of the week, I have to change the sort order to the custom list in both cases.
is there a way to save space and make the slicer a interactive via a drop down list as apposed to either scrolliing or making it large enough to see all available options?
How we dynamically can extract the number of rows for: this month and last month, this quarter and last quarter, this year and last year from a table? Lets say there is column for thin 1, thing 2, thing 3 etc and each occur on daily basis and we want to know the number of those occurrences for the current month, quarter and year as well as the last ones? Could someone please help?
256👍
2^8 ;-)
Great video but why do you need to “go ahead” so often?
I suppose that I do not need to say that phrase so often, but, then again, I also do not need to make video tutorials online or repsond to comments, but I am happy when I can help someone and I am sorry for you if my speech patterns are too distracting. The funny thing is that I am more worried about saying "so" too often, haha.
I find your articulation melodic-didactic-methodical-cool/kewl.
You're always understandable to me. Some of your peers are, too.
I LIKE watching your tutorials for the lessons and engaging speeches.
Thanks!
What a nice thing to hear! Thank you David!!! :)
Very informative video, thank you so much, but can I ask you how I go on making it a start and end date like a 6-day week summary, can you please assist me with this problem