Clearly and Simply

An Alternative to the Bar Chart Race

An alternative solution to the controversial animated Bar Chart Races in Microsoft Excel

1,276 words, ~ 6.5 minutes read

The other day, I watched an as interesting as entertaining dispute between Andy Kirk and Andy Cotgreave about the popular Bar Chart Races: Data Viz Debate: Is the bar chart race more than just a gimmick?

Although I truly admire Andy Kirk’s work, I am with Andy Cotgreave, here. I wouldn’t go so far to call them the “fidget spinners of data visualization”, but I find them hard to follow, especially when the speed of the animation is too fast.

Last week, Philip Treacy of MyOnlineTrainingHub published a post and a workbook, how to do a Bar Chart Race in Microsoft Excel. Philip visualized the 2019/2020 Premier League results in an animated Bar Chart. I am a big fan of Mynda and Philip’s MyOnlineTrainingHub, which is definitely one of the best Excel websites worldwide.

This made me think. Although I love Philip’s work, I am not a fan of the Bar Chart Race. So, how would I approach this? Today’s post provides my solution of an alternative for animated Bar Charts. As always, the post comes with the Excel workbook for free download.

A practical Example for Dynamic Storyboards

A more practical use case for Dynamic Storyboards in Excel: support the Animation of 2-dimensional data by showing the years before and after the current year on a Storyboard

717 words, ~3.5 minutes read

The previous post Dynamic Storyboards in Excel showed how to create small multiples (or panel or trellis charts) for 2-dimensional data in a sequence and let the user decide dynamically, how many small multiples shall be displayed. To describe the technique, the article used a kind of funny, but pointless data set: a part of an episode of the Italian cartoon series La Linea.

Well, creating a dynamically sized comic strip in Excel is a funny little example. But what’s the point here?

Here's the point: a Dynamic Storyboard may well be worth considering for a more serious dashboard, too. For instance, a Dynamic Storyboard may support a chart which displays the data for the periods one by one in an animation. The Storyboard can be very useful by showing the data in the context of a user-defined number of years before and after the current period. This can help to better understand the development of the data over time or to discover patterns.

Today’s post will show how a Dynamic Storyboard may support an animated chart using the example of the Gapminder Replica in Microsoft Excel, published here back in 2014. As usual, the article comes with the workbook for free download.

Dynamic Storyboards in Excel

How to create a Dynamic Storyboard of Small Charts (similar to Small Multiples, Panel or Trellis charts) in Microsoft Excel

812 words, ~4 minutes read

The article Motion Chart Excel Template used a small sequence of an episode of the Italian cartoon series La Linea to demonstrate how a chart can be animated in Microsoft Excel. I also used the same data in one of my entries to a Tableau data viz contest: Tableau’s Viz as Art Contest – My Entries.

Since it is a cartoon series, it is kind of obvious to also think about visualizing the data in a comic strip (or what I will call storyboard hereafter).

Today’s challenge is to make the size of the storyboard dynamic, i.e. let the user decide with one mouse click, how many images shall be displayed.

This article describes how to do this in Excel and – as usual - comes with the example workbook for free download.

How to handle Line Charts with many Data Series

2 Alternatives to avoid cluttered Line Charts: a Band Chart and an interactive Line Chart highlighting one selected data series when hovering over

804 words, ~4 minutes read

A Line Chart is definitely the best way to visualize the development of a continuous metric over time for several categories. That being said, Line Charts easily get cluttered and even may become useless, if you have many categories. Now, what is "many"? In my humble opinion Line Charts are already hard to read if they show more than 6 to 8 data series. But what if you have way more than 6? Like all countries in Europe or all states of the US?

And then what? No worries, there are alternatives. Adding an interactive feature to your chart can solve the issue.

Today’s post describes two alternative options for Line Charts with many data series: an interactive band chart (without VBA) or a line chart highlighting one data series when hovering over (empowered by VBA). As usual, the article comes with the workbooks for free download.

Power BI lookalike Tooltips in Microsoft Excel

How to create Power BI lookalike Tooltips in Microsoft Excel Charts

1,361 words, ~7 minutes read

Tooltips are an extremely helpful feature to explore and understand data.

When hovering over a data point of a chart, a textbox appears and displays the values and – if applicable - even additional information.

I love working with Microsoft Excel. I truly love the application. But I will make no bones about it: Microsoft Excel’s native chart tooltips are mostly pointless. There is no option to adjust or format them, to link the content to a cell range or the like. They only display default information and this is pretty useless in most cases. All you can do is to turn them off in Excel’s options to get them out of your hair.

I already posted a few articles about this subject, the first one back in 2010, including the same rant as above: Better Chart Tooltips with Microsoft Excel 2010.

Microsoft didn’t do anything about it. Excel’s chart tooltips are still as lame as they have been in the past 20 years. This is remarkable, because Microsoft provides much better tooltips in Power BI. So, they are aware that tooltips are helpful, but apparently they don’t see the necessity to let Excel users take advantage of it, too. So we are still on our own here.

Although I already provided a few options to display better tooltips in Excel (e.g. Customizable Tooltips on Excel Charts), I would like to come back to this topic again.

Power BI does not only automatically insert tooltips on charts, it also shows a vertical line across the entire plot area and displays the tooltip even if the mouse cursor is not above the plotted data series.

That intrigued me. Today’s post will provide 2 options how to mimic Power BI lookalike tooltips on Microsoft Excel charts. As always, the post comes with the Excel workbooks for free download.