Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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

by Allen Wyatt
(last updated February 14, 2015)

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.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1121) applies to Microsoft Excel 2007, 2010, and 2013. 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

Embedding TrueType Fonts in E-mails Composed in Word

When you use Word as your e-mail editor, it allows you to format the text of your e-mail messages using tools you are ...

Discover More

Editing Graphic Objects

Want to change the way that a graphics object appears in your worksheet? You need to edit it, then, using the techniques ...

Discover More

Using an Exact Number of Digits

Excel allows you to format numeric data in all sorts of ways, but specifying a number of digits independent of the decimal ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Negatives in Pie Charts

Pie charts are a great way to graphically display some types of data. Displaying negative values is not so great in pie ...

Discover More

Plotting Times of Day

Got a chart created from your worksheet? You can plot times of day in the chart if you apply the simple techniques in this ...

Discover More

Unselecting a Chart Item

When formatting a chart, you select elements and then change the properties of those elements until everything looks just the ...

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}] in your comment text. You’ll be prompted to upload your image when you submit the comment. 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 9 + 4?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


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.