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.

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