The Missing Link (Part 1)

Every good discipline needs a missing link. Evolutionary biology had a missing link between humans and the ‘lower’ animals. Physics has a missing link between quantum mechanics and general relativity. The Information Visualization community discovered the Missing Link Between Information Visualization and Art.

Now we discovered the missing link in Excel Data Visualization.

As we can see in Wades winning Bank Dashboard we can greatly increase the amount of information that can be included in a dashboard by using sparklines in an overview table

image

Wades shows the shape of thousands of figures. This overview allows you to identify trends and patters. But as we learned from Ben Schneidermann, the great pioneer in Computer Visualization, this is not enough. Ben formulated the Information Seeking mantra for effective interactive computer visualization:

Overview first, zoom and filter, then details-on-demand.

The data dense overview in Excel causes a strong desire to have details of these small charts just one mouse click away, details-on-demand.

Stephen Few detailed this principle in his book Information Dashboard DesignDashboard design principle diagram

Jon Peltier showed us in recent posts techniques to dynamically provide details in Excel in the form of a detail chart. The problem with these techniques is that you have to select the object you want to have detailed via combo boxes and list boxes.

Wouldn’t it be nice to get the details by following a hyperlink in Excel? A hyperlink is the natural way to effortlessly do the overview -detail navigation in the web. We follow a hyperlink to see more detail about an idea and use the back button to go back to an overview page. A post on Tufte’s forum shows that this is not an uncommon request:

Jack posted : […]I’m very excited about using sparklines at work [… Has anyone played around with making the sparkline a hyperlink to a larger graph? I have mixed feelings about this as the sparkline should function just fine on its own- but the possibility of making it a link to a more complex version of itself is also tempting. Thoughts? Forgive me if this has been covered- I did a search on “sparkline hyperlink” and found no relevant results…

So the missing link, the missing Excel feature, is the ability to have a hyperlink in an Excel cell that sets cell values or passes names when the hyperlink is followed.  XLCubed introduced a new extended hyperlink formula that allows exactly this, passing the actual cell content as parameters to a target sheet.

Click on an in-cell sparklines (MicroCharts) to get the details for a specific KPI:

clip_image008


Follow the hyperlink of the variance cell to get the variance details for a specific KPI:

KPI dashboard

Don’t miss the next post! As this is such a useful thing for Excel Dashboards we have decided to give away a free version of the parameter hyperlink Add-In. In the follow up post, Antonio will give you the hyperlink bits and bites and show you some techniques how to use this new hyperlink formula in Excel.

13 Replies to “The Missing Link (Part 1)”

  1. “Jon Peltier showed us in recent posts techniques to dynamically provide details in Excel in the form of a detail chart. The problem with these techniques is that you have to select the object you want to have detailed via combo boxes and list boxes.”

    You don’t necessarily need to use combo boxes and the like for this purpose. If you have some programming and organizational skills, you could assign macros to charts, so when they are clicked on, they bring up another view of the data. Or even more sophisticated, you could assign code to clicks on specific chart elements (i.e., series or axes) and have the active chart change or make another view pop up. This requires having the dashboard open in Excel itself; I kno of no way to do this using the web versions of an Excel worksheet.

  2. The problem we encounter with client quite often is rather that they have an overview table that shows, let’s say, variance data for products and the user now want to follow a hyperlink on a particular product, that jumps into a detail sheet and passes some parameter, or – if the user has MicroCharts- he wants to click a sparkline to see a detail Excel chart on the same sheet. I know that you could program some VBA to solve this but, as you know, most business users are not to eager to code VBA

    So we devised a formula that creates a hyperlink that opens a sheet and set parameters. When you follow the hyperlink, it jumps to the sheet stored at linkLocation and set the parameters to at the specified parameter addresses in the target sheet.

    ParamLink([linkLocation], [friendlyName], [linkType], [parameterAdress1], [parameterValue1],…, [parameterAdress14], [parameterValue14])

    Samples:

    A hyperlink that jumps to the sheet ‘LinkTarget’ and sets value ‘Car and Bike Stores’ at the parameter reference LinkTarget!A1.

    =ParamLink(“LinkTarget!A1″,”Jump to Target Sheet and pass ‘Car and Bike Stores'”,1,”LinkTarget!A1″,”Car and Bike Stores”)

    A hyperlink that shows a MicroChart that ,when clicked, dynamically updates a name with the current row.

    =ParamLink (,MicroColumns(D3:O3,2),1,”ChartRow”,”=”&ROW())
    So I think the formula can be quite useful in Excel Dashboards.
    The ParamLink formula works perfectly in Excel and does no t need to jump into an web page, but if you publish the Excel workbook to the web with the WebEdition the whole worksheet become a web application and ParamLink actually jumps into a detailed web pages (well a web version of the detail sheet).

    As mentioned, more details about the Missing Link Post Part 2.

  3. Jon,

    Just to make myself clear, we created a formula that works like the Excel hyperlink formula BUT can pass parameters and set names. Once you have such a formula you can’t imagine living without it. It’s unbelievable versatile for Excel reporting and dashboarding . Well more in the promised later post. 🙂

    Andreas

  4. Andreas – Is this some kind of UDF you’ve built into MicroCharts? Very interesting, especially if a cell can contain both a MicroChart graphic and a hyperlink. Does the cursor indicate a hyperlink?

    Maybe I just have to wait for part 2.

  5. This sounds great, but do I have to send a copy of the Add-in with every spreadsheet I make, with instructions for setting up and activating the add-in? Or can I instead include the add-in in the spreadsheet, with a macro button instructing the recipient to “click here to activate links!”?

  6. You state above that the add-in can be bundled with the sheet provided you have MicroCharts SR1 installed. I downloaded the trial version of your software and am evaluating it. Is this function not in the trial version? Is it indeed embedded with the latest version of MicroCharts being sold?

    Love this concept, but don’t wish to have to walk users through adding the add-in to their Excel environment or create a custom installer.

    Please advise and thanks.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.