Clearly and Simply proudly presents a new guest article: Michael Martin of Business Information Arts, Tableau Partner, Tableau Certified Consultant and leader of the Toronto Tableau User Group shows us how to visualize Network Graphs using Tableau Software. Enjoy.

Network Graphs can help us see and measure relationships and connections between people, places, and things over time. This can be expressed as identifying, measuring and understanding process flows, the mix of products in shopping carts, social network and email traffic, affinities and interests people share (or don’t share), and the “hierarchies of influence” in business and / or social systems by identifying who or what triggers events, and the impacts they have on others.

Today’s post describes how you can build Network Graphs using Tableau Software versions 6 or 7, including a detailed how-to tutorial and some information on the background of Network Theory.

What is a Network Graph? A Picture says more than 1,000 words

What are Network Graphs for? Here are just a few practical examples:

Contacts Between Philanthropic Twitter Users

The Organization of Hierarchical Communities

The Path to Products People Buy

Last, but not least a Network Graph built in Tableau:

Association of Food Groups, Brands and Flavors

Tableau’s Out of the Box Network Graphs

Tableau Desktop is one of my favorite data analysis and reporting tools. Other excellent products such as Visokio Omniscope support network graphs as one of a wide number of supported view types. But what I have always found so impressive about how Tableau is engineered is how various “loosely coupled” features can be re-assembled to create new ones. Examples of this include double axis graphs, bullet charts, and the support for bubble graphs and tree maps in the upcoming Tableau 8 release (Q1 of 2013). Tableau is a fabulous “Swiss Army Knife” for visualizing data.

Build Network Graphs with Tableau – The How to

My implementation of network graphs in Tableau leverages features that have been around since version 1, the circle and the line mark types, and support for scatter plots – and ability to draw double axis graphs (hackable for years before being officially supported as “combo charts” in version 6). With a little bit of data preparation, this is all you need to draw a network graph in Tableau.

For me, the fun really starts when other great Tableau functionalities (actions, parameters, page field animation, filtering, highlighting, size by, color by, table calculations to name just a few) are brought into play.

Data Preparation

Key to my implementation is data preparation given the requirement to connect elements in the form of a transaction and lay out the design of the network graph in the Tableau view:

If you want to follow the step-by-step below using my example data, here is the Excel workbook with the data for free download:

Download Network Graphs Example Data (Microsoft Excel 2007/2010, 14.3K)

Demo – A simple Network Graph

Here’s a simple network graph based on the example data.

The Step-by-Step Tutorial

Step 1 – The Basic Set Up

To get started, put the “Line Y” field to the Row Shelf and “Line X” field on the Column shelf. Tableau will automatically set the mark type to circle and render a basic scatterplot. The “Line Y” and “Line X” co-ordinates in the source data are visible via the field value headers.

Step 2 – Dual Axis

Add the “Circle Y” to View on the rows shelf as a double axis, and synchronize the two Y axes (right click on the axis, and click on “Synchronize axis”).

Step 3 – Multiple Mark Types

The next step is to format the Tableau Marks Card to show "Multiple Mark Types“. Then cycle to the "Circle Y” mark and set mark type as "Pie“. Drag the "Node Name" field to the Label Pill. You can optionally color the “Node Name” field by ID by dragging ID to Level of Detail Shelf Resize the Pie mark to make larger – each pie slice represents a Transaction ID.

Step 4 – Connect the Dots

Then cycle to the "Line Y" Mark. Drag the “ID” and “Relationship” fields to Level of Detail shelf. Set the mark type to “Line”. Tableau will connect the dots – and you have a simple Network Graph. Resize the Line Y series to make lines thinner and color the lines as desired.

Optionally, you can format the canvas to include grid lines and turn brushing on in the Color Legend. Uncheck the “Show Header” in the “Line X”, “Line Y”, and “Circle Y” fields in the row and column shelves.

Step 5 – The Tooltips

If you hover over nodes in the view with the mouse, you’ll see Tableau generated tooltip text:

