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

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

the-table-of-contents-image-gallery-for-excel

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.

table-of-contents-gallery-clickable-image-thumbnail-links

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-table-of-contents-macro-vba-screenshot-of-vb-editor

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

the-table-of-contents-gallery-feature-for-the-tab-hound-add-in

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

tab-hound-table-of-contents-gallery-menu-customize-options

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.

tab-hound-table-of-contents-style-options-side-by-side

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.
    Sheets("Chart1").Select

    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!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 56 comments
gp - October 26, 2017

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?

Reply
SANDY LARUE - October 24, 2017

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

Reply
Patty - March 22, 2017

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.

Thanks

Reply
    Jon Acampora - March 29, 2017

    Hi Patty,
    It might be due to hidden rows. Do you have hidden rows on the sheets that contain pivot tables?

    Reply
      Patty - March 29, 2017

      Yes, I think they do have some some hidden columns. That’s probably the issue. Thanks

      Reply
Vinicius Budny - December 6, 2016

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 😉

Reply
    Jon Acampora - December 10, 2016

    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!

    Reply
Tage - October 28, 2016

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.

Reply
Tage - October 28, 2016

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?

Reply
wilson - October 20, 2016

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

Reply
linda - October 13, 2016

Hi Jon,
Thank you. I am great to enjoy learn it.

Reply
Gayle - October 10, 2016

John,

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

Thanks!

Reply
    Jon Acampora - October 11, 2016

    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.

    Reply
Luis - October 8, 2016

Hi, This is great but it doesn’t work with chart sheets

Reply
    Jon Acampora - October 11, 2016

    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.

    Reply
      Jon Acampora - October 11, 2016

      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!

      Reply
Pablo Baez - October 7, 2016

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.

Pablo

Reply
    Jon Acampora - October 11, 2016

    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!

    Reply
Frank Tonsen - October 7, 2016

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.

Reply
    Jon Acampora - October 7, 2016

    Hi Frank,

    If possible, could you send me a file that you are getting that error on? jon@excelcampus.com 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!

    Reply
Pinky - October 7, 2016

Can i use these functions in MS Excel 2007

Reply
Suyog Patil - October 6, 2016

Great Job! I think it is what we require always.
Thanks for these efforts and share.

Reply
Giang Pham - October 6, 2016

It would be better if you can make a button in each sheet to go back to your toc gallery sheet!

Reply
    Jon Acampora - October 7, 2016

    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!

    Reply
      Giang Pham - October 7, 2016

      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

      Reply
        Jon Acampora - October 13, 2016

        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!

        Reply
Giang - October 6, 2016

Thank you John, you are amazing!

Reply
MF - October 6, 2016

Brilliant idea! Thanks for sharing.

Reply
Joel Kimzey - October 6, 2016

Will the Tab Hound work on a Mac?

Reply
    Jon Acampora - October 6, 2016

    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!

    Reply
Jim C - October 6, 2016

cant download the file John

Reply
Super Joke - October 6, 2016

The button “Back to TOC Gallery” does NOT(!) exists on EVERY (!) worksheet.

Reply
    Jon Acampora - October 6, 2016

    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.

    Reply
      Super Joke - October 6, 2016

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

      Reply
Bob Umlas - October 6, 2016

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!

Reply
    Jon Acampora - October 6, 2016

    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!

    Reply
Doug - October 6, 2016

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.

Reply
Peter - October 6, 2016

Genius! Jon, what a great concept and even better execution!

Reply
Jeaux - October 6, 2016

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?

Reply
    Jon Acampora - October 6, 2016

    Hi Jeaux,

    That is frustrating. Sorry to hear that. How many sheets are in your workbook? And what version of Excel are you using?

    Reply
      Jeaux - October 7, 2016

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

      Reply
Elba - October 6, 2016

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!

Reply
    Jon Acampora - October 6, 2016

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

    Reply
Marek - October 6, 2016

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

Reply
Rod Chapman - October 6, 2016

I bought Tab Hound last year and have enjoyed using it. How do I get this updated version?

Thanks.

Reply
    Jon Acampora - October 6, 2016

    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!

    Reply
Peter Buyze - October 6, 2016

Hi Jon, an excel article. I published a G+ post about it here https://plus.google.com/+PeterBuyze/posts/8Qx6cgK16wy

Reply

Leave a Reply: