How to Put Excel Tabs on Top of Worksheet (2 Easy Ways)

Here’s a GIF overview of how to access worksheets from the Quick Access Toolbar (QAT) which are added in QAT using VBA code.

Put Excel tabs on top of worksheet


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.

Using VBA to Put Excel Tabs on Top of Worksheet

  • 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.

Using VBA to Put Excel Tabs on Top of Worksheet

  • Select the drop-down icon for the Quick Access Toolbar.
  • Select More Commands.

Using VBA to Put Excel Tabs on Top of Worksheet

  • In the Excel Options dialog box, click on the drop-down “Choose commands from”.

Using VBA to Put Excel Tabs on Top of Worksheet

  • Select Macros from the options.

  • Select the macro, click on Add, and press OK.

Using VBA to Put Excel Tabs on Top of Worksheet

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.

Put Excel tabs on top of worksheet

Note:
When working with macros in Excel, you must save the workbook as an Excel Macro-Enabled Workbook.


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.

Applying HYPERLINK Function to Put Excel Tabs on Top of Worksheet

  • 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.

Applying HYPERLINK Function to Put Excel Tabs on Top of Worksheet

  • 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.

Applying HYPERLINK Function to Put Excel Tabs on Top of Worksheet

The sheet named Sales Overview for Missouri is open.

Applying HYPERLINK Function to Put Excel Tabs on Top of Worksheet

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


<< Go Back to Sheets Tab in Excel | Excel Parts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo