Bottom line: Learn time saving tips and shortcuts for selecting and copying worksheet tabs. Includes a few simple VBA macros. Show
Skill level: Beginner Tips for Navigating Worksheet TabsIf 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+DragThis 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.
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 SheetIf 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 SheetIf 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 SheetsWe 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 SheetsWhen 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 SheetsTo 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) SheetsTo 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 SheetsTo hide multiple sheets:
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 ListIf 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 ConclusionI 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! 🙂 |