Have you ever opened an Excel workbook and been surprised by the number of sheets it contains?   Perhaps you are looking for something, or don’t know where to begin?   This article shares a great trick in Excel if you need to see at-a-glance what all the tabs are in a workbook.  You can build your own built-in “Table of Contents” and optionally add helpful information as well.

In Operations or in Financial Planning & Analysis (FP&A) too often we can encounter large overwhelming Excel workbooks (despite those New Year’s Resolutions to put Excel on a diet!).  Examples abound, such as in gathering business requirements for a project, building new templates that replace old Excel reports, or trying to trace formulas that involve hidden sheets.  All those tabs can be daunting to navigate even for users who work with them on a regular basis.  This technique has so many benefits, especially if you have newly hired team-members or for anyone who is new to the data and how it is organized.

Full Disclosure: this trick uses an old “Excel 4 macro” function which still happens to work in Excel versions through Excel 365.  The function is a quicker option than coding a macro using VBA.  Microsoft may stop supporting this (and other Excel 4 functions) in the future.

Not only is this tip quick to implement in any workbook, but it is also non-invasive.  It won’t impact existing data.  There is no need to touch or unhide/hide any of the other tabs when creating this instant list.  Once you have generated the initial list of tabs, the results stay put whether or not the macro is saved into the workbook (as a *.xlsm).   It is easy to take that initial output to make a more useful and formatted “Table of Contents.”  The Example below is shown from Excel 365, but the steps should be similar in recent individual-license versions.

Steps:

  1. Open the large workbook & insert a new worksheet in the workbook (preferably at the beginning – but it can be anywhere) If there happen to be many hidden sheets, you don’t need to bother “finding” the beginning sheet.
  2. Label the new sheet tab as “TOC” or whatever you prefer

  1. Navigate in the Excel top menu to the Formulas ribbon

  1. Select the Name Manager feature

5. Click on the New button and type in a name (this example uses “LISTER”); leave the Scope as Workbook; the Comment is Optional input; and in the Refers To field, type in this formula =TRANSPOSE(REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””))

  1. Close the Name Manager box and go back in the newly added worksheet. Choose a cell (“A1” here in the example in sheet “TOC”) and type in the following formula    =LISTER
    (or = whatever Name you used)

7. Once you hit Enter, that formula will populate cells vertically, with the set of cells showing in order the names of all the tabs of the workbook, first (top) to last (bottom). If there are hidden tabs that occur before the new TOC tab, the TOC name will be listed after them.

8. An option at this point is for you to add really useful information to your Table of Contents such as referring to each listed sheet name to make a hyperlink within the workbook and adding comments or color-coding to help explain what sheets do.

9. Notes on saving the workbook:

a. When you save the workbook with your Table of Contents, if you want the named function to stay “live”, remember to Save-As a macro-enabled workbook.

This ensures a future benefit where after any other new tabs are added to the workbook or tab names change or old tabs removed, the horizontal list can be refreshed directly at the cell with the formula (such as A1 with “=LISTER”) by recalculating that cell.

As a saving reminder, Excel shows a message similar to this

b. Otherwise, if you must keep the workbook as a *.xlsx file, then the best approach is to copy the formula calculated list of tabs as a Copy-Paste Special-Values. This way the results will stay in place as a static list.  In an *.xlsx, the Name Manager will no longer show the function, and the cell having the function formula will not be able to calculate/will show an error.  Clear out the stale formula to remove the error.

  1. For those of you out there who are curious about coding VBA as a macro to list out tabs, the Microsoft.com 365 Blog includes an easy to follow How-To post.

Whichever path you take, building a Table of Contents should help you gain some X-Ray vision into those massive workbooks that we know are out there!