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.

The Table of Contents Gallery Macro

The file that contains the macro can be downloaded here.
excel-2016-file-icon-16x16Table Of Contents Gallery.xlsm (255.8 KB)

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 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.

  • 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,

    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 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?

  • 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!

  • 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

  • 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?

  • 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.

  • 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 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!

  • 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 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?

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

  • 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.

    • 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!

  • 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 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? 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!

    • 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 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!

    • 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.

  • 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!

  • 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.

  • 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. 🙂

  • 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 very excited. Thank you for another dose of knowledge. Certainly the possibilities presented here Excel will be useful in my work.

    • 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!

Generic filters
Exact matches only
Filter by Custom Post Type


Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...