Trick for Listing the names of all sheets in Excel workbook

List of Sheet Names output

Here is a 2 step technique to retrieve the names of all sheets in Excel workbook:

Step:1. First define a named range using the below formula:

Step: 1a. Click “Define Name” command in “Define Names” group under “Formulas” ribbon tab to invoke “New Name” dialogue

Step: 1b. Let the name of the range be “SheetNames”

Step: 1c. Let the name refer to the range defined by the formula: = Get.Workbook(1)

Step: 1d. Click OK

New Name Dialogue
New Name Dialogue

 

 

Step:2. Write  the below array formula simply referring to the range name defined in Step 1 to get the names of all the sheets in the current workbook listed:

– Since there are 3 sheets in the workbook in the screenshot, select any three consecutive cells in a row.

– Enter “=” to start writing the formula and then type the range name defined in Step.1 as in the below screenshot:

Array Formula referring the defined Name
Array Formula referring the defined Name

 

– Hit CTRL + SHIFT + ENTER , to enter the formula as array formula and the list of all sheet names are listed. See the below screenshot:

List of Sheet Names output
List of Sheet Names output

Note that the sheet names are listed along with the workbook name. You can write a formula using Excel’s built-in functions to extract only sheet name from these names that are qualified by the workbook name.

Happy learning..!! Do leave your comments.

Advertisements