Bluffing Tableau Actions with Microsoft Excel

Selected techniques to emulate a Tableau lookalike dashboard using Microsoft Excel, including some interactive features similar to Tableau Actions

The recent post described the power of Tableau Actions. Tableau actions allow you to add context and user-defined interactivity features across your workbook. If the user clicks on one of your visualizations, Actions give you full control over what should happen on other worksheets or visualizations. Setting up a Tableau dashboard with various actions like filtering, highlighting and linking to web pages is a piece of cake.

How about Microsoft Excel? Is it possible to implement a similar interactivity on a Microsoft Excel dashboard? Yes it is.

Today’s post describes a set of techniques and tricks to build a replica of the Tableau 50 most prominent summits on earth dashboard using Microsoft Excel. As always, including the workbook for free download.

The Reference and the Challenge

Here is the reference from the recent post: a Tableau dashboard visualizing the 50 most prominent summits on earth, including great interactive features like updating the Google Map view after clicking on a summit on the map or in the data table:

The challenge is easy to describe: implement something as close a possible in Microsoft Excel.

To make it a bit easier, we allow one restriction: we will not rebuild the filter action zooming into the map after clicking on a continent in the top left bar chart. Other than that, we will create a Microsoft Excel workbook bluffing all other Tableau action functionality.

A picture is worth a thousand words, so here is a screenshot explaining the interactive features to be implemented in our Excel workbook:

The Implementation

Unlike in the previous post, I will not provide a detailed step-by-step tutorial here, because all used techniques are already described elsewhere. Thus, I restrict myself to a quick overview of the implementation including some links to other sources.

Part 1 - The Structure of the Workbook

Nothing new under the sun. If you have already downloaded one of my other Excel workbooks, you might be familiar with the way I am structuring my models. The workbook consists of three worksheets: the dashboard, the data worksheet and a calculation worksheet containing the control parameters and the formulas needed to implement the functionality and the charts on the dashboard.

Part 2 - Controlling User Inputs

For controlling all user inputs and actions, we need 4 parameters, defined on the worksheet Calculations:

• Actual sort criterion: the target cell of the radio buttons
• Actual snapshot start: the target cell of the scroll bar
• Actual selection in table: the position of the actual selected summit in the snapshot table on the dashboard (1 to 10)
• Actual selection in raw data: the position of the actual selected summit in the source data (1 to 50)

These 4 parameters are the switch board of our interactive features. The values in these cells will be changed after a user input, either by the form controls or the VBA code (see below). All other calculations in the workbook are based on these parameters.

Part 3 - The Visualizations on the Dashboard

The World Map Bubble Chart

Basically it is a simple standard bubble chart with 2 data series. One data series contains all summits, the other data series will be used to highlight the selected mountain. Jon Peltier provides a great tutorial on how to do this in his post Custom Format for Chart Minimum and Maximum.

A bit more tricky is the World Map. Here are the main steps in a nutshell:

• Export the map visualization from the Tableau workbook including the circles
• Insert the map as the background of the chart area (chart area|fill effects|picture)
• Relocate the bubbles by using the technique described in my article Background Image Maps with Tableau until all bubbles are exactly on top of the circles on the map.
• Go back to Tableau and export the map again without the bubbles (set color to minimum)
• Insert the map again as the chart area background in Excel

That’s it. A bit of laborious work, but afterwards we have a Tableau lookalike map in Excel.

The Data Table including the Bar Charts

• Creating a snapshot of a longer list of data on a dashboard and letting the user scroll up and down is a technique I once described on Chandoo’s blog
• Combine Tables and Charts on Excel Dashboards was a post here on my own blog that showed the power of integrating charts into tables
• Simple standard conditional formatting highlights the row of the selected summit
• The functionality to sort the table using the radio buttons has nothing to do with the actions, but sorting is always a helpful feature. You can find a detailed description on how to do this in my second guest post over at Chandoo’s blog
• The technique to highlight a selected bar is the same we already used for the Bubble chart
• Finally, an additional dummy series with the maximum of the values ensures that the scale of the horizontal axis stays the same if the user scrolls up or down in the data table

The Web Browser Control

This one is a piece of cake. All necessary information on how to use a Web Browser Control in VBA can be found at the MSDN library. The Web Browser object provides our dashboard with the full Google map view functionality. The user can zoom in and out, go to a different view like map or earth, etc.

 Part 4 - The VBA Code

