If you have a workbook with multiple worksheets, you may want to create a list of all the sheet names in a single cell or a range of cells. This can be useful for navigation, documentation, or reference purposes. However, Excel does not have a built-in function that can do this directly. In this article, we will show you how to use a combination of formulas and a named range to achieve this task.
The basic idea is to use a macro command called GET.WORKBOOK to retrieve an array of sheet names in the current workbook. This command is not a regular Excel function, so you need to save your workbook as a macro-enabled workbook (with .xlsm extension) for it to work. The GET.WORKBOOK command returns an array like this:
{"[workbook.xlsm]Sheet1","[workbook.xlsm]Sheet2","[workbook.xlsm]Sheet3"}
Notice that each sheet name is enclosed in double quotes and prefixed with the workbook name and a bracket. We need to remove these extra characters and extract only the sheet names. To do this, we can use the MID and FIND functions to get the text after the closing bracket in each element of the array. For example, the formula =MID("[workbook.xlsm]Sheet1",FIND("]", "[workbook.xlsm]Sheet1")+1,255) returns "Sheet1".
Next, we need to create a named range that contains the GET.WORKBOOK command and a trick to force it to update whenever the sheet names change. We can do this by concatenating the command with the T and NOW functions, like this:
=GET.WORKBOOK(1)&T(NOW())
The T function returns an empty string for numeric values, so it does not affect the output of the GET.WORKBOOK command. The NOW function returns the current date and time, and it is a volatile function that recalculates with every worksheet change. This way, the named range will refresh whenever you add, delete, or rename a sheet.
Finally, we need to use the INDEX and ROWS functions to display the sheet names in a range of cells. The INDEX function returns the value at a given position in an array, and the ROWS function returns the number of rows in a range. By using an expanding range as the row number argument of the INDEX function, we can create a dynamic formula that returns the next sheet name in each row. For example, the formula =INDEX(MID(sheetnames,FIND("]",sheetnames)+1,255),ROWS($B$5:B5)) returns the first sheet name in cell B5, the second sheet name in cell B6, and so on.
Procedures
To create a list of sheet names in Excel with a formula, follow these steps:
- Save your workbook as a macro-enabled workbook (with .xlsm extension).
- Go to the Formulas tab and click Name Manager.
- Click New and type a name for the named range, such as “sheetnames”.
- In the Refers to box, enter the formula
=GET.WORKBOOK(1)&T(NOW()). - Click OK and close the Name Manager.
- In a cell or a range of cells where you want to display the sheet names, enter the formula
=INDEX(MID(sheetnames,FIND("]",sheetnames)+1,255),ROWS($B$5:B5)), where B5 is the first cell of the output range. Adjust the cell references as needed. - Copy the formula down to fill the rest of the output range.
Example
Suppose you have a workbook with four worksheets named “Overview”, “Sales”, “Expenses”, and “Summary”. You want to create a list of these sheet names in column A, starting from cell A2. Here is how you can do it with the formula method:
- Save your workbook as a macro-enabled workbook (with .xlsm extension).
- Go to the Formulas tab and click Name Manager.
- Click New and type “sheetnames” as the name for the named range.
- In the Refers to box, enter the formula
=GET.WORKBOOK(1)&T(NOW()). - Click OK and close the Name Manager.
- In cell A2, enter the formula
=INDEX(MID(sheetnames,FIND("]",sheetnames)+1,255),ROWS($A$2:A2)). - Copy the formula down to cell A5.
The result is a list of sheet names in column A, as shown below:
| A |
|---|
| Sheet Names |
| Overview |
| Sales |
| Expenses |
| Summary |
Other Approaches
There are some other ways to list sheet names in Excel, such as using VBA macros, Power Query, or third-party add-ins. However, these methods may require more technical skills, additional steps, or external tools. The formula method is relatively simple, flexible, and compatible with most versions of Excel. However, it also has some limitations, such as:
- It only works with macro-enabled workbooks, which may not be allowed or supported by some systems or organizations.
- It may not update automatically if you change the sheet names outside of Excel, such as by using Windows Explorer or another program.
- It may not work well with sheet names that contain special characters, such as brackets, quotes, or commas.
- It may return an error if there are more sheet names than the output range can accommodate.
Therefore, you should choose the method that best suits your needs and preferences. You can also combine different methods to achieve the desired result. For example, you can use a VBA macro to create a named range with the sheet names, and then use the INDEX function to display them in a range of cells. Alternatively, you can use Power Query to import the sheet names from the workbook properties, and then load them to a table in a worksheet.
