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 Tabs - Time Saving Shortcuts for Sheets in Excel Workbooks

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.

How to Copy a Worksheet with Ctrl Left Click

  1. Left-click and hold on the sheet you want to copy.
  2. Press and hold the Ctrl key.  A plus symbol will appear in the sheet mouse icon.
  3. Drag the sheet to the right until the down arrow appears to the right of the sheet.
  4. 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.

Dedicated Buttons to Scroll to First and Last Sheet in Excel 2010 and Earlier

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.

Ctrl+Click to Scroll to First and Last Sheet - Buttons Removed in Excel 2013

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

Keyboard Shortcut to Select Next or Previous Sheet in Excel Ctrl Page Down 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.

Hold Ctrl or Shift to Select Multiple Sheet Tabs in Excel

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.

Keyboard Shortcut to Select Multiple Sheets in Excel Ctrl Shift Page Up Down

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.

Making Changes to Multiple Sheets in Excel

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.

Group Appears in Application Header Title when Multiple Sheets Selected in Excel

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.

Select All Sheets in Excel

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.

Ungroup or Deselect Sheets by Selecting a Tab Outside the Group

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.

Ungroup Sheets Option from the Right Click Menu on Excel Sheet Tabs

 #7 Hide & Unhide Multiple Sheets

To hide multiple sheets:

  1. Select the sheets using the methods mentioned above.
  2. Right-click one of the selected tabs.
  3. Choose Hide.

The sheets will be hidden.

Hide Multiple Sheet Tabs in Excel

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.

Cannot Unhide Multiple Sheets

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.

Sheet List in Excel - Right-Click Tab Navigation Buttons

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.

Tab Hound Quickly Search and Navigate to any sheet in the workbook

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.

Macros and VBA Webinar Banner - Join Me - 550x200

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! 🙂

22 comments

Your email address will not be published. Required fields are marked *

  • 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!!!

  • 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!!

  • 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.

  • 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.

  • 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

  • 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

  • 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!

  • 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.

  • 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!

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly