Here’s a GIF overview of how to access worksheets from the Quick Access Toolbar (QAT) which are added in QAT using VBA code.
Method 1 – Using VBA to Put Excel Tab Navigation in the Quick Access Toolbar
- Go to the Developer tab and select Visual Basic or press Alt + F11.
- In the Visual Basic window, select ThisWorkbook from VBAProject.
- Insert the following code in the ThisWorkbook module.
Sub WorksheetTabs() Application.CommandBars("Workbook tabs").ShowPopup End Sub
- Save the code and go back to your worksheet.
- Select the drop-down icon for the Quick Access Toolbar.
- Select More Commands.
- In the Excel Options dialog box, click on the drop-down “Choose commands from”.
- Select Macros from the options.
- Select the macro, click on Add, and press OK.
In the following GIF, you can see the list of Excel tabs on top of the worksheet. You can go to any worksheet directly by using this list. To do that, just select the worksheet you want to go. We selected the sheet named Sales Overview for Virginia. You can see that the sheet named Sales Overview for Virginia is open.
Method 2 – Applying the HYPERLINK Function to Put Excel Tab Navigation on the Top of the Worksheet
In this method, you will learn to add a hyperlink using the HYPERLINK function to put Excel tabs on top of the worksheet. The HYPERLINK function brings out a cutoff link that will open on a server, or make a move to other worksheets. Excel opens the URL when we click the cell that holds the HYPERLINK function.
Follow the steps below to put Excel tabs on top of the worksheet using the HYPERLINK function:
- Create and name a new worksheet to put all the worksheet tabs. Here, we created a worksheet named Worksheet Tabs.
- Use the following formula in cell A1:
=HYPERLINK("#'Dataset'!A1","Dataset")
Here, in the HYPERLINK function, I selected “#’Dataset’!A1” as “link_location”, and “Dataset” as “friendly_name” argument. The function will return a link that will directly lead to the selected location, which is cell A1 of the sheet named “Dataset”.
- Press Enter to get the result.
- You can add hyperlinks to other worksheets. In cell B1, insert the following formula:
=HYPERLINK("#'Sales Overview for Texas'!A1","Sales Overview for Texas")
- We have inserted all the other worksheet links. You can see the Excel tabs on the top row of the worksheet. To make these links more eye-catching, format the cells according to your choice. We formatted them with fill color and bold font.
- Go to any worksheet from this worksheet directly by using these links. Select the worksheet you want to go to. We selected the sheet named Sales Overview for Missouri.
The sheet named Sales Overview for Missouri is open.
Read More: How to Group Tabs Under a Master Tab in Excel
Download the Practice Workbook
Frequently Asked Questions
Why is Excel not showing tabs?
The “Show sheet tabs” feature might be disabled. To enable it, follow these steps:
- Click on File > Options > Advanced.
- Under “Display options for this workbook“, make sure that the “Show sheet tabs” box is checked.
Can I change the order of sheet tabs in Excel?
Yes. The easiest way to change the order of sheet tabs is to select and drag them to the location you want to place them.
Is it possible to group or color-code Excel sheet tabs?
Yes. To group or color-code Excel sheet tabs, right-click on the tab, select Tab Color from the context menu, and choose a color.
Related Articles
- How to Create Tabs Automatically in Excel
- How to Create Tabs Within Tabs in Excel
- How to Change Worksheet Tab Color in Excel
- [Fixed!] Excel Sheet Tabs Hidden behind Taskbar
<< Go Back to Sheets Tab in Excel | Excel Parts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!