We can do a few things to make the Tooltips more meaningful. With the “Line Y” Mark selected in the Marks Card, place the “Relationship” and “Sales vs Sales For Display” fields in the Level of Detail Shelf.

The “Sales For Display” field is a calculated field that I will describe shortly.

Then cycle to the “Circle Y” field in the Marks Card and place the “Total Sales”, “InDegree”, “OutDegree”, “Node Name”, and “ID” fields in the Level of Detail Shelf.

Step 6 – Calculated Fields and the Tooltips again

The next step is to define a simple calculation which I named “Sales vs Sales For Display” in my workbook:

IF Sum([Sales])=0 THEN Sum([Sales For Display]) ELSE Sum([Sales]) END

The output of this calculation is the value of either the “Sales” or the “Sales For Display” data fields associated with a single transaction. My implementation needs this calculation because without this calculation the value of the “Sales” field will change to zero (or change from zero to the value of the sale) as you pass the halfway point between two connected nodes when traversing the line between connected nodes with the mouse.

If you take another look at the source data, you’ll see that the value of the “Sales For Display” field is the same as the value for “Sales” in the previous row for a given transaction ID. As Tableau aggregates the “Sales” and “Sales For Display” metrics by Transaction ID, the value of the calculation will change as you pass the halfway point in the line connecting the nodes in the transaction with the mouse.

Then edit the tooltip text is shown in the figure below.

The first two lines will appear when the mouse hovers over a line connecting two nodes (the “Line Y” field). The remaining four lines will display when the mouse hovers over a Node (the “Circle Y” field).

Step 7 (optional) – A Summary Table

You can optionally make a summary sales table that sums the “Sales” field by the “Node Name” field which includes creating a calculation named “Sales Label” that suppresses the display of zero values in the “Sales Field”.

If you look at the source data, you’ll see that the “Relationship” field is encoded to show who the seller and buyer were. The value Ken à Bill describes a transaction where Ken was the seller and Bill was the buyer. Ken is listed as the “Initiating Person” and Bill is listed as the “Secondary Person”. The “Direction” field explains this in another way; from Ken’s point of view as the “Initiating Person”, this is an “Out Degree” connection. From Bill’s point of view as the buyer, this is an “In Degree” connection.

Step 8 (optional) – Filter Actions

You can optionally define a Tableau Filter Action to filter data that will appear in the “Summary Sales Table” based in which transactions in the view are selected with the mouse. In my implementation, the Action is set to run “On Select” based on the values of the “ID” and “Node Name” data fields.

For more information on how to use Actions in Tableau, have a look at this how-to tutorial: The Power of Tableau Actions.

Step 9 (optional) – Animated View

You can optionally animate the view by dropping the “ID” field into the Pages Shelf and inserting the Pages Shelf into the Dashboard by selecting “Current Page”.

After you start the Page Player, transactions will come into the view sorted by the Transaction ID number. With the use of calculations and “Page History” settings, you can create very interesting animated views of transaction oriented data.

The Result

Here is the example packaged Tableau workbook for free download:

Download Prototype Scene Graph (Tableau 7 Packaged Workbook, 62.3K)

Network Metrics

The “Network Density” metric is commonly calculated as the number of actual possible connections divided by the number of possible connections. There are 9 actual connections and 56 possible connections in the example data, resulting in a Network Density value of .1607 which depending on the context could be considered to be low or high.

The “Network Centralization” metric tells us how “centered” the network is around the member(s) of the network with the highest number of connections. In a network with three members, this metric is of little value – but in a network with thousands or millions of connections, knowing the people or persons the network is centralized around is meaningful to our understanding of the network. In the data driving my implementation, Jane is involved in four of the nine transactions which would be commonly calculated as (4 / 9) = .444. This would be considered a high value in most cases, so you could say that the total network is highly centralized (around Jane).

The “Network Homophily” metric describes the degree that connected nodes share similar characteristics - i.e. are connected nodes largely alike? The richer the source data is, the more important and interesting this metric can be as the row count increases. This metric is of particular interest to marketers.

