License

Licensed under a Creative Commons License

Disclaimer

All data and information provided on this site is on an as-is basis and for informational purposes only. We do neither guarantee for accuracy, completeness, suitability or validity of information on this blog nor be liable for any errors in this information or any damages arising from its use. This blog may contain links to other web sites. We do not have any control over the content contained on those sites.

Sunday, November 04, 2012

Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard

Bluffing the “Beer Prices at Oktoberfest” Tableau Dashboard with Microsoft Excel using Roll Over Tooltips and Web Actions

Recently we had an article visualizing the beer prices and beer price development at Oktoberfest with a Tableau dashboard: O'zapft Is!

Oktoberfest is long over already and there is peace in the valley of Munich again. Having said that, I would like to come back to this visualization once more. We had a couple of posts here on Clearly and Simply showing how to bluff Tableau’s great interactive features with Microsoft Excel:

Drawing on this tradition, today’s article presents an Excel workbook emulating the Tableau Oktoberfest Dashboard. The technique behind this bluff is a very clever approach my blogging colleague Jordan Goldmeier published in several articles over at his Option Explicit VBA Blog.

A UDF can usually not change values or other properties of other cells. However, if they are used within hyperlinks, they can

The UDF is not only fired when clicking on the cell, but also when hovering over it with the mouse

An impressive and very innovative idea by Jordan which offers a variety of new possibilities. In his latest post, for one, Jordan used this technique for an excellent visualization of an Interactive Electoral Scoreboard of the United States:

Fantastic work, Jordan!

If you want to learn more about the details of this technique, please refer to one of Jordan’s articles:

Now let’s try to apply this technique to the Oktoberfest Dashboard. Here is the step-by-step how-to:

Step 1 – The Data

This is the easy part. We already have the data available from the original Tableau article (O'zapft Is!). We simply insert 2 worksheets ([data tents] and [data_prices] into a new Excel workbook. The sheet [data_tents] contains the names of the tents, the seats, the breweries and the URLs of the tents. The sheet [data_prices] holds all the prices for all tents and all beverages from 2002 to 2012.

Step 2 – Prepare the Roll Over

First we create a new worksheet (the dashboard) and format it to have a squared cell grid, i.e. cells which have approximately the same width and height. Next, we insert the image of the Oktoberfest:

The Interactive Electoral Scoreboard uses a map of the United States consisting of freeform shapes (one shape per state). To determine the current state (i.e. the shape the mouse is currently hovering over), Jordan developed a very clever technique combining Excel worksheet formulas with some VBA User Defined Functions. I will not go into the details here. To find out more about the approach, please refer to Jordan’s article and workbook.

The “map” of the Oktoberfest does not consist of several shapes. It is just one single image inserted into the Excel worksheet. Hence, we have to replace Jordan’s technique of calculating the correct shape / state by a manual setup.

First we number all 14 (large) tents visible on the map:

Next step is the laborious part of the implementation: we manually insert the numbers of the tents according to their positions on the map into the cells of the worksheet:

The result is a lookup matrix we will later use to identify which cell of the grid belongs to which tent. Since we will need the cells on the dashboard for the HYPERLINK formulas, we move this lookup matrix of tent positions to another worksheet ([control]):

Agreed, this isn’t 100% exact, since the cell grid is not detailed enough to exactly cover the sizes of the tents. However, it is close enough for our purposes.

Step 3 – The Control Worksheet

Besides the tent position matrix (see step 2 above), some more preparation work is necessary on the [control] worksheet, like

lists and target cells for the drop downs (year and tent) on the dashboard

a formula to calculate the position of the selected year and tent in the data

a formula to retrieve the URL of the selected tent

a cell range with all labels and formulas we want to show in the tooltip

a cell range to calculate the relevant data series for the band chart

INDEX, VLOOKUP, MATCH and a couple of more complex array formulas, but nothing really new under the sun.

Finally we define a couple of names for some cells on worksheet [control], like the index and name of the current tent, the URL and the range containing the tooltip content.

Step 4 – The Dashboard

Step 4.1 – The Hyperlinks

First step of setting up the dashboard is defining a name for the cell range with the tents beneath the image. This range has exactly the same size as the lookup matrix for the tent positions we created in step 2. In my example workbook the name of this range is “myWiesn” (for your understanding: we Bavarians call the Oktoberfest “Wiesn”…).

This is the point where the hyperlink trick of Jordan’s rollover technique comes into play. We insert the following formula in the first cell of “myWiesn”:

where control!I6 is the reference to the upper left cell of the tent position matrix on worksheet [control] and Define_Hyperlink is a User Defined Function we will create in step 5. Finally we copy this formula to all cells of “myWiesn.”

Step 4.2 – The Shapes

We need two textboxes on the dashboard:

The first one (called “shpClickBox”) has the same size as the cells and is formatted to be invisible (no fill color, no line color). It is used to handle the web actions when clicking on them

The second one (called “shpToolTip”) is linked to the cell range on worksheet [control] containing all information to be displayed in the tooltip

Step 4.3 – Everything else on the Dashboard

The usual suspects: 2 combo boxes to select a year and a tent and a band chart (see also: An Underrated Chart Type: The Band Chart) using the data series we calculated on the sheet [control] in step 3. I guess I do not have to explain this in detail.

Step 5 – The VBA

You may expect a lot of very complicated VBA code to make this work. However, the VBA is pretty simple. In fact it only consists of 5 subs / functions and only 39 (!) lines of code in total.

Here is a short description of those 5 subs:

In the event driven Worksheet_SelectionChange sub of the worksheet [dashboard], the tooltip shape is set to be invisible if the user clicks some cell outside of the map.

The User Defined Function called in the HYPERLINK formulas (see step 4.1) checks first if the cell value is greater than 0 (i.e. the cell beneath the image is a tent). If so, it updates the cell containing the current tent index, positions the click box shape above the cell and positions and shows the tooltip shape. If not, it makes the tooltip invisible. Finally it calls another procedure: SetHyperlink

SetHyperlink adds / updates the hyperlink to the current cell at the top right of the dashboard

ChangeTentDropDown is hooked up to the combo box for selecting a year and simply makes the tooltip invisible and calls SetHyperlink

OpenURL is called when the the user clicks on the click box shape and simply opens the browser with the URL of the current tent

If you are interested in more details, please have a look at the VBA of the workbook (download link see below). If you have any questions, please leave me a comment.

That’s it.

With Tableau, creating the dashboard only took a few minutes. It is by far more work to implement this in Microsoft Excel, but it isn’t impossible. If you can’t get your hands on Tableau, you can still create create an interesting and highly interactive dashboard with Microsoft Excel.

Comments

Bluffing the “Beer Prices at Oktoberfest” Tableau Dashboard with Microsoft Excel using Roll Over Tooltips and Web Actions

Recently we had an article visualizing the beer prices and beer price development at Oktoberfest with a Tableau dashboard: O'zapft Is!

Oktoberfest is long over already and there is peace in the valley of Munich again. Having said that, I would like to come back to this visualization once more. We had a couple of posts here on Clearly and Simply showing how to bluff Tableau’s great interactive features with Microsoft Excel:

Drawing on this tradition, today’s article presents an Excel workbook emulating the Tableau Oktoberfest Dashboard. The technique behind this bluff is a very clever approach my blogging colleague Jordan Goldmeier published in several articles over at his Option Explicit VBA Blog.