Excel is a very powerful program. If you use spreadsheets a lot, learning Excel can make a huge difference. But even if you already know the basics, there are always a few more tricks that can improve your overall app experience.
One of the helpful tricks to learn is the various techniques you can use to quickly switch between worksheets in Excel. We’ll start with simple options, like doing it manually, and then gradually move on to other alternatives for more advanced users.
Keyboard shortcuts for switching sheets in Excel
So, here is an Excel document with several sheets. When you need to switch between them, the obvious choice is to do it manually by selecting a different sheet at the bottom of the book. However, this option may not be available.
If you cannot use your mouse or touchpad, or you have a heavy workload and are not wasting your time, you can switch between sheets in Excel using a keyboard shortcut.
There are two shortcuts you need to learn here: Ctrl + PgDn (page down) to go to the sheet on the right and Ctrl + PgUp (page up) to go to the sheet on the left. Or, simply follow these steps to switch between sheets in a workbook:
- To move one sheet to the right, hold Ctrl and then press the PgDn key.
- Press the PgDn key again to continue to the right.
- To move backward or one sheet to the left, hold Ctrl, then press the PgUp key.
- To continue to the left, press the PgUp key again.
This is just one example of a handy keyboard shortcut for Excel. Learning a few more important shortcuts can help boost your productivity. For example, it will save you time when navigating Excel or performing routine tasks such as changing row height and column width in a program.
Use Go To Command
Using the Go To command is another convenient way to navigate your Excel worksheet. You will especially love this method if you have to deal with overloaded Excel documents with a lot of rows and columns on a daily basis.
To navigate to a different location in the workbook using the Go command, follow these steps:
- Open an Excel worksheet.
- Select “Modify” from the menu at the top. screen.
- Select Go To… from the drop-down menu. You will see the Go To window open. You can use this to jump from one place in a document to another.
- In the dialog box, find the reference string and enter a cell reference, such as “C22” or “H134”. Then click OK.
You will move to the location you selected by moving the active cell to that specific cell.
Enable Go To With Keyboard Shortcuts
A quicker way to use the Go command in Excel is to learn the correct keyboard shortcuts for it. There are two different options here: you can press the F5 key or use the Ctrl + G keyboard shortcut to open the Go window.
Once you start using the Go To feature, you will see that it also saves all of your previous searches in this little window. This way you can find a list of all your previous cell references in the top box above the link line. A useful feature if you find yourself jumping between specific areas of an Excel document.
However, remember that this information will disappear after you close the Excel workbook. So if you think you will need it for future use, be sure to save it elsewhere before closing the document.
Switch between sheets in Excel using Go To
If you don’t want to jump to a specific cell in another sheet, but just want to open the whole thing, you can also use the Go command to do this.
To switch between sheets in an Excel workbook, follow these steps:
- Open an Excel workbook.
- Open the Go window using one of the shortcuts or manually.
- In the Reference row, enter the desired sheet name along with the cell reference. For example, if you are on Sheet 2 and want to go to Sheet 3, you would need to enter “Sheet3 + exclamation mark + cell reference” in the Reference line, for example Sheet3! B5.
The exclamation point here serves as a separator between the sheet name and the cell number, since you cannot use spaces in cell references.
- This command will take you to another sheet and move the active cell to the selected cell. To return to the previous sheet, repeat the entire process, but change the sheet name that you enter in the Go To window.
Use the hyperlink to change worksheets
You can navigate your Excel workbook and jump from one sheet to another using hyperlinks. There are different types of hyperlinks in Excel, and you can link to an existing file on your computer, web page, or email address.
If you want to create a hyperlink and use it to navigate to another sheet, do the following:
- Select the location on the sheet (cell) where you want to insert the hyperlink.
- Right-click it and select Hyperlink from the drop-down menu.
- From the options in the center of the box, select Document.
- Go to the anchor line below, find the Find… button next to it and click it.
- In the pop-up window, select the sheet you want to link to and enter the reference to the cell where you want to move the hyperlink.
- After clicking the “OK” button, you will be taken to the previous window, where you can change the text of the hyperlink displayed in the document. Enter it in the “Display” section.
You can later remove or edit this hyperlink to change its purpose or appearance.
Use VBA script to navigate sheets
While all of the above options are great for switching between Excel sheets, you may find yourself in a situation where you need more. For example, if you work with an Excel document consisting of many sheets, and you constantly need to move between them. In this case, you can create a macro or VBA script to automate this process.
Excel allows you to automate many different functions and commands using Visual Basic for Applications (VBA) scripting. It is the programming language that Excel uses to create macros.
If you are new to this topic, check out our beginner’s guide to VBA and the advanced VBA guide for MS Excel to learn all about self-programming in Microsoft Office.
If you already know a thing or two about this, you can use macros like the ones below to navigate your Excel workbook.
VBA Macro To Move Right
Sub Change_Sheets_Right () Dim SheetNum, CurrentSheet As Integer SheetNum = Sheets.Count CurrentSheet = ActiveSheet.Index If CurrentSheet
VBA Macro to Move Right
Sub Change_Sheets_Right () Dim SheetNum , CurrentSheet as integer SheetNum = Sheets.Count CurrentSheet = ActiveSheet.Index If CurrentSheet < SheetNum then Sheets ( CurrentSheet +1). Activate Sheets (1). Select End If End Sub
VBA Macro To Move Left
Sub Change_Sheets_Left () Dim SheetNum, CurrentSheet As Integer SheetNum = Sheets.Count CurrentSheet = ActiveSheet.Index If CurrentSheet> 1 then Sheets (CurrentSheet – 1). Activate Else Sheets (SheetNum ). Activate End If End Sub