Working with multiple worksheets can be quite tough for Excel users, especially if there is a lot of work. In Microsoft Excel, there are several tricks that individuals can use to switch between Excel worksheets. In this tutorial, we will explain seven ways people can switch between worksheets in their Microsoft Excel program.
How to switch between Worksheets in Excel
You can switch between Excel worksheets using any one of these methods:
- Using keyboard shortcuts.
- Using the Watch Window.
- Go to any sheet with the Activate Sheet option.
- Using the Name Box.
- Using the Go To dialog box.
- Using Hyperlinks.
- Using VBA Macro.
1] Using keyboard shortcuts
If you want to move between worksheets in the same workbook, you can use shortcut keys Ctrl + Page Up and Ctrl + Page Down.
- The Ctrl + Page Down shortcut key moves to the right.
- The Ctrl + Page shortcut key to move to the left.
2] Using the Watch Window
The Watch Window is a great feature in Microsoft Excel that allows users to switch between worksheets. The Watch Window stays on top so that you can watch these cells, even when you are working on another sheet.
Follow the steps below on how to switch between sheets using the Watch Window in Microsoft Excel:
Click a cell in Sheet 1, then click the Formulas tab.
Click the Watch Window button in the Formula Auditing group.
A Watch Window dialog box will open.
Click the Add Watch button.
It will add the Worksheet and the cell to the Watch Window.
Click Add.
Click on the Add Watch button again.
Change it to the sheet you want to go to and the cell there.
Then click Add.
Double click the sheets to switch between the worksheets.
If you want to delete any of the worksheets in the Watch Box, click on the Worksheet that you want to delete and select Delete Watch.
One of the benefits of the Watch Box is if you close the workbook and open it later, you can continue to switch between the worksheets you have added.
3] Go to any sheet with the Activate Sheet option
In Microsoft Excel, there is Activate sheets option that allows users to jump between worksheets. Follow the steps below on how to the Activate Sheet option.
Go to the area of the navigation button on the left and right-click between them.
An Activate Sheet dialog box will open.
Choose the sheet where you want to switch to and click Ok.
4] Using the Name Box
We can use the Name box in the top left of the spreadsheet in Excel. Follow the steps on how to use the Name Box below:
Type a reference in the Name Box on Sheet for Example Sheet1B2.
Then go to Sheet 4 and type a reference in the Name Box, for example, Sheet4B2, then press enter.
Now you will see a drop-down arrow. Click the drop-down arrow and choose which workbook you want to switch to.
5] Using the Go To dialog box
Follow the steps below to use the Go To feature in Microsoft Excel to switch between sheets in Microsoft Excel.
To open the Go To dialog box, you can press F5 or click the Find and Select button in the Editing group on the Home tab.
Select Go To from its menu.
The Go To dialog box will open.
In the Reference section, enter the Workbook sheet you wish to switch to, for example, Sheet5!B3, then click OK, and it will switch to sheet 5.
If you open the Go To dialog box and you will see the reference you entered in a list, and you can switch to it anytime.
6] Using Hyperlinks
You can also use hyperlinks to switch between sheets in Microsoft Excel. Follow the steps below to switch between hyperlinks in Microsoft Excel:
Click on Sheet 2, then Right-click the cell where you want to create the hyperlink.
Select Link from the context menu.
An Insert Hyperlink dialog box will open.
Click the Place in this document tab on the left pane.
In the select a place in this document list box, select any of the sheets in the list. We have selected Sheet4.
In the section Text to display, you can type Go to sheet4.
In the Type a cell reference section, type where you want the hyperlink to take you. We have left the cell reference as A1.
Then click OK.
A hyperlink is created in cell A1.
7] Using VBA Macro
You can use VBA (Visual Basic for Application) to switch between sheets. Visual Basic for Application is a programming language in Microsoft and is available in other Microsoft Office programs such as Word and PowerPoint. Follow the steps below on how to use the VBA macro to switch between worksheets in Excel.
On the Developer tab, click the Macros button in the Code group.
Name the Macro and click Create.
The Visual Basic for Application window will open.
Type the following:
Sub SwitchSheets()
If ActiveSheet.Name = “Sheet1” Then
Sheets(“Sheet5”).Activate
Else
Sheets (“Sheet1”).Activate
End If
End Sub
Click F5 to run the Macro or click the Run Button icon on the standard toolbar or click the Run tab and select Run Sub/UserForm from the menu.
Go on the Excel sheet whenever you run the program on the Microsoft Visual Basic for Applications window; you will see that the worksheets are switched each time you click Run.
Read: Difference between Worksheets and Workbooks in Excel
How do I navigate to the second worksheet?
To navigate between worksheets in Microsoft Word is quite simple. At the bottom of your Excel worksheet, you will see the worksheet tab; you can add more worksheets. To switch between the tabs, click on each of the worksheet tabs.
What is the shortcut to switch between sheets in Excel?
Shortcut keys are key combinations that allow users to make fast commands. In Microsoft Excel, the shortcut keys to switch between worksheets are Ctrl +Page Down and Ctrl Page Up combination keys.
READ: 10 most useful Excel Tips and Tricks for beginners
We hope this tutorial helps you understand the seven ways to switch between worksheets in Microsoft Excel.
