How To Generate A List Of Sheet Names From A Workbook Without VBA

2016-12-07

In this post we’ll find out how to get a list of all the sheet names in the current workbook without using VBA.

This can be pretty handy if you have a large workbook with hundreds of sheets and you want to create a table of contents. This method uses the little known and often forgotten Excel 4 macro functions.

These functions aren’t like Excel’s other functions such as SUM, VLOOKUP, INDEX etc. These functions won’t work in a regular sheet, they only work in named functions and macro sheets. For this trick we’re going to use one of these in a named function.

step-001-how-to-generate-a-list-of-sheet-names-from-a-workbook-without-vba

In this example, I’ve created a workbook with a lot of sheets. There are 50 sheets in this example so I was lazy and didn’t rename them from the default names.

step-002-how-to-generate-a-list-of-sheet-names-from-a-workbook-without-vba

Now we will create our named function.

  1. Go to the Formulas tab.
  2. Press the Define Name button.
  3. Enter SheetNames into the name field.
  4. Enter the following formula into the Refers to field.
    =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
  5. Hit the OK button.

step-003-how-to-generate-a-list-of-sheet-names-from-a-workbook-without-vba

In a sheet within the workbook enter the numbers 1,2,3,etc… into column A starting at row 2 and then in cell B2 enter the following formula and copy and paste it down the column until you have a list of all your sheet names.

=INDEX(SheetNames,A2)

step-004-how-to-generate-a-list-of-sheet-names-from-a-workbook-without-vba

As a bonus, we can also create a hyperlink so that if you click on the link it will take you to that sheet. This can be handy for navigating through a spreadsheet with lots of sheets. To do this add this formula into the column C.

= HYPERLINK ( "#'" & B2 & "'!A1", "Go To Sheet" )

Note, to use this method you will need to save the file as a macro enabled workbook (.xls, .xlsm or .xlsb). Not too difficult and no VBA needed.

Video Tutorial

This video will show you two methods to list all the sheet names in a workbook.

  • The first method uses a VBA procedure from this post.
  • The second (skip to 3:15 in the video) uses the method in the above post.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

37 Awesome Excel Mouse Tips & Tricks You Should Know

37 Awesome Excel Mouse Tips & Tricks You Should Know

While the keyboard is generally quicker, you shouldn’t completely ignore the mouse. There are also some great time saving mouse shortcuts as well. In this post we’ll take a look at some of the best Excel mouse time saving tips and tricks.

read more
25 Amazing Power Query Tips and Tricks

25 Amazing Power Query Tips and Tricks

Power query is amazing tool that allows you to import and transform data with ease and helps to create repeatable and robust procedures with your data. Here are some tips and tricks to help you get the most out Power Query.

read more

Comments

53 Comments

  1. Kinsen

    Hi,
    this is very helpful, but it would be great if you mention how to do this only for visible sheets?

    • John

      Unfortunately, with this method it’s not possible.

  2. andrew

    The hyperlink doesn’t work when there are spaces in the worksheet names

    • Rajid ALi

      Exactly…. any solutions ????

    • John

      When there is a space you need to add single quotes around the sheet name. Just after the # and just before the !

      =HYPERLINK("#'"&B2&"'!A1","Go To Sheet")

  3. Ed

    Or you can simply rightClick either the left or right tab-horizontal-scroll arrow at the bottomLeft, and then click the worksheet name (from the simple vertical list) that you want to jump to.

    • John

      That’s a great tip if you want to navigate between sheets, but won’t help if you want to list out all the sheet names.

  4. Dea Mengers

    This is exactly what I was looking for! It’s a major time saver when my client provides a workbook with 80 sheets and wanted the data from each combined into one sheet.

    • John

      Good to hear it’s of use to you!

  5. Trevor

    Very useful and helpful.
    I have modified the index part though for my use.
    Others might also find this helpful:
    You can generate the list of sheet names directly without having to first create an index column by using the ROW() function.
    I used:
    =INDEX(SheetNames,ROW()-“row offset”+”sheet offset”)
    where the “row offset” is the number of rows down the sheet you intend to start the numbering from and “sheet offset” is the number of sheets in from the start of the workbook you intend to start the list from.
    An example is =INDEX(SheetNames,ROW()-2+2) from what I’ve done.
    I start my sheet list on row 3 of a summary page and allowed the first two rows for headings etc. , and I skipped the first two sheets in the workbook. In this case ROW()-2+2 will be evaluated to be 3.
    Maybe have a play with the numbers used in the row and sheet offsets and you will see what i mean.

    • John

      Thanks Trevor, good tip!

  6. Julie

    Thanks! I had to switch the commas by semi-colons, but otherwise it’s fine! 🙂

    • John

      I believe that’s due to regional version of Excel, maybe French?

    • Pancho

      Pliz, put the result.
      Can’t get it to work

  7. YEOW

    my excel end up with #N/A , please help me !! thanks

    • John

      When the index is past the number of sheets in the workbook the formula will produce an #N/A error. You can wrap the formula in an IFERROR to avoid this.

  8. daniel styslinger

    Very helpful thank you.

  9. Nanda

    Thank you so much John. You’re a life saver.

  10. Bob Smiley

    Wow. Just wow.

  11. Trygve

    My list does not automatically update if the sheet is renamed. Is there a way to get around this?

    • John

      Use the keyboard shortcut Ctrl + Alt + F9 to recalculate.

      • Zo

        Hello, John,

        My indexing and redirecting works fine until I invoke Ctrl + Alt + F9 to recalculate; then it shows #NAME errors!?

        Same happens when I add a sheet to the workbook.

        Can you, please help with this?

        Zo

        • zo

          I fixed it by applying the names in the list through the “Define names” function as shown above here.

          Simply typing the same that this formula produces didn’t work for me?!

  12. Lisa

    Hello,
    What would the formula be to then retrieve a ‘named cell’ value from one of the worksheets?

    GOAL – list all names of sheets, then retrieve the value of a ‘Total’ cell from each sheet. Then I have a summary sheet where I can calculate a grand total of expenses from each worksheet.

    Thanks so much!

    • John

      You could use the INDIRECT function to create a reference to your total on each sheet, by referring to the sheet name and location.

      But honestly, it sounds like you should reorganize your data so that you have one proper data set instead of data across many sheets. Then use a pivot table to do any analysis.

      • Claudius

        Thanks a lot that’s awesome!
        How can I use this to reference cells in the other sheets?

        Thanks a lot!

        • John

          If your sheet name is in A1 and you want to reference a cell on that sheet in C2, use this formula:

          =INDIRECT("'"&A1&"'!C2")

          • Claudius

            Thanks a lot John! Much appreciated!

          • Lisa

            Thank you!! This worked perfectly for me.

  13. Andrew Herbert

    Try this to combine the Hyperlink as part of the name… Also gets rid of any that aren’t used so you can make it a full page and add tabs later if needed.

    =IFERROR(HYPERLINK(“#’”&INDEX(SheetName,A3)&”‘!A1″,INDEX(SheetName,A3)),””)

  14. Narender Reddy S

    Thank you,
    It was very helpful.

  15. Bob

    I tried this earlier on another sheet and it worked, but today i’m trying it again and i’m getting a “#REF!” error. When I step through the calculations, it appears to resolve the SheetName function to an array, follows by “(A2)”, which resolves to 1. Either I’ve got an elusive type-o or Excel has changed how it does this since the last time I tried. Any suggestions? I’m on office 365 so updates come whenever they feel like it.

  16. Bob

    Nevermind… I found my elusive Type-o! I had done SheetNames(A2) rather than Index(SheetNames,A2).
    Doh!

  17. Iain

    John – am using a Mac with the latest version of Office. When I try to save the file with your REPLACE formula, I get a message saying that Excel 4.0 formula’s can’t be saved in a macro-free workbook.

    Is there a workaround for this?

    Thanks.

    • John

      Try saving as a xlsm file.

      • Iain

        Isn’t that a macro enabled file type? Thought the idea of this formula was to stay away from that.

        • John

          They are a type of old legacy macro from before VBA, so an xls file will also work.

          Here’s the modern way of doing this.

  18. Abhay Antala

    I used this formula for both Hyperlink and Sheet Name

    =HYPERLINK(CONCATENATE(“#”,”‘”,INDEX(SheetNames,P1),”‘”,”!A1″),IFERROR(INDEX(SheetNames,P1),””))

    • John

      Thanks! Good tip on combining them into one.

  19. jc

    Would you share the file with the example? my excel is in spanish and GET.WORKBOOK doesn´t work because of language,

    • John

      Sorry for the delay, here’s the link.

  20. zain

    Thanks John for a valuable post!

  21. sarat

    i want all sheets single client name in one sheet, please tell me

  22. SuperSingh

    I don’t want the name of the 1st sheet . I want to get the index start with 2nd sheet onwards. How do I do that ?

  23. richard waddington

    This is genius!! Thanks vm.

  24. Jon

    Will this work in Google Sheets?

    • John

      I highly doubt it.

  25. Samuel

    HI John, It worked well before and suddenly it does not work anymore and has a #Name? error. any idea about the problem?

    • John

      Not sure. Maybe try deleting everything in the name manager and start over.

    • Thomas

      I’m probably too late to help you, but for others, I had a similar error of a spreadsheet working for years, then suddenly getting a bunch of #NAME? errors. Turned out macros were automatically being disabled, and the GET.WORKBOOK function relies on macros.

  26. bhaskar B Khandke

    This is one of the best tip I have seen … Not only it lists the workbooks in one sheet but also provides hyperlink… such a saviour..!!!

  27. John

    Works like a charm. Thanks!

  28. Love Rai

    Note :- Formula Work only if your Sheet Name in Numeric
    =”=’E:\Contacts\”&”[“&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”.csv”&”]”&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”‘!$B$1048576″
    Use this Formula To Get Value from other Excel Sheet
    ________________

    Define Path = “=’E:\Contacts\”&”[”
    Give Sheet Name [if Sheet Name in Numericl] = SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”.csv”&”]”&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1
    Cell No. = !$B$1048576

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This