How do We switch between sheets in Excel? (7 Superior Ways)

Excel Tutorial MS EXCEL VBA

It could be challenging to deal with a vast Excel worksheet that includes several sheets. Even seasoned Excel users become overwhelmed when attempting to transition between many worksheets in the same spreadsheet.

Imagine switching back and forth between worksheets #3 and #12 while working on an Excel spreadsheet with 15 worksheets.

In this article, I will teach you some extraordinarily impressive and sophisticated tactics that will make switching between tabs simpler and faster. There are many ways to transition between worksheets in Excel.

This instruction explains:

1. Page Up/Down Shortcut on the Keyboard for Switching Between Sheets
2. Switch across Sheets Using the Watch Window
3. Access any sheet with the option to activate that sheet.
4. Utilize the Name Box to Change Between Sheets
5. Using the Go To Dialog Box, Change Between Sheets
6. Using hyperlinks, jump from one sheet to another
7. Simple VBA Macro for Tab/Sheet Switching

Page Up/Down Shortcut on the Keyboard for Switching Between Sheets

Use the keyboard keys listed below if you need to switch between sheets that are close together in the same workbook (i.e., there aren’t many worksheets between the two sheets you wish to switch between).

Keyboard Shortcut for Right-Moving Sheets:

Control + PageDown

Keyboard Shortcut for Moving Leftward Through Sheets:

Control + Up

You must hold down the Control key while using the PageUp or PageDown key to access this shortcut.

You may substitute the Command key for the Control key while using a Mac.

Press the PageDown/PageUp key once to advance to the next sheet on the right or left, respectively. If you continue to press the key, it will cycle through several pages until it hits the final or first worksheet, whichever comes first.

When you only have a few sheets, this strategy works well. Even this approach could feel daunting if you have 10 or more sheets. Moving the sheets you wish to flip between next to one another will make it simpler (but that may not always be possible)

Switch across Sheets Using the Watch Window:

This technique is probably the best approach to transition between many Excel sheets.

Excel has a lesser-known tool, Watch Window, which enables you to monitor data in particular workbook cells.

Although moving between pages in the worksheet is not its primary function, it is a good solution in this use case.

Let’s say you’re working on a worksheet and wish to flip between Sheets 1 and 5.

The procedures to accomplish this using the Watch Window are as follows:

Choosing cell A1 on Sheet 1 (one of the sheets between which you want to move back and forth)

Open the ribbon and choose the Formulas option.

The Watch Window option may be found in the Formula Auditing group.

Click Add Watch in the Watch Window dialogue box.

Ensure that the appropriate cell is chosen in the Sheet. If you’d like, you may alter the cell reference.

Select Add. With this, the Watch Window will get the new watch instance.

Click the Add Watch button once more.

Navigate to the next sheet you wish to switch to in the Add Watch window, then pick any cell there.

Select Add. The Watch Window will receive a second watch instance as a result.

When you’re finished, there will be two instances in the Watch Window, and if you double-click on any of them, you’ll be brought to that cell.

Because these two cells are on different sheets, double-clicking on one of them will take you to the other cell on the other worksheet.

This technique for flipping between sheets has many advantages:

This technique is probably the best approach to transition between many Excel sheets.

Excel has a lesser-known tool, Watch Window, which enables you to monitor data in particular workbook cells.

Although moving between pages in the worksheet is not its primary function, it is a good solution in this use case.

Let’s say you’re working on a worksheet and wish to flip between Sheets 1 and 5.

Watch Window is docked below the ribbon so it’s always visible

Access any sheet with the option to activate that sheet.

The “Activate” Sheet option in Excel displays a list of all the workbook’s sheets, allowing you to quickly choose one and navigate to it.

Therefore, switching between two or more pages at once is a simple process.

But where is the option to “Activate Sheet”?

You must be aware of a simple method to do it.

Go to your workbook’s grey area to the left of the first sheet tab.
The two grey arrow symbols should be the focus of the cursor.
Note that you need to press the right key on your mouse (or trackpad)—not the left. The ‘Activate’ dialogue box, which contains all of the sheet names, will then be opened.
Double-click on the Sheet that contains the destination.
If you wish to switch to a different sheet, just follow the same steps again. Therefore, you may use the Activate Sheet option to transition between two or more sheets.

You should be aware that clicking on any sheet will take you to the cell that was most recently active when you were on that page.

Utilize the Name Box to Change Between Sheets:

You may rapidly go to any Named Range in the worksheet using Excel’s Name Box.

We may take advantage of this by designing named ranges that point to the cell or range of cells in the sheets that we need to flip between.

Let me describe how it operates.

For example, let’s say I wish to flip between Sheet1 and Sheet3.

I would first make two named ranges that referred to cells in Sheets 1 and 3.

The procedures are as follows:

