How to List All Sheet Names in Excel (With Example)


Often you may want to list out all sheet names in an Excel workbook.

Fortunately this is easy to do using a simple formula in Excel and the following example shows how to do so.

Example: How to List All Sheet Names in Excel

Suppose we have the following Excel workbook with five sheets:

Suppose we would like to list out each sheet name in the workbook within the sheet called Sheet5.

To do so, we must click the Formulas tab along the top ribbon, then click the Define Name icon with the Defined Names group:

In the new window that appears, type GetSheets into the Name box and then type the following formula into the Refers to box:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Then click OK.

Next, type the following formula into cell A1 of Sheet5:

=INDEX(GetSheets, ROW())

Then click and drag this formula down column A until you encounter #REF! to indicate that all sheet names have been listed:

Excel list sheet names

Notice that all sheet names in the workbook are now displayed in column A.

Note: In this example we used a workbook with five total sheets, but this same formula will work with any number of sheets.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Compare Two Excel Sheets for Differences
How to Sum Across Multiple Sheets in Excel
How to Calculate the Sum by Group in Excel

3 Replies to “How to List All Sheet Names in Excel (With Example)”

Leave a Reply

Your email address will not be published. Required fields are marked *