Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Hyperlinks to Charts.

Hyperlinks to Charts

Written by Allen Wyatt (last updated August 21, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


1

Excel allows you to create hyperlinks, either to resources on the Internet or to cells in other worksheets. Excel won't, unfortunately, allow you to create hyperlinks that display chart sheets in your workbook. If a worksheet includes a chart object (the chart was created as an object in a worksheet), then you can create a hyperlink that displays the worksheet on which the chart object is located. You cannot, however, use an actual chart sheet as the target of your hyperlink.

The way to work around this problem is to create a macro that actually displays the desired chart sheet. You can then assign the macro to the Quick Access Toolbar. You would use a macro such as the following:

Sub GotoChart1()
    Sheets("Chart1").Select
End Sub

This is a very simplistic version of a macro that displays a specific chart sheet. In this case, the chart sheet is named Chart1; you can change the name to reflect your needs. You can create a macro like this for each destination chart sheet in your workbook.

An alternative is to enhance the macro so that it accepts a parameter indicating the name of the chart sheet you want selected. For instance, consider the following macro:

Sub GotoChart2()
    Sheets(ActiveSheet.Shapes(Application.Caller) _
      .TopLeftCell.Value).Select
End Sub

With this macro in place, go back to your worksheet and select the cell where you want your hyperlink. Type the name of the chart sheet, and format it to look like a hyperlink. (Blue, underlined text, or formatted as desired. You are simulating a hyperlink; you are not creating a real one.)

Using the legacy form controls on the Developer tab of the ribbon, create a label object within the same cell, and format the label to not be visible. You do this by modifying the properties of the object so it has no lines, no text, etc. Then, right-click the label object and use the Assign Macro choice to assign the GotoChart2 macro to the object.

Now, when someone tries to click the "hyperlink," they are really clicking the invisible label object, and the macro is being executed. The macro determines the name of the object that called it (Application.caller), figures out what cell the object's top-left corner is in, and grabs the value of that cell. The value is then used as the destination name for the desired chart sheet.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1121) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Hyperlinks to Charts.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Avoiding Rounding Errors in Formula Results

Some formulas just don't give the results you expect. Sometimes this is due to the way that Excel handles rounding. ...

Discover More

Editing a Hyperlink

Excel will cheerfully keep track of all sorts of hyperlinks for you. If you want to change the hyperlink in some way, ...

Discover More

Working with Multiple Printers

If you have multiple printers accessible to your computer, you may need a way to quickly print your worksheet on a ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Adding Data Labels to Your Chart

Adding labels to a chart can make the information presented in the chart more understandable. Excel allows you to add ...

Discover More

Preparing a Chart Sheet for Printing

One type of chart that Excel allows you to create is one that occupies an entire worksheet. When it comes time to print ...

Discover More

Showing Elapsed Time in a Graph

Working with elapsed times can be a bit tricky in some situations. One such situation involves the displaying of elapsed ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five more than 3?

2021-08-21 11:17:46

J. Woolley

There are certain issues when a hyperlink is created using either the Insert > Link (Ctrl+K) dialog or the HYPERLINK function:
+ A hyperlink cannot reference a worksheet without identifying a cell, range, or name on that sheet
+ Activating such a hyperlink will change the worksheet’s previously selected cell or range
+ A hyperlink cannot reference a chart sheet because a chart sheet has no cells
+ A hyperlink referencing a hidden sheet will fail silently when activated
These issues are resolved by the SheetNameLink function in My Excel Toolbox. Here is its syntax:
=SheetNameLink(Sheet_Name, [Friendly_Name], [Screen_Tip])
where Sheet_Name is like [file]'sheet' or [file]#'sheet' or 'sheet' or #'sheet'. This function creates a hyperlink to any worksheet or chart sheet in a workbook. The hyperlink does not address a cell; therefore, the sheet's previous selection is not altered. SheetNameLink is fully described in UseSheetName.pdf, which is available here: https://drive.google.com/open?id=1A0kHot1tggfGBX7O8FRaMg5qu5jnWbdC
See https://sites.google.com/view/MyExcelToolbox/


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.