Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

If you want to put worksheet tabs in Excel on top, then this article will be beneficial for you. The main focus of this article is to explain how to put Excel tabs on top of worksheet.


Download Practice Workbook


2 Easy Ways to Put Excel Tabs on Top of Worksheet

Here, I have taken an Excel workbook that contains 5 worksheets. You can see that the worksheet tabs are located at the bottom. You can hide them if you want but can not move them. But you can show them as a list and put them on the top of your Excel worksheet. Now, I will explain how to put Excel tabs on top of worksheet in 2 easy steps.

2 Easy Ways to Put Excel Tabs on Top of Worksheet


1. Using VBA to Put Excel Tabs on Top of Worksheet

In this method, I will use VBA to put Excel tabs on top of the worksheet. Let’s see the steps.


Step-01: Using Macros to Put Excel Tabs on Top of Worksheet

In this first step, I will show you how to create a Macro to put Excel tabs on top of worksheet.

  • Firstly, go to the Developer tab.
  • Secondly, select Visual Basic.

Or, you can use the Keyboard Shortcut ALT+F11 to open the Visual Basic window.

Now, you will see the Visual Basic window has opened.

  • After that, select ThisWorkbook from VBAProject.

Using VBA to Put Excel Tabs on Top of Worksheet

Here, you will see the ThisWorkbook module will open.

  • Now, write the following code in the module.
Sub WorksheetTabs()
  Application.CommandBars("Workbook tabs").ShowPopup
End Sub 

Code Breakdown

  • Here, I created a Sub Procedure named WorksheetTabs.
  • Then, I used the Application property to return the Application object. Application.CommandBars will return a CommandBars object which represents Excel command bars.
  • Finally, the Application.CommandBars(“Workbook tabs”).ShowPopup displays a commander as a shortcut menu which will contain the worksheet tabs of that workbook.
  • After that, I ended the Sub Procedure.
  • Finally, save the code and go back to your worksheet.

Using VBA to Put Excel Tabs on Top of Worksheet


Step-02: Adding Macros to Quick Access Toolbar

In this step, I will explain how you can add the Macro you created in the Quick Access Toolbar.

  • Firstly, select the drop-down option for Quick Access Toolbar.
  • Secondly, select More Commands.

Using VBA to Put Excel Tabs on Top of Worksheet

Now, a dialog box named Excel Options will appear.

  • Firstly, click on the marked drop-down option to Choose commands.

Using VBA to Put Excel Tabs on Top of Worksheet

Here, you will see a drop-down menu will appear.

  • Secondly, select Macros.

  • After that, select the Macro you created.
  • Next, select Add.

Using VBA to Put Excel Tabs on Top of Worksheet

Now, you will see that the Macro is added to your Quick Access Toolbar.

  • Finally, select OK.

Here, you can see the Macro in the Quick Access Toolbar in the following image.

  • Now, click on the Macro and you will see the list of Excel tabs on top of worksheet.

Using VBA to Put Excel Tabs on Top of Worksheet

You can go to any worksheet from this workbook directly by using this list.

  • To do that, just select the worksheet you want to go to. Here, I selected the sheet named Sales Overview for Virginia.

Here, you can see that the sheet named Sales Overview for Virginia is opened.

Using VBA to Put Excel Tabs on Top of Worksheet

Read More: How to Unhide Sheet Tab in Excel (7 Effective Ways)


2. Applying HYPERLINK Function to Put Excel Tabs on Top of Worksheet

In this method, I will explain how you can put Excel tabs on top of worksheet using the HYPERLINK function. Here, I will show them on the top row.

Let’s see the steps.

Steps:

  • Firstly, create a new worksheet where you will put all the worksheet tabs and then name it as you want. Here, I created a worksheet named Worksheet Tabs.

Applying HYPERLINK Function to Put Excel Tabs on Top of Worksheet

  • Secondly, select the first cell in the worksheet which is A1.
  • Thirdly, in cell A1 write the following formula.
=HYPERLINK("#'Dataset'!A1","Dataset")

Here, in the HYPERLINK function, I selected “#’Dataset’!A1” as link_location, and “Dataset” as friendly_name. The function will return a link that will directly lead to the selected location which is cell A1 of the sheet named Dataset.

  • Finally, press ENTER to get the result.

Applying HYPERLINK Function to Put Excel Tabs on Top of Worksheet

  • Now, select the next cell of row 1 which is B1.
  • Next, in cell B1 write the following formula.
=HYPERLINK("#'Sales Overview for Texas'!A1","Sales Overview for Texas")

Applying HYPERLINK Function to Put Excel Tabs on Top of Worksheet

Now, in the HYPERLINK function, I selected “#’Sales Overview for Texas’!A1” as link_location, and “Sales Overview for Texas” as friendly_name. The function will return a link that will directly lead to the selected location which is cell A1 of the sheet named Sales Overview for Texas.

  • Finally, press ENTER to get the link.

Now, I have inserted all the other worksheet links in the same way. You can see the Excel tabs are showing on the top row of the worksheet.

Applying HYPERLINK Function to Put Excel Tabs on Top of Worksheet

  • After that, format the cells as you want to get a clear view. In the following image, you can see that I have formatted mine.

  • 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

Here, you can see that the sheet named Sales Overview for Missouri is opened.

Applying HYPERLINK Function to Put Excel Tabs on Top of Worksheet


How to Hide Excel Worksheet Tabs

In the following picture, you can see the worksheet tabs are showing at the bottom of your Excel. Here, I will show you how to hide Excel worksheet tabs.

Let’s see the steps.

Steps:

  •  Firstly, go to the File tab.

  • Secondly, select Options.

How to Hide Excel Worksheet Tabs

Here, a dialog box named Excel Options will appear.

  • Firstly, select Advanced.
  • Secondly, uncheck the Show sheet tabs from the Display options for this workbook.
  • Thirdly, select OK.

How to Hide Excel Worksheet Tabs

Now, in the following image, you can see that the worksheet tabs are hidden from your Excel workbook.

Read More: How to Unhide Very Hidden Sheets in Excel (2 Effective Methods)


Things to Remember

  • It should be noted that whenever working with Macros in Excel you must save the workbook as Excel Macro-Enabled Workbook.


Conclusion

In this article, I tried to cover how to put Excel tabs on top of worksheet with 2 easy steps. I hope this was clear to you. If you like this article, then you can visit ExcelDemy to get more articles like this. Lastly, if you have any questions please, let me know in the comment section below.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo