Clearly and Simply

Highlighting on Excel Dashboards

Highlight one selected item across all charts and views of an Excel Dashboard: an example and the how-to

Very often, a dimension is displayed on more than one view of a dashboard.

Let’s say you want to analyze data of the European Union. Chances are that you will have the dimension “member state” on more than one of your charts.

If you pull these charts together into one dashboard, highlighting a selected member state across all charts is very helpful to explore and analyze the data. It supports the user to easily focus on the selected state and to identify its position in the context of all members at a glance.

Highlighting is the very simple act of selecting one item out of many (by e.g. clicking) and automatically seeing this item emphasized in all views across the entire dashboard. This is a very effective and user-friendly visualization technique and should be available on every interactive dashboard.

Today’s post shows how to implement highlighting on an Excel dashboard. As always, including the Excel workbook for free download.

La Gazzetta dello Sport gives Tableau a try

La Gazzetta dello Sport, the famous Italian sports newspaper uses Tableau Software to visualize 20 years of Italy’s Serie A

In spring this year, I received an email from Marco Nicolucci. Marco stumbled across my Tableau workbook visualizing the history of the English Premier League:

England Premier Football League - Historical Statistics

This workbook already got some exposure from Tableau: it is part of the Tableau Public Gallery, it was mentioned in the wrap-up to the first Tableau Sports Viz Contests (Other Winners from the Sports Viz Contest) and it even made the cut for the 25 of the best Tableau Public Vizzes.

Marco apparently liked my dashboards, too. He asked me, if I could support him to rebuild the Tableau workbook for Italy’s Serie A. So far nothing unusual. From time to time readers contact me with some questions and requests. However, this email was something special and really exciting, because Marco is a sports journalist at La Gazzetta dello Sport, Italy’s famous and prestigious daily sports newspaper.

For those of you who do not know La Gazzetta: La Gazzetta dello Sport is not only the Italian sports newspaper with the widest circulation (ca. 450,000 per day on average with a readership in excess of 3 million), but also one of the eldest sports newspapers in the world, first published in 1896. La Gazzetta online according to Alexa is ranked 843 worldwide and 21 in Italy. They have 1.13 million followers on Twitter and 1.5 million likes on Facebook. In Italy, La Gazzetta is more than a newspaper. It’s an institution.

It goes without saying that I was thrilled and honored being asked to become part of this project. In the next few weeks, Marco and I used my Premier League Workbook as a basis and built a comprehensive Tableau workbook to analyze and visualize the last 20 years of Serie A:

• 2 storyboards, 9 dashboards, 32 views
• the classical visualizations like tables, fixtures, goal differences, win-draw-loss chart, etc.
• additional visualizations like a results cross table for one entire season, an all time table (all time = 20 years), a view for one selected team, a comparison of 2 selected teams and a simulation of the 3 points rule versus the old 2 points rule
• various options to slice and dice the visualizations, like home and away table, sorting options, include or exclude points deductions, etc.

Our workbook was published on La Gazzetta online last Tuesday (August 26, 2014):

Serie A, l'era dei 3 punti

It is worlds apart from my original Premier League viz and provides many more options and dashboards. Thus, I thought the Tableau users and football fans among you may be interested in having a look. The dashboards are in Italian, of course, but I think you can easily figure out how it works and what is shown.

So far, the visualization received almost 60,000 views. To put this into context: my Premier League workbook had a little bit more than 31,000 views in three years(!).

Special thanks go to Marco Nicolucci for making me part of this great project. I am proud and honored. Not only that I had a lot of fun during our collaboration, I am feeling I made a new friend. Thank you, Marco!

Stay tuned.

The Next Level of Interactive Microsoft Excel Dashboards

A highly interactive Microsoft Excel Replica of Tableau’s Seattle Real Estate “Around the Sound” Dashboard

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.

Prior to our discussion, I prepared a simple interactive dashboard based on an example taken from the Tableau Software Visual Gallery: Around the Sound - Seattle Real Estate Prices. After our interview, I spent some more time with the model and enhanced this dashboard with more analytical features.

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.

Today’s post describes the enhanced model and provides the Excel workbook for free download. 

Microsoft Excel Site Catchment Analysis (Part 2)

Techniques to implement the Site Catchment Analysis of Germany in Microsoft Excel

I am sure you are already fed up with articles on Site Catchment Analysis here. I swear it was not my intention to have such an avalanche on this topic here. But it turned out to be a very good example to demonstrate different interesting ideas in Tableau and Excel. After using this example for a how-to tutorial on Calculated Fields in Tableau, I wrote an update for Tableau 6 (Site Catchment Analysis with Tableau 6) and Richard Leeke was kind enough to provide a fabulous guest post series (part 1, part 2, part 3) with excellent insights on how table calculations work in Tableau, also based on the Site Catchment showcase. I finally couldn’t resist to use the example again and showed how this would look like in Microsoft Excel: Microsoft Excel Site Catchment Analysis (Part 1). Since the Excel model is quite a complex workbook, I decided to split this up again into a 2-post series.

So, please bear with me, I have to bring this to an end now. Today’s post provides some more details on the Site Catchment Analysis of Germany with Microsoft Excel.

Microsoft Excel Site Catchment Analysis (Part 1)

The Microsoft Excel version of the Site Catchment Analysis of Germany including the calculation of the optimal center

In October 2010 I used a show case of a site catchment analysis of Germany to demonstrate the power of Calculated Fields in Tableau. During the weeks that followed we had quite an avalanche of follow-up posts on this article: Site Catchment Analysis with Tableau 6 described an upgrade of the original workbook to the new version 6 of Tableau, including the roadblocks I hit during the upgrade. In a fantastic guest post series called “Another Look at Site Catchment Analysis with Tableau 6” (part 1, part 2, part 3), Richard Leeke showed how to overcome most of the disadvantages in my implementation.

I suspect you may already be fed up with this example. Despite the risk of boring you, I decided to stay a little longer with this topic, mainly for the following 3 reasons:

• The use case turned out to be a very interesting and yielding example for the discussion of features, techniques, workarounds and performance facets of Tableau Software. I guess everything has been said (especially by Richard) with regards to Tableau. But what about Microsoft Excel?
  
  
• Furthermore, finding data points within a certain radius from a given center has many more use cases than just a site catchment analysis. You may analyze sales performance KPIs, customer behavior or logistical statistics within a certain area. I am convinced, a Microsoft Excel version of the site catchment analysis can be very useful for some of your analyses of geographical data.
  
  
• Last, but not least, the Tableau implementation allowed to display the results of any user selected postcode, but it lacked a feature to calculate the optimal center of any given radius. A task tailored for Microsoft Excel.

Thus, today’s article will provide a Microsoft Excel workbook to conduct a site catchment analysis and to compare the results of the optimum with any given center, including some nice interactive features on the dashboard. As always the Excel workbook is provided for free download.