Bottom line: Learn time saving tips and shortcuts for selecting and copying worksheet tabs. Includes a few simple VBA macros.
Skill level: Beginner
Tips for Navigating Worksheet Tabs
If you work with Excel files that contain a lot of sheets, then you know how time consuming it can be to work with the tabs. So in this post I share a few quick tips and shortcuts to save time with navigating your workbook.
#1 Copy Worksheets with Ctrl+Drag
This is one of my favorite shortcuts that every Excel user should know. The quickest way to make a duplicate copy of a sheet is using the Ctrl+Drag method. Here are the steps.
- Left-click and hold on the sheet you want to copy.
- Press and hold the Ctrl key. A plus symbol will appear in the sheet mouse icon.
- Drag the sheet to the right until the down arrow appears to the right of the sheet.
- Release the left mouse button. Then release the Ctrl key.
I broke it out into 4 steps, but it really feels like 2 steps once you get the hang of it. It's much faster than right-clicking the tab and going to the Move or Copy… menu.
You can also use this technique when multiple sheets are selected. More on that below.
If you are in need of a high-five or pat on the back (and who isn't), then feel free to share this one with your boss and co-workers. 🙂
#2 Navigating to the First or Last Sheet
If your workbook has a lot of tabs then you might want to quickly navigate to the first or last sheet in the workbook.
In Excel 2010 and earlier this was easy. There were dedicated buttons to scroll to the first or last sheet in the workbook.
Starting in Excel 2013 we lost the dedicated buttons to navigate to the first or last sheet. These actions were consolidated into the sheet navigation buttons in the bottom left corner of the application window.
You now have to hold the Ctrl key when clicking the sheet navigation buttons to scroll to the first or last sheet. You can see this tip by hovering your mouse over the buttons.
So yes, this action now requires two hands unless you have really really long fingers or use a left-handed mouse. Something to brag about lefties… 😉
Once you have scrolled to the front/back, you can then click the first/last sheet to select it.
If you want to speed up this process, checkout my post on how to Create Keyboard Shortcuts to Select the First or Last Sheet in Excel. This is much faster than scrolling, then selecting the first/last sheet with the mouse.
#3 Select Next or Previous Sheet
If you're a keyboard shortcut lover, like me, here are a few shortcuts to quickly move between sheets.
The keyboard shortcut to select the next sheet is: Ctrl+Page Down
The keyboard shortcut to select the previous sheet is: Ctrl+Page Up
These are great if you are toggling back and forth between two sheets. Just move the sheets next to each other. You can then copy/paste or audit the sheets without having to navigate all over the workbook.
Having the right keyboard can be important for us Excel users. Especially when you are using a laptop keyboard. Checkout my post on Best Keyboards for Excel Keyboard Shortcuts to learn more.
#4 Select Multiple Sheets
We can use the Ctrl and Shift keys to select multiple sheets.
Hold the Ctrl key and left-click sheet tabs to add them to the group of select sheets.
You can also hold the Shift key and left-click a sheet to select all sheets from the active sheet to the sheet you clicked.
The keyboard shortcuts to select multiple sheets are Ctrl+Shift+Page Up / Page Down. This will select the previous/next sheet. You can continue to press this shortcut to select multiple sheets.
IMPORTANT NOTE About Selecting Multiple Sheets
When multiple sheets are selected, any changes you make the active sheet will also be applied to ALL selected sheets. This is a great time saver if you want to modify the value, formula, or formatting of specific cells on multiple sheets at the same time.
However, if you forget to ungroup the sheets (see tip #6) then you could really mess up your workbook. I've done this more times than I'd like to admit.
When you have multiple sheets selected, the word “Group” appears after the file name in the header of the Excel application window. This is not much of a warning though. I wish the application would turn a different color, or do a better job of warning us.
If you make a lot of edits to a sheet without realizing multiple sheets are selected it can spell disaster. Sometimes you won't be able to undo the changes, and then have to pray that you saved the file.
#5 Select All Sheets
To select all sheets in the workbook, right-click any tab and choose Select All Sheets.
The same rule applies here. Any edits you make to the active sheet will also be made on all of the other selected sheets.
#6 Deselect (Ungroup) Sheets
To deselect multiple sheets you can just click on any tab that is not in the current selection.
You can also right-click any of the selected tabs and choose Ungroup Sheets. The tab that you right-click will become the active sheet.
#7 Hide & Unhide Multiple Sheets
To hide multiple sheets:
- Select the sheets using the methods mentioned above.
- Right-click one of the selected tabs.
- Choose Hide.
The sheets will be hidden.
Unfortunately, unhiding multiple sheets is not directly possible in Excel. When you right-click a tab and choose Unhide, you can only select one sheet from the list of hidden sheets in the Unhide window.
I have a post on 3 Ways to Unhide Multiple Sheets in Excel that explains techniques for unhiding sheets with a macro.
Bonus Tip: Sheet List
If your workbook contains a lot of sheets then you can right-click the tab navigation buttons to see a list of all visible sheets. You can then double-click a sheet in the list to jump to it.
This list only shows the visible sheets in the workbook, and there is no way to search it.
So, I developed The Tab Hound Add-in to solve both of these problems and a lot more.
The add-in is packed with features (including unhiding multiple sheets) that make it faster & easier to navigate and modify the sheets in your workbooks.
I developed Tab Hound with VBA and the tools that are built into Excel. If you'd like to learn more about macros & VBA then checkout my free training webinar that is going on right now.
Click here to learn more and register for the webinar
Conclusion
I hope those tips help save some time out of your day. What are your favorite shortcuts for working with sheet tabs? Please leave a comment below with your suggestions, or any questions.
Thank you! 🙂
this is great! do you know if there is a way to “list all” sheets? Sometimes I want to create a instructions tab that contacts instructions for each tab. I just copy paste each tab title into one sheet, but its time consuming. Wondering if there is a way to list all of them or get a list of tabs to copy paste. In the hide function one cannot select and copy.
thanks!
Hi Diannat,
Yes, absolutely. We can use a simple macro for this. In my free webinar on macros & VBA I actually explain how to create a list of all sheets in the workbook using a For Next Loop. Here is a variation of the macro I share in the webinar.
Sub Sheet_List()’Create a list of all sheets in the workbook’Use Object instead of Worksheet to include Chart sheetsDim ws As Object For Each ws In ActiveWorkbook.Sheets ActiveCell(ws.Index).Value = ws.Name Next ws End Sub
We can use this to create a table of contents with clickable links to each sheet as well.
Here are a few resources that will also help with this.
Free webinar on Macros & VBA
The For Next Loop Explained for VBA
Excel Table of Contents that Automatically Updates
How to Create a Personal Macro Workbook
I hope that helps. Thanks!
i don’t know how to make a report. like when you type the name of the person, all of the file she/he have will appear and put all of all the columns.
Even an Excel old-timer like myself always learns something new from your posts. Keep up the good work!
Thanks Bob! I appreciate your support. 🙂
Its helpful.
Thankyou
Hi
Nice tips~
Plz add an option to save your posts as PDF.
Thank you for the tips Joe. I’m sure they’ll be helpful to me and many others.
Gr8 Tips Jon. Some I already knew! Nevertheless, thanks again!
When I want to copy a worksheet, I press CTRL-A, then the copy icon. Click on a blank tab at the bottom of the worksheet then paste onto the blank worksheet. Voila, copied. Takes less than 15 seconds.
Hi Jon.. thanks for the great tips. I’m a heavy keyboard user and so always look for ways to keyboard things vs. mouse clicks. I learned about the “F6 Loop” from Mr. Excel Bill Jelen and use it for the following:
1. To Select All Sheets from the keyboard:
WINDOW KEY F6 ‘selects the active tab name
SHIFT F10 ‘opens right click context sensitive menu
s ‘selects all sheets
2. To De-Select or Un-Group All Selected Sheets
WINDOW KEY F6 ‘selects the active tab name
SHIFT F10 ‘opens right click context sensitive menu
n ‘de-selects or un-groups all selected sheets
I have found the above to be very handy when I want to keyboard select all sheets, print or print preview and then de-select or un-group all sheets after completion. Of course, you can do the same with mouse clicks, but keyboarding is almost always faster and more efficient, once you train your brain and fingers with the procedure.
I hope the above tips are useful to you and other users. Thanks again for all of your resources, classes, tips and advice. It makes me a better EXCEL user every day, a little bit at at time. Thumbs up!
i Like Bonus Tip: Sheet List, this is very helpful for me
but i unable to do — ..If your workbook contains a lot of sheets the #1 Copy Worksheets with Ctrl+Drag
pleases guide
These are great, thanks! I’m a bit of an Excel luddite, so these make my life easier.
Hello,
Can you show how to toggle all sheets of the workbook In one sheet.
Ex. Jan, feb,mar etc and this tabs would be placed in the middle of the one excel sheet and if I click on jan tab I could get the data of that sheet.
Thanks
Do you know the Mac shortcuts? I have long used the keyboard shortcut for selecting multiple sheets in windows, but can’t for the life of me figure out how to do it without resorting to clicking on my new macbook.
thank you – very helpful
How do I save a workbook with multiple sheets and when I open it again, I want all the sheets to be viewed vertically.
Now I have open the workbook and then choose view and then choose vertical.
I have recently upgraded to Windows 10 and Microsoft 2019.
I was able to save under a custom view name before and it will open the way I want as described above.
hi nice work in your website thank u
Thank you for the information, this was helpful for me on the Mac as well.
I have been using Excel since it was introduced and am amazed at what I don’t know, like most of your tips. I especially like the method to copy a worksheet. Huge timesaver. Thank you!!
Thanks a lot 😀
These are really fantastic tips!
Is it possible in the newest version, or is there shortcut keys to email just 1 or a select few worksheets (like we were able to a long time ago) of a multi-page workbook without all of the rigmarole of copying to a new workbook and then choosing to email that workbook?
If they would bring that feature back, I believe that it would make a whole lot of people HAPPY!!!
Thank you so much!
GOOD
Right click… AHH, I was wondering how to do that! Thank you
Doesn’t work for me. Ctrl PgDn doesn’t move to the next sheet, it just moves to the bottom of the current sheet.
For some compacted keyboard, they keys are shared and you’ll need to use a Fn key to assign to a different function. Your PgDn key is probably shared with the End key, in that case, you’ll need to hold the Fn key as well.
so it’ll be Ctrl + Fn + PgDn
wat ur saying tats rite
Awesome very helpful tips 🙂
I’m a veteran excel user. Wanted to know shortcut for moving between tabs. Fabulous!
Thanks!
https://www.excelcampus.com/tips/shortcuts-worksheet-tabs/#comments/83395
Dear Sir,
Thanks. All worksheet wrongly groups,
so please provide solution.
Thanks-Ajay-ajay pune 99 @ gamil . com +91 820 891 2 -891
Is there a way to jump to a specific tab as I can in Chrome with . If that’s not possible, is there a way to jump to the first tab?
I am also looking for a quick way to toggle back and forth from current tab to previous tab. Any suggestions?
Anyone know of a command to advance one “page” worth of tabs at a time (advance the set of displayed tabs, to now display the next set before or after the currently displayed tabs)? I know the ctrl-key in tip #2 well, and if you do shift instead of ctrl for tip #2 it will advance 1 set of tabs at a time either before/after. But is there a keyboard shortcut for this?
great and thanks for the excel tips
Thank you!!! Your instructions were the easiest out of the few I read online -AND- I learned more things that I didn’t know. Thanks for the time saving tips!
For years, I’ve been meaning to search and find a way to get to the first tab quickly – just too busy. So glad I found your website.
Thank you for the tips! Is there a way to quickly toggle back and forth from current tab to a previous tab without having to Ctrl+ page up or page down going through all the tabs?
bumping this up– I have the same question
This is very very very very useful for me
Thanks a lot
Hi,
In Excel 365, is there a shortcut or easy set of steps to achieve Ctrl + Home on MULTIPLE worksheets simultaneously?
I read your article with interest. I still have a question though. Is there a way to add a cell to a worksheet, that if I click on it it will take me to another worksheet in the same book? And then on that worksheet, have a cell / button that I can click to return me to the original worksheet?
Dear Jon,
I am searching for a way to use the MOUSE SCROLL WHEEL to be able to scroll the work sheet tab list left or right, but without leaving the active sheet; so basically, as if I was using the left or right worksheet scroll buttons, but instead of using the Excel buttons, I would use my mouse wheel to scroll the list, then when I see the worksheet I need, I can select it with the mouse. Do you know if this could be accomplished? Thank you. Richard
Thank you Sir
Hey everyone, I was wondering if anyone’s scrolling function (specifically among the sheet tabs at the bottom) stopped working. I have already checked to make sure scroll lock is off but it just won’t work anymore for these sheet tabs and it’s super annoying
Very good and useful tips!
Thank you
highly appreciate your very good sharing, I learnt a lot from this passage, thank you
my excel sheet does not show a tab at the bottom of the sheet.
can’t show yearly tabs at the bottom
doesn’t show ready with arrows at bottom left of the sheet
please help
thank you
Why is it that I can’t ctrl-drag the first sheet to the left to create a copy of the tab to become the new first sheet?
Thank you
What is the shortcut for switching between recently viewed tabs? (I want to view the 1st tab in a Workbook, but shortcut to the 7th tab to the right in the same Workbook, and then back and forth — similar to toggling between 2 favorite TV channels that were recently viewed using the key on the remote control.)
This was an eye opener for me. It has taking me to another level in my work in excel.
Excellent job.