Here are two simple ways to navigate between sheets in Excel:
- To move to the adjacent sheet tabs, press Ctrl + PgDn to move to the next sheet, and press Ctrl + PgUp to move to the previous sheet.
- To move to any particular sheet, irrespective of the tab position, go to the bottom left corner of the sheet > right-click on it to open the Activate dialog box > choose any particular sheet that you want to move to > press OK.
Navigating between sheets in Excel is crucial for accessing and managing distinct sets of information within a single workbook. This allows efficient organization, comparison of data, and seamless referencing across different sections.
Whether analyzing financial data, working on projects, or reviewing reports, the ability to move effortlessly between sheets enhances productivity and ensures a more streamlined workflow.
In the Excel tutorial, you will learn several techniques with relevant examples to navigate between sheets in Excel.
Consider a workbook that consists of multiple worksheets. Here, I have moved from one sheet to another using the Activate dialog box.
To navigate between sheets in Excel, you can use any of the following 10 methods:
Clicking on the Sheet Name Tab
This method is widely used for tab navigation in Excel. You can switch between sheets by simply left-clicking on the sheet name tabs.
To perform this action, follow the steps below:
- Place the cursor on the name of the desired worksheet.
In this case, we will place the cursor on the “Sheet2” tab. - Then, left-click on it.
Now, the “Sheet2” sheet will be the active sheet.
Using Keyboard Shortcut
You can use a basic keyboard shortcut to navigate between worksheets in Excel. This approach is efficient for navigating to adjacent (previous or next) sheets in Excel.
To navigate to adjacent worksheets, follow the given instructions:
- If you want to move to the previous sheet (e.g. from the “Keyboard Shortcut” sheet to “Sheet2” sheet), press Ctrl + PgUp.Consequently, it will take you to the “Sheet2” sheet.
- Press Ctrl + PgDn from the “Sheet2” sheet to move to the right.
Therefore, you can see that we have moved toward the right. Thus, you can navigate between sheets using keyboard shortcuts in Excel.
Using Navigation Pane
The Navigation Pane in Excel makes it easy to find and access objects like worksheets, tables, charts, images, and more.
To navigate between worksheets using the Navigation Pane, apply the following steps:
- Go to the View tab.
- Select the Navigation command under the Show option. As a result, the list of the prevailing worksheets will appear on the right side of the active sheet.
- You can select any sheet name to navigate from this navigation pane.
Here, I have selected “Sheet2“.
As a result, I have navigated to “Sheet2“.
Using Go To Dialogue Box
The Go To dialog box in Excel is used to navigate between worksheets. When the Go To dialog box appears, it asks for the reference of any cell in the desired sheet. Once the command is executed, it will immediately navigate to the specified sheet.
To accomplish this action, follow the steps below:
- Go to the Home tab > Editing group > Find & Select group > Go To command. At this point, a Go To dialog box will appear.
- In the Reference box, put the worksheet name followed by an exclamation mark (!) before the cell address.
For example, I inserted Sheet9!B5 here. - Click OK.
Therefore, I have reached cell B5 of “Sheet9“.
Applying the Watch Window Feature
The Watch Window can be used for navigating between specific sheets in Excel. This method is helpful when your workbook contains a large number of worksheets and you only need to navigate between some specific sheets.
To navigate between sheets using the Watch Window in Excel, follow the steps below:
- Select any cell from the sheet you want to navigate.
- Click the Formulas tab > Watch Window. At this point, a Watch Window toolbar will appear.
- Then, click on Add Watch. In the Add Watch dialog box, the reference to the active sheet and the selected cell will appear in the select cells that you would like to watch the value of field.
- Click on Add. Now, you can see the added sheet in the Watch Window.
- To add another sheet, click on Add Watch.
- Then, navigate to another sheet and select any cell.
The reference to that sheet and the selected cell will appear in the select cells that you would like to watch the value of field. - Click the Add button.
Now, you can see two sheets in the Watch Window. You can add more sheets by following the same steps. - Once the targeted sheets are added in the Watch Window, double-click on any worksheet name to navigate to that sheet.
Here, I have double-clicked on the Sheet2 option.
As a result, we have reached cell B5 of “Sheet2“.
Read More: How to Navigate Large Excel Spreadsheets
Using Activate Sheet Option
The Activate dialog box is a useful tool for worksheet navigation. As you access the Activate dialog box, a list of the sheet names will be displayed. Then, you can select any sheet to navigate between sheets. This is a quick and handy method to navigate through several sheets.
To navigate between worksheets using the activate sheet option, follow the steps below:
- Go to the bottom left corner of the sheet.
- Then, right-click on itAs a result, the Activate dialog box will appear with a list of the existing sheets of the workbook.
- Now, select your preferred sheet (in this case, we have selected Sheet2) from the list and press OK.
Consequently, “Sheet2” will be activated.
Using Name Box
We can use the Name Box to specify the name of either a cell or a cell range. In this method, we will assign a name to a single cell on each sheet within the Name Box. Then, we will use the Name Box to move between sheets in Excel.
To perform this action, follow the steps below:
- Select a cell (in this case, cell B5).
- Then, go to the Name Box and define the name NameBoxA1 in the Name Box.
You can type any name in the Name Box. - Press Enter.
- Again, go to another sheet (e.g. Sheet2), select a cell (e.g. B5), and type Sheet2B5 in the Name Box.
- Then, press Enter.
- Now, click on the drop-down arrow in the Name Box.
Therefore, the names of two reference sheets will appear. - Here, we want to navigate to “Sheet2.” Therefore, we select Sheet2B5.
Hence, it will take us to “Sheet2“.
Using Hyperlink to Navigate Between Sheets
You can establish a connection between two sheets by linking the cell value of one sheet to the corresponding link of another sheet. By clicking on the cell value, Excel will navigate us to the linked sheet.
To navigate worksheets by applying hyperlinks, follow the provided steps:
- Select a desired cell (in this case, we have selected the C5 cell) in your worksheet.
- Now, select Link from the context menu.
- In the Insert Hyperlink dialog box:
- Go to Place in This Document.
- Then, write your desired cell (in this case, cell C5) in the Type the cell reference box.
- After that, click on the desired sheet (in this case, the “Sheet9 ” sheet) that you want to link.
- Now, click on OK.
Therefore, cell C5 has now a Hyperlink.
- Click on this Hyperlink.
It will navigate to cell C5 in”Sheet9” worksheet.
Using VBA Code to Navigate Between Two Specific Sheets
When you are working with multiple worksheets, it might be difficult to switch back and forth between two specific worksheets. In these situations, creating a dependable navigation tool can be extremely beneficial.
You can create a macro and add it as a button in the Quick Access Toolbar. By clicking on this icon, you will be able to switch between sheets effortlessly.
To accomplish this task, follow the necessary steps below:
Step 1: Creating VBA Macro
- To open the VBA Editor window, go to the Developer tab and select Visual Basic.
Or, you can also press Alt+F11.
As a result, the Visual Basic Editor window will appear.
- Click Insert > Module.
- Then, we will type the following code in the Module.
Sub navigate_between_Sheets()
If ActiveSheet.Name = "VBA" Then
Sheets("Sheet9").Activate
Else
Sheets("VBA").Activate
End If
End Sub
Step 2: Add VBA to the Quick Access Toolbar
After creating the VBA macro, you can add it to the Quick Access Toolbar. This will allow it to function as a toggle button, making it easy to switch between two particular sheets with a single click.
Here are the steps to add the VBA macro as a toggle button in the Quick Access Toolbar:
- Click on the More Commands option from the Quick Access Toolbar drop-down menu.
This will open the Excel Options window. - Select the Macros option from the Popular Commands drop-down menu.
- Select the navigate_between_Sheets macro from the available options in the drop-down menu.
- Click on Add.
After that, it will be added to the Quick Access Toolbar. - Click OK.
Now, you can see the toggle icon named navigate_between_Sheets has been added to the Quick Access Toolbar.
You can click the toggle button to navigate between the two sheets, “VBA” and “Sheet9”.
Using Dedicated Navigation Button
If your workbook contains numerous worksheet tabs, you may find it difficult to swiftly navigate to either the first or final sheet within the workbook.
In previous versions of Excel, such as Excel 2010, this task was easy due to the presence of dedicated buttons for scrolling to the first or last sheet in the workbook. From Excel 2013 to the newer version, the dedicated buttons for navigating to the first or last sheet are no longer available.
If you still use Excel 2010 or lower versions, you can use these buttons to navigate to the first and last worksheet tabs in your workbook.
Download Workbook
You can download the following Excel file and practice while reading this article.
Conclusion
In this article, we have shown you ten easy methods to navigate between sheets in Excel. Thank you for reading this article. We hope it was helpful. If you have any questions, please let us know in the comment section.
Frequently Asked Questions
Can I customize the appearance of sheet tabs for better organization?
Yes, you can! Right-click on a sheet tab, select “Tab Color,” and choose from a variety of colors to enhance visual organization. This customization helps identify and locate specific sheets quickly.
What’s the significance of naming tabs in Excel?
Renaming tabs with descriptive titles improves navigation clarity. Right-click on a sheet tab, select “Rename,” and enter a meaningful name. This practice is especially helpful in complex workbooks.
How do I navigate between open workbooks using Excel’s ribbon interface?
In the “View” tab, within the “Window” group, use the “SwitchWindows” dropdown to navigate between open workbooks. This feature is useful when dealing with multiple Excel files.
How do I protect certain sheets in Excel to prevent accidental changes during navigation?
Right-click on a sheet tab, select “Protect Sheet,“ and set a password if needed. This ensures that only authorized users can make changes to protected sheets, adding a layer of security to your workbook.
Related Articles
- How to Create Navigation Buttons in Excel
- How to Perform Tab Navigation in Excel
- How to Perform Cell Navigation in Excel
- [Fixed] Excel Navigation Arrow Keys Not Working
- How to Use Navigation Keys in Excel
<< Go Back to Navigation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!