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:
Bluffing Tableau Actions with Microsoft Excel
Bluffing a Visual Cross-tab with Excel
The Next Level of Interactive Microsoft Excel Dashboards
Microsoft Excel Site Catchment Analysis
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.
Stealing Jordan’s idea, today’s post shows how to recreate the Oktoberfest Beer Price Tableau dashboard with Microsoft Excel, as always including the Excel workbook for free download.
The Challenge
O'zapft Is presented a Tableau Dashboard visualizing the prices of beer and other beverages and the beer price trends at Oktoberfest, including
- a map displaying tooltips with beverage prices and additional information about each beer tent and a selected year in the tooltips
- a URL action opening a link to the tent’s website on muenchen.de
- a band chart visualizing the beer price development of the selected tent since 2002 within the total range of the beer prices across all tents
The Tableau folks liked my little dashboard and selected it as the “Tableau Viz of the Day” on September 26, 2012: Beer Prices at Oktoberfest.
Today’s challenge is re-creating this dashboard with Microsoft Excel. In the end, the Excel dashboard will look like this:
To be more precise, the Excel Dashboard should not only look like the Tableau Viz, it should also have the main interactive features Tableau provides more or less natively:
Tooltip when hovering over a tent
Open a link in a web browser after clicking on a tent
And of course the option to select a year and a tent in a dropdown (combo box) as well as the band chart.
The Technique
The heart of this solution is Jordan Goldmeier’s “Roll Over Macro Execution” technique described in this blog post: How to Create a Rollover Effect in Excel.
The basic idea is stellar: Jordan uses a User Defined VBA Function inside a Excel HYPERLINK function like this:
=HYPERLINK(myUserDefinedFunction(),"")
There are a few interesting things which were totally new to me
- I didn’t know you can use a UDF within a hyperlink
- 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:
and / or to Chandoo’s post featuring this technique:
Interactive Dashboard in Excel using Hyperlinks
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”:
=IFERROR(HYPERLINK(Define_HyperLink(ROW(P9),COLUMN(P9),control!I6),""),"")
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.
The Download Link
Here is the workbook for free download:
Download Beer Prices at Oktoberfest (Microsoft Excel 2007/2010 workbook, 1,042.4K)
Acknowledgements
Special thanks go of course to Jordan Goldmeier of the Option Explicit VBA Blog for inventing and sharing this technique.
Excellent work. Kudos, Jordan!
What’s next?
That’s it with Oktoberfest. I promise. At the very least for this year. I am planning a couple of new posts on something completely different in November for both tools, Excel and Tableau.
Stay tuned.