How to Add a Table of Contents Image Gallery Sheet to Your Excel Files

Bottom line: Make your hard work shine, and learn how to add a beautiful Table of Contents sheet to the front of your Excel workbook that contains clickable image tiles to each sheet in the workbook.

Skill level: Intermediate


There's no doubt that first impressions are important in life.  They are also important with our Excel files. πŸ™‚

When someone opens your Excel workbook, you want them to know that there is a lot of awesome stuff inside.  Often times our workbooks contain a lot of sheets, and users have a difficult time finding the chart, dashboard, report, or data sheet they are looking for.  They don't know what is included in the workbook until they scroll through all the sheet tabs.

The Table of Contents Gallery solves this by adding a nice looking navigation sheet to the front of your workbook.  This TOC Gallery sheet contains thumbnail images of each sheet in the workbook.  Each image tile is a clickable hyperlink that jumps to the sheet.


This creates a visual image gallery of all the sheets in the workbook, allowing the user to get a preview of all your hard work.  It is a great way to show off your workbooks that contain charts, dashboards, slicers, and interactive reports.

The TOC Gallery is a simple feature that will make your users & boss say, “WOW!!!” =)

Video: The Table of Contents Gallery Explained

Watch the video below to see the Table of Contents gallery in action, and learn how to add it to your workbooks.

Watch the video on YouTube

How Do I Create the Table of Contents Gallery?

There are three ways to create the TOC Gallery sheet in your workbook:

  1. Manually copy a sheet range, paste it as a picture, resize it, and create a hyperlink.  Repeat for each sheet.  That would take a long time and get boring!
  2. Use the macro in the workbook that is available for free download below.
  3. Use the Tab Hound Add-in to create the sheet with the click of a button (NO macros or coding required).

I'm not even going to explain option 1 because that would be a waste of your time, so let's look at options 2 & 3.

Download The Table of Contents Gallery Macro

The file that contains the macro can be downloaded here.

Note: I updated the file on October 10, 2016 to be compatible with Excel 2007.  Thanks to Doug for reporting the issue and help testing.

The macro contains instructions on how to run it.  There are also parameters that can easily be modified in the VBA code to change the look of the sheet:

  • Shape size – Make the image tiles smaller or larger.
  • Zoom amount – Make the text within the tiles appear smaller or larger.
  • Column count – change the number of columns of image tiles.
  • Visible sheets – skip any hidden and veryhidden sheets.

The macro runs on the ActiveWorkbook.  It basically does the copy & paste picture on a range of cells in the sheet.  This is similar to taking a screenshot or snapshot of an area on each sheet, then pasting it to the TOC Gallery sheet.  It also resizes and crops the images to get them all lined up perfectly.

The macro can be copy/pasted to your Personal Macro Workbook, so you can run it on any open workbook.

Note about Chart Sheets:  I updated the file on October 11, 2016 to work with Chart Sheets.  A Chart Sheet is a specific type of sheet that only contains a chart, no cells or ranges.  If you placed a chart on a regular worksheet, then you do not have to worry about this. Hyperlinks do not work with Chart Sheets

Tab Hound's Table of Contents Gallery Feature


Don't worry if macros aren't your thing.  The TOC Gallery feature has also been added to the Tab Hound Add-in.


The Table of Contents Gallery allows you to customize the look of the TOC Gallery sheet by selecting options in the menu.  You can quickly recreate/update the TOC Gallery sheet after you make changes to the workbook.  This is the fastest and easiest way to create one of these nice looking TOC sheets in any of your workbooks.

Tab Hound already has a Table of Contents feature that adds a sheet with a clickable list of sheet names to the front of the workbook.  The TOC Gallery feature now gives you the option to add either the regular list, gallery, or both.


The Tab Hound Add-in is packed with additional features that help you navigate and organize the worksheets in your workbooks.  If your workbooks contain a lot of worksheets, then you will wonder how you ever lived without Tab Hound.

