How to get the list of all sheets with hyperlinks automatically? How to create an index in Excel?

Create the list of all sheets with hyperlink blog thumbnail

Excel is a powerful tool that is used daily all around the world. According to Satya Nadella, Microsoft’s most important consumer product is Excel, which is used by an estimated 750 million people worldwide.

One of the difficulties that Excel users have is figuring out how to go to a specific sheet while working on another, or how to generate indexing automatically in Excel. Even if the indexing is created manually, it must be updated manually whenever the user adds, deletes, or modifies the sheet names.

While working on multiple sheets in Excel, I used to have problems searching for the specific sheet and even waste a lot of time because it was tough to remember which sheet comes after another.

Now, Excel users will not face this challenge in the future. We have designed an Excel Add-in that allows the user to build a list of all the Excel sheets in one sheet and automatically establishes the hyperlinks on each sheet name. If the user adds, deletes, or changes the names of the sheets, the list will update automatically after refreshing which will save a lot of time.

Anyone who uses Excel can add this Add-in to their excel files very easily and then use it to perform functions on any existing or new excel files.

How to Install the Add-In:

Follow the below steps to add the Add-In in Excel:

Step 1 – Click here to download the Excel Add-In.

Step 2 – Open an existing or new Excel file. Then click on the File Tab.

Install the Add-In Blog

Step 3 – Click on Options.

Install the Add-In step2 Blog

Step 4 – In the Excel dialog box, click on the Add-ins options in the left sidebar pane.

Step 5 – Select Excel Add-ins from Manage drop down option and then click on Go button.

Install the Add-In step 3 Blog

Step 6 – Click on Browse option in the Excel dialog box.

Install the Add-In step - 4 Blog

Step 7 – Select the Add-In file (List of all sheets and Hyperlinks) which you have downloaded in Step 1.

Install the Add-In step - 5 Blog

Step 8 – Click on “List of All Sheets And Hyperlinks” Add-In check box as per below dialog box and then click OK.

Install the Add-In step - 6 Blog

Now, the Excel Add-In is installed in all the Excel files. The user can use this Add-In in any of the Excel Files.

How to Use this Add-In:

Once the Excel Add-In is installed, open any Excel file where you want to use this Add-In and follow the below steps:

Step 1 – Press shortcut Ctrl + Shift + M from the keyboard.

Install the Add-In step - 7 Blogg

Step 2 – You will find the below dialog box.

Install the Add-In step - 8 Blog

Step 3 – According to your needs, select the options from the dropdown as shown below and then click on submit button.

You can select the order of the sheets:

  • Ascending Arrange the name of the sheets in ascending order.
  • Descending – Arrange the name of the sheets in descending order.
  • Default / Actual Order – Arrange the name of the sheets in the default or actually given order.
Install the Add-In step - 9 Blog

You can select the sheets name which you want to extract:

  • Only Hidden Sheets This will show only the name of the sheets which are hidden.
  • Only Visible Sheets This will show only the name of the sheets which are visible.
  • All Sheets This will show the list of all the sheet names (Hidden and Visible.)
Install the Add-In step - 9 Blog

When a user presses the submit button, a new sheet called Master Sheet(MData) is created with the hyperlinks automatically. The user will find S.No in Column A and a list of all the sheets available in the Excel file with hyperlinks in Column B, depending on the dropdowns selected. The user can go straight to a specific sheet by clicking on sheet name. If the user makes any changes to the sheet, such as deleting a sheet, adding a new sheet, changing the sheet’s name, etc., the user must repeat these steps to refresh the list of all sheets by pressing the shortcut Ctrl + Shift + M.