I am absolutely loving all these videos. Been working in Excel for 30 years and have expertise, but some of the little things are amazing. Love the # in the source section. Love the arrow to capture the table name. Love the unique usage in the date, etc. I use Alt (or F10) and A T for filters. I am a huge keyboard person, so it is nice the newer Excel version still allows me to use my old timey keystrokes. :)
Some other program on my pc high jacked my ctrl+shift+L earlier this year and I haven’t been able to use it. I work around it with menu acceleration keys but muscle memory really wants that shortcut to work. Very clear video. Excellent!
Hi Mike, I never have used the TEXT function to create a unique identifier for dates in a specific month but always the EOMONTH function. It also works in this scenario. For Data Validation list I will use =SORT(UNIQUE(EOMONTH(--HireTableAnswer[HireDate],0))) and for the main function "=FILTER(HireTableAnswer,EOMONTH(--HireTableAnswer[HireDate],0)=D4). I don't know if there will be a speed advantage, but just another FUN way of doing it. Extra thing to do is to do your formatting in Format Cells where in your example there is no need to do this.
@@excel_along_the_way Thank you for your keen insight, Laster!!! The funny things is that in DAX, with Date Tables, I do use the EOMONTH for month unique identifier. But here I was thinking that visually for the dropdown, it would be better to use TEXT. However, I am not sure the time advantage in Microsoft 365 (because I have not done much testing in this version), but in older versions there is a big calcuation time advantage to use EOMONTH or combo of YEAR and MONTH over TEXT : )
@@excelisfun I have notice that you use it in DAX as I have started to follow your tutorials on PQ and Power BI. Years ago I discovered EOMONTH and always used it as a unique identifier and your video on how to get EOMONTH to spill in 365 with the double negative was such a life saver.
I was using the same formula you used, =SORT(UNIQUE (TEXT("Column", "yyy-mm"))). But I had a lot of blank cells, and those blank cells would return as 1900-01. The blank cells will be filled with more future data that I would get. So what I did was I took a random column(AV), and I typed. =IF(A8="","", TEXT(A8, "yyy-mm")) . And I draged that formula down to the amount of columns I was using. Column A is my date column btw. Than I used the =SORT(UNIQUE (TEXT(AV8:AV600, "yyy-mm"))). But it wouldn't sort the month and day properly, soo I ended up switching the formula around to, =UNIQUE (SORT(TEXT(AV8:AV600, "yyy-mm"))). And it worked perfectly. 👌 Thank you soo much, Mr. Excel. This was exactly what I was looking for. Thank you.
FILTER is just about best new function and at least: it seems to have the most different applications for a single function. You are welcome as always, Teammate Chris M!!!
I like this video !! I'm a big fan and regular user of FILTER. I often filter by date parameters. Helper columns seem not to be politically correct but I find them extremely tempting esp with anything that might become a FILTER criterion. After all spreadsheet real estate is not Scarce with 2^14 columns avaiable!!! So a sheet I was updating a few moments ago has separate columns for MONTH, YEAR & WEEK by using TEXT() for month and year and WEEKNUMBER for week. This gives me max flexibility to build reports quickly with FILTER. Putting month and year in the same cell is a good look but some of my sheets eventually grow to five or even ten years of transaction data making such combo dropdowns rather unweildy. Often I filter by complex multiple criteria (such as jan 2021 and jan 2019 in one FILTER report) so I need max flexiblity in referencing. I use linked reference cells but type in criteria for each report and reference such cells with FILTER. I haven't used filter buttons for a long time. If I want to Filter in plce I normally use Table Slicers.
Great methods for filter by data criteria, Richard "Excel Word Poet" Hay!!!! The one use I have for the Filter feature is to quickly copy and paste a set of records. Nothing beats that. I like your helper columns too. The only reason that people might not want to use them is that they add lots of formulas. And TEXT function calculates more slowly than other functions like MONTH and YEAR. But as you say: the clear advantage to helper columns is that they make that FILTER function mush easier to use later on. Thanks for liking this video, Richard Hay!!!
@@excelisfun I use the TEXT Function for month to get the month name while MONTH function just extracts numbers. I use it for year out of (bad?) habit. YEAR Function works just as well as TEXT. Why are Table Slicers not more popular and more regulaly taught? Many users seem not to even realize they exist. I find them quick, easy and flexible!!
@@richardhay645 I don't think YEAR is bad habit. I think Table Slicers are not known, because they came out for PivotTables first, then much later they came out for Excel Tables. Buy that time, all of use PivotTables and didn't think about the power they can bring to Excel Tables.
@@excelisfun yeah, I usually clean up my tables by turning off the Filter buttons and inserting Table Slicers for the copy/paste thing and for the other quick and dirty stuff!!
Great video, thank you. How about creating a dynamic report showing the totals in this format: This Month, Last Month and this quarter, last quarter and this year, last year at all times without any extra steps. something like a dashboard which always only automatically calculates and shows above mentioned values?
When we convert date numbers into text, how can we convert them into value from the text again? As we know that date values seem just text not value and sometimes it causes some calculation problem because of text convertion. Thank you Mike for this benefit tutorial. 🙏🏻
As, Joseph said, any math operation (that does not change the value) on text numbers converts the text numbers back to numbers. *1, /1, ^1, --, +0 all will convert text numbers back to numbers,
Ctrl + T creates an Excel Table Object with formatting, sorting, filtering, a total row and dynamic ranges. Ctrl + Shift + L just adds filter dropdowns.
@@excelisfun I've not ordered an actual, tangible book in a decade or more. Very excited! I don't know what's in it - but I do know it's all going to be in my head in about 45 days. Truly an invaluable present.
In ms 365 for data validation, even if the list of cells referenced by the list function is not a duplicate value, it removes the duplicates and creates a list with unique values. I don't know when it was updated, but I found out about these features a while ago.
True. But the really crazy thing is that Excel has "always" had that capabilty and willingly provided it in the built-in in-cell data validation but did not provide it in the list option in the dialog box!!
@@excelisfun That's correct. Even so called professional post graduate universities Don't teach Excel in their course. All Government should Ask to teach Excel Right from 12th std to all Universities.
That is a good idea. But that dialog box is not that polite. So, no, I don't know how to do that. Funny thing is that I also tried putting formula: =SORT(UNIQUE(TEXT(HireTableAnswer[HireDate],"yyy-mm"))) into Defined name and that did not seem to work either...
I learn a lot from your classes, thank you for sharing your knowledge
I am absolutely loving all these videos. Been working in Excel for 30 years and have expertise, but some of the little things are amazing. Love the # in the source section. Love the arrow to capture the table name. Love the unique usage in the date, etc.
I use Alt (or F10) and A T for filters. I am a huge keyboard person, so it is nice the newer Excel version still allows me to use my old timey keystrokes. :)
You are awesome! You and I have this in common: we both love Excel and seem to have a lot of fun with it : ) : )
You are a literal Excel Guru, always something new to learn. Thanks for sharing this knowledge FREELY with us. God bless you.
You are welcome for the free fun, Simoiya!!!!
Some other program on my pc high jacked my ctrl+shift+L earlier this year and I haven’t been able to use it. I work around it with menu acceleration keys but muscle memory really wants that shortcut to work.
Very clear video. Excellent!
Yes, good idea for the keyboard. Glad the video was excellent for you, Jonathan!!!
Instead of Ctrl+CC, I use Win+v and it works for all programs, not just Excel.
Whoa! I did not know that... : ) : ) That is epic. I am going to try and use "Clipboard "bagnon" method in the future : )
Thanks Mike. Data Validation removing dublicates in the List. Never noticed that. Just tried it now. That's Great!!!
Yes, it is a great new-ish capability to an old favorite : )
thanks for all
because you give me sample file to practice
thanks for all
thanks
You are welcome, Rusdi!!! Practice is fun : )
Excel 365 is magic. Thank Mike! 😀
You are welcome, Mark!!!
Thank you Mike. When Amazon open in South Africa hopefully your book can be purchased here.
I hope so too : ) : ) : ) : )
Hi Mike, I never have used the TEXT function to create a unique identifier for dates in a specific month but always the EOMONTH function. It also works in this scenario. For Data Validation list I will use =SORT(UNIQUE(EOMONTH(--HireTableAnswer[HireDate],0))) and for the main function "=FILTER(HireTableAnswer,EOMONTH(--HireTableAnswer[HireDate],0)=D4). I don't know if there will be a speed advantage, but just another FUN way of doing it. Extra thing to do is to do your formatting in Format Cells where in your example there is no need to do this.
@@excel_along_the_way Thank you for your keen insight, Laster!!! The funny things is that in DAX, with Date Tables, I do use the EOMONTH for month unique identifier. But here I was thinking that visually for the dropdown, it would be better to use TEXT. However, I am not sure the time advantage in Microsoft 365 (because I have not done much testing in this version), but in older versions there is a big calcuation time advantage to use EOMONTH or combo of YEAR and MONTH over TEXT : )
@@excelisfun I have notice that you use it in DAX as I have started to follow your tutorials on PQ and Power BI. Years ago I discovered EOMONTH and always used it as a unique identifier and your video on how to get EOMONTH to spill in 365 with the double negative was such a life saver.
@@excel_along_the_way Yes, those darn Analysis Toolpak add in functions like EOMONTH and their double negatives lol
Thank you so much for taking the time to do this. I truly appreciate you!
You are welcome, monica!!!!
Thanks Mike for this EXCELlent video.
You are welcome for the EXCELlence, Fellow Teacher!!!
Mind blowing ❤️
Glad it helps, C P O!!!!
Brilliant!
Glad you like it. FILTER does make Excel life much easier : )
I was using the same formula you used, =SORT(UNIQUE (TEXT("Column", "yyy-mm"))).
But I had a lot of blank cells, and those blank cells would return as 1900-01.
The blank cells will be filled with more future data that I would get.
So what I did was I took a random column(AV), and I typed. =IF(A8="","", TEXT(A8, "yyy-mm")) .
And I draged that formula down to the amount of columns I was using.
Column A is my date column btw.
Than I used the =SORT(UNIQUE (TEXT(AV8:AV600, "yyy-mm"))).
But it wouldn't sort the month and day properly, soo I ended up switching the formula around to, =UNIQUE (SORT(TEXT(AV8:AV600, "yyy-mm"))). And it worked perfectly. 👌
Thank you soo much, Mr. Excel. This was exactly what I was looking for. Thank you.
Boom!Super Cool Formula,Loving The Awesome Filter Function...Thank You Mike :)
You are welcome, Bike Brother!!!
Thank you sir
You are welcome, Prasad!!!
Thanks Mike! FILTER is just so awesome.
FILTER is just about best new function and at least: it seems to have the most different applications for a single function. You are welcome as always, Teammate Chris M!!!
I like this video !! I'm a big fan and regular user of FILTER. I often filter by date parameters. Helper columns seem not to be politically correct but I find them extremely tempting esp with anything that might become a FILTER criterion. After all spreadsheet real estate is not Scarce with 2^14 columns avaiable!!! So a sheet I was updating a few moments ago has separate columns for MONTH, YEAR & WEEK by using TEXT() for month and year and WEEKNUMBER for week. This gives me max flexibility to build reports quickly with FILTER. Putting month and year in the same cell is a good look but some of my sheets eventually grow to five or even ten years of transaction data making such combo dropdowns rather unweildy. Often I filter by complex multiple criteria (such as jan 2021 and jan 2019 in one FILTER report) so I need max flexiblity in referencing. I use linked reference cells but type in criteria for each report and reference such cells with FILTER.
I haven't used filter buttons for a long time. If I want to Filter in plce I normally use Table Slicers.
Great methods for filter by data criteria, Richard "Excel Word Poet" Hay!!!! The one use I have for the Filter feature is to quickly copy and paste a set of records. Nothing beats that. I like your helper columns too. The only reason that people might not want to use them is that they add lots of formulas. And TEXT function calculates more slowly than other functions like MONTH and YEAR. But as you say: the clear advantage to helper columns is that they make that FILTER function mush easier to use later on. Thanks for liking this video, Richard Hay!!!
@@excelisfun I use the TEXT Function for month to get the month name while MONTH function just extracts numbers. I use it for year out of (bad?) habit. YEAR Function works just as well as TEXT. Why are Table Slicers not more popular and more regulaly taught? Many users seem not to even realize they exist. I find them quick, easy and flexible!!
@@richardhay645 I don't think YEAR is bad habit. I think Table Slicers are not known, because they came out for PivotTables first, then much later they came out for Excel Tables. Buy that time, all of use PivotTables and didn't think about the power they can bring to Excel Tables.
@@excelisfun yeah, I usually clean up my tables by turning off the Filter buttons and inserting Table Slicers for the copy/paste thing and for the other quick and dirty stuff!!
@@richardhay645 Nice : ) : )
This a great thanks! one question, when the data is displayed, i get numbers in the column rather that dates shown. any ideas?
Great explanation sir
Glad this helps, Pandharinath!!!!
Excellent video. Thank you.
You are welcome, Joshua!! : ) :)
excellent tutorial Mike 👌 I try to never miss any of your videos 📹
Thanks for watching and never missing the fun, Nader!!!
Great video, thank you. How about creating a dynamic report showing the totals in this format: This Month, Last Month and this quarter, last quarter and this year, last year at all times without any extra steps. something like a dashboard which always only automatically calculates and shows above mentioned values?
Great video. I would like to see a Power Query approach, too!
In Power Query you can use the filter dropdown arrows at to of Date Field. It is exactly like the first part of this video : )
I needed this to complete a n assignment! Thank you
You are welcome! Was the assignment at work or school?
@@excelisfun work
Excellent. Thanks Mike.
You are welcome, Salim!!!
Thanks!
Mike, my learning never end when your video is on. Thanks for this cool trick.
You are welcome, Teammate Santosh : ) : ) No, wait, Thank you, Teammate Santosh!!!!!!!
@@msantosh1220 You are welcome for the cool tricks : ) : ) : )
Perfect Mike! Keep it up
Yes!! I will keep it up because it is fun for the Team!!!!
Great trick Mike Thanks
You are welcome, Sevag!!!!
Another amazing video! Thanks Mike…
You are welcome, Meziane!!!!
Great trick 👌❤️
Thanks, shubham!!!!
When we convert date numbers into text, how can we convert them into value from the text again? As we know that date values seem just text not value and sometimes it causes some calculation problem because of text convertion.
Thank you Mike for this benefit tutorial. 🙏🏻
As, Joseph said, any math operation (that does not change the value) on text numbers converts the text numbers back to numbers. *1, /1, ^1, --, +0 all will convert text numbers back to numbers,
Amazing,,, thanks Mike 🙏
You are welcome, Bashir!!!
Brilliant 👏
Glad you like it, Lan!!!
Nice to see this
Glad you like it, Raj!!!!
Great! But what's the difference between Ctrl-Shift-L and Ctrl-T when establishing a table?
Ctrl + T creates an Excel Table Object with formatting, sorting, filtering, a total row and dynamic ranges. Ctrl + Shift + L just adds filter dropdowns.
@@excelisfun Thanks! Love your stuff! Bought the PDF and now the full, physical book for plane/travel reading. Can't wait to dig through it all.
@@realtoast7036 Thanks for your support in buying my book : ) I hope that you will like it.
@@excelisfun I've not ordered an actual, tangible book in a decade or more. Very excited! I don't know what's in it - but I do know it's all going to be in my head in about 45 days. Truly an invaluable present.
@@realtoast7036 It is a heavy book - more than 4 lbs! I hope you will like it : )
In ms 365 for data validation, even if the list of cells referenced by the list function is not a duplicate value, it removes the duplicates and creates a list with unique values. I don't know when it was updated, but I found out about these features a while ago.
True. But the really crazy thing is that Excel has "always" had that capabilty and willingly provided it in the built-in in-cell data validation but did not provide it in the list option in the dialog box!!
That is crazy, I forgot this. I saw it one time a while back, but forgot. Thanks for the reminder. It is such a great aspect of Data Validation List.
: ) : )
The diagonal arrow!
Yes, it is an efficient trick : )
A college professor followed by Many Working Professional round the world...
Rare, right? Because many academics are decades behind the working world ; ) lol
@@excelisfun That's correct. Even so called professional post graduate universities Don't teach Excel in their course. All Government should Ask to teach Excel Right from 12th std to all Universities.
@@simfinso858 Probably even better: teach it to all students, everywhere, all the time!!! It should start in K-12 : )
Hey Mike ... i wonder why Unique Function is not working on Rows ?
By default, UNIQUE does work on rows, but the second argumnet allows you to choose: rows or columns.
Could you have put the YYYY-MM generator formula into the Data Validation box, so it isn't on the sheet?
That is a good idea. But that dialog box is not that polite. So, no, I don't know how to do that. Funny thing is that I also tried putting formula:
=SORT(UNIQUE(TEXT(HireTableAnswer[HireDate],"yyy-mm")))
into Defined name and that did not seem to work either...
😍
: ) : ) : )
1st comment
Yes: First Place Trophy, Syed Hassan!!!!!
If you thought Excel couldn't be sexy, think again!
Riiiiiiight : ) : )
Fantastic. Thanks Mike
You are welcome, Matt!!!
Brilliant!
Glad you like it, Shawn!!!