This can be pretty handy if you have a large workbook with hundreds of sheets and you want to create a table of contents. This method uses the little known and often forgotten Excel 4 macro functions. These functions aren’t like Excel’s other functions such as SUM, VLOOKUP, INDEX etc. These functions won’t work in a regular sheet, they only work in named functions and macro sheets. For this trick we’re going to use one of these in a named function.
In this example, I’ve created a workbook with a lot of sheets. There are 50 sheets in this example so I was lazy and didn’t rename them from the default names.
Now we will create our named function.
In a sheet within the workbook enter the numbers 1,2,3,etc… into column A starting at row 2 and then in cell B2 enter the following formula and copy and paste it down the column until you have a list of all your sheet names.
As a bonus, we can also create a hyperlink so that if you click on the link it will take you to that sheet. This can be handy for navigating through a spreadsheet with lots of sheets. To do this add this formula into the column C. Note, to use this method you will need to save the file as a macro enabled workbook (.xls, .xlsm or .xlsb). Not too difficult and no VBA needed.
Video Tutorial
This video will show you two methods to list all the sheet names in a workbook.
The first method uses a VBA procedure from this post.The second (skip to 3:15 in the video) uses the method in the above post.