Switching to Node specific metrics; the “In Degree” metric is the count of in-coming connections to a Node from other nodes in the network. The “Out Degree” metric is the count of outgoing connections from a single node to other nodes in the network. These two metrics are often used to help analysts and marketers understand how “social” products within particular retail categories are with products in similar or different retail categories.

The “Betweeness” metric helps us understand how important a particular node is to the overall “performance” of the network from the perspective of a particular metric or class of metrics. The example data describes connections through “Sales”. If Sally and Roger had made huge sales to each other or to Jane, removing Jane from the network would lower the “total value” of the network because Roger and Sally are in the network by virtue of their relationships to Jane.

The “Closeness” metric helps us understand how useful a given network member is for getting a message from outside the network circulated within the network as soon as possible. If an outside person wanted to circulate a message within the network described in the example data, the go-to person is Jane because she is directly connected (one hop away) to five other network members, who in turn are a hop away from the remaining network members (Roger and Ken).

Although the “Betweenness” and “Closeness” metrics are important, they don’t necessarily predict the ranking of members in a network by the governing metric (in this example, sales). The top seller in the network is Wayne by virtue of a 20k sale to Marjory. If you size the “Node ID” field by “Sales”, you would immediately realize how important Wayne is to the network from a sales performance point of view.

The “Eigenvector Centrality” metric explains the degree to which a given node is connected to the most important node in the network. In a given network, an “introverted” member with low “in degree” and “out degree” metrics and has little or no “betweenness” or “closeness” could in fact be quite important due to its influence on members who are very well connected. If Jane is heavily influenced by Sally’s purchasing recommendations, Sally’s role in shaping the profile of the network is important given Jane’s position in the network as the most important buyer in the network.

Recommended Further Resources

There are many great resources on and off the web for learning about network theory and metrics. Here are a few that I’ve found helpful, with apologies to other great resources that I haven’t encountered yet.

University of Maryland Human / Computer Interaction Lab contains links to many interesting data visualization projects and white papers related to network data visualizations.

Node XL is an Excel add-in for visualizing network graphs.

Analyzing Social Media Networks With NodeXL by Derek Hansen, Ben Schneiderman and Mark Smith, published by Morgan Kaufman.

Gephi is an open source tool for visualizing network graphs.

Aaron Koblin provides a great visualization of airline flight patterns over North America.

About Michael

Michael Martin (email Michael) works internationally in a variety of business sectors that include market research, consumer packaged goods and retail, banking, hospitality, commercial construction, entertainment, governmental, and non-profit.

His project deliverables include business performance forecasts, strategic and operational case study white papers, operational dashboards and scorecards, associative and neural networks, customer / product segmentation and market-basket analyses.

Michael is a Tableau Partner, a Certified Tableau Consultant and leads the Tableau Toronto User Group.

Robert’s Note

A big time thank you very much to Michael for contributing this fantastic article. If you enjoyed what you have read, please drop Michael a line to say thank you by email (email link see above) or leave him a comment here.

Stay tuned.

On several occasions I thought (and stated) that I already published everything I have to say about Choropleth Maps with Excel.

Having said that, it seems as if “the ghosts I called I can’t get rid of now”. Recently someone asked me if I could provide an Excel Choropleth Map of Germany by zip codes. Careless and stupid boy I am, I answered “sure this is no problem”. What a misjudgment.

If you followed my previous Excel Choropleth Map articles, you know that it takes either an Enhanced Metafile of the map you can ungroup in Excel to get the shapes or at least an SVG file to transform it to an EMF file using e.g. Inkscape. 

And here is the roadblock I encountered: I simply couldn’t find a map of Germany by zip codes in one of the required formats. All I could find were ESRI shape files. After hours of searching for EMF and SVG files, I gave up, simply reversed my thinking and looked for a tool to convert shape files into SVG. 5 minutes later I had the solution.

Today’s post describes how to use Indiemapper, a free online tool, to transform shape files into SVG which can then be used for Microsoft Excel Choropleth Maps in the well-known way.

The Challenge

