List the Sheet Names with Formula

Display all of the worksheets in an Excel workbook in an easy to read table.  In Excel you might want a table which contains all of the sheets in a workbook.  You can list all of the Excel sheet names within an Excel workbook with formula using the following steps;

List All Sheets Excel

The above lists the tab names in the attached workbook.  The process to achieve the above is as follows, press Ctrl F3, this brings up the Name Manager

show names of sheets excel

Click on New - Type the named Range -  Sheets

In the area at the bottom of the Name Manager where it says Refers to, type;

=GET.WORKBOOK(1)&T(NOW())

Now in the worksheet put this following formula;

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROWS(A$1:A1))))

Drag the formula down. The Excel formula should now produce sheet names.  I have attached an Excel file to show workings.

Or In Later versions of Excel the following will also work;

=IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A2),1),"")

Here is an example from the Excel file below showing the sheet names of all of the worksheets in the file.