Quickest ways to switch between excel sheets or tabs

Excel is an extremely strong tool. If you frequently use spreadsheets, learning how to use Excel can be beneficial. Even if you already know the essentials, there are always a few more tips and techniques to help you get the most out of the software.

The various strategies for fast switching between sheets in Excel are some of the useful tricks to learn. We'll start with the most basic solutions, such as manually, then work our way up to more complicated options on how to switch between excel sheets.

Excel Keyboard Shortcuts to Switch Between Sheets

So you've got a multi-worksheet Excel document in front of you. Switching between worksheets in an Excel workbook is simple enough, simply clicking on the tabs at the bottom of the worksheets, but it is a slow method.

You can switch between sheets in Excel using a keyboard shortcut if you can't use your mouse or touchpad or have an enormous task and don't want to waste time on it.

You'll need to master two shortcuts: Ctrl + PgDn (page down) for navigating to the right-hand sheet and Ctrl + PgUp (page up) for moving to the left-hand sheet. To transition between sheets in the workbook, follow these steps:

1. Hold down Ctrl and then press the PgDn key to move one sheet to the right.

2. Press the PgDn key once more to continue moving to the right.

3. Hold down Ctrl and then press the PgUp key to go back or one sheet to the left.

4. Press the PgUp key once again to continue moving to the left.

This is just a single example of an Excel keyboard shortcut. Learning a few additional useful shortcuts will help you work more efficiently. It will save you time when navigating Excel.

Use the command "Go To."

Even though "Go To" option is not specifically designed to switch between worksheets, you can use that method also to switch between sheets. Let's first understand how to move around your Excel worksheet using the Go To command. This method can be used to move quickly between cells in a worksheet. Although Go To isn't especially helpful for worksheets with only a few columns and rows, you'll appreciate it if you have to deal with overloaded Excel spreadsheets with many rows and columns regularly.

Follow the steps given to use the Go To command to move to a different part of your workbook:

• Open your Microsoft Excel spreadsheet.

• At the top of Excel, click the Home tab. If you don't see it, go to the Edit menu and look for it.

• From the Editing group, select Find & Select. Choose Find if you used the Edit menu.

• Select the choice to Go To...

• The Go To window will appear to be open. This will allow you to jump from one section of your document to another.

• Find the Reference line in the dialog window and type in your cell reference, such as "D26" or "K146." After that, press OK or the Enter key on your keyboard.

• As a result, the active cell highlight switches to the cell reference specified in the dialog box.

Use Keyboard Shortcuts to Go To Command

Learning the appropriate keyboard shortcuts for the Go To command in Excel will quickly help you use it. You can either click the F5 key or use the Ctrl + G keyboard shortcut to bring up the Go To window.

When you use the Go To option, you'll notice that it also keeps a record of all of your previous searches in that small box. In the top box above the Reference line, you'll see a list of all your past cell references. If you frequently switch back and forth between sections of your Excel document, this feature will come in handy.

However, keep in mind that if you close your Excel spreadsheet, this information will vanish. So, if you believe you'll need it in the future, save it somewhere else before closing the document.

Use 'Go To' to Switch Between Worksheets Now let's see how to use the "Go To" command to switch between different sheets in excel.

By entering the sheet name and the cell reference, Go To can also navigate different worksheets within the same workbook.

Follow these procedures to move between sheets in your Excel workbook:

• Open your Excel spreadsheet.

• Use one of the shortcuts or manually open the Go To window.

• Type the desired sheet name and the cell reference in the Reference line. For example, if you're on Sheet 1 and wish to go to Sheet 4, put Sheet4 + exclamation mark + cell reference in the Reference line, such as Sheet4!B5.

• Because you can't use spaces in cell references, the exclamation mark acts as a divider between the sheet name and the cell number.

• That command will change the active cell box to the cell you specify on a different sheet. Repeat the steps above but change the sheet name you type in the Go To window to return to the previous sheet.

Use Hyperlinks to Change Worksheets

You can use hyperlinks to move around your Excel workbook and jump from one sheet to the next. You can link to an existing file on your computer, a web page, or an e-mail address with several hyperlink kinds in Excel.

To build a hyperlink and use it to navigate to a separate worksheet, follow these steps:

• Choose a location on your sheet (a cell) where you wish the hyperlink to appear.

• Choose Link from the drop-down menu when you right-click on it.

• Choose Place in This Document from the options on the left side of the box.

• Choose the sheet you want to connect to and type the cell reference where you want the hyperlink to lead you.

• After pressing OK, you'll be directed back to the previous window, where you can edit the wording of the document's hyperlink.

• Once you click this, you’ll be taken to the related cell of the corresponding worksheet.

• You can modify the destination or appearance of this hyperlink later by deleting or editing it.

Use a VBA Script to Move Between Sheets

So all of the above choices are excellent for moving between Excel sheets; you may discover that you require more. For example, if you're working with a large Excel document with several spreadsheets and need to switch between them frequently. In that situation, you can automate the procedure using a VBA macro or script.

Using Visual Basic for Applications (VBA) scripting, you may automate many different functions and operations in Excel. Excel macros are written in this programming language.

To begin, you must understand everything about VBA programming in Microsoft Office. If you're already familiar with it, you can use macros like those below to move around your workbook.

Move To The Right Using VBA Macro

Sub Change_Sheets_Right()

Dim SheetNum, CurrentSheet As Integer
SheetNum = Sheets.Count
CurrentSheet = ActiveSheet.Index

If CurrentSheet < SheetNum Then	
Sheets(CurrentSheet + 1).Activate
Else
Sheets(1).Select
End If

End Sub

Move To The Left Using VBA Macro

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

Instructions for Using VBA to Move Worksheets

The Developer tab on the Ribbon menu must be enabled before creating macros or VBA scripts in Excel. By default, the Developer tab is disabled. To enable it, follow these steps:

• Open an Excel spreadsheet.

• Select File > Options > Customize Ribbon from the File menu.

• Mark the box near the Developer with a tick and click OK.

Then to use the above mentioned VBA macros or programs, please follow the step-by-step instructions below:

• Open a spreadsheet in Excel.

• Go to the Developer Tab and open VBA Editor.

• From the Insert menu, select Module.

• Copy and paste the above code into the code window to activate the worksheet (VBA Editor) and click the Save button or press ctrl+s.

• Then close the window that popups.

• After that, the Save window will be opened. Save the file as Excel Macro-enabled Workbook(.xlsm)

Install both codes in your workbook to be able to move one sheet at a time in both directions, right and left.

• Click Run and check how the Worksheet is Moving.