The Choropleth Map approach I used in all articles here requires ungrouped shapes in Excel. The easiest way of getting there is an EMF file you simply import into Excel and ungroup it until you have one shape for each region you want to visualize. If you don’t have an EMF file, this article shows you how to transform an SVG file into EMF format.

But what if you can find the map you need neither in EMF nor in SVG format. Global Administrative Areas, for one, provides the administrative regions of all countries of the world. The problem: they are ESRI shapes files, not EMF or SVG.

Today’s challenge is to find an easy way of transforming ESRI shape files into EMF which can be directly used in Excel for creating Choropleth Maps.

The Solution - Indiemapper

Indiemapper is a free service provided by Axis Maps making static, thematic maps from geographic data. It also as an export feature to transfer shape files into SVG, JPG and PNG.

Step 1: Load your Shape Files

After launching Indiemapper, click on Shapefile and browse for the file on your computer.

If available, also upload the .dbf file in the following dialogue.

Step 2: Skip New Layer Option

Skip the next dialogue window to add a new layer by clicking on Cancel:

Step 3: Turn off the Graticule

If necessary (like it was in my case), uncheck the strokes checkbox in the Graticule window.

Step 4: Export 

Click on export at the top right of the website, choose “layered SVG”, select a filename and click on Generate File:

Finally export the generated SVG file to your computer:

That’s it. In 4 simple steps we transformed an ESRI Shape File into SVG, i.e. the file format we can now transform into an EMF file and prepare it for being used as a Choropleth Map in Excel, exactly the way I already described it here: Build your own Choropleth Maps with Excel.

The Show Case – Germany by Zip Codes

With the few steps described above combined with the technique to transform SVG files into Excel Choropleth Maps (here), I was able to produce the following 2 maps of Germany by zip codes in almost no time.

For your understanding: German zip codes have 5 digits and there are more than 8,200 of them (only the ones which represent a geographical region). Very often, geographical visualizations use only the first 2 digits of the zip code, dividing Germany into 95 regions. 95? Shouldn’t that be 99? No. the remaining 4 are unused or do not represent a geographical area.

So, here is a map of Germany by zip code 2 (what we call PLZ 2):

And here is the comprehensive one: Germany by zip code 5 (PLZ 5) with more than 8,200 shapes:

The Disadvantages

Let’s call a spade a spade: using Choropleth Maps in Microsoft Excel is nothing else than the poor man’s geographical visualization tool. It takes some time and know-how to set them up and they come with a couple of disadvantages.

The zip code 2 map with 95 shapes works pretty well. With the zip code 5 map, however, the technique is definitely reaching its limits:

• More than 8,200 shapes bloat the file size to almost 9 MB
• Although I used the optimized VBA code provided here, the map takes around 10 seconds to update on my machine. Everything else than a good performance and user experience

Still, if you do not have a professional geographical visualization tool available, Microsoft Excel can be a reasonable workaround, even for a very detailed map like Germany by zip codes.

The Download Links

Germany by zip code 2 (PLZ 2):

Download Choropleth Map Germany by Zip Code 2 (Microsoft Excel 2007/2010, 965.7K)

Germany by zip code 5 (PLZ 5):

Download Choropleth Map Germany by Zip Code 5 (Microsoft Excel 2007/2010, 8910.4K)

The data used in these example workbooks is made up.

Acknowledgements

Many thanks go to the developers of Indiemapper, Zachary Johnson, Andy Woodruff, Dave Heyman, Ben Sheesley and Mark Harrower for creating this great online tool and to Axis Maps for providing it for free. Thank you!

Stay tuned.

The recent post described how to use an ActiveX Spreadsheet Control as a tooltip on Excel worksheets. At first sight this seemed to be a nifty little idea to spice up tooltips on Microsoft Excel worksheets. In the meantime, however, I had to learn that there is a major pitfall:

The heart of the solution, the ActiveX Spreadsheet Control is part of the so called Office Web Components. So far, so good. The problem is that Microsoft does not ship the Office Web Components with Office 2007 and later anymore. In other words, in a clean, new Office 2007/2010 installation, the ActiveX Spreadsheet Control is not available anymore.

