3 Quick Ways to Get a List of All Worksheet Names in an Excel Workbook

If you have an Excel workbook that has hundreds of worksheets, and now you want to get a list of all the worksheet names, you can refer to this article. Here we will share 3 simple methods with you.

Sometimes, you may be required to generate a list of all worksheet names in an Excel workbook. If there are only few sheets, you can just use the Method 1 to list the sheet names manually. However, in the case that the Excel workbook contains a great number of worksheets, you had better use the latter 2 methods, which are much more efficient.

Method 1: Get List Manually

  1. First off, open the specific Excel workbook.
  2. Then, double click on a sheet’s name in sheet list at the bottom.
  3. Next, press “Ctrl + C” to copy the name.Copy Sheet Name
  4. Later, create a text file.
  5. Then, press “Ctrl + V” to paste the sheet name.Paste Sheet Name
  6. Now, in this way, you can copy each sheet’s name to the text file one by one.

Method 2: List with Formula

  1. At the outset, turn to “Formulas” tab and click the “Name Manager” button.
  2. Next, in popup window, click “New”.Name Manager
  3. In the subsequent dialog box, enter “ListSheets” in the “Name” field.
  4. Later, in the “Refers to” field, input the following formula:

Customize New Name

  1. After that, click “OK” and “Close” to save this formula.
  2. Next, create a new worksheet in the current workbook.
  3. Then, enter “1” in Cell A1 and “2” in Cell A2.
  4. Afterwards, select the two cells and drag them down to input 2,3,4,5, etc. in Column A.Enter Sequential Numbers
  5. Later, put the following formula in Cell B1.

Enter Formula in Cell B1

  1. At once, the first sheet name will be input in Cell B1.
  2. Finally, just copy the formula down until you see the “#REF!” error.Copy Formula Down to List Sheet Names

Method 3: List via Excel VBA

  1. For a start, trigger Excel VBA editor according to “How to Run VBA Code in Your Excel“.
  2. Then, put the following code into a module or project.
Sub ListSheetNamesInNewWorkbook()
    Dim objNewWorkbook As Workbook
    Dim objNewWorksheet As Worksheet

    Set objNewWorkbook = Excel.Application.Workbooks.Add
    Set objNewWorksheet = objNewWorkbook.Sheets(1)

    For i = 1 To ThisWorkbook.Sheets.Count
        objNewWorksheet.Cells(i, 1) = i
        objNewWorksheet.Cells(i, 2) = ThisWorkbook.Sheets(i).Name
    Next i

    With objNewWorksheet
         .Cells(1, 1) = "INDEX"
         .Cells(1, 1).Font.Bold = True
         .Cells(1, 2) = "NAME"
         .Cells(1, 2).Font.Bold = True
    End With
End Sub

VBA Code - List Sheet Names

  1. Later, press “F5” to run this macro right now.
  2. At once, a new Excel workbook will show up, in which you can see the list of worksheet names of the source Excel workbook.Listed Sheet Names in New Excel Workbook


Advantages Disadvantages
Method 1 Easy to operate Too troublesome if there are a lot of worksheets
Method 2 Easy to operate Demands you to type the index first
Method 3 Quick and convenient Users should beware of the external malicious macros
Easy even for VBA newbies

Excel Gets Corrupted

MS Excel is known to crash from time to time, thereby damaging the current files on saving. Therefore, it’s highly recommended to get hold of an external powerful Excel repair tool, such as DataNumen Outlook Repair. It’s because that self-recovery feature in Excel is proven to fail frequently.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql fix and outlook repair software products. For more information visit www.datanumen.com

31 responses to “3 Quick Ways to Get a List of All Worksheet Names in an Excel Workbook”

  1. Thank you for this. To save a step, I used the function:


    and copied it down. That way, I don’t need a Column to hold the numbers 1 to n

  2. Method 3 works like a charm. Does anyone know how to change the VBA code to create a worksheet within the existing workbook to show the list of worksheet names?

  3. I love your blog.. very nice colors & theme. Did you make this website yourself or did you hire someone to do it for you? Plz reply as I’m looking to design my own blog and would like to find out where u got this from. thanks a lot


  5. If I change a sheetname, i doesn’t update and cells referenced to it get an #REF! error. How to solve without macro? Thanks.

  6. Thanks a lot!
    With the new O365 functions, you can directly use =TRANSPOSE(ListSheets).
    If you also want/need the sheet numbers, =SEQUENCE(COLUMNS(ListSheets)) is even dynamic.

  7. Thank you so much for sharing these three options. I disagree with the user who referred to the post as “idiotic”. It’s very easy to criticize, but takes real effort to help others. Thank you for your help!

  8. Just a comment method 2 doesnt require a list first if you replace the =INDEX(ListSheets,A1) with =INDEX(ListSheets,Row(A1)).

    Method 2 also requires you to save as macro enabled workbook as it uses Excel 4.0 legacy function

  9. Thanks, how do I make the results output in my current file on the tab “Tab Index” which already exists, better yet into a table?

  10. I get an error #BLOCKED! when I try the method 2 (list with formula). I want to use the sheet name within my workbook, so the VBA method is no help.

  11. Hi, List via Excel VBA works great, plz suggest what to change the code to get it start in row4, column B (Index in B4, Name in C4)

  12. Thank you! This saved so much time for me, I really appreciate your sharing this with all!

    BTW, is there a way to print just the visible worksheets (and not the hidden ones)? I know i’m asking for a lot here, but that might make the formula even better 🙂

Leave a Reply

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