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.

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!