Overview
Worksheets are a key component of any good Excel file. They help you organize your files and separate the inputs, outputs, and other areas. Whether you work with simple files containing just a couple of sheets, complex files with hundreds of sheets, or anything in between, navigating them quickly will save you a ton of time. In this article, we’ll explore ten different ways you can navigate worksheets in Excel, covering them from the slowest approach to the fastest.
Ten Ways to Navigate Sheets
1. CLICK THE TABS
| How: | At the bottom, simply click the tab with the sheet name you want to go to. |
| Pros: | It’s easy, straightforward, and pretty much everyone knows this method. |
| Cons: | It’s slower, especially with a lot of sheets, and it requires the mouse. |
| Tips: | Right-click sheet tabs to hide them, color them, and perform other sheet tasks. |
2. TAB SLIDER ARROWS
| How: | Click the arrows to the left of the sheet tabs at the bottom. This will slide the displayed sheet tabs left or right. |
| Pros: | It helps you navigate to sheets that are not displayed. |
| Cons: | It can be slow, especially with a lot of sheets. |
| Tips: | Click Ctrl while clicking the arrows to scroll all the way to the left or right! |
3. DISPLAY SHEET LIST
| How: | Right-click the slider arrows to the left of the sheet tabs at the botttom. |
| Pros: | It’s easier to see all the sheets in one scrollable, horizontal list. |
| Cons: | It’s not searchable, and it’s slow with lots of sheets. |
| Tips: | Put your most important sheets in the front where they are easy to access! |
4. NAVIGATION PANE
| How: | Toggle the navigation pane using the View ribbon tab, and click the Navigation button. Use the sheet names to navigate to them or ranges/objects within them. |
| Pros: | It’s a persistent pane that is displayed until you close it. It shows ranges and objects in addition to sheets. It’s searchable. |
| Cons: | Although it’s quite flexible, it’s still very mouse-driven. |
| Tips: | Use the keyboard shortcut Alt-w-k to toggle the navigation pane! |
5. KEYBOARD SHORTCUTS
| How: | Use the keyboard shortcuts Ctrl+PageUp (go to previous sheet) and Ctrl+PageDown (go to next sheet). |
| Pros: | They are quick and easy to learn and use, and don’t require the mouse. |
| Cons: | This approach can still be slow when your file contains a lot of sheets. |
| Tips: | Use Alt+PageUp to scroll left horizontally within your sheet and Ctrl+PageDown to scroll right horizontally. |
6. FOLLOW CELL REFERENCES
| How: | With a cell selected that contains a formula with cell references, click Ctrl+[ to jump to the first cell reference in formula. If that reference is in another sheet, it will jump to the containing sheet. |
| Pros: | It helps you quickly jump to a specific sheet and range, which is often what you’re after when navigating sheets. |
| Cons: | This shortcut only works with the first cell reference in the active cell’s formula. |
| Tips: | Use a macro like this one search for and/or to jump to any reference in the active cell’s formula! |
7. NAME BOX
| How: | For the sheets you want to be able to jump to quickly, set up range names. Then use the name box to the left of the formula bar to jump between them. |
| Pros: | This is another method that allows you to jump to a specific sheet and range. |
| Cons: | This approach requires some setting up in advance. |
| Tips: | Use a macro like this one to quickly search, jump to, define, or edit named ranges, including in bulk! |
8. JUMP TO THE FIRST OR LAST SHEET
| How: | Use macro shortcuts to jump to the first sheet, last sheet, or toggle between your most recent sheets. |
| Pros: | It’s a quick and easy way to navigate to common sheets in your file. |
| Cons: | It requires macros – either building them or installing them. |
| Tips: | Use a macro like this one to provide all kinds of sheet-related shortcuts! |
9. AUTO-COMPLETE SEARCH BOX
| How: | Use an auto-complete search form that offers suggestions as you type so you can jump to any sheet in your file. |
| Pros: | It’s a super-quick way to jump to any sheet in your file without touching the mouse. |
| Cons: | It requires macros – either building them or installing them. |
| Tips: | Use a macro like this one to provide all kinds of sheet-related shortcuts! |
10. TABLE OF CONTENTS
| How: | Build a hyperlinked list of worksheets manually or with a macro. |
| Pros: | Jump to any sheet in your file with hyperlinks. It is also an always-visible way of showing all the sheets to anyone who uses your file. |
| Cons: | It may require updates to refresh the sheet list if you make changes to your file. |
| Tips: | Use a macro like this one to instantly build or refresh a table of contents! |
Summary
The more sheets you work in, the more it’s worth knowing about all the different options covered above. They may even encourage you to use more sheets to separate your schedules more logically. I’ve written and used several macros that make working with lots of sheets so much easier. The next time you feel like you’re spending too much time working with sheets, remember these approaches!
What is your favorite approach to navigating sheets in Excel? Did we miss anything? Let us know in the comments below!
Recent Comments