Choosing cell A1 on Sheet 1

Fill up the Name Box with Sheet1A1 (you can use any name you want, but it should not have a space character in the name)

Cell A1 on Sheet3 should be chosen.

MS EXCEL VBA

Fill up the Name Box using Sheet3A1.

In the workbook, the aforementioned processes would produce two named ranges. Cell A1 in Sheet1 and Cell A1 in Sheet3 is designated as Sheet1A1 and Sheet3A1, respectively.

Now, click on the drop-down symbol in the Name Box to move between these two sheets. The drop-down will display the names of each named range.

As a result, to convert from Sheet3 to Sheet1, choose Sheet3A1 from the drop-down menu.

Although I’ve only demonstrated how to do this with two sheets, you can use as many as you like.

I’ve also shown you how to move to cell A1, but you can use this technique to hop to any cell or group of cells. Simply choose the cells or range of cells you wish to leap to, and then give the range a name.

Pro Tip for you: Make sure the Named Ranges names are straightforward and informative. Additionally, names cannot contain a space character.

Using the Go To Dialog Box, Change Between Sheets

Using the Go-To dialogue box is an additional simple method for navigating to a certain sheet, individual cell, or range of cells.

You may manually input the sheet name and cell address in the Go-To dialogue box, and it will immediately send you there.

Let’s say you have a workbook and you want to access Sheet3’s cell D20.

Enter Sheet3!D20 in the Reference area.

Tap “Enter” (or click on the OK button)

The aforementioned actions would immediately place you in Sheet 3’s cell D20.

It, if you want to move between sheets and don’t mind using the Go-To special dialogue box, you may accomplish so by entering the name of the Sheet followed by the cell reference.

If you use the Go-To Special dialogue box to navigate to a particular cell,
the choice will still be available the next time you open the dialogue box
since it has a temporary memory. So, rather than inputting the reference
to the cell you want to leap to, you may just double-click on it if you see it.

Using hyperlinks, jump from one sheet to another:

Another effective method for enabling users to easily go from one sheet to another is by adding hyperlinks.

A hyperlink can be added to a shape or picture as well as a cell. The user will be immediately sent to the connected cell or range after clicking the hyperlink when it has been added.

Additionally, if this cell or range is located in another sheet, clicking the hyperlink will also open that page.

MS EXCEL VBA

Let me give you an illustration.

Let’s say I want to add a hyperlink to cell A1 in my Excel worksheet so that when I click it, it takes me to cell A1 in Sheet 2.

The procedures are as follows:

Turn on Sheet 1
simply right-click on cell A1 (the cell where I want to create the hyperlink)
Select “Links.” The “Insert Hyperlink” dialogue box will appear as a result.
Click the “Place in This Document” option under the “Link to:” options on the left.
Select Sheet2 from the list of sheet names that appeappear whatever sheet to which you want to jump to)
In the “Text to Display” field of cell A1, type the text you want to appear. I’ll add the words “Go to Sheet2” in this example.
Enter A1 in the area labelled “Typlabeledll reference” (this is the cell in Sheet2 where the hyperlink would take us)
Input OK.

By following the instructions above, the desired text would be inserted into the chosen cell, and a hyperlink would be created that, when clicked, would transport us to cell A1 in Sheet 2.

Pro Tip: Use the keyboard shortcut Control + K (or Command + K on a Mac) to activate 
the insert hyperlink dialogue box after selecting a cell or range of cells.

Note that you may construct a hyperlink to a shape in the worksheet using the same procedures as above.

Once complete, clicking on that shape would direct you to the desired area (which could be in the same sheet or any other worksheet)

Simple VBA Macro for Tab/Sheet Switching:

The last technique I want to discuss is utilising VBA macro code.

To swap the sheets everytime you click on an icon in the Quick Access Toolbar, you may develop a macro and add an icon to the toolbar.

The macro code for switching between Sheets 1 and 3 is provided below.

'This macro code has been developed by Sumit Bansal of https://trumpexcel.com
Sub SwitchSheets()
If ActiveSheet.Name = "Sheet1" Then
Sheets("Sheet3").Activate
Else
Sheets("Sheet1").Activate
End If
End Sub

The code above validates the name of the current sheet; if Sheet1 is the active sheet, Sheet3 is activated; else, Sheet3 is activated.

This code may be added to the Quick Access Toolbar after being placed in a module in the VB Editor. In this manner, it will function as a toggle icon that, with a click, switches between sheets.

I’ve used the names Sheet1 and Sheet3 in this code. With the sheet names in your workbook, you may alter these.

These are a few of the techniques you may use in Excel to swiftly transition between spreadsheets. You can use one or more of these workarounds in place of manually attempting to transition between sheets, which can be time-consuming and error-prone.

As I indicated before, the watch window option is likely the best and most effective way to transition between pages.