When you work with Excel, you can switch between sheets by just clicking them. But when you have many of them, that you have to click the button to see the next ones, it’s a good idea to use different ways to navigate between them.
There are a few ways to switch to different worksheets. In this lesson, I’m going to show you the most popular ones.
Switch sheets with a keyboard shortcut
The previous and the next tab
The quickest way to do it is to use the Ctrl + PgUp to navigate to the previous cell and Ctrl + PgDn to move to the next cell.
If you move to the first tab, and you press Ctrl + PgUp, it will stay on this sheet. The same thing about the Ctrl + PgDn and the last sheet.
The Go To command
This command allows you to navigate to the sheet you want by specifying the sheets name and the cell you want to activate. In the case of switching sheets, you can always use cell A1 as the beginning of the worksheet.
I’ll illustrate this to you by using this simple example.
There are four worksheets: employees, earnings, travel, and expenses.
To switch to any of these sheets, you can use the Go To command. It’s located in Home >> Editing >> Find & Select >> Go To.
Now, if you click OK, it will switch to worksheet earnings and make cell A1 active.
There are two disadvantages to this method.
- The process takes longer than with a shortcut, or a mouse-click.
- You have to know the name of the worksheet you have to switch to.
Arrows on the sheets tab
There are two arrows at the bottom-left part of the screen, to the left of the worksheet tabs.
If you click the left one, the whole stripe with worksheet names will move to the right, so the next sheet name on the left will be visible. You can repeat this until it reaches the first sheet name, then it will become greyed out. If you move right to the last cell, the right arrow will become greyed out as well.
It’s important to remember that clicking the arrow won’t switch the sheet, it just moves the stripe making a sheet name visible, so you can click it.
If you hover over these arrows, a popup will appear:
If you click arrows while holding down the Ctrl key it will move the whole way to the first or last sheet.
The Activate window
Another quick and easy way to navigate between worksheets is by using the Activate window.
You can open it by right-clicking the arrows at the bottom-left corner.
Now, just select a sheet and click OK. You can also double-click the selected position. This will have the same effect.
Another way to quickly navigate between sheets is to create a hyperlink with a reference to a particular sheet. After you click it, you will be moved to the referenced worksheet.
Here’s how it works:
Click a cell (e.g. A1), and navigate to Insert >> Links >> Link.
On the left side of the window, there are four icons. Click Place in This Document and click the earnings worksheet. You can also remove A1 in the Text to display and keep only the name of a sheet.
Click OK, and a hyperlink will appear inside a cell.
Now, when you click the hyperlink, it will automatically take you to the earnings tab.
VBA Code for the first and last worksheet navigation
There are no keyboard shortcuts to jump to the first and last worksheet. But there is a VBA code, and we can use it to write our shortcuts.
Let’s create a new macro.
Press Left Alt + F11, create a new module, and enter this code:
Sub sheet_first() Sheets(1).Activate End Sub Sub sheet_last() Sheets(Sheets.Count).Activate End Sub
Close the window. Now, navigate to View >> Macros >> Macros:
Click the first macro and choose Options.
What’s important, is that you can only use letters when you add a shortcut to a macro, so we can’t create a shortcut like this one: Ctrl + Shift + PgUp. Instead, press Shift + o to create a shortcut for sheet_first and press OK.
Repeat the same process for sheet_last and apply a new shortcut: Ctrl + Shift + p.
After you create these shortcuts, you can easily switch between the first and last sheets.