@@MyOnlineTrainingHub I have question about something I've noticed that seems strange. On my QAT I have the Save GUI. When I open an Excel file (in the app) from SharePoint that GUI looks different than when I open an Excel file in the Excel 365 app from my local network. Do you know why?
And you can add the icon for Refresh current. That helps if one of your queries takes a while and you don't want to refresh it and the others with the current one.
@@drsteele4749 I don't know if you know this, I just learned it... but click in file explorer to open an Excel file, or Teams or Sharepoint > Open, etc.... and hold ALT as it's opening. Excel will ask if you want to open another Excel instance. You can refresh all your PQs independently in their own instances. I use it all the time. There is some kerfluffery with copy-paste between windows, but sometimes it's worth the clunkiness.
I use Excel alot and I have my QAT below my ribbon while hiding the main toolbar and only accessing it for one off items now and again. This gives me a much larger screen area too, especially on laptops! What you did not show in your video is the ability to turn off the name labels for each command, even when the QAT is located below the ribbon, resulting in loads of space for all your favourite commands!
@@sokirish I do the same thing for same reason (max laptop screen real estate), esp after learning shortcut to double-click on any Ribbon tab to expand or collapse Ribbon menu.
With so many UA-cam videos on improving my efficiency, I am so fast that today's new project was finished last week. I was so fast my keyboard caught fire and I have 2nd degree burns on my fingers, my mouse melted and every Excel command is now on my Quick Access Toolbar. My boss bought me a 100 inch monitor to accommodate the wide toolbar. There is no one left in the IT department except me and I only work 1 minute a week.
I actually have an excessive number of items (19) in the QAT, show it below the ribbon, then hide the ribbon to save real estate. The biggest thing I got from your video was the name of the "Backstage" area where I was able to disable the view from open and save. Thanks
Million thanks Myanda for this video from which I learned two things. Firstly, adding just a single item into the QAT instead of the whole lot and the gem of it all is the facility to export your 'QAT'. Up to this point, I have screenshots of my QAT and always have to faff around when I get a new computer in order to recreate my QAT. I never knew about the export. 😒 Many thanks!
Depending on the job I was doing, customized toolbar changed over the time for me with different needs of repeating steps. But what I've learned new today from you is using the shottcuts and exporting/importing my customized toolbar is really helpful if I need to work on more computers with different projects :) Thank you a lot!
And apparently CTRL-F12 is the old File Open, and Alt-F12 is the PowerQuery Editor as someone mentioned below. Seems like everything I do in Excel is on the F12 key!!
I must have been following you for even longer than I thought, because I seem to be mostly aligned with you on the shortcuts that you have highlighted here. I definitely rely on Ctrl+Shift+L for toggling fiLters, and personally wouldn't add it to the QAT. It's funny because I usually can't remember that key combination when I speaking with someone, but it comes naturally when I'm in front of a keyboard with Excel open. 😏 The one other that I do add is the Camera function - I don't use it often, but it isn't readily available otherwise, so it's handy to have on the QAT if/when needed.
During idle days of Covid lockdown (when we fortunately remained open as “essential business in essential industry”, but had little to do), I finally set QAT after years of telling myself I would get to it. Two suggestions to add to yours, Mynda: (1) user can add vertical separator(s) between icons which helps users locate target if QAT has lots of icons; (2) some shortcuts open menus (for example, I installed the Erase button that opens menu of all Erase options: All, Formats, Contents). You’re right: QAT is game-changer for efficiency. FWIW, I don’t try to memorize shortcuts - I work in too many apps to keep them straight. That & an ever-diminishing short-term memory. QAT compensates enormously for that. As always, another great tutorial. Thanks!
@ In Customize QAT dialog box, at top of “Choose commands from:”, first item is at top of all lists (Popular Commands, etc.). Use it like any other command. Don’t you just hate it when you discover something right before your eyes that’s been there for countless times you’ve seen it? That’s how I often feel when viewing your tutorials even if I call them aha moments. 😁
I guess I don't have to watch this video no more because I've been using this for a year now. I too would recommend using it... I an also a keyboard-shortcut guy but there are tools that I want to be in this strip and it helps me a lot in terms of productivity. I'd still watch the video because I'm curios how you're using it.
Below the ribbon and then collapse the ribbon. Also group like items together with a separator bar. Also great if you need to put a command there temporarily if you are doing cleanup or repetitive work.
I agree with most of your comments. Top 9 places for most frequently used shortcuts. The remainder for those that are harder to find/not on the ribbon or buried somewhere in the ribbon, those requiring the mouse and also for new functions (as a reminder to get used to using them). For filtering instead of the Filter button I use the Autofilter button as well as Clear Filter in my QAT. As you say Ctrl+shift+L toggles the filters and I nearly always have them on so no need for the Filter button there. The advantage of Autofilter is that I can instantly filter by the item I currently have selected and quickly clear the filter. For me it is a quick Alt+4 then Alt+5. I also have Accounting number format, Remove duplicates, Insert Pivot Table, (data) Form and the new Focus Cell toggle on my QAT among others.
For paste values you can also use the menu key + v which is only 2 keys to hold down. This is an older shortcut that not many people seem to be aware of.
Thank you for this video! I love the Alt key trick!!! On my QAT, I have: - Print Preview and Print (only for preview purpose) - Page Break Preview - Data Validation - Refresh All - Clear (filter) - Creat PDF/XPS - Select Objects. I added today thanks to you, and also on my MS Word QAT. Soooooooooooooooooooooooo usefull! - Launch Power QUery Editor. I added today thanks to you And thank you for the F12 key too! It's 9 am, I've learned enough for today, I go back to bed 😂
Very useful, thank you. One additional shortcut to add is Select Visible Cells which is very hidden. Question for you: I have saved customizations for when I switch computers, but how do I ensure macros I had created for specific formatting, can also transfer to the new computer? I usually end up having to re-record my 3 macros. Thank you!
You can export modules from the VBE, and import them to your new machine. I don’t remember the exact steps, but I think you can export by right-clicking the module from the navigation window, if that’s what it’s called. The module is saved as a .bas file. Sorry this is a bit vague but my computer is offline and it is a while since I had to do it.
Hi Mynda, Once again, a very thorough review and guidance, which always inspires Launch Power Q Editor is the one I've added, I can see that it's good together with Queries and connections which is on my QAT. 3 features I often use are Remove Duplicates, Filter and Copy as Picture
I need to consider some of these ideas, thank you. The PQ editor is a good candidate for me. But, my favourite commands mostly have a 3- or 4-keystroke shortcut in my muscle memory, so it might not be worth relearning some. Also: kiboshed?? Excellent word I hardly ever hear, and had to check the spelling to write this. 😊
Awesome video, Mynda!! :) QAT is such an undersung hero, and it's been there for a while now. It's funny, I seem to recall feeling a bit of umbrage when I first saw it, like, how dare Microsoft coddle me with this cute little toolbar? I know how to tap ALT and then follow the letters to my menu options of choice, I don't need your handholding! ....... for shame, me. Only in the current year did I hear about real applications for QAT and started actually using it, and now I can never go back! I have the Data Model (Power Pivot window), Refresh and Refresh all, Number Format, and Name Manager in mine as well as some of the ones you mentioned. On my keyboard, F-keys are not convenient, or I would just use the shortcut for Name Manager. And thank you so much for the tip about the PQ window launcher! It always bugged me trying to open it the standard way!!!
I use the Keyboard short cut to open PQ, no need to put that on the QAT. Then again, I put From Table/Range and New Blank Query on the QAT (yes, in Excel, and also in PQ itself, but only via the group button - PQ is a bit limited when it comes to the QAT). For the filters, I put Clear Filters and… Reapply Filters (CTRL+SHIFT+L is easy to remember). PS: it seems that ‘someone’ does put the QAT below the Ribbon… ;-)
Is there a way to add a divider line between QAT icons or groups of icons so they are grouped together. I realize that this will take some extra space in the toolbar if it is possible, but is it even possible?
Yes, thanks to Jim who taught me this earlier, you can add a separator line in the Customize QAT dialog box. It's the first item in the list on the left.
@@MyOnlineTrainingHub I just checked, and I do not see a separator line option in the first item of any list, including the "All Commands" list. Can you tell me exactly how it appears in the list (ie. spell it out for me so I can search for it)? In case it is version dependent, I am using Excel 2021 v16.9.1 on a Mac (not using Office 365)
EDITED for typos. I’ve had Centre across Columns on my QAT so long I forgot how it got there! Pretty soon after starting to use Excel 2010 (still on that version), so I went and had a quick look at how it came to be - a macro as suggested. Another tool I use a lot is Protect/Unprotected Sheet since I use sheet protection to keep my clumsy, elderly fingers away from areas where I don’t want data entered normally. But moderately often I want to deliberately edit one of those off-limits areas I tried moving my QAT under the ribbon, but on my 2010 version it remained the same size as above the ribbon, so no advantage. I have used my QAT pretty well since learning Excel years ago, partly because I missed (and still miss) having good customizable context sensitive toolbars.
Maybe instead of entering it as date/time serial numbers i.e. proper times, enter them as time in decimals where there are no restrictions on negatives.
❓What are you going to add to your QAT?
Master Excel with my courses: bit.ly/qat24courses
As a Power Query junkie, my favourite ribbon shortcut is 'Refresh All'. Add in the convenience of the Alt+ keyboard shortcut and it is super handy.
Totally agree! Refresh All on the QAT is a game-changer.
@@MyOnlineTrainingHub I have question about something I've noticed that seems strange. On my QAT I have the Save GUI. When I open an Excel file (in the app) from SharePoint that GUI looks different than when I open an Excel file in the Excel 365 app from my local network. Do you know why?
The best!
And you can add the icon for Refresh current. That helps if one of your queries takes a while and you don't want to refresh it and the others with the current one.
@@drsteele4749 I don't know if you know this, I just learned it... but click in file explorer to open an Excel file, or Teams or Sharepoint > Open, etc.... and hold ALT as it's opening. Excel will ask if you want to open another Excel instance. You can refresh all your PQs independently in their own instances. I use it all the time. There is some kerfluffery with copy-paste between windows, but sometimes it's worth the clunkiness.
Alt+F12 to launch the query editor is my favorite
Yeah, that shortcut came in after I added it to the QAT and now I never remember it 😂
I use Excel alot and I have my QAT below my ribbon while hiding the main toolbar and only accessing it for one off items now and again. This gives me a much larger screen area too, especially on laptops! What you did not show in your video is the ability to turn off the name labels for each command, even when the QAT is located below the ribbon, resulting in loads of space for all your favourite commands!
@@sokirish I do the same thing for same reason (max laptop screen real estate), esp after learning shortcut to double-click on any Ribbon tab to expand or collapse Ribbon menu.
@sokirish I didn't know you could turn the labels off! Learning so much 🙏
For years I've QAT ordered by my setup actions:
1.freezing panes
2.pivoting
3.refresh all
4.export pdf
5.share by mail
Nice! Thanks for sharing.
With so many UA-cam videos on improving my efficiency, I am so fast that today's new project was finished last week. I was so fast my keyboard caught fire and I have 2nd degree burns on my fingers, my mouse melted and every Excel command is now on my Quick Access Toolbar. My boss bought me a 100 inch monitor to accommodate the wide toolbar. There is no one left in the IT department except me and I only work 1 minute a week.
🔥😂 I bet your boss is impressed with your efficiency!
:-DDDDD
Great video !
My favs so far that I did not know 😅
05:07 Ctrl + Shift + V = paste values!!!
11:18 Ctrl + Tab = Switch windows
Awesome! So pleased you found some new tips.
Ctrl-Tab also moves forward through browser tabs! Shift-Ctrl-Tab moves backward! It humbled me when I never tried that command in Excel, hahaha.
I actually have an excessive number of items (19) in the QAT, show it below the ribbon, then hide the ribbon to save real estate. The biggest thing I got from your video was the name of the "Backstage" area where I was able to disable the view from open and save. Thanks
So pleased you discovered something new 😅
Million thanks Myanda for this video from which I learned two things. Firstly, adding just a single item into the QAT instead of the whole lot and the gem of it all is the facility to export your 'QAT'.
Up to this point, I have screenshots of my QAT and always have to faff around when I get a new computer in order to recreate my QAT. I never knew about the export. 😒
Many thanks!
Yay! No more faffing 😁
Depending on the job I was doing, customized toolbar changed over the time for me with different needs of repeating steps. But what I've learned new today from you is using the shottcuts and exporting/importing my customized toolbar is really helpful if I need to work on more computers with different projects :) Thank you a lot!
Glad to hear exporting and importing your QAT will be useful to you 😊
I've been using Excel for years and didn't know F12!!! I hate fighting with those new OneDrive based screens. Thanks!
And apparently CTRL-F12 is the old File Open, and Alt-F12 is the PowerQuery Editor as someone mentioned below. Seems like everything I do in Excel is on the F12 key!!
@@trevordennisthanks so much for Ctrl-F12. I thought easy access to the old Open dialogue box was lost forever 😊
Select Visible Cells is my most used. Table Name and create Pivot from Model are also handy
Ooh, table name is a good one 👍
I must have been following you for even longer than I thought, because I seem to be mostly aligned with you on the shortcuts that you have highlighted here.
I definitely rely on Ctrl+Shift+L for toggling fiLters, and personally wouldn't add it to the QAT. It's funny because I usually can't remember that key combination when I speaking with someone, but it comes naturally when I'm in front of a keyboard with Excel open. 😏
The one other that I do add is the Camera function - I don't use it often, but it isn't readily available otherwise, so it's handy to have on the QAT if/when needed.
😁I know what you mean about only remembering keyboard shortcuts when you are about to use them!
One of my favourite commands is Autofilter. Very handy and fast using Alt key
During idle days of Covid lockdown (when we fortunately remained open as “essential business in essential industry”, but had little to do), I finally set QAT after years of telling myself I would get to it. Two suggestions to add to yours, Mynda: (1) user can add vertical separator(s) between icons which helps users locate target if QAT has lots of icons; (2) some shortcuts open menus (for example, I installed the Erase button that opens menu of all Erase options: All, Formats, Contents). You’re right: QAT is game-changer for efficiency. FWIW, I don’t try to memorize shortcuts - I work in too many apps to keep them straight. That & an ever-diminishing short-term memory. QAT compensates enormously for that. As always, another great tutorial. Thanks!
Great tips, Jim. How are you adding the vertical separators between icons in the QAT?
@ In Customize QAT dialog box, at top of “Choose commands from:”, first item is at top of all lists (Popular Commands, etc.). Use it like any other command.
Don’t you just hate it when you discover something right before your eyes that’s been there for countless times you’ve seen it? That’s how I often feel when viewing your tutorials even if I call them aha moments. 😁
Wow, Jim! 🤯🙏
I guess I don't have to watch this video no more because I've been using this for a year now. I too would recommend using it... I an also a keyboard-shortcut guy but there are tools that I want to be in this strip and it helps me a lot in terms of productivity.
I'd still watch the video because I'm curios how you're using it.
Thanks for watching. Great to hear you already make use of the QAT.
Below the ribbon and then collapse the ribbon. Also group like items together with a separator bar. Also great if you need to put a command there temporarily if you are doing cleanup or repetitive work.
Excellent tip, thanks for sharing! 😉
I agree with most of your comments. Top 9 places for most frequently used shortcuts. The remainder for those that are harder to find/not on the ribbon or buried somewhere in the ribbon, those requiring the mouse and also for new functions (as a reminder to get used to using them).
For filtering instead of the Filter button I use the Autofilter button as well as Clear Filter in my QAT. As you say Ctrl+shift+L toggles the filters and I nearly always have them on so no need for the Filter button there. The advantage of Autofilter is that I can instantly filter by the item I currently have selected and quickly clear the filter. For me it is a quick Alt+4 then Alt+5.
I also have Accounting number format, Remove duplicates, Insert Pivot Table, (data) Form and the new Focus Cell toggle on my QAT among others.
For paste values you can also use the menu key + v which is only 2 keys to hold down. This is an older shortcut that not many people seem to be aware of.
So you are a QAT pro! I have never really used the menu key, even though it is a treasure trove. I should make more effort to make it a habit.
Thank you for this video!
I love the Alt key trick!!!
On my QAT, I have:
- Print Preview and Print (only for preview purpose)
- Page Break Preview
- Data Validation
- Refresh All
- Clear (filter)
- Creat PDF/XPS
- Select Objects. I added today thanks to you, and also on my MS Word QAT. Soooooooooooooooooooooooo usefull!
- Launch Power QUery Editor. I added today thanks to you
And thank you for the F12 key too!
It's 9 am, I've learned enough for today, I go back to bed 😂
😂so pleased you discovered some new tips. You can also access print preview with CTRL+P 😉 now you have a spare spot on your QAT.
@@MyOnlineTrainingHub
+1 Thank you for this extra tip!😂
Very useful, thank you. One additional shortcut to add is Select Visible Cells which is very hidden. Question for you: I have saved customizations for when I switch computers, but how do I ensure macros I had created for specific formatting, can also transfer to the new computer? I usually end up having to re-record my 3 macros. Thank you!
You can export modules from the VBE, and import them to your new machine. I don’t remember the exact steps, but I think you can export by right-clicking the module from the navigation window, if that’s what it’s called. The module is saved as a .bas file.
Sorry this is a bit vague but my computer is offline and it is a while since I had to do it.
@claudiat.8508 alt+; (semi-colon) will Select Visible Cells
I use CTRL-W to close individual files, but I often have many files open, so I have Close All on my QAT - it's under "Commands not in the ribbon".
That’s a great tip - thanks for sharing!
Hi Mynda,
Once again, a very thorough review and guidance, which always inspires
Launch Power Q Editor is the one I've added, I can see that it's good together with Queries and connections which is on my QAT.
3 features I often use are Remove Duplicates, Filter and Copy as Picture
Thanks for watching and sharing your QAT tips, Ivan.
I did not know that you could import/export. Very useful. thanks
You bet!
My favorite is send as attachment and reset all filters
Send as attachment is another one I hadn't thought of. 👍 I use OneDrive so I share links, but I guess many users still aren't using OneDrive.
Alt+e,sv == paste values since the early 1990's. Alt+e was the edit menu before the ribbon came along and removed many of the keyboard shortcuts.
I used to use Alt, E, S, V too, but not since we got CTRL+SHIFT+V 😅
8:56 I am so used to using the keyboard shortcut Ctrl+Shift+L to turn filters on and off, that I don’t know if it would be worth it for me.
Yeah, I wouldn't waste an icon on the QAT when you can use that shortcut easily enough.
I need to consider some of these ideas, thank you. The PQ editor is a good candidate for me. But, my favourite commands mostly have a 3- or 4-keystroke shortcut in my muscle memory, so it might not be worth relearning some.
Also: kiboshed?? Excellent word I hardly ever hear, and had to check the spelling to write this. 😊
"Kiboshed" spelling😂 me too! I initially thought it would by kyboshed, lucky I checked for the subtitles.
I have a couple of power query types I use regularly in my QAT.
Nice! I would never have thought to add those. Good idea if you use them daily 👍
OH! 'Clear Filters' works in Access too! You are awesome.
I don't use Access, but thanks for sharing as others might find it helpful.
Love the QAT. Great tips, thanks Mynda!
Cheers, Chris! 😊
Awesome video, Mynda!! :) QAT is such an undersung hero, and it's been there for a while now. It's funny, I seem to recall feeling a bit of umbrage when I first saw it, like, how dare Microsoft coddle me with this cute little toolbar? I know how to tap ALT and then follow the letters to my menu options of choice, I don't need your handholding! ....... for shame, me. Only in the current year did I hear about real applications for QAT and started actually using it, and now I can never go back!
I have the Data Model (Power Pivot window), Refresh and Refresh all, Number Format, and Name Manager in mine as well as some of the ones you mentioned. On my keyboard, F-keys are not convenient, or I would just use the shortcut for Name Manager. And thank you so much for the tip about the PQ window launcher! It always bugged me trying to open it the standard way!!!
It's great to hear you're embracing the QAT! Better late than never 😊
Another way to paste values is to use the Context Menu keyboard button (between the right side ALT and CTRL keys) + V
Ah, yes. That’s a good one I often forget. Thanks for sharing.
I use the Keyboard short cut to open PQ, no need to put that on the QAT. Then again, I put From Table/Range and New Blank Query on the QAT (yes, in Excel, and also in PQ itself, but only via the group button - PQ is a bit limited when it comes to the QAT).
For the filters, I put Clear Filters and… Reapply Filters (CTRL+SHIFT+L is easy to remember).
PS: it seems that ‘someone’ does put the QAT below the Ribbon… ;-)
Nice tips, Geert 🙏 I forgot there is now a shortcut to open the PQ editor. Must memorise that 😅
I have a fairly extensive QAT and put it below the ribbon for the extra real estate there.
Nice tip, Rodney. Quite a few people have said the same. Great idea.
Is there a way to add a divider line between QAT icons or groups of icons so they are grouped together. I realize that this will take some extra space in the toolbar if it is possible, but is it even possible?
Yes, thanks to Jim who taught me this earlier, you can add a separator line in the Customize QAT dialog box. It's the first item in the list on the left.
@@MyOnlineTrainingHub I just checked, and I do not see a separator line option in the first item of any list, including the "All Commands" list. Can you tell me exactly how it appears in the list (ie. spell it out for me so I can search for it)? In case it is version dependent, I am using Excel 2021 v16.9.1 on a Mac (not using Office 365)
Very informative thanks
You're welcome!
Thanks for the great tips.
You're welcome! Glad you found it helpful.
At work I export my QAT UI setup to everyone's PC, so when I have to help them with something I don't have to hunt.😂
😁 smart!
Well to be honest … the biggest takeaway from this was that last tip with exporting profile in order to be able to have it on new machine 😅 …
Glad you found something useful 😁
EDITED for typos. I’ve had Centre across Columns on my QAT so long I forgot how it got there! Pretty soon after starting to use Excel 2010 (still on that version), so I went and had a quick look at how it came to be - a macro as suggested.
Another tool I use a lot is Protect/Unprotected Sheet since I use sheet protection to keep my clumsy, elderly fingers away from areas where I don’t want data entered normally. But moderately often I want to deliberately edit one of those off-limits areas
I tried moving my QAT under the ribbon, but on my 2010 version it remained the same size as above the ribbon, so no advantage.
I have used my QAT pretty well since learning Excel years ago, partly because I missed (and still miss) having good customizable context sensitive toolbars.
Great to hear, John!
Is there a simple way of showing negative time figures without changing the Year format to 1904 which simply skews all dates?
Maybe instead of entering it as date/time serial numbers i.e. proper times, enter them as time in decimals where there are no restrictions on negatives.
Making a custom ribbon toolbar is also very useful.
I agree, that’s another fantastic way to save time. 👍
QAT junkie. My ribbon is collapsed to only show menu headings. QAT is located below the ribbon. Less mouse travel.
Nice tip!
Amazing ❤❤❤
Glad it was helpful. 🙏
Sadly in online Excel QAT not working.
Yeah, no QAT in Excel Online 😢
?? Alt+F12 opens Power Query editor.
Yes, I forget about that shortcut because I've had it on my QAT since before they released it. Must try to commit it to memory 😁
9:15 Ugh… I HATE merge & center… Please don’t use it - and use “Center Across Selection” (Under “Horizontal” in the settings) instead.
Great to hear. Spread the word 😁
Merge and centre are blasphemous.
Amen!
Agree😂