skip to Main Content

                            image

Hyperlinks Are a MUCH Better than Making the User Find the Right Sheet Tab         

Sorry About Last Week Smile

Yeah, we were a bit “light” on new material here on the blog last week.  That happens when I have four straight days of presentations out of town (more on PASS BACON later).

Inspired by Kasper’s Book!

I was reading Kasper’s new book on the plane (yes, I was home less than 24 hours, and am now back on the road), and its singular focus on building a full-featured set of dashboards inspired me.

It is clearly time for a mega-post on hyperlinks!

First Usage:  a Menu Sheet

As pictured at the top of this post, hyperlinks are a great way to construct a “menu” sheet.

Not only is this a BIG convenience for the “consumers” of your workbooks, but it makes the whole thing “feel” much better too.  More professional, more like an application and less like a spreadsheet.

Yes, hyperlinks can link to other locations in the workbook!  It’s easy in the Insert Hyperlink dialog:

 

image

Select “Place in this Doc,” Pick the Sheet, And Even Type the Cell You Want to Jump To!

Explicitly Set the Font!

By default, when you click a newly-created link, it takes on that old 1990’s “visited” color.  You know, this one:

image

This is ugly.  Avoid this.

But if you go ahead and set a font color, that will NOT happen:

image

I Explicitly Set the Font Color to My Preferred Color, and that Prevents the Ugly
Purple “Visited” Appearance Later On.

Also, if you don’t want the underlined format, you can go ahead and clear that too.  The link still works.

Set the column width!

If your link “spills over” the cell boundary, when the user clicks the link outside of that boundary, the link won’t work:

image

Watch Out For This Problem – Resize the Column or
(Gasp!) Merge Some Cells to Avoid It

Set the Screentip!

By default, when you hover over the link, you will get the destination address, which is UGLY:

image

Ugly Tooltip on Hover

But you can fix that in the Hyperlink dialog:

image

Setting the Screentip, Step 1

image

Setting the Screentip, Part 2

image

Results of Setting the Screentip

Part 2 on Thursday!

Come back Thursday for more hyperlink, cross-worksheet navigation goodness Smile

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 6 Comments
  1. I “bought” Kasper’s book on March 1st this year, but have yet to see it …. but thanks for sharing your take on part of it … hopefully mine won’t be too far behind!

  2. Rather than resizing columns or merging cells to get the Hyperlink to cover the full length of the text – just select all relevant cells when you’re inserting your Hyperlink – that way all relevant cells are linked without having to change the pages formatting.

  3. Sorry – something else I ought to have suggested as well (which is especially handy in a big workbook), is add a Hyperlink onto each worksheet to take the user back to the Menu Sheet to allow them to navigate around the workbook more easily.

  4. Hi

    Instead changing color and other features link by link, you better modifify the hyperlink and visited hyperlink styles.

  5. We often use this also within PowerPivot & Excelfiles on Sharepoint to jump between PowerPivot reports or pages. But this can cause some trouble. We had the issue that the links first worked in Excel and Sharepoint, than somebody else opened the Excelfile and after that the links were still ok in Excel but no longer on Sharepoint.

    While Excel also works with “short” links, Sharepoint or ExelServices needs the full path. You should also have the “Update links on save” deactivated in your advanced Exceloptions.

  6. Hey, I must be getting better because many of the tips in these past two posts I’ve already been doing. Haha, yay for me! 😀

Leave a Reply

Your email address will not be published. Required fields are marked *