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.

Saturday, March 19, 2011

The Next Level of Interactive Microsoft Excel Dashboards

A couple of weeks ago I had the privilege to contribute to Chandoo's great Excel School. Chandoo and I discussed Excel dashboards in general and how to add interactive features. If you are subscribed to the dashboard module of Chandoo's Excel School, you will soon be able to watch the whole interview.

We already had a post discussing interactivity on Excel dashboards: Bluffing Tableau Actions with Excel. However, the model I developed subsequent to my discussion with Chandoo includes more, hopefully helpful functionality. Thus, I thought you might be interested.

The Challenge

“Beauty and Brains. See what you can accomplish with Tableau”. This is the title of the Tableau Software Visual Gallery where Tableau provides a variety of beautiful and interesting Tableau workbooks. “Seattle Real Estate - Around the Sound” is one of them, visualizing the most important figures of the local real estate market of the Seattle region, including supply, number of closings and average prices. Here is the link to Tableau’s original implementation: Real Estate Prices.

I like the dashboard, so I downloaded the workbook and did some minor changes like adding a checkbox filter control, a data table with the actual numbers and a browser element to provide the Google map view of a selected county. Here is a screenshot of my version:

As already mentioned many times before, Tableau comes with a variety of interactive built-in features, automatically available on your dashboard: Clicking on a data point of a chart, on a data row of a table or on an entry of the legend highlights the selected category and shades off all others across all visualizations of the dashboard.

Tableau actions offer even more. If you are interested in what is possible with Tableau Software, you may be interested in this article: The Power of Tableau Actions. That post used an example workbook visualizing the 50 most prominent summits of the world. The following article Bluffing Tableau Actions with Excel discussed and provided a Microsoft Excel version of the 50 peaks dashboard including a lot of Tableau lookalike interactive features.

Today’s challenge is along these lines: Build a Microsoft Excel replica of the “Around the Sound” dashboard and provide as many additional analytical features as possible. In other words, we try to step up to the next level of interactive Excel dashboards.

The Fundament: The 50 Summits Dashboard

Here is a screenshot of the 50 summits dashboard in Excel:

This dashboard included a basic set of Tableau lookalike interactive features in Microsoft Excel:

Clicking on a data point of the map or clicking on a row or a bar of one of the 2 bar charts in the data table…

…highlights the summit on the map with a red fill color,

…highlights the row of this summit in the data table with a light blue fill color and

…updates the Google Map view in the browser element with the view on this summit.

Besides the basic functionality of triggering actions across all elements of the dashboard with one click on one element, there are some more interesting things:

You do not have to activate the charts (the bubble / map chart and the bar charts) first. The clicking on a data point works seamlessly.

The Google Map view in the browser window is not a static screenshot. It not only updates automatically, but also provides the whole functionality of Google Maps: zooming in or out, switching to another type of view (Map, Sat, Ter, Earth) and so forth.

On this solid ground, we will build an “Around the Sound” dashboard in Excel providing even more interactive, analytical features.

The Next Level: Around the Sound

I am usually not putting the cart before the horse, but in this case I think a sneak preview of the final result will help you to understand the additional functionality included in today’s dashboard. So here is a screenshot of the dashboard:

Compared to the 50 summits dashboard, the “Around the Sound” model provides a couple of more interesting features. Not all of them are interactive, but I think the dashboard is one step up:

Optimized data table

Clicking on a row in the data table selects a county. The according row of the table is highlighted with a grey fill color. Also, the legend symbols (see below) will be shaded off in light grey. Clicking somewhere outside of the data table deselects the county and shows all entries again.

Furthermore, the legend (i.e. the color code of the counties) is integrated into the data table. Please notice that the colors of the counties are consistent across all visualizations. This way, we only need one legend and save a lot of real estate on the dashboard.

Filter by counties

The checkboxes right to the data table allow a filtering by counties. Only checked counties will be shown in the charts Including an extra checkbox to select and deselect all counties with one click as described in the recent article Excel Dashboard Controls – Multiple Value Filters. Counties not included in the filter will be shaded off with a grey font color.

Automatic set of filter by selecting a county

If you select a county that is currently not included in the filter (i.e. the check box is unchecked), the model will automatically set the check and include this county in the filter.

