Quickly Jump to a Sheet in Excel (5 levels)
Last updated on Mar 21, 2024 By Victor Chan
Have you ever wanted to quickly jump from one sheet to another in Excel? There are several ways to navigate between worksheets efficiently, and this article will guide you through them, ranging from simple keyboard shortcuts to more advanced VBA solutions. We'll cover five different levels, so there's something for everyone.
Download FREE Excel Workbook
Step 1: Sign up for free Click Here
Step 2: Log in for access Click Here
Step 3: Download file Jump-Sheet-Levels-1-5.zip
Are you new to Excel VBA Macros?
• Find out how to enable and disable macros in Excel with this tutorial
• Save time and effort by copying VBA macros from one workbook to another with these instructions
Level 1: Keyboard Shortcuts for Moving Between Sheets
The most basic way to move between sheets is by using keyboard shortcuts:
- Hold Ctrl + Page Down or Ctrl + Page Up to move to the next or previous sheet, respectively. This is like turning the pages of a book, but you can only go forward or backward one sheet at a time.
- Hold Ctrl + Shift + Page Down or Ctrl + Shift + Page Up to select multiple sheets. Be cautious, as any changes you make will be applied to all selected sheets.
These keyboard shortcuts are handy if you have a few sheets to navigate, but they don't work well if you have numerous sheets.
Level 2: The Activate Sheet Dialog Box
Most people who use Excel don't know about this feature in the bottom left-hand corner.
See those scroll arrows? Here's how to use them:
- Press Ctrl + Left Click on the right arrow to scroll to the last sheet.
- Press Ctrl + Left Click on the left arrow to scroll to the first sheet.
Note that this doesn't select the first or last sheet; it just scrolls there.
You can also right-click to see all sheets, which brings up the "Activate Sheet" dialog box. It's like having a table of contents for your workbook. Double-click on a sheet to jump there, then right-click and double-click to jump back.
Level 3: VBA Helper for Jumping to Sheets
While the "Activate Sheet" dialog box is neat, there's no keyboard shortcut for it, and we like keyboard shortcuts because they save time. We'll write a VBA macro that shows a menu of workbook tabs and bind it to the keyboard shortcut Ctrl + J (for "jump").
Here are the steps:
- 1. Go to the Developer tab and click on Visual Basic to open the Visual Basic Editor.
- 2. Right-click in the area and insert a new code module.
- 3. Type in the following subroutine:
[VBA Code Box]
Sub Workbook_Tabs_Popup_v1()
Application.CommandBars("Workbook Tabs").ShowPopup
End Sub
- 4. Head back into Excel, click on Developer > Macros, and select the macro you just created.
- 5. Click Options and press J to assign the shortcut key Ctrl + J.
- 6. Click OK and close the dialog box.
Now, when you press Ctrl + J, you can use the mouse to select a sheet by left-clicking on it, or use the arrow keys and press Enter to select a sheet.
Remember to save the workbook as an Excel macro-enabled workbook to keep the VBA code the next time it's opened.
To find out more about enabling and disabling VBA code read How to Enable and Disable Macros in Excel: A Complete Guide.
One issue we run into is that when we have more than 15 sheets in the workbook, the sheets after Sheet 15 are not listed, and you have to click on "More Sheets" to show the full scrollable list.
Level 4: Upgrading the VBA Helper
To improve our VBA helper, we'll make it check how many sheets are in the workbook. If there are fewer than 15 sheets, it will show the quick list, and if there 15 sheets or more, it will show the scrollable list without needing to click on "More Sheets." We'll also add the VBA macro to our personal macro workbook so we can access it from any workbook.
Follow these steps:
- 1. Go to Developer > Visual Basic, and in the VBA editor, add the following code to the Workbook_Tabs_Popup_v1 subroutine:
[VBA Code Box]
Sub Workbook_Tabs_Popup_v2()
' Check number of sheets to decide which dialog box to show
' Assign to keyboard shortcut CTRL + J -> Jump to sheet
' Check the number of sheets in the active workbook
If ActiveWorkbook.Sheets.Count > 15 Then
' Show the scrollable list
Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
Else
' Show the quick list as a popup
Application.CommandBars("Workbook Tabs").ShowPopup
End If
End Sub
- 2. Copy this code into your personal macro workbook (in a module called "Navigation," for example).
- 3. Head back into Excel, click on Developer > Macros, and unassign the previous keyboard shortcut from Level 3 if you made one. We can’t assign the same keyboard shortcut to two different macros.
- 4. Select your personal workbook, find the tabs_popup macro, click Options, and assign the key J.
Now, when you press Ctrl + J, it will show the "Activate Sheets" dialog box with a scrollable list if there are more than 15 sheets, or a quick list if there are fewer than 15 sheets.
Ensure you save your workbook as a macro-enabled file to keep the VBA magic intact.
For a beginner’s guide on how to work with VBA macros in Excel, read this article How to Edit Macros in Excel: The Ultimate Beginner's Guide.
Learn how to create and use your own Personal Macro Workbook here: Excel VBA Tutorial: Get your personal macro code vault (aka Personal Macro Workbook)
Level 5: Using the Watch Window to Jump Between Specific Sheets
Sometimes, you may want to jump between a few specific sheets without scrolling through the entire list of sheets. This is where the Watch Window comes in handy.
- 1. Click on the Formulas tab and toggle the Watch Window on.
- 2. Click "Add Watch" and select a cell location you want to jump to (e.g., H5 on Sheet2).
- 3. Click "Add Watch" again and select another cell location (e.g., H5 on Sheet1).
- 4. Double-click on the row in the Watch Window to jump to that location.
This is like adding bookmarks in your browser or favorite book. You can pick specific cells to jump to without having to scroll through a long list of all your sheets.
Pro Tip: You can dock the Watch Window in different locations (top or left) and resize it to suit your working style.
Conclusion
You now have five tools at your disposal for jumping between sheets in Excel: Keyboard shortcuts, the "Activate Sheet" dialog box, a VBA helper, an upgraded VBA helper, and the Watch Window. Explore these options and find the ones that work best for your needs.
Remember, Excel is full of useful tools and features that most users never use. Don’t be like them. Learn Excel VBA! It can be both fun and useful, so subscribe to my email newsletter for more awesome Excel and VBA resources.
I also have a library of videos you can watch to learn more about Excel. You’ll learn new ways to improve your spreadsheets. Here’s the link to my YouTube channel.
And if you’d like to learn Excel VBA in a structured, methodical way, I have an online course called “Launch Excel Macros & VBA School”. You can find it in the shop.
Happy Excelling!
Hi, I'm Victor!
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.