As humans, we are always looking for smarter, better and quicker ways of doing things – especially the repetitive and often annoying day to day tasks. There are days where we quite literally wish we had a monkey by our sides to perform these tasks, allowing us to spend our time doing things that are of actual value. Thankfully Excel offers tab shortcuts that help us easily navigate between workbooks, change the name of workbooks at the drop of a hat and enter formulas, formatting and other Excel goodies onto multiple sheets all at the same time – can you believe it! In this post we are going to look at 3 smart and simple tab shortcuts to make our lives easier and really, just make our day way better.
Smart And Simple Tab Shortcut # 1: Lightning Fast Tab Navigation
This one may be well known within the Excel community but it is definitely one of my favorites. This is simple yet effective. If you would like to move from left to right between your worksheets/tabs, use CTRL + PageDown. If you’re looking to do the opposite, that is to go from the right to the left, use CTRL + PageUp. Bammmm, simple as that.
Smart And Simple Tab Shortcut # 2: Drop the Mouse and Change the Tab Name Without Leaving Your Keyboard
This one is a new to me shortcut but a gem nonetheless. If you are already an avid keyboard user but you don’t know this one, you’ll thank me later. All we are going to do here is type ALT, H, O, R. This will bring us directly into type mode for the tab of the current sheet that we are working on. To me having to double click, or right click and rename the tab is just a nuisance.
###UPDATE### This shortcut is Excel Language Version Specific. This one works fine in the English version of Excel 2010/2013. The path nonetheless is Home Tab on Ribbon (H) ⇒ Format (O) ⇒ Rename (R). Thank you to everyone who piped in on this one.
Smart And Simple Tab Shortcut # 3: Entering Data Into Multiple Tabs/Worksheets At Once
This shortcut is incredible. All you need to do is use shift or Ctrl + Left Click and select all of the tabs/worksheets that you want to enter your data into. Once you’ve done this, you can enter data, formatting and formulas into any of the worksheets that you’ve selected and everything will be copied over exactly the same way across multiple sheets. This one is a beauty for repetitive tasks and data entry and should make your life much easier. This can save you tons of time especially if you have very similar data or formulas that are required across multiple tabs. Unfortunately, you can’t add objects when in multi-select sheet mode (I think I may have just made that phrase up).
Summary
The best part about this post is that it was quick, painless and hopefully not boring. Sometimes it only takes a minute to learn something new (and hopefully it is something new to you). Let’s do a quick breakdown of what we learned.
1 » CTRL + PageUp or PageDown – Navigate between worksheets/tabs
2 » ALT, H, O, R – Update tab name without reaching for your mouse
3» CTRL or SHIFT + Left Click All Tabs – After tabs are selected, enter your data int any worksheet and it will be copied across all of them.
And that is all. Simple and to the point. If you are feeling ambitious and would like to checkout some other shortcuts, have a look at the other resources section below. Be sure to leave a comment with your favorite Excel shortcut. I know you Excel junkies are passionate about your shortcuts!
Hi Brad,
Nice shortcuts, thanks.
Tip # 3 relates to creation of a group in Excel, however this can be done without the mouse (for a small number of consecutive worksheets ):
CTRL+SHIFT+PageDown – Selects the current and the next worksheets (creation of a Group). Hold down the “CTRL+SHIFT” and press PageDown. Each additional press adds the next worksheet to the group.
CTRL+SHIFT+PageUp – same as CTRL+SHIFT+PageDown, but selects worksheets from right to left.
These methods replace the SHIFT+LeftClick of your tip (consecutive worksheets, selection from first to last), however for non-consecutive worksheets you still need the mouse (CTRL+LeftClick on tab/s)
There are, indeed, some other shortcuts related to worksheets.
For example:
1) Shift+F11 – Inserts a new worksheet
2) Alt+Shift+F1 – Inserts a new worksheet [same as: Shift+F11]
3) Ctrl+PageUp/PageDown – Undoes selection [“deselects”] of multiple worksheets [=Ungroup]
4) Ctrl+Drag&Drop – Copies the selected worksheet (when the mouse’s pointer is located on the copied worksheet’s tab)
P.S.
About the phrase you made up,
I think that “multi-sheet select mode” is better: you are in “select mode” not in “sheet mode”…
I love it! I never really thought of using the same functionality that is available at the cell level (CTRL+SHIFT). Also, some very solid additions for other useful shortcuts. Shift + F11 is definitely one that I’ll need to get in the habit of using more often.
As for the made up phrase – I’m with you on multi-sheet select mode. Can I use the excuse that I was up late trying to finish the blog post – :)?
Thanks for the comment, Meni!
Brad, nice tips.
But tip 2 Alt+H+O+R does only work in english versions of Excel.
I found what you mean, in the dutch version i’m working with its Alt+K+B+N.
But somethimes i don’t know what those letters mean because i don’t know the exact english words. So, will you be so kind to mention the words too? Does H stands for Home? And what are the O and the R stand for?
If i know the words i can translate it to my own language-version of Excel.
Henk
Thanks for bringing that up! That makes sense that it would be different in your version.
The path is essentially as you said Home, O – in this case under the cells section it is the format drop down, and finally you may have guessed that the R stands for rename.
Thanks again for mentioning that this will differ between language versions of Excel!
Cheers
Thanks for sharing Brad! Here is one additional that I use often – Alt + E + M (to move/copy worksheet)
Ah the 1st , 3rd actually i used this before ,But the 2nd one is very new for me its very good
thanks for this
Amjad Khan
Data Analyst
MSF B
Timergara
Amjad,
Very glad to hear it. I was the same as of just recently as well. Thank you for checking it out!
Brad
Brad,
#3 is a great tip but should be used with caution. The danger is in forgetting that you’re in Group Mode and beginning to make edits that were only intended for one worksheet! That said, I find this invaluable when I want to apply the same print formatting to every worksheet.
Thanks for posting.
Ben
I can agree with that one Ben. It’s also happened to me. It is definitely something that is really easy to forget. Thanks for checking it out.