Filter by time range

The two scrollbar controls on top right of the dashboard allow to filter the data by any given date range. The data will be consolidated accordingly and the line charts will only include the selected time range.

Select a county by clicking on a line in one of the line charts

The 50 summits dashboard allowed to select a data point by directly clicking on data point in the bubble chart (map). The same functionality is available on today’s dashboard, too. However, this interaction is not only working with the bubble chart, but also with both line charts. The lines and bubbles of the counties not selected are shaded off in grey. Again, clicking somewhere outside of the data table reverses the selection again.

Optional average lines in line charts

Two checkboxes above the line charts give you the option to include two different average lines: the average of all counties or the average of the filtered counties.

Additional bubble chart legend

A bubble chart legend and a small additional overview map of the northwest coast of the United States provide more context and information.

As I said, not all of these additional features are interactive. However, I think this dashboard provides more analytical power than the 50 summits dashboard. That’s why I called today’s article “the next level of interactive dashboards”.

The Implementation

I guess I don’t have a snowball’s hope in hell to provide a detailed step-by-step how-to tutorial here. This would definitely go beyond the scope of a blog post. However, there is nothing new under the sun: a few chart tricks, conditional formatting, array formulas and some VBA routines (less than 250 lines of code).

If you are interested in the details, download the workbook provided below, dig deeper into it and dissect the used techniques. If you have any questions, please leave me a comment. I am happy to answer all of them.

More Excel Compatibility Oddities

Whenever I am implementing Excel models of higher complexity, I am stumbling across some compatibility issues. Not sure if you are interested, but for what it’s worth, here are the 2 issues I encountered this time:

The VBA sub Reset_LineCharts adjusts the data source of the line charts after changing the date range filter. I am using the well known trick to exclude counties and averages not selected for display: the data source of lines not to be displayed are set to #NA instead of the real values. Setting the date range in the VBA sub works like a charm with Excel 2010. With Excel 2003 and earlier, however, changing the range of the data source does not work correctly for the data series containing only #NA. As a workaround I changed the VBA to simply store the actual selections first (i.e. the filters and the average lines), select all, change the data source and finally set back the selection to the stored values. Maybe a brute force method, but it does the job. I have no clue what might be the background of this oddity.

Shading off the legend symbols in the first column of the data table for not selected counties is done by simple conditional formatting. This works well in 2003 and 2010, as long as you save the file in the according file format. However, it does not work in 2010 if you are using a file in 2003 format. Don’t ask me why.

If you have an idea of what causes these issues, I would highly appreciate if you would share them with us in a comment.

The Download Link

The workbook was implemented in Excel 2010 and tested using Excel 2002, 2003 and 2010.

Acknowledgements

A big-time thank you very much goes again to Matt Grams, who seems to never grow tired of meticulously reviewing all the workbooks I am sending to him and providing fantastic feedback. Many thanks, Matt!

Many thanks go also to Ulrik Willemoes, one of my fellow students over at Daniel Ferry’s Excel Hero Academy. Ulrik was kind enough to spend some of his precious time reviewing the model. His very thorough feedback and great suggestions considerably improved the dashboard. Many thanks, Ulrik!

Once more, I would like to thank Jon Peltier for making all these interactive dashboards possible by providing the core piece of the VBA code for free. Many thanks, Jon!

Last, but not least, my special thanks to Chandoo for our very interesting and fruitful discussion and for your encouragement. Many thanks, Chandoo!

What’s next?

Frankly, I do not know yet. I am working on two new Tableau related articles, on the update to the Export Project to Outlook post (still! I know I promised this a very long time ago) and more Excel stuff. Most of my time, however, I am spending with a series of posts on forecasting techniques. First come, first serve. Whatever I will have ready, I will publish next.

Comments

A couple of weeks ago I had the privilege to contribute to Chandoo's great Excel School. Chandoo and I discussed Excel dashboards in general and how to add interactive features. If you are subscribed to the dashboard module of Chandoo's Excel School, you will soon be able to watch the whole interview.

We already had a post discussing interactivity on Excel dashboards: Bluffing Tableau Actions with Excel. However, the model I developed subsequent to my discussion with Chandoo includes more, hopefully helpful functionality. Thus, I thought you might be interested.