Microsoft still provides the Office Web Components for free download (Office 2003 Add-in: Office Web Components), but there is still a major drawback: if you want to use the described technique, you have to make sure that every user of your workbook either uses Excel 2003 or has the Office Web Components installed.

At the end of the previous post I announced a follow up article with more practical examples using the technique. Knowing now what I didn’t know before, I pondered if this article would still make sense. To cut a long story short, I finally decided to publish it despite the limitation described above. Maybe some of you will still find it interesting and useful.

Today’s post provides 3 different use cases of how to take advantage of the ActiveX Spreadsheet Control in a tooltip:

• a scrollable tooltip
• side calculations within a tooltip
• an improved version of the Summary Card tooltip

As always the articles provides all Excel workbooks for free download.

Example 1 – Details on the selected cell or row in a scrollable list

Let’s assume we have an Excel workbook with unemployment rates from 2008 to 2012 by state on the first worksheet and by county on the second sheet. An ActiveX Spreadsheet Control can provide the county details for the active row (i.e. the selected state) in a scrollable tooltip:

The technique for setting up the Spreadsheet Control is exactly the same as described in the how-to post: Interactive Tooltips on Excel Worksheets.

What’s different?

Firstly, you need 2 named ranges for the data (e.g. “myStates” and “myCounties”).

Secondly, the VBA is different. The code in the sheet module (sub Worksheet_SelectionChange) is even simpler than before.  However, you need another function retrieving the county details for the selected state (function GetCountyDetails in module modGetCountyData). The code isn’t very complicated. Basically it is working like a filter, i.e. it loops through all counties and returns only those belonging to the current state (selected row).

Here is the workbook for free download:

Download Interactive Tooltip Lookup Details (Microsoft Excel 2003, 670.5K)

Example 2 – Side calculations based on the value of the active cell

In this example, we have data including some currency values (like prices or margins) and a second worksheet containing the exchange rates for other currencies updated via a data connection to a financial website.

The idea of this use case is to provide a list of other currencies, including the exchange rate and the conversion of the USD amount of the active cell into each currency. The amounts in all currencies are not calculated by the VBA code but rather by a simple formula in the ActiveX Spreadsheet Control (e.g. = B4*$C$1). Hence, the user has also the option to overwrite the USD value in cell C3 of the tooltip (top right) and immediately gets the results for the entered value:

Again we need a second range name (“myExchangeRates”) and a few modifications in the VBA: the code updates the exchange rates in the ActiveX Spreadsheet Control, checks if the active cell has a currency format, writes the value of the active cell to cell C3 of the Spreadsheet Control and finally displays and positions the tooltip:

Download Interactive Tooltip Exchange Rates (Microsoft Excel 2003, 123.5K) 

Example 3 - Enhanced Summary Card

The how-to post used the example of a Summary Card in the tooltip, i.e. showing aggregation results of the selected range, like sum, average, maximum, etc.

A helpful little tooltip, but it comes with 2 limitations:

1. It does not provide much more information than Excel’s status bar displays already by default
2. It aggregates all data of the selected range. This is fine as long as the selected range only covers one column of the data, but it doesn’t make much sense to display e.g. the sum of order quantity and order prices.

The Summary Card of Tableau, for one, shows the aggregations per measure used in the view:

Having said that, we can enhance our Summary Card spreadsheet tooltip to do something very similar, i.e. to show the aggregations in different columns, like this:

The VBA code is a bit longer (55 lines of code compared to 37 in the simple version), but it isn’t really complex.

In a nutshell it loops through the columns of the selected range, performs the calculations column by column, writes it to the according columns of the ActiveX Spreadsheet Control and finally adjusts the viewable range of the tooltip.

There is a lot of room for optimization (e.g. the count is always the same for all measures, so it should be displayed only once above the table, see also the Tableau Summary Card screenshot above), but I am sure you get the idea. For the details please have a look at the workbook:

