Clearly and Simply

  • Better Chart Tooltips with Microsoft Excel 2010

    Create Tableau lookalike Chart Tooltips on your Microsoft Excel 2007/2010 Charts

    Better Chart Tooltips with Microsoft Excel 2010Let’s call a spade a spade: Microsoft Excel’s chart tooltips are lame.

    When talking about tooltips I refer to textboxes that appear when hovering over a data point of a chart with the mouse.

    Excel’s chart tooltips show by default the name of the data series, the point (e.g. the category) and the values. There is no built-in feature to change anything about them except for turning the tooltips off in Excel’s options.

    However, chart tooltips are a great interactive feature. They give the user the opportunity to easily explore the data and get additional information about selected data points on the chart.

    Have a look at Tableau as a benchmark. Tableau allows you to display any information in the tooltips (i.e. any given dimension or measure), to format them and to replace the field names by whatever you choose. There is even much more. For instance: my highly esteemed Tableau blogging colleague Andy Cotgreave showed on the outstanding blog of the data studio how to add conditional formatting to tooltips and even how to implement pseudo bar charts inside of a tooltip. Fantastic work, Andy.

    Back to Microsoft Excel. Can we do at least something similar in Excel? Let’s stay humble. I am not dreaming of great formatting features or even the fabulous things Andy did with Tableau. I am talking about just some nice and meaningful tooltips displaying more information than the Excel default does. Is this possible?

    Yes, it is.

    Today’s post shows how to improve Microsoft Excel’s chart tooltips using a textbox and some VBA. As always, providing the Excel 2007/2010 workbook for free download. 

    (more…)

  • The History of FIFA World Cup Host Elections

    FIFA World Cup Host Elections since 1930 – the applicants, the winners and some additional statistics visualized using Tableau Software

    © Rainer Sturm / pixelio.de

    Most of the Tableau related articles here on Clearly and Simply include how-to-tutorials or workarounds. Today’s post is different.

    In the light of recent events, today’s article will be straitened to a very simple, yet hopefully interesting visualization: The FIFA World Cup Host Elections over the course of time. The applicants, the withdrawals, the winners and additional interesting statistics of the football associations in the applying countries, like the number of players, the number of clubs and the officials.

    As always including the visualizations for direct access using Tableau Public and the option to download the workbook.

    (more…)

  • Wordle Tag Clouds in Microsoft Excel

    Create dynamic Tag Clouds in Microsoft Excel using advanced Wordle

    10k Comments Excel DashboardOne week back, my friend and Excel MVP Chandoo published a post to celebrate the very impressive milestone of the 10,000th comment on his blog: 10k Comments Excel Dashboard. Who dared to think he would do this without providing a dashboard? Of course he did.

    Chandoo provided a very nice dashboard showing all kinds of analysis and visualization of the comments received on his blog so far. By the way: Kudos on the marvelous success of your blog, Chandoo!

    Great layout and very interesting insights on an at a glance dashboard. Chandoo also included a tag cloud showing “what do they say”. He obviously created the cloud using Wordle, but he inserted it as a static image.

    This made me think. Is it possible to create tag clouds in Excel dynamically?  Yes it is.

    Today’s post shows a way how to include Wordle tag clouds into your Excel workbook, dynamically based on texts in any cell ranges. As always, the article includes the Excel example workbook for free download.

    (more…)

  • Site Catchment Analysis with Tableau 6

    Use Parameters and Table Calculations in Tableau 6 for a Site Catchment Analysis of Germany

    © R.-B. / pixelio.deFor a recent post called Calculated Fields in Tableau I used a site catchment analysis of Germany to explain how to use and take advantage of Calculated Fields in Tableau. The example posted back then was produced with Tableau 5.2.

    The main disadvantage of that workbook is the fact that you have to change a constant in a Calculated Field if you want to switch to another center location for the site analysis. Inconvenient and even impossible if you only have the Tableau Reader available.

    With version 6, available since November 10, 2010, Tableau introduced an extremely powerful new feature: Parameters. Parameters are user-defined constants that can be used in Calculated Fields. They can be entered by the user of the workbook either on worksheets or dashboards, are valid throughout the whole workbook (e.g. they are global) and can be used in any Calculated Field. Thus, it wasn’t far to seek that with parameters Tableau 6 would easily overcome the main drawback of the Tableau 5 site analysis workbook.

    Today’s post shows how to transform the site analysis workbook to Tableau 6 using parameters. As usual including the Tableau Public workbook for direct access and free download.

    (more…)

  • Animate Cumulative Data with Tableau 6

    Create a motion chart and animate cumulative data using the new functionality “Show History” of Tableau 6

    © Pixel King / pixelio.deThe article Animate cumulative data with Tableau described a workaround to visualize the history of data on the page shelf of Tableau’s version 5. The example provided in that post showed the growth of Walmart since 1962 on a map of the United States.

    The workaround included a custom SQL statement in the data connection to create a second date field to be used on the page shelf.

    The workaround did the job, but it came with a couple of serious disadvantages:

    • SQL knowledge needed to create the Custom SQL statement
    • Additional time needed to set up the data connection
    • Extremely slow performance: executing the SQL query after opening the workbook took between one and three minutes
    • Impacts on other data analysis and visualizations due to the artificially bloated data source

    As I already said at the end of the original article: A workaround. No more, no less.

    All that belongs to the past. With Tableau 6 you can easily animate cumulative data on the page shelf using the new feature “Show History”. Today’s post includes a how-to tutorial and the Tableau workbook for direct access here and for free download.

    (more…)