A Microsoft Excel Replica of the Tableau Infographic on Bruce Springsteen’s Discography
The recent post presented a Tableau Infographic on the Discography of Bruce Springsteen’s Studio Albums.
Tableau apparently liked my Infographic and selected it as the Viz of the Day for June 25, 2013 and even incorporated it in the Tableau Public Gallery. I am feeling honored. Thanks, folks!
I originally planned to directly continue with a follow-up post about how to create and when to use Word Clouds in Tableau.
On second thought I decided to postpone this follow-up article. I haven’t published anything for more than 6 months and I had a strong feeling I should quickly do something for those of you primarily interested in Microsoft Excel.
So, if you are waiting for the article on Word Clouds in Tableau, please bear with me. The post will come soon.
Those of you interested in Excel: this is for you. What would have been more obvious than publishing a replica of the Bruce Springsteen Discography in Microsoft Excel? It goes without saying that the Excel workbook mimics the most interesting interactive features of the Tableau original. As always the article provides the Excel workbook for free download.
The Original
The original Tableau Infographic on Bruce Springsteen’s Discography came with the following main interactive features:
- Hover over the thumbnail gallery to see detailed information in a tooltip and click on a thumbnail to select an album
- The bigger cover at top right shows you at a glance which album you are currently looking at
- Explore the selected album by browsing through the list of tracks. Click on a track and the lyrics of this song will be displayed in the view right to the list
- Clicking on a track will bring up a tooltip with a link called “Play song on last.fm”. Click on this link and you can listen to this song or maybe even watch a video on last.fm
- Finally a Word Cloud of the lyrics is displayed at the bottom of the dashboard with the option to filter this cloud by the minimum number of occurrences.
Click on the image above to see and play around with the Infographic or click here to read the entire article.
We already had a couple of posts here bluffing Tableau’s great interactive features like Bluffing Tableau Actions with Microsoft Excel or Better Chart Tooltips with Microsoft Excel 2010.
Today’s article draws on this little tradition: it provides the Microsoft Excel replica of the Discography Infographic, mimicking some of the features Tableau provides natively.
The Excel Replica of the Infographic
Before we go into the interactive features of this Excel infographic and the used techniques, let’s have a quick look at the overall result:
Well, I hope you will agree: except for the missing Word Cloud at the bottom of the Tableau dashboard, the Excel replica looks a lot like the Tableau original, doesn’t it? But there is more to it than meets the eye. Let’s have a look at the interactive features.
The Features
Most of the techniques to bluff Tableau’s built-in interactive features have already been covered in articles here on Clearly and Simply and elsewhere. Hence, I will only briefly explain the functionality and provide the links to those articles.
Roll over tooltips and click thumbnail to select an album
When you hover over the thumbnail gallery of album covers or the big cover at the top of the dashboard, a tooltip appears providing more information on this particular album. Clicking on one of the thumbnails selects this album and all other views like the big cover, the peak chart position and the list of songs will automatically update.
This feature is based on the stellar idea of my friend Jordan Goldmeier to use a User Defined Function within a HYPERLINK function. Jordan described this in several posts on his exceptional blog Option Explicit VBA Blog:
How to Create a Rollover Effect in Excel
Many people have used Jordan’s technique with great success, like my friend Chandoo:
Interactive Dashboard in Excel using Hyperlinks
and – of course - myself:
Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard
All time peak chart positions in 6 countries
You can pick one of 6 different countries to see the all time peak chart position of the selected album in this country. The implementation: a form control combo box and an array formula combining INDEX and MATCH. I think I do not have to go into the details.
Scrollable list of the tracks of the selected album
All tracks of the selected album and their duration are displayed in a scrollable list. Use the scrollbar to flip through this list and select a track by clicking on it. The currently selected song is highlighted:
I described the technique of a scrollable list on Excel dashboards on my friend Chandoo’s blog back in 2008 and you see, after so many years I am still eating my own dog food:
KPI dashboards with Microsoft Excel
Highlighting the selected song is simple Conditional Formatting.
Selecting a song by clicking is nothing new either: a very small VBA code snippet in the Worksheet_SelectionChange sub. For more details, see here:
Bluffing Tableau Actions with Microsoft Excel
The lyrics
The lyrics of the currently selected track are displayed right to the list. It is a usual text box, linked to a cell and this cell gets the lyrics of the selected song from a simple INDEX formula.
Listen to a track on last.fm
Right to the tracks you see a little triangle. This is my poor attempt of mimicking a play button. Click on a triangle (on the cell, to be exact) and your browser will open a new tab with the according site on Last.fm. For most (but not all) tracks you have the option to listen to a preview of the song online or even watch a video.
The trick is as simple as can be. The cells contain the entire hyperlink to the last.fm site, but I set up the custom formatting of those cells and their font type to simply show the little triangle to the right.
Search lyrics
I waived the Word Cloud (why? see the next section) and hence I was looking for a nice alternative interactive feature. I thought an interactive search function could be nice. Imagine you have a line of a Springsteen song in your head, but you can’t recall which track and from which album it is. This dashboard helps: type the word or line into the search box at bottom right and Excel will search the entire Discography as you type.
Let’s say we are looking for “the Magic Rat”. Click into the search box and start typing. After typing “the ma” we already found “the marble dome” in “The Angel” on “Greetings from Asbury Park, NJ”:
Keep on typing and already with “the mag” we found what we have been looking for: “Jungleland” on “Born to run”:
A nifty little search functionality for Excel dashboards, but nothing really new under the sun. I already described the basic technique in a blog post back in September 2010:
Scroll and Search in Excel Dashboard Tables
Having said that, there is one thing, you should be aware of: in order to select / highlight the first found occurrence of the search string in the lyrics, you have to set the property “EnterFieldBehavior” of the textbox to “1 - fmEnterFieldBehaviorRecallSelection”, like this:
Furthermore we have to activate the lyrics textbox after every change in the search textbox in order to update the selection after each entered character. Have a look at the VBA code for more details (see download link below).
The “search as you type” feature forces Excel to recalculate after every character entered. This is probably ok in this workbook, but it may become annoying if you are using it in a workbook with more and / or more complex formulas. Therefore I provided a checkbox left to the search box. After unchecking this box the search will be conducted only after the search box lost focus, i.e. you clicked somewhere on the dashboard outside of the box.
No Word Cloud? Why?
In an article back in November 2010 I described how to integrate Wordle Tag Clouds in Microsoft Excel. So, why didn’t I use exactly this technique for the Discography workbook? Well, there are 2 reasons:
- The technique I showed in the article above is a bit clunky. It requires an extra click in the web browser object to create the Word Cloud. Everything else than a good user experience.
- More important is the fact that the technique does not work with Excel 2013 anymore since Microsoft decided to disable the usage of scriptable controls like the ActiveX Webbrowser (more details).
The Disadvantage
Jordan’s Rollover Effect in Excel works great and is straight forward to implement. There is a little drawback coming with it, though: if you move the mouse quickly away from the thumbnail gallery or the big cover, it can happen that the tooltip stays visible although the mouse cursor isn’t over a thumbnail or the big cover anymore. In this case simply click somewhere on the dashboard and the tooltip will disappear.
The Download Link
Here is the Excel replica of the Bruce Springsteen Discography for free download:
Download Bruce Springsteen Discography (Microsoft Excel 2007 – 2013, 1179.2K)
I protected the worksheets to ensure a smooth and secure user experience, but there is no password and so you can easily unprotect the sheets and have a look at the formulas and techniques.
Acknowledgements
Many thanks go again to my good friend Jordan Goldmeier of the Option Explicit VBA Blog for inventing and sharing the versatile hyperlink rollover technique.
By the way, in autumn 2013 Jordan will publish a book on “Dashboards for Excel”. You are interested in Excel Dashboards, aren’t you? So what are you waiting for? Go and pre-order a copy:
Dashboards for Excel - Jordan Goldmeier
What’s next?
As already announced in the previous post, the next article will provide a how-to and a small additional trick for the new Tableau 8 feature Word Clouds as well as some thoughts on this type of visualization in general.
Stay tuned.