Download Interactive Tooltip Summary Card Enhanced (Microsoft Excel 2003, 86.5K) 

Conclusion

As mentioned in the update at the bottom of the previous post and in the introduction to this article, the entire technique has one major limitation: The ActiveX Spreadsheet Control is not available by default if you are running Excel 2007 / 2010. However, if you are still using Office 2003 or don’t mind to install the Office Web Components, a tooltip including the ActiveX Spreadsheet Control might still be an interesting interactive feature for your models.

Stay tuned.

Tooltips are one of the most helpful features when prospecting large data sets.

We already had a couple of articles talking about tooltips, on charts and on worksheets:

Tooltips on Microsoft Excel Tables

Charts in Excel Table Tooltips

Display all Fields of a Row in large Excel Tables

Better Chart Tooltips with Microsoft Excel 2010

Today’s post is trying to take this to the next level: an interactive tooltip on a Microsoft Excel worksheet. You may ask:

“What the heck is an interactive tooltip?”

What I am thinking of is a tooltip enabling you not only to read additional information but also interact with it. E.g. copy data from the tooltip and paste it somewhere else, do side calculations based on the currently selected data, browse through a longer list of data in the tooltip, etc.

Impossible? No it isn’t. Microsoft provides a very useful ActiveX control which enables us to do exactly this: the Microsoft Office Spreadsheet Control. Today’s post shows how to take advantage of this ActiveX control in order to provide an interactive tooltip on Excel worksheets - as always including the Excel workbook for free download.

The Idea

Microsoft Office / Excel comes with a lot of very interesting and powerful ActiveX controls. One of them is the Microsoft Spreadsheet ActiveX Control. It may sound a bit weird to use a spreadsheet control within a spreadsheet, but it offers a lot of interesting options.