Tab Hound + Tab Control Box Windows Edition 394x207

You do NOT need to know how to use macros to use the Tab Hound Add-in.  This is an add-on application for Excel that will save you time with your everyday tasks.

Click here to learn more about the Tab Hound and Tab Control Add-ins

Tips for Implementation

Whether you are running the macro or using Tab Hound, there are a few things to consider when creating the TOC Gallery sheet.

  • Screen size – The user's screen size and resolution are important.  If your users will be viewing your workbook on a small laptop screen, then you might want to use smaller image tiles or less columns.  It is best to eliminate horizontal scrolling on the sheet.  It will be much easier for the user to scroll vertically to view the sheet images.  To accommodate laptop screens, you might want to use 3 columns of medium size tiles, or 2 columns of large tiles.
  • Rerun after changes – When sheets are modified or added to the workbook, you will need to re-create the TOC Gallery sheet to include the changes.  This is very easy to do.  You can rerun the macro or click the TOC Gallery button in Tab Hound to recreate/update the sheet.    The process can also be rerun when you are trying to determine the best settings (size, zoom, etc.) for the images.  Test different settings and see which looks best for your workbook.
  • Chart Sheets – I updated the file on October 11, 2016 to work with Chart Sheets.  A Chart Sheet is a specific type of sheet that only contains a chart, no cells or ranges.  If you placed a chart on a regular worksheet, then you do not have to worry about this.
    Hyperlinks do not work with Chart Sheets.  This is a limitation of Excel.  The macro will still create the image tile for the Chart Sheet, but it will not be hyperlinked to the Chart Sheet.  The workaround is to create a macro that selects the Chart Sheet.  Then assign the image tile to the macro.  I added an example macro in the file named Chart_Sheet_Link.  The code to select the Chart Sheet looks like the following.  Again, you only need this if your workbook contains the special type of Chart Sheet.

    I also added a macro to the file that can be used for the hyperlink for all chart sheets. It determines the image/textbox that is clicked and selects the corresponding chart sheet.

Make Your Workbooks Shine

I wrote this macro last week, loved the results, and had to share it with you.  I think it can help add a nice shiny cover to any of your workbooks, and help your users view the reports you spent so many hours perfecting.

