Clearly and Simply

Category: Dashboards

  • The Implementation of Word Clouds with Excel

    Approach, algorithm, VBA code and performance optimization of the Word Cloud with Excel implementation

    Word Cloud - VBA CodeThe previous post Word Clouds with Microsoft Excel introduced another workaround to create a word or tag cloud in a standalone Microsoft Excel workbook.

    The article briefly describes the approach, the user-defined settings, the result and the drawbacks of the workaround. It also provides 3 different versions of the workbook for free download.

    Today’s follow-up article will have a closer look into the machine room: some more details on the approach, the data structure, the algorithm, the implementation in VBA and a dirty little trick to optimize the performance of the code for inserting and formatting freeform shapes on a worksheet using VBA.

    As a little bonus, today’s post also includes a tiny Excel tool to easily split continuous texts into a list of words and their count, i.e. the data structure needed for the word cloud workaround. Of course the tool is provided for free download.

    (more…)

  • Word Clouds with Microsoft Excel

    Create dynamic Word Clouds / Tag Clouds in Microsoft Excel

    Word Clouds in ExcelUnlike Tableau Software, Microsoft Excel provides no native feature to create a word cloud (aka tag cloud), i.e. a visual representation of text data where the font size of a word depicts the frequency of this word in a text.

    Workarounds for creating word clouds in Microsoft Excel are available for free, some of them for almost 10 years already.

    I also made my contribution with the article Wordle Tag Clouds in Microsoft Excel in 2010. All of the workarounds come with some considerable limitations and mine is probably the worst: it requires an Internet connection, needs another click in the webbrowser control and – worst of all – it doesn’t work anymore with Excel 2013, because Microsoft disabled scriptable ActiveX controls on worksheets in Office 2013.

    I am fully aware of the fact that word clouds are a very poor tool for data analysis and business data visualizations. However, they definitely have their place in infographics. Moreover, realizing a word cloud in Excel is an interesting VBA challenge.

    Hence, I decided to create another workaround for word clouds in Microsoft Excel.

    Today’s article introduces my new implementation of word clouds in Excel including the workbook(s) for free download. I will go into the details of my approach in a follow-up post.

    (more…)

  • 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

    La Gazzetta dello Sport and Tableau SoftwareIn 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.

    La Gazzetta dello SportFor 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.

  • Bruce Springsteen Discography in Excel

    A Microsoft Excel Replica of the Tableau Infographic on Bruce Springsteen’s Discography

    Bruce Springsteen at the Austin Music Awards - Photographer: Charlie Llewellin (flickr.com)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.

    (more…)

  • Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard

    Bluffing the “Beer Prices at Oktoberfest” Tableau Dashboard with Microsoft Excel using Roll Over Tooltips and Web Actions

    Oktoberfest Impressionen - Photographer: sanfamedia (flickr.com)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.

    (more…)