The basic idea of this post is using the spreadsheet control as an interactive tooltip on Excel worksheets. In order to keep things “as simple as possible, but not any simpler” (Albert Einstein), I will describe the idea by displaying a “Summary Card” (see also Tableau Quick Tip #1 - The Summary Card) of the selected range in a tooltip on simple list of data in Excel:

The Summary Card displays sum, average, minimum, maximum, count, median and standard deviation of the values in the selected range. Agreed, all of this information (except for median and standard deviation) is available in Excel’s status bar at the bottom right already (in Excel 2007/2010, in Excel 2003 and earlier you only have one of them). However, this is just the first step to show the technique in general. I will provide more useful examples in the next article.

Let’s get started. Here is the how to:

The Step-by-Step Tutorial

Step 1 – Bring your Ducks in a Rows

Insert a data table into a Microsoft Excel worksheet and define a name for the range containing the data (“myData” in my example).

Step 2 – Insert a Microsoft Spreadsheet ActiveX Control

On the Developer tab click on Insert and More Controls:

In the following dialogue window scroll down to the Microsoft Spreadsheet 11.0 control and click on Ok:

The cursor changes to a cross. Simply click somewhere on your worksheet and the Spreadsheet Control will be inserted.

Step 3 – Change the Properties of the Microsoft Spreadsheet ActiveX Control

On the Developer Tab click on Design Mode, select the ActiveX Control and click on Properties:

You do not have to change much here. Simply define the caption (“Summary Card” in this example), set DisplayOfficeLogo and DisplayToolbar to False and define an appropriate height and width of the control object.

Step 4 – Change the Options of the Microsoft Spreadsheet ActiveX Control

Right click on the control and select Microsoft Office Spreadsheet 11.0 Object and Command and Options:

First tab we go to is Workbook:

Uncheck the scrollbars and the sheet selector, delete all sheets except for Sheet1 and rename the sheet (Summary Card).

Next we go to the Sheet tab:

Uncheck row headers, column headers and gridlines and define the viewable range. For the summary card example, we need only 2 columns and 7 rows, so we set the viewable range to $A$1:B$7.

Finally we define the column width and row height on the Advanced tab:

Step 5 – Format the Cells on the Microsoft Spreadsheet ActiveX Control

You can use the Format tab in the Command and Options window (see step 4) to format the cells on the worksheet. However it is much easier and faster if you simply create a range on the Excel worksheet, format it as you like (fill colors, borders, number formats, etc.) and simply copy this range and paste it into the ActiveX control. Before you do so, you have to turn off Design Mode on the Developer tab.

Step 6 – The VBA

You guessed it: to make this work we need some VBA. But don’t worry, it is a really small code snippet (2 subs, 37 lines of code). The main part is the event driven procedure Worksheet_SelectionChange. There is one additional function checking if all cells in the selected range are numeric.

Here is what the code does in a nutshell:

1. Check if the active cell is in the data table, i.e. within the defined range name (“myData”)
2. If so, check if the selected range has more than one cell and all cells in the selected range are numeric
3. If so, make the ActiveX control visible, position it at the bottom right of the selected range and insert the results (sum, average minimum, maximum, count, median and standard deviation) for the selected range using the Application.WorksheetFunction method
4. If not, hide the ActiveX control
5. Save the workbook as an Excel Macro Enabled Workbook

That’s it.

The Pros

Well the pros are obvious, aren’t they?

The first one is the ease of implementation: 6 steps and a small piece of code and you are good to go.

Even more important is the interactivity. The ActiveX control is a limited spreadsheet in terms of its functionality, no doubt about it. Having said that, it offers many options you would not expect to have in a tooltip: users can do their own side calculations by entering values into the cells of the tooltip, you natively have the scrolling options for larger tables to be shown, you can easily copy content from the tooltip and paste it somewhere into the Excel workbook or elsewhere, and many more.

Last but not least, the technique offers many possibilities to create a highly interactive and responsive tooltip to prospect data tables. Maybe not suitable for all of your models, but definitely a very interesting option in some use cases.

The Cons

Well, there are not many cons I have encountered so far except for this one: if you change the zoom factor of the worksheet, the ActiveX control resizes too, but the content (i.e. the cell range of the spreadsheet tooltip) doesn’t. You will see something like this:

Not really nice, but a minor drawback in my humble opinion.

The Download Link

Download Interactive Tooltip Summary Card (Microsoft Excel 2003, 73.5K)

Acknowledgements

Special thanks go again to Jordan Goldmeier of the Option Explicit VBA Blog for reviewing my workbook, for his feedback and for encouraging me to write this article. Thanks, Jordan!

What’s next?

As mentioned above, this technique offers a variety of possibilities. Agreed, the Summary Card example does not really take advantage of the interactive features (except for the option to copy the content of the tooltip and paste it somewhere else).

The next post will provide a small selection of examples of what you can do with the ActiveX Spreadsheet control and how to make a really interactive tooltip.

Stay tuned.

Update on Sunday, November 11, 2012 – A serious limitation

In the cons section of the article I claimed the zooming issue would be the only drawback of this technique. It turned out that unfortunately this is only half the story. The ActiveX Spreadsheet Control is part of the so called Office Web Components. In his comments to this post (see below), reader "Sg" pointed to the fact that Microsoft discontinued the shipping of the Office Web Components with Office 2007 and later. This one was new to me. Although I am running Office 2010, the Spreadsheet Control is available on my computer, probably because I have also installed Microsoft Project 2003.

Microsoft still provides the Office Web Components for free download (Office 2003 Add-in: Office Web Components) and if you install it, I suspect the ActiveX Spreadsheet should also work with Excel 2007/2010.

However, this is a serious limitation of the technique: you can’t easily distribute the workbook to other users running Excel 2007/2010. They would have to install the Web Components first and this considerably decreases the applicability of the ActiveX Spreadsheet Control for professional Excel models.

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:

The Excel Rollover Mini FAQ

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:

1. 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
2. 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:

1. 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.
2. 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
3. SetHyperlink adds / updates the hyperlink to the current cell at the top right of the dashboard
4. ChangeTentDropDown is hooked up to the combo box for selecting a year and simply makes the tooltip invisible and calls SetHyperlink
5. 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.