12

7 Shortcuts for Working with Worksheet Tabs in Excel

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

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 12 comments
Anne-Marie - October 22, 2018

These are great, thanks! I’m a bit of an Excel luddite, so these make my life easier.

Reply
preeti - October 22, 2018

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

Reply
Wayne Edmondson - October 20, 2018

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!

Reply
Jonni Jones - October 20, 2018

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.

Reply
Edil - October 19, 2018

Gr8 Tips Jon. Some I already knew! Nevertheless, thanks again!

Reply
Sandy - October 19, 2018

Thank you for the tips Joe. I’m sure they’ll be helpful to me and many others.

Reply
Ahmed Sheikh - October 18, 2018

Hi

Nice tips~

Plz add an option to save your posts as PDF.

Reply
Rashid Mehmood - October 18, 2018

Its helpful.
Thankyou

Reply
Bob - October 18, 2018

Even an Excel old-timer like myself always learns something new from your posts. Keep up the good work!

Reply
    Jon Acampora - October 18, 2018

    Thanks Bob! I appreciate your support. 🙂

    Reply
diannat - October 18, 2018

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!

Reply
    Jon Acampora - October 18, 2018

    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 sheets
    Dim 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.

    I hope that helps. Thanks!

    Reply

Leave a Reply: