How to get the list of all sheets with hyperlinks automatically? How to create an index in Excel?
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.
Step 3 – Click on Options.
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.
Step 6 – Click on Browse option in the Excel dialog box.
Step 7 – Select the Add-In file (List of all sheets and Hyperlinks) which you have downloaded in Step 1.
Step 8 – Click on “List of All Sheets And Hyperlinks” Add-In check box as per below dialog box and then click OK.
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.
Step 2 – You will find the below dialog box.
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.
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.)
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.