The sophisticated part of the VBA code are the two modules and two class modules implementing the mouse events and clicks. I haven’t written one single line of this code. Guess where I got the code from? Of course from Jon Peltier, whom else? Here is the link to Jon’s implementation:  Get XY on any Chart. I simply took out the parts I didn’t need in my workbook and called my update procedures from there.

The best thing about Jon’s code is the fact that the mouse events work seamlessly. You do not have to activate a chart first. You can directly click on a data point on the map or a bar and the dashboard will be updated.

The rest of the code isn’t too complicated:

• The Workbook_Open sub and the Workbook_Close sub turn the application events on and off and navigate the Web Browser to the right URL respectively to an empty page (this reduces the file size before closing).
• The Worksheet_SelectionChange sub of the worksheet Dashboard checks whether the user clicked into the data table and – if he did – calls the according update sub
• The module modActionsBluff contains 4 update procedures which change the parameters (see above) to the desired values and navigates the Web Browser to the correct URL. Pretty simple VBA code.

The Final Result and the Download Link

That’s it. There is nothing new or innovative to this way of bluffing Tableau actions with Excel. All techniques are well-known and documented somewhere on the net. My only part in this little project was gathering the information and techniques and stirring them it into my workbook.

Here is a screenshot of the Excel dashboard. It is not exactly the same as the Tableau dashboard (see above), but you have to admit that it is quite a lookalike:

And here is the download link:

Download Bluffing Tableau Actions with Excel (Microsoft Excel 2003, 576K)

Why bluffing Tableau with Excel?

Don’t get me wrong. I will not start a Tableau versus Excel discussion here. In my humble opinion, these two products are not competitive, they are complementary. I use both a lot and I would never dismiss one of them.

But then: why bluffing Tableau charts and functionality using Microsoft Excel? From my point of view, there are 3 main reasons:

1. You will learn a lot. Believe me, if you try to emulate Tableau using Excel, you will learn a lot about data visualization from Tableau and a lot about Excel’s capabilities and flexibility during the implementation of the replicas. Know-how that will definitely be helpful somewhere else.
2. Of course, there are a lot of people out there who have only Excel available. Bluffing Tableau’s best-of-breed visualizations and features with Microsoft Excel enables those people to considerably improve their Excel workbooks.
3. Even if you have Tableau and Excel available, you will have projects where Tableau is not the right tool: a business case, a simulation, an optimization algorithm, a what-if-scenario-analysis, a forecasting model, whatever. Challenges, Tableau is simply not built for. Excel is the right tool for this type of projects. Looking at Tableau’s best practices of visualizing data and knowing how to bluff them with Excel will considerably increase the quality and user experience of your Excel models.

You may be asking:

"Come on, there has to be something where the Excel implementation of the 50 summits dashboard outperforms the Tableau workbook?”

Yes, there is something: After selecting a summit on the map that is currently not one of the 10 displayed mountains in the data table, the Excel dashboard navigates to this summit in the data table. In other words, Excel (respectively the implemented VBA code) automatically scrolls up or down until the selected summit is visible. Tableau doesn’t. A small advantage brought to you by Excel’s and VBA’s flexibility.

However, don’t forget about the time needed to set up the workbooks. As soon as you have your ducks in a row (i.e. the source data ready), the implementation of the Tableau workbook takes less than 30 minutes. Try to build this model in Excel from scratch and stop the time. You will see the difference.

Acknowledgements

Many thanks go to Matt Grams, not only for inventing the “Bluffing Tableau with Excel Category” here on Clearly and Simply with his wonderful guest post Bluffing a Visual Cross-tab with Excel, but also for taking the time to review the workbook for this article and for his enduring encouragement. Thanks, Matt!

I would also like to thank Daniel Ferry who was kind enough to spend some of his precious time looking at my workbook, although he was very busy with preparing his amazing Smith Chart implementation at the same time. Daniel found a bug in my workbook using Excel 2007 that I haven’t noticed. Thanks, Daniel!

Last, but not least, special thanks to Jon Peltier for providing the core piece of the VBA code used in the workbook. Thanks, Jon!

What’s next?

As already mentioned in the introduction, the recent post was a how-to tutorial on standard functionality of Tableau Software. I received a couple of comments and emails encouraging me to write more step-by-step Tableau tutorials. Thus, I am tempted to do so. Maybe on Custom Shapes, maybe on Calculated Fields. Or maybe on something else? What do you think? Which feature of Tableau would you like to be covered in a future post here? If you have a minute, please leave me a comment and let me know what you think.

On top of that, I still owe you a follow-up post to Export Microsoft Project Tasks to Outlook. I promised this one a long time ago, but I never wrote it. I guess this one should be next.

Stay tuned.