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

Get FREE Advanced Excel Exercises with Solutions!

The default setting in Excel positions the sheet tabs at the bottom of the workbook window. However, users may find it more convenient or visually appealing to have the tabs on top of the worksheet, resembling a setup similar to popular web browsers. This arrangement allows for easier navigation between sheets, especially when working with a large number of worksheets within a workbook.

In this Excel tutorial, you will learn how to put tabs on top of a worksheet using VBA code and adding hyperlinks.

In the GIF below, you can see how to access worksheets from the Quick Access Toolbar (QAT) which are added in QAT using VBA codes.

Put Excel tabs on top of worksheet


Here are 2 methods to put the Excel tabs on top of worksheets:

Using VBA to Put Excel Tabs on Top of Worksheet

If you want to automate the process of placing tabs on top of a worksheet in Excel, you can use VBA code. This code creates a list of worksheet tabs on the Quick Access Toolbar (QAT), which is located at the top of your worksheet. By accessing the tabs from the QAT, you can quickly navigate between them.

To place Excel tabs on top of the worksheet using VBA code, follow the steps below:

  1. Go to the Developer tab > Code group >  Visual Basic. Or, press ALT + F11.
  2. In the Visual Basic window, select ThisWorkbook from VBAProject.Using VBA to Put Excel Tabs on Top of Worksheet
  3. Write the following code in the ThisWorkbook module.
    Sub WorksheetTabs()
      Application.CommandBars("Workbook tabs").ShowPopup
    End Sub 
  4. Save the code and go back to your worksheet.Using VBA to Put Excel Tabs on Top of Worksheet
  5. Select the drop-down icon for the Quick Access Toolbar.
  6. Select More Commands.Using VBA to Put Excel Tabs on Top of Worksheet
  7. In the Excel Options dialog box, click on the drop-down option of “Choose commands from”.Using VBA to Put Excel Tabs on Top of Worksheet
  8. Select Macros from the options.
  9. 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. Here, I 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.


Applying HYPERLINK Function to Put Excel Tabs on Top of 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:

  1. 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
  2. Write 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”.
  3. Press ENTER to get the result.

Applying HYPERLINK Function to Put Excel Tabs on Top of Worksheet

Similarly, you can add hyperlinks to other worksheets. Such as in cell B1, insert the following formula: =HYPERLINK("#'Sales Overview for Texas'!A1","Sales Overview for Texas")

Thus you can see the “Sales Overview for Texas” tab is hyperlinked in cell B1.

Following this method, 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.

Now, you can go to any worksheet from this worksheet directly by using these links. Just select the worksheet you want to go to. Here, I selected the sheet named Sales Overview for Missouri.

Applying HYPERLINK Function to Put Excel Tabs on Top of Worksheet

You can see that 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 Practice Workbook


Conclusion

In this Excel tutorial, you have learned two easy methods to put Excel tabs on top of a worksheet. One method uses VBA to add the sheets in the Quick Access Toolbar for better navigation to tabs. The other method uses the hyperlinking method. This method makes the tabs appear on the top row of your sheet for better visualization. If you have any questions, please let me know in the comment section.


Frequently Asked Questions

Why is Excel not showing tabs?

The “Show sheet tabs” feature might be disabled. To enable it, follow these steps:

  1. Click on File > Options > Advanced.
  2. 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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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