I also think we can do more with this.  I am interested to hear your ideas on how we can improve it or add features.  Please leave a comment below with your suggestions or any questions.  Thank you!


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

    • Hi Rod,
      I sent an email out yesterday to existing customers with instructions on how to get the update. Let me know if you did not receive it.

      I am happy to hear you are enjoying Tab Hound. Thanks again Rod!

  • I am very excited. Thank you for another dose of knowledge. Certainly the possibilities presented here Excel will be useful in my work.

  • Jon this is awesome. This will come to great use for me. We have different district total 7 and when I create the graph from data,I only show them the graphs. The data is very long roughly 65000 to 85000 records. With this feature they can see what each graph, instead of the just the dashboard. IS this add in available on your site?

    thank you again you a life saver!

    • Thank you Elba! That sounds like a great use for this feature. Yes, the Tab Hound Add-in is a paid product and you can learn more about it here. Let me know if you have any other questions. Thanks again Elba. πŸ™‚

  • I am really excited to use this. However, after the first several times I used it today, it began to crash my Excel. It’s only working about 50% of the time now. So that is frustrating. It crashed Excel to the extent that it is stuck and I have to go into end processing. Any suggestions Jon?

      • Hi Jon,
        I’m using the sheet I downloaded from this – Table-of-Contents-Gallery.xlsm
        I also loaded the macro into my personal.xlsb and then tried on another. It works sometimes but most it crashes.
        Excel 2013
        I’m going to keep trying because I think it’s fantastic. But if you have thoughts, let me know. πŸ™‚

  • Hello Jon,
    Great site and an abundance of great tools and tips!!! When running the TOC macro I get a compile error, assume it is due to a reference setting. Is there a specific reference required to run this? Thanks.

  • No need for the TOC link to get back. Once you click on a gallery item you can get back by Alt/Left arrow.

    See you in a few weeks!

    • Wow, that’s an awesome tip Bob! I did not know that one. Are there any other scenarios where that works besides clicking a hyperlink?

      Do you have that tip in one of your books? If so, let me know which one and I will share a link. Thanks and looking forward to seeing you!

    • The TOC Gallery backlink is NOT added to each sheet by the macro. This is intentional because we don’t want it to overwrite any existing data. You might also want to place it in a specific cell below the header, and each sheet might be different. I have thought about ways to add it programmatically by finding a blank cell, but I also don’t want to mess up any existing sheets.

      Therefore, the backlink is placed on the TOC Gallery sheet. You can then copy and paste that cell to any or all sheets in the workbook. The link can be pasted to multiple sheets by holding the Ctrl or Shift keys to first select multiple sheets. I hope that helps.

      • Thanks for answer. Also I could NOT find or load the form “Table of Contents Creator” shown on this page and in video.

    • Hi Joel,

      At this time Tab Hound only works on the 2011 version of Excel for Mac. The 2016 version does not have a developer environment that will support this type of add-in yet. Microsoft is working on updating this, so hopefully I will have a Mac version in the future. You should also know that the Mac version does not have this new Gallery feature yet. I do plan to add it, but the compatibility issues have made the Mac version hard to support. Please let me know if you have any other questions. Thanks again!

    • Thanks Giang! That is a great suggestion. The shape would be less destructive than the cell link.

      It’s pretty easy to do manually. You could use a shape as a button, and then assign the shape a hyperlink to the TOC Gallery sheet. Then copy/paste the shape to each sheet in the workbook. That could also be added to the macro without too much effort. Thanks again!

      • I already created a small macro to add a text hyperlink to go back to the TOC sheet in each sheet. Hope this would be helpful
        Sub add_hyperlink()
        Dim i As Integer, ws_num As Integer

        ws_num = Worksheets.Count

        For i = 2 To ws_num
        With Sheets(i)
        If .Cells(1, 1).Hyperlinks.Count > 0 Then
        .Cells(1, 1).EntireRow.Delete
        End If
        .Cells(1, 1).EntireRow.Insert
        .Hyperlinks.Add .Range(“A1”), “”, “‘” & Sheets(1).Name & “‘” & “!A1”, “Go back to the table of contents”, “Go back to TOC Gallery sheet”
        End With
        Next i

        End Sub

        • Thanks for sharing Giang! If anyone tries to run this, just know that the code will delete row 1 in each worksheet of your workbook if there is a hyperlink in cell A1. That shouldn’t be a problem for most people, just wanted to give a warning. Always save your workbooks before running a macro. Thanks again!

  • Hi Jon,

    using the macro in my personal workbook (Excel 2013), even for small files, I often get run time error 1004 on the following line:

    ‘Paste shape to TOC
    .Pictures.Paste Link:=False

    It’s a nice macro, but it would even be nicer if non-worksheets, ie charts, would be taken into account as well.

    • Hi Frank,

      If possible, could you send me a file that you are getting that error on? [email protected] Or provide more info on what you think might be causing it. I’d be happy to take a look.

      The macro could definitely be modified for Chart Sheets. Right now it is only looping through Worksheets, so any chart sheets will be skipped. I will post a macro that includes chart sheets when I get some time. Thanks for the suggestion!

  • Hi John,
    You always send great and useful information and I do use that often, so I appreciate everything you do for your readers.
    Today’s macro exceeds all expectations… This is truly remarkable, what an excellent idea and implementation!!! Kudos. You have set the bar very high.

    Thanks again for sharing, now my files are going to look so cool and professional and easy to navigate that everyone is going to envy me and ask how do I get the gallery.
    Awesome indeed.

    Keep up the good work.


    • Hi Pablo,
      Thank you so much for the nice comment. I am grateful to have awesome readers like you, and I really appreciate your support. I’m happy to hear you will be putting this to good use and making your files look a little cooler. πŸ™‚

      Thanks again and have a good one!

    • Hi Luis,
      I have updated the file to work with chart sheets. However, hyperlinks do not work with chart sheets in Excel. This means the image of the chart sheet will not link to the actual sheet. This is a limitation of Excel. There are workarounds for this but it requires adding a macro to the workbook to select the chart sheet. I added further explanation in the Tips for Implementation section above. I hope that helps.

      • Hi Luis,
        I just added a macro to the file that can be used to select any Chart Sheet. The macro determines which image or textbox was pressed by the user, and selects the corresponding chart sheet. This way you can use one macro and assign it to all chart sheets. This macro assignment could also be added to the TOC_Gallery macro, so you do not have to assign it manually.

        Let me know if you have any questions. Thanks!

  • John,

    Great article and learning opportunity. Do you think this macro could be modified to also make TOC entries for Chart objects as well?


    • Thank you Gayle! Yes, the macro can be modified for Chart Sheets. However, there is a limitation in Excel that does not allow hyperlinks to be created for Chart Sheets. I updated the code in the file to accommodate Chart Sheets. You can download the new file above.

      The macro will NOT create hyperlinks to the Chart Sheets. The workaround is to create a macro that selects the Chart Sheet, then assign that macro to the image and label in the TOC gallery. I hope that helps.

  • I noticed you had this out there so I copied it. (hyperlink used TOC go back and forward) (Double checking how the dashboard created)

  • Hi Jon,
    I love this macro. However, for whatever reason, not all images are sized the same. For example, towards the end of my gallery, my second to last image is sized h:1.96″ and w:4.03″ and my last image is sized h:2.4″ and 4″. This makes it look very strange. Any thoughts?

  • After some testing, it appears to be related to how wide columns are in each sheet. Is there a way to standardize the size of the thumbnail regardless of column width?

    Different column widths in different sheets is fairly common in the work that I do.

  • Hi John, sometimes I hear myself saying “Hi, Welcome to Excel Campus, My name is John …”. Your Knowledge of Excel and VBA is quite impressive and I do appreciate your videos and also learn very much as well. Indeed, TOC Gallery was one of my favorite videos. Thank you for sharing your work. Hugs from Brazil πŸ˜‰

    • LOL! Thank you Vinicius! I’m slowly trying to brainwash everyone to dream about Excel Campus… πŸ™‚ Totally kidding. I really appreciate your support and happy to hear you are enjoying the videos. Thanks again!

  • There appears to be an issue with Pivot Tables. I get an Error 1004 Application-defined or object-defined error on this line “lRowHieght = lRowHieght + .Cells(1, lRowCnt).RowHeight” whenever I have a Pivot Table on a worksheet. It loops through all the through to where lRowCnt is equal to 16385 when it gets the error and in actuality there are only 53 rows with data on the worksheet. Just wonder if you’ve run across an issue with Pivot Tables. I’m using Excel 2010.


  • Hi Jon,
    I think I might have an older version of the tab hound – I don’t see the really cool option to do the TOC gallery……………thoughts? Is there another add on I can get? Thanks.

  • Hi there,

    I am using Excel 2016. I created the TOC Gallery macro, but I have three issues:

    1. The back-to-TOC links do not show up.
    2. I cannot get the YES/NO pop up box to work.
    3. Now that I have the TOC Gallery macro in my personal macro workbook, whenever I close one of my workbooks,
    Excel stays open with a blank area where the spreadsheet cells would normally be.

    Can you help me fix these issues?

  • Got a question for anyone out there, I have always been able to attach a PDF or any kind of file in an email in my Yahoo Mail account and then be able to Right-Click and check it out before sending that email….now all of a sudden, that feature does not work….anyone got an answer to that

  • Jon,

    This tool is so simple to install and use, works PERFECTLY, and is a BRILLIANT idea. Just what I needed for the title page of my applications. I will be purchasing Tab Hound soon. Thank you so much for sharing this code!

  • Hi Jon,

    Thanks for attaching download file with the macro it’s a really good piece of work which has provided me with wonderful ideas of how to present some of my working files.

    I’m being a bit obtuse but I couldn’t find a way to create the same macro from scratch? Is there another link to a video or do I just modify your currently file?

  • Hi,

    Very Nice code, and great output.

    just one challenge I cant seem to find a workable way around.

    how can the hyperlinks on the TOC Gallary/Table of contents be set to unhide hidden sheets that are included in the list.

    I need to have all sheets hidden apart from the Table of contents when the workbook opens. The hyperlink clicked would then open a particular sheet, in which there would be a button/code/ hyperlink to redirect to save, hide the sheet and redirect to the TOC.

    Any help will be appreciated. (need it like yesterday – quite literally) πŸ™‚

  • Hi John,
    This TOC is absolutely amazing but i have a problem, mainly when i run the TOC macro all the slicers that I have in other sheets are resizing and it’s not OK. To have them back to their original size I have to zoom out the resolution in each sheet and after to zoom in. My file contains more then 40 sheets so it’s takes a lot of time to do this. How can be solved ? Also, another thing on TOC sheet all the screenshots are the same except one which is smaller and i couldn’t figure out why. Please advice how to solve these 2 issues. Thanks a lot in advance!

  • Hi John,

    I have another problem with the code. Sometimes, when I run it, i get the error:

    “Run-time ‘1004’:
    Microsoft Excel cannot paste the data.”

    I kindly ask for your reply!

    Thank you.

  • hey, thanks for the macro, it was working fine however, after a while it is giving me the ” β€œRun-time β€˜1004’: Microsoft Excel cannot paste the data.” error.

    and it only creates the first sheet thumbnail on the toc_gallery sheet. my file contains work related stuff and its 200 mb so I cant share.

  • Hi, John. I was lucky to find your website and thoroughly enjoyed the Index-Match course. I downloaded the TOC gallery but came up with an error message – that it cannot paste the data. When I went into the macro it points to this command line : .Pictures.Paste Link:=False Can you please let me know how to correct this? Thank you.

  • this is my new favourite Macro! I am still in the early days of learning – so not too sure where to look for this answer.

    I have received an error message
    ‘Run time error ‘1004’:
    Microsoft Excel can not paste the data

    would you be able to translate what may have gone awry for us & where we need to look / what to do to get pass this error?


    • I am getting this same error. Can you review and update the code due to this error Jon Acampora? It is a shame that I cannot use such an awesome script due to this error.

      • Hey Sherry

        I think we found it occurs if you try to rush, or click on another window & not let it run it’s course. We found if we were patient & waited it tended to work…..

        that helps?!

        • I will try that and let you know. I had a project requiring a TOC, so I just used one of the others Jon has available. Thanks for the response!

  • Your TOC Gallery is an awesome VBA Script. I have found it very helpful on many of the projects I develop for management. How would I add the option to display “Last Updated: MM/DD/YYYY” below each page name. For the current project the data (via PowerQuery) is updated in some sheets and not in others. Letting the user know this information would be very helpful. So this data would be based on the last modified date for that sheet. Can that be done?

  • Hi Jon
    Your TOC is awesome and I want to use in my workplace which would be fabulous but I hate to say that I cannot download anything and cannot addins as it must be restricted in my workplace by IT so what should I do as I love your TOC

  • I have downloaded this Table of contents file before but lost my computer. I was trying to download it again, but I am not finding the file. It looks as if it has downloaded the file, going through the process, but I cannot find it. Help, please. I love this function.

  • Nice one, I have always done the simple list but never did the one with screenshots. Tried to download your sample file but looks like the link is broken or something – I get that funny line
    [wpfilebase tag=file id=106 tpl=’simple_excel_file’ /]

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter