Navigating workbooks with lots of sheets can be tedious. In this tutorial Iโm going to show you how to dynamically list Excel sheet names and add some user-friendly hyperlinks to help users easily navigate the file.
It requires an old Excel 4.0 Macro Function called GET.WORKBOOK and this means the file must be saved as a .xlsm file type. Donโt let that put you off though. Itโs super easy and doesnโt require any macro/VBA programming knowledge.
Watch the Video
Download Workbook
Enter your email address below to download the sample workbook.
Formula to Dynamically List Excel Sheet Names
The crux of this solution is the GET.WORKBOOK function which returns information about the Excel file. The syntax is:
=GET.WORKBOOK(type_num, name_text)
type_num refers to various properties in the workbook. Type_num 1 returns the list of sheet names and thatโs what weโll be using.
name_text is the name of the workbook you want to get the sheet names from. Weโre going to omit this argument, and it will simply return the names from the active workbook.
Excel 4.0 macro functions like GET.WORKBOOK cannot be typed in cells like the functions we know and love today, they must be defined in a name.
Iโve defined a name (Formulas tab > Define Name) GetWorkbook as you can see below:
And if I reference that name in a formula it returns the list of sheet names prefixed by the file name.
You can see in the image below that I wrapped the defined name in the TRANSPOSE function because GET.WORKBOOK returns a horizontal array of sheet names and I wanted them in a vertical array.
Note: I have Excel for Microsoft 365 with dynamic arrays, so my formula spills the results to the cells below, as denoted by the blue border around cells B6:B12 in the image above.
If you have Excel 2019 or earlier you need a different formula, but more on that later. First, I just want to explain what GET.WORKBOOK does.
Now, I only want the sheet names, so Iโll use the REPLACE function with FIND to extract them:
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
Tip: you could use the SUBSTITUTE function instead of REPLACE.
Lastly, to ensure the formula dynamically updates Iโm going to append the volatile function, NOW, wrapped in the T function on the end:
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")&T(NOW())
We do this because NOW, which returns the current time, triggers a recalculation of the defined name. The T function returns blank when the value returned isnโt text. In other words, T hides the time returned by NOW. The only reason weโre appending the NOW function is because itโs a volatile function that triggers a recalculation of the defined name which is required to update the list of sheet names.
Iโll define a new name for this formula, SheetNames:
And when you enter this formula wrapped in TRANSPOSE in a cell it returns the sheet names:
Again, my formula spills the results to the cells below because I have dynamic arrays.
Dynamically List Excel Sheet Names - Excel 2019 and Earlier
In all versions of Excel we can use the INDEX function with ROW to return the list of sheet names:
Note: the ROW function simply returns the row number of a cell. The ROW function in the formula in the image above returns 1. As you copy it down the column it returns 2, then 3 and so on. However, if you copy the formula down more rows than you have sheets for it will return an error.
We can handle the errors with the IFERROR function:
=IFERROR(INDEX(SheetNames,ROW(A1)),"")
This allows you to copy it down past the current number of sheets you have which will ensure any new sheets added are automatically included in the list.
Dynamically List Excel Sheet Names with Hyperlinks
Of course, what good is a list of Excel sheet names without hyperlinks to take you to those sheets? To solve this we can nest the INDEX formula inside HYPERLINK:
=IFERROR(HYPERLINK("#'"&INDEX(SheetNames, ROW(A1))&"'!A1", INDEX(SheetNames,ROW(A1))),"")
Copy the formula down to get a list of sheet names with ready-made hyperlinks:
Be sure to copy the formula down past the last current sheet name so that new sheets are automatically included.
More on the HYPERLINK function here.
Note: you may find that the formula doesnโt automatically update. If that happens, press F9 to force a recalculation, or perform any of the other actions that trigger recalculations.
Excel for Microsoft 365
If you have dynamic arrays you might be wondering if this can be done using spilled ranges and the answer is kind of. It requires the sheet name and hyperlink to be in separate columns, as you can see below:
The formula in column D spills the results and will grow as new sheets are added. The formula in column E references the spilled range with D5# and will therefore also grow as new sheets are added. Unfortunately, you cannot create a single formula by replacing D5# in column Eโs formula with โSheetNamesโ or โTRANSPOSE(SheetNames)โ.
Limitations
This technique has a few limitations:
- It includes hidden sheets however the hyperlink can't open a hidden sheet
- It requires the file to be saved as a .xlsm and macros enabled
- Requires recalculation to add new sheets to the list. The T(NOW()) part of the formula will trigger recalculation based on various actions, but if you donโt perform any of those actions, you can force a recalculation by pressing the F9 key.
Related Lessons
We can also generate a list if Excel Sheet Names with VBA
And you can get a complete list of the elusive Excel 4.0 Macro Functions here.
Matthew Kearns
I was wondering if there was a way to limit the list of worksheets output by the Get.WorkSheet macro function. I have say X number of sheets but only want to start listing from worksheet #3. Can that be done?
Mynda Treacy
Hi Matthew,
Yes, you can wrap the TRANSPOSE(GetWorkbook) formula in any function that filters a list, like FILTER, INDEX, TAKE, DROP etc. For example, with the formula below you can return rows 2, 3 and 4:
Mynda
Sylvia
This is AMAZING!! THANK YOU!
Philip Treacy
You’re welcome. Glad it was helpful
Prashant
I have two columns; one is for sheet name and the other for tracking some activity. If I delete the sheet from the workbook, I need the entire row to be deleted, and when I add a new sheet a new blank row should be created. Any tip for this.
Thanks for the support.
Mynda Treacy
Hi Prashant,
Pressing F9 will recalculate the formulas to include any new sheets, and any deleted sheets are automatically removed from the list. Unfortunately, this will result in any data you’ve added in adjacent columns to be out of sync with the list of sheets. I don’t see any easy way to have both a dynamic list of sheets and have the data you’ve stored in adjacent columns to stay in sync using this formula approach to listing the sheet names. You might need to write a VBA routine to handle this instead of this formula approach.
Mynda
ira haron
TYPO Alert:
Excel LET Function allows you to declare variables and intermediate cacluations
cacluations
what’s my reward?
Philip Treacy
Your reward is our thanks!
Rajiv
Dear Mynda,
Is there a way that I can exclude the first sheet (Tutorial) from the list of sheet names displayed as Hyperlinks in the 2nd method (Dynamic Arrays)? I was able to do it in the Index formula method wherein I started from Row(A2) instead of Row(A1).
Thanks,
Rajiv
Mynda Treacy
Sounds like you answered your own question, Rajiv. If you’re still stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Dave S.
This was fantastic!! Thank you so much. The video was very well done, and the transcribed text helped me get the formulas just right. Good timesaver and will be returning here for future questions! Thank you,
Mynda Treacy
Great to hear, Dave!
Agidyne
Love this post, informative and adding the T(NOW()) solved a huge problem I was having!
Mynda Treacy
Great to hear!
Patrick
There’s always some hidden tip within a tip, and this time it was the use of T(NOW()) to make the function volatile. I have used “+ N(“some text”)” to document functions in a similar but opposite manner.
Mynda Treacy
Great to hear you found some nuggets in this post
Mynda Treacy
Great to hear you found some nuggets in this post, Patrick
Mynda Treacy
Great to hear you found some nuggets in this post, Patrick
anon
Hi there thanks for the great solution!
Oddly, when I close the excel sheet and open again, my sheet name column would return #N/A. I have to manually go to define names and click the saved SheetNames again.
How do I ensure that excel automatically uses SheetNames when it starts up?
Mynda Treacy
I’m not sure what would be causing this. Please post your question on our Excel forum where you can also upload your file and we can help you further.
Rob Romero
Hello and thank you for this video. It has helped me merge two different reports into one data source.
I am coming across something when I add some data to the existing two spreadsheets and then press the Data/Refresh All button.
The query data is added as additional data into the combined data sheet so it is doubling the information. How do I prevent this from happening?
Mynda Treacy
Hi Rob, I think you meant to post this comment on this post https://www.myonlinetraininghub.com/power-query-consolidate-excel-sheets. You need to filter out the query table from the ‘source’ data as it’s including this sheet/table in the query because you’re getting all sheets/tables in the current file.
Marie Louise Steenbjerg
Hi Mynda! Thank you for the tip about listing a number of sheets. Years ago (2015-2017) I worked on a technical project about Intelligent Signaling Systems (ITS) on a highway.
My designed sheets was both design, semi automated Quality assessment and always the latest “final” blueprint. I had of 195 sheets in one workbook with each sheet prepared to be printed out as a A4 paper (a size used in Northern Europe close to Legal size). My sheets should make sense if printed out as you can imagine.
On each sheet I had the name of the sheet (so it could been seen on a printout and you could refer back to Excel workbook sheet at a later day). I used the following dynamic text-formula:
MIDT(@CELLE(“filnavn”;P104);FIND(“]”;@CELLE(“filnavn”;P104))+1;255)
I am using a Danish translation of Excel, so sorry for the commands. Line 104 happened to be the title line on the printout.
Due to the many sheets, it had to be short, uniq and descriptive, so I could navigate among the sheets, but as support for future users I had to add a sub title on each sheet which I referred to on the index page.
On the indexpage I had the page number on the printout, Excel sheetname, descriptive subtitle, revision date (each sheet had individual revision date to make it easier to find latest revisions for the external PLC programmer).
I used a method similar to your video blog on the index page, except of the extra columns, so I don’t need to write it here.
Mynda Treacy
Hi Marie Louise,
Thanks for sharing your interesting story. I dread every having to work in a file with that many sheets ๐
I did wonder why you didn’t use the header/footer print options to automatically insert the file &[File] and sheet names &[Tab] when printing. I’m sure you had your reasons, but I thought I’d mention it here for those who may read these comments in future.
Mynda
Marie Louise Steenbjerg
Hi Mynda
I will use that option today, but I had a frame around all data that needed printout and would avoid holes between header and footer (in the frame). The authorities couldn’t decide on some of the signalling along the 3 mile long project area, so I had to add and remove on short notice. I used the group function to add/remove data from printout should they wish. Actually I can’t remember why.
Each sheet had 104 row and 20 coulumns. The last 12 coulomns was a design of 12 different timers (The cells showed what signalimage a future driver would see), but in most cases I could do it with up to 6 timers. That gives an enormous amount of data to keep track of. A “wrong” signal could confuse a driver and result in a traffic crash.
The page number had a break between 7 and 10 and again after page 97. I had to nummerate the pages myself in this case.
A fun side effect of all my efforts was an external consultant added macros to test my design so we could see how cars would “see” my signaling in almost real time in the 16 different traffic options. I had the pleasure to correcting a few errors in the macroes without knowing anything about VBA, but due to the many sheets and data we had to replace the formulas with the displayed text. We did that by adding the value on top the formula cells. Otherwise the macros would either run very slowly at best, or not at all.
That led to macro courses online ๐
Marie Louise
Mynda Treacy
I knew you’d have a good reason ๐ Sounds like a very rewarding project!