Microsoft Excel: Create an automated list of worksheet names

Q. I have an Excel workbook with dozens of custom-named worksheets. Is it possible to create a list of those worksheet names in Excel without having to retype them?

A. Yes, you can create a list of your Excel workbook’s worksheet names as follows. From the Formulas tab, select Defined Names, Define Name to launch the New Name dialog box pictured below.

Enter SheetNames into the Name field, enter the following formula into the Refers to field: =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””), and then select OK. This action will create a named formula that can then be used in conjunction with the INDEX function to produce a list of worksheet names. Continuing, let us assume you have 25 named worksheets. Next, select a cell where you want your list of names to appear (cell B2 in this example), and enter the numbers 1 through 25 (in cells A2 through A26 in this example, as pictured below). Then in cell B2 enter the formula =INDEX(SheetNames,A2), and then copy and paste the formula down 25 rows. These formulas will return a list of the names of your worksheet tabs in the same order as your worksheet tabs.

(Note: The workbook must be saved as an Excel Macro-Enabled Workbook file type in order to retain the Defined Name formula.)

Now that you have successfully added a table of contents to your workbook, I recommend you add hyperlinks as well, to aid navigation. This can be done by adding the formula =HYPERLINK(“#'”&B2&”‘!A1″,”Go To Sheet”) in cell C2 (in the example below) and copying this formula down next to each of the worksheet names.

As a result, you will then be able to click each link to jump to the various worksheets listed in your table of contents. (As an alternative, you could instead select each name in the table of contents one at a time and press Ctrl+K to apply a hyperlink directly to each worksheet name. The advantage of this alternative approach is that the separate column of hyperlinks (such as column C in the example above) is not necessary, but the disadvantage is that each hyperlink must be applied one at a time.)

Further, I recommend you insert a button on each worksheet that will return you to your table of contents, as follows:

1. Name your table of contents. Start by placing your cursor at the top of the table of contents and in the Name Box (located directly above column A), type TOC and press the Enter key. This named location will be the cell where the navigational button created in the steps below will lead.
2. Create a hyperlinked text box button. Anywhere in the workbook, insert a Text Box (from the Insert tab’s Shapes menu), label it Return to the Table of Contents, and format the button to your desire. (In the example pictured below, I made the Text Box red, centered and bolded the text, applied a button effect, and applied a bottom right shadow.)
3. Apply a hyperlink to your text box button. Right-click the text box to select it, press Ctrl+K to launch the Insert Hyperlink dialog box, select Place in This Document (under the Link to menu), scroll down to Defined Names, select TOC, and press OK. These actions will create a clickable button that will return you to your table of contents.
4. Copy and paste the hyperlinked text box button throughout your worksheet. Right-click the text box button again to select it, left-click on the very edge of the text box button to ensure you have selected the entire text box button and not just the text within the text box, press Ctrl+C to copy the text box button, then navigate to each worksheet and paste the text box button on each of your worksheets to make navigating your large workbook easier. (You might want to paste several copies of this text box button on your larger worksheets for added navigation convenience.)

(Hint: To select a hyperlink-enabled text box without triggering the hyperlink effect, right-click the text box button and then left-click the very edge of the text box button — this action will allow you to resize or reposition the button as desired.) You can download an example Excel workbook containing a table of contents and navigational hyperlinks at carltoncollins.com/list.xlsm.

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a conference presenter, and a JofA contributing editor.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.