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.).
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.
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!!!!
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: th-cam.com/video/8q6VfNC560M/w-d-xo.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.
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!
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 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
Mate, your videos are clear and useful. You deserve more subscribers and we deserve more videos from your channel.
Hah, thanks 😀
Thanks, Wyn. Added the filter let function to my favourite snippets today. Never thought about auto correct before 👍
You’re welcome.
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
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
Thank you for good presentation. 🙏🙏
Thanks. Thanks for taking the time to leave a kind comment
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
Thank you for the tips. A couple of those examples have driven me crazy!
Glad to help
Great use of Autocorrect, Wyn. Thank you for this tutorial. That's very useful!
Cheers Iván
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!
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
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 .
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!
thank you for the great tips
You’re welcome.
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:
th-cam.com/video/8q6VfNC560M/w-d-xo.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
For locking vertically and horzontaly I usually end up using index so first number a a column;
INDEX(Table1[[Number]:[Number]],2,1)
Interesting
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
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”
Fantastic! Greetings from Brazil!
Thanks and de nada
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
Fabulous... these problems driven me also crasy!
You’re welcome.
Thanks
No worries
Wow incredibly useful
You’re welcome.
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.