How to Create an “Alt+Tab” Style Shortcut to Switch Excel Sheets

Navigating through a massive Excel workbook with dozens of sheets can be a productivity killer. While Windows offers the incredibly useful Alt + Tab shortcut to toggle between your last two active windows, Microsoft Excel surprisingly lacks a native equivalent to toggle between your last two active sheets.

If you find yourself constantly clicking back and forth between a “Summary” dashboard and a “Data” tab located far apart, you know the frustration. The default shortcuts (Ctrl + Page Up / Ctrl + Page Down) are linear, forcing you to scroll through every single intermediate sheet.

In this guide, we will use a simple VBA (Visual Basic for Applications) solution to create a custom Alt + ` (grave accent) shortcut. This will allow you to instantly toggle between your current sheet and your previously viewed sheet, significantly speeding up your workflow.

The Problem: Why Native Shortcuts Aren’t Enough

Excel’s default navigation shortcuts are designed for sequential browsing, not toggling.

• Ctrl + Page Down: Moves one sheet to the right.
• Ctrl + Page Up: Moves one sheet to the left.

If you are working on Sheet1 and need to cross-reference data on Sheet20, you have to press the shortcut 19 times or use your mouse. This breaks your focus and slows down data entry.

By implementing the VBA solution below, we mimic the behavior of the Windows Alt+Tab function within the Excel environment. We will assign this behavior to Alt + `` (the key usually located above the Tab key), making it ergonomically perfect for power users.

Step-by-Step Guide: Creating the Macro

We will use the Personal Macro Workbook so that this shortcut is available every time you open Excel, regardless of which file you are working on.

1. Create the Personal Macro Workbook

If you have never used macros before, you might not have a PERSONAL.XLSB file generated yet. We need to create one by recording a dummy macro.

1. Open Excel and navigate to the View tab.
2. Click the arrow under Macro and select Record Macro….
3. In the dialog box, find the Store Macro In dropdown menu.
4. Select Personal Macro Workbook.
5. Click OK.
6. Click any cell in your spreadsheet (this records an action).
7. Go back to Macro and click Stop Recording.

You now have a global container for your custom code.

Menu settings for recording a macro into the Personal Macro Workbook

2. Access the Visual Basic Editor

Now that the environment is set up, we need to add the code that drives the shortcut.

Press Alt + F11 on your keyboard. This opens the Visual Basic Editor (VBE). On the left side, look for the Project Explorer pane (if you don’t see it, press Ctrl + R).

You should see a project named VBAProject (PERSONAL.XLSB). We will be working entirely within this project.

3. Add the Workbook Event Code

First, we need to tell Excel to activate our shortcut script every time the application starts.

1. Expand the VBAProject (PERSONAL.XLSB) folder.
2. Expand the Microsoft Excel Objects folder.
3. Double-click on ThisWorkbook.
4. Paste the following code into the main code window on the right:

Private Sub Workbook_Open()
    'Initialize the TabBack macro when Excel starts
    Call TabBack_Run
End Sub

This code ensures that as soon as you open Excel, the macro is ready to listen for your keystrokes.

VBA editor window showing the Workbook_Open code pasted into ThisWorkbook

4. Create the Standard Module

Next, we create the module that defines the shortcut key and the logic for switching sheets.

1. Right-click on the Modules folder inside your Personal project (if it doesn’t exist, right-click the project > Insert > Module).
2. Double-click Module1 (or the newly created module).
3. Press F4 to open the Properties window and rename the module to TabBack.
4. Delete any existing code and paste the following:

Dim TabTracker As New TabBack_Class

Sub TabBack_Run()
    'Initialize the tracker and assign the shortcut key
    Set TabTracker.AppEvent = Application
    Application.OnKey "%`", "ToggleBack"
End Sub

Sub ToggleBack()
    'Switch to the previously stored sheet
    With TabTracker
        On Error Resume Next
        Workbooks(.WorkbookReference).Worksheets(.SheetReference).Activate
        On Error GoTo 0
    End With
End Sub

Technical Note: In the code Application.OnKey "%“, the percent sign%represents the **Alt** key, and the backtick `` “ is the key we are binding to.

VBA editor showing the standard module code for key binding

5. Create the Class Module (The Event Listener)

This is the most critical part. Excel doesn’t natively “remember” your history. We need a Class Module to “listen” to Excel events and record the name of the sheet you are leaving before you leave it.

1. Right-click on the Modules folder (or the Project name).
2. Select Insert > Class Module.
3. Press F4 to name this Class Module TabBack_Class (The name must match the reference in the previous step exactly).
4. Paste the following code:

Public WithEvents AppEvent As Application
Public SheetReference As String
Public WorkbookReference As String

Private Sub AppEvent_SheetDeactivate(ByVal Sh As Object)
    'Store the sheet details immediately before leaving it
    WorkbookReference = Sh.Parent.Name
    SheetReference = Sh.Name
End Sub

Private Sub AppEvent_WorkbookDeactivate(ByVal Wb As Workbook)
    'Store details when switching between different workbooks
    WorkbookReference = Wb.Name
    SheetReference = Wb.ActiveSheet.Name
End Sub

VBA editor showing the Class Module code for tracking sheet history

6. Save and Restart

Once all code blocks are pasted:

1. Click the Save icon (diskette) in the Visual Basic Editor.
2. Close the Editor.
3. Restart Excel completely.

When you reopen Excel, the Workbook_Open event will fire, setting up your shortcut. Open a workbook, click on Sheet1, then click on Sheet5. Now, press Alt + `. You should instantly jump back to Sheet1. Press it again to return to Sheet5.

Animation showing the VBA coding environment in action

How It Works: The Technical Logic

For those interested in the “why” behind the code, here is a breakdown of the mechanics:

• Event Trapping: Standard macros run only when clicked. However, by declaring Public WithEvents AppEvent As Application in a Class Module, we create a script that runs in the background and monitors Excel’s behavior.
• The Deactivate Event: We specifically monitor SheetDeactivate. The moment you click away from a sheet, this event triggers before the new sheet loads, allowing the code to save the name of the sheet you are leaving into a variable (SheetReference).
• The Shortcut: When you press Alt + `, the macro reads that stored variable and activates the specific workbook and sheet stored in memory.

Conclusion

By adding this simple VBA script to your Personal Macro Workbook, you have essentially added a missing “Pro” feature to Microsoft Excel. The ability to toggle between two specific sheets using Alt + ` mimics the muscle memory of Alt + Tab, creating a seamless experience for heavy multitasking.

This trick not only saves time but also reduces the cognitive load of visually hunting for sheet tabs. It is a perfect example of how a little technical knowledge can significantly optimize your daily digital environment.

References

• Microsoft Learn: Application.OnKey Method (Excel)
• Microsoft Learn: Using Events with the Application Object
• TechRepublic: customizing Excel shortcuts via VBA