The first time I discovered your channel, you had 1.2k people plus 1- me :) -. Your channel deserves more. Please upload more analytic videos. Teach people how can use Excel for statistics and analytics.
Thank you. I’m glad you’ve been along for the journey. My day job doesn’t involve statistics and I’m now using Power BI mainly for analytics but I’ll keep it in mind.
Liking the FILTERED / NOT FILTERED idea, good to help a quick reference to see what you have. Going to make it a LAMBDA function and just allow it to be entered as =ISFILTERED(TblReference) then use conditional formatting for Green or Red Text. Never thought of doing this, but maybe a good idea to try to catch your attention. As always, another great video.
Nice. One minor downside of Lambdas is they are workbook specific, but the simplicity of the Syntax and the ease of copying between files makes it worthwhile doing I think.
Hello Win for dropdown menu when i use a table for the values, i write the formula =INDIRECT("tblName[columnName]") the formula for filtered table is gold
Thanks for the video, Wyn. Most of the tricks & techniques I’ve been using since years (next to the Clear Filter button I have the Reapply Filter button as well, BTW). But that last trick avoiding the Name Mgr duplication glitch is new to me: thanks for that! So sad, though, that we need to avoid Table Formula Nomenclature in the Name Mgr for that: that shouldn’t be! I wish MS would universally make arrays, array calculations (which is the same) and dynamic array references work wherever we can enter ranges. And that includes in the “*IFS” functions (looking at you SUMIFS, et al.).
Love the FILTER IS ON tip. Just added it to all my company files. The users keep forgetting the filters on and then phone me to say something is wrong in the file they cannot see all the lines!! THANKS!!!!
Thanks Wyn, why did I never knew ctrl-space before. One "hack" you could show next time is how you could show how the list is filtered. So show those values in Excel. You need an extra table for that. For Pivot Tables it is easy to harvest the values you selected in a slicer, but for tables it is not.
@@AccessAnalytic Hi Wyn, I tried to send my solution for this to you via Linkedin, but then I saw I already sent it to you last time, maybe this message got lost? Anyway I hope you find it usefull....greetings Bart.
@@barttitulaerexcelbart9400 I think I found it (back in January?) For a single column filter I'd do the following: 1. Add a "Is Visible" helper column to the table =AGGREGATE(3,5,[@city]) 2. Write this formula to display the filtered items =TEXTJOIN(", ",, UNIQUE( FILTER(City[city], City[Is Visible]=1) ) )
Still a few shortcuts that my fingers refuse to remember (ctrl-F3 to name one). So my Christmas wishlist has "F4 equivalent for table columns" and "F4 equivalent for adding a #". I really have to rewatch this and make notes / cheat sheet!
Great and informative for me I've noticed the Access Analytics tab on your videos and am intrigued by its tools and shortcuts. I was wondering if there's a previous video that covers these in more detail.
Check this out: ruclips.net/video/8q6VfNC560M/видео.html I'm not 100% sure the Chat GPT buttons still work as things have changed so much since original recording and I use an older toolbar that doesn't have the Chat GPT section
1. Those hidden formula could ALSO contain links to other workbooks - and I've had this show up and the links are nearly impossible to find... 2. I completely agree on the anchoring a structured ref - I simply cannot remember this but I had put this down to age. 3. I use clear filter many dozens of times per day - it's in position 1 in the quick access toolbar for me - thus ALT+1. I also like delete table rows as a quick access (ALT+4 for me).
Copying sheets is so annoying with these pop up messages. Thanks for the solution! Hopefully Microsoft could fix this bug some time in future. Also, I noticed that if I share my file with other users, they tend to rename some names in the drop down list, but names do not change automatically in the main table, although green triangles appear in the cells.
Note: you also can't have formulas in the headings of a table. At the moment I can not remember why I was trying to do that, but I know I have run into that limitation a couple of times. The formulas get converted to values.
Thanks, Wyn! Your video is very helpful. With the SUMIFS formula, in my case, both the department and sum range remain constant. While having the department remain constant is beneficial, the fixed sum range is quite frustrating. Do you have any suggestions on how to make it adjust so that when I copy the formula across columns, the sum range updates accordingly? I noticed it works perfectly in your example.
Pls get me a Formula to count meals (breakfasts, lunches & Diners) between 2 dates in different columns i.e 20/11/2024 Diner to 24/11/24 Breakfast BF (Column) 4nos Lunch (Column) 3nos Diner (Column) 4nos If you give me a solution i will be very greatful.
Hi, I'd recommend you post a screenshot and your question to one of these forums www.reddit.com/r/excel/ techcommunity.microsoft.com/category/microsoft365/discussions/excelgeneral
Thanks for the ISFIl tip and the solution for that dreadful error message on new sheets! My sanity is saved! Thanks Wyn ❤
You’re welcome Grainne
The first time I discovered your channel, you had 1.2k people plus 1- me :) -. Your channel deserves more. Please upload more analytic videos.
Teach people how can use Excel for statistics and analytics.
Thank you. I’m glad you’ve been along for the journey.
My day job doesn’t involve statistics and I’m now using Power BI mainly for analytics but I’ll keep it in mind.
Liking the FILTERED / NOT FILTERED idea, good to help a quick reference to see what you have. Going to make it a LAMBDA function and just allow it to be entered as =ISFILTERED(TblReference) then use conditional formatting for Green or Red Text. Never thought of doing this, but maybe a good idea to try to catch your attention. As always, another great video.
Nice. One minor downside of Lambdas is they are workbook specific, but the simplicity of the Syntax and the ease of copying between files makes it worthwhile doing I think.
Mate, your videos are clear and useful. You deserve more subscribers and we deserve more videos from your channel.
Hah, thanks 😀
Hello Win
for dropdown menu when i use a table for the values, i write the formula =INDIRECT("tblName[columnName]")
the formula for filtered table is gold
Yes, that is an option. I tend to avoid it as it breaks if the table or column is renamed.
Thanks for the video, Wyn.
Most of the tricks & techniques I’ve been using since years (next to the Clear Filter button I have the Reapply Filter button as well, BTW).
But that last trick avoiding the Name Mgr duplication glitch is new to me: thanks for that!
So sad, though, that we need to avoid Table Formula Nomenclature in the Name Mgr for that: that shouldn’t be!
I wish MS would universally make arrays, array calculations (which is the same) and dynamic array references work wherever we can enter ranges. And that includes in the “*IFS” functions (looking at you SUMIFS, et al.).
Totally agree
Filter indication tip is incredible, will apply it in all my files
Thanks a million for sharing this tip😊
You’re welcome. Glad to help
Thanks, Wyn. Added the filter let function to my favourite snippets today. Never thought about auto correct before 👍
You’re welcome.
5:10 Wow! That is very unintuitive. I never would have known that if you didn’t show me! Thanks!
You’re welcome.
Thank you for the tips. A couple of those examples have driven me crazy!
Glad to help
This is brilliant! Thanks!
Glad it helps.
Great use of Autocorrect, Wyn. Thank you for this tutorial. That's very useful!
Cheers Iván
Thats great, the lock cell and the error will help massively, thank you
I appreciate you taking the time to let me know you found it useful
OMG- I never knew how to get rid of the hidden formula. We always complained about the ghost in our file. Thank you!!!!!’
Glad to shed some light on the matter!
Love the FILTER IS ON tip. Just added it to all my company files. The users keep forgetting the filters on and then phone me to say something is wrong in the file they cannot see all the lines!! THANKS!!!!
😃 glad to help. Yep stopping others doing the wrong thing with your file can be a full time job!
Thanks Wyn, why did I never knew ctrl-space before. One "hack" you could show next time is how you could show how the list is filtered. So show those values in Excel. You need an extra table for that. For Pivot Tables it is easy to harvest the values you selected in a slicer, but for tables it is not.
Cheers, I’ve not come across the technique to identify which filters have been applied to a table .
@@AccessAnalytic Hi Wyn, I tried to send my solution for this to you via Linkedin, but then I saw I already sent it to you last time, maybe this message got lost? Anyway I hope you find it usefull....greetings Bart.
@@barttitulaerexcelbart9400 I think I found it (back in January?)
For a single column filter I'd do the following:
1. Add a "Is Visible" helper column to the table
=AGGREGATE(3,5,[@city])
2. Write this formula to display the filtered items
=TEXTJOIN(", ",, UNIQUE( FILTER(City[city], City[Is Visible]=1) ) )
@@AccessAnalyticI am interested in learning more about this.
Still a few shortcuts that my fingers refuse to remember (ctrl-F3 to name one).
So my Christmas wishlist has "F4 equivalent for table columns" and "F4 equivalent for adding a #".
I really have to rewatch this and make notes / cheat sheet!
Add your vote here feedbackportal.microsoft.com/feedback/idea/7e63b229-74bd-ed11-83ff-000d3a1ab7d1
Great and informative for me
I've noticed the Access Analytics tab on your videos and am intrigued by its tools and shortcuts. I was wondering if there's a previous video that covers these in more detail.
Check this out:
ruclips.net/video/8q6VfNC560M/видео.html
I'm not 100% sure the Chat GPT buttons still work as things have changed so much since original recording and I use an older toolbar that doesn't have the Chat GPT section
1. Those hidden formula could ALSO contain links to other workbooks - and I've had this show up and the links are nearly impossible to find...
2. I completely agree on the anchoring a structured ref - I simply cannot remember this but I had put this down to age.
3. I use clear filter many dozens of times per day - it's in position 1 in the quick access toolbar for me - thus ALT+1. I also like delete table rows as a quick access (ALT+4 for me).
Thanks for the excellent thoughts / tips
Thank you for good presentation. 🙏🙏
Thanks. Thanks for taking the time to leave a kind comment
Fantastic! Greetings from Brazil!
Thanks and de nada
Thank you so much. The first (inconsistent formula) was so annoying. Until now. 💕
You’re welcome. Thanks for taking the time to leave a kind comment
For locking vertically and horzontaly I usually end up using index so first number a a column;
INDEX(Table1[[Number]:[Number]],2,1)
Interesting
6:40 So what does the @ sign operator actually mean? Could you do a video on that? Thanks!
It just means “this row”
Fabulous... these problems driven me also crasy!
You’re welcome.
Copying sheets is so annoying with these pop up messages. Thanks for the solution! Hopefully Microsoft could fix this bug some time in future. Also, I noticed that if I share my file with other users, they tend to rename some names in the drop down list, but names do not change automatically in the main table, although green triangles appear in the cells.
You’re welcome. Would be good if you could choose! “ change all current selections to new name”
thank you for the great tips
You’re welcome.
The Names! 😂 You got me! 🤣
I make my own fun 🤗
Note: you also can't have formulas in the headings of a table. At the moment I can not remember why I was trying to do that, but I know I have run into that limitation a couple of times. The formulas get converted to values.
Ah yes could point to note, thanks.
Wow incredibly useful
You’re welcome.
Thanks, Wyn! Your video is very helpful.
With the SUMIFS formula, in my case, both the department and sum range remain constant. While having the department remain constant is beneficial, the fixed sum range is quite frustrating. Do you have any suggestions on how to make it adjust so that when I copy the formula across columns, the sum range updates accordingly? I noticed it works perfectly in your example.
Cheers. You need to drag to make it change. Don’t use copy paste
@@AccessAnalytic I just tried it and can confirm that dragging works. Thank you very much!
@muhammadtambawala6379 You’re welcome.
Thanks
No worries
Pls get me a Formula to count meals (breakfasts, lunches & Diners) between 2 dates in different columns
i.e
20/11/2024 Diner to 24/11/24 Breakfast
BF (Column)
4nos
Lunch (Column)
3nos
Diner (Column)
4nos
If you give me a solution i will be very greatful.
Hi, I'd recommend you post a screenshot and your question to one of these forums
www.reddit.com/r/excel/
techcommunity.microsoft.com/category/microsoft365/discussions/excelgeneral