Comparing survey responses over two time points. Same data, displayed 6 different ways

February 18, 2021

I’ve been working with a few local councils over the last year. One thing that regularly pops up is the need to display and compare data over two time points. I scoured the web to find a good example and came across one local council’s website showing a comparison of their performance from 2019 to 2020. This repeated cross sectional data looks at seven main measures and are displayed using percentages in a standard table. I’ve changed the results slightly for anonymity purposes, but it looks a little something like this.

Summary of council’s performance. Displayed as a table

How many different ways could we effectively display this data? I’ve come up with six variations, all with positives and negatives. Let’s have a look through.

Display 1 – Clustered bar chart

This is by far the most frequent display that comes across my desk. It normally looks a little something like the one below.

While a clustered bar chart can be a valid option to use, we need to steer away from the standard Excel template and make some big improvements.

Let’s make some changes!

Some of the category names are quite long and therefore Excel has angled the writing on the X axis. Swap it to a horizontal bar so none of the writing will be squished or angled and it’s much easier for your audience to read through.
What is the focus? In this chart 2020 is our main focus with 2019 there for comparison. Therefore, use an action or focus colour for 2020 (in my case blue) and grey for the comparison (2019).
Add data labels directly to the chart, that way your audience doesn’t have to dart back and forth to work out the values.
Remove all the clutter like the gridlines and border.

With the improvements your chart should look something like this. As I’ve said before, people know how to read bar charts so you don’t run the risk of your audience not understanding it. However, it is very busy and may require a bit more visual processing time, so we risk the audience skipping over it all together.

Display 2 – Back-to-back bar chart

This back to back bar chart is a little easier to navigate through. We have a horizontal bar for 2019 and a mirrored horizontal bar for 2020. They share the same spine.

Again, we are focused on the 2020 results, so I have coloured them in our action colour blue and ordered them biggest to smallest. We can easily assess each category individually and then by its equivalent year.

This chart works for all necessary purposes. Without the data labels directly embedded it could be a lot harder to decode by comparing lengths.

Display 3 - Dumbbell dot plot

Here we have our categories on the Y axis, percentage values across the X axis and data series displaying 2019 (grey) and 2020 (blue). The points are connected by a thin grey line.

The benefit of this chart over the clustered bar and back to back bar is that we can focus a lot more on the change between the two time points. Why is that? Because unlike a bar chart which needs to start at 0 to accurately decode the chart, a dot plot can start at the lowest value (or thereabouts). In my case the X axis starts at 40%.

There is a lot more white space on this chart which makes it easier to take in and we are drawn to the length of the grey line in between the two circles. In the case of “Lobbying”, the values have stayed the same over the two years. I’ve represented this with a grey circle and a blue border. If your values are too close together, this chart may not be the most suitable.

Display 4– Table with sparklines

Sometimes the best solution for displaying your data may be keeping it in a table. It could be that you have a large number of categories or that you were required to add a table of results in the appendix of your report. Whatever the reason, add an extra column in your table and insert sparklines so your audience can easily see the trend or pattern in the data. Excel has a sparkline feature which is simple and intuitive to use. Go one step further and colour code your high and low points. In the case above, I have used orange for the low point and blue for the high point.

It should be noted that the Y axis of the sparkline is measured by the highest and lowest point of the data in that particular row. Therefore, it may not be accurate to compare between sparklines. If you want a consistent axis throughout, you can change this under the sparklines tab by clicking Axis > Vertical > and select Same for all sparklines under Minimum value and Maximum value.

Display 5 – Small multiple bar charts

Now we are getting down to the business end. If you have a busy chart with a lot happening, consider small multiples. They are an efficient way to break down a cluttered chart that allows your audience to walk through one category at a time and make comparisons.

In this case, we have seven smaller bar charts each representing one measure. They are all designed to the same scale so we can compare not only within each chart, but across the categories as well. I have colour coded 2019 in grey as it’s there for comparison, and 2020 in either blue, if it did better than the previous year, or orange if it did not.

On quick glance we are able to see there are three blue bars and three orange. We are easily able to navigate through each category or measure and see how it’s performed against the previous year. The advantage of small multiples is that you can split them up and use the individual charts throughout a report or PowerPoint presentation as well.

Display 6 – Small multiple slope charts

In sticking with the small multiple theme, a slope chart is an excellent way to display and compare data over two time points. We are decoding the slope chart by not only looking at the position of the dots but also the angle of the line that joins to the dots. By colour coding blue for positive performance and orange for negative, I’m further aiding readability and digestibility.

Like the previous small multiple bar chart, we can split this chart up and use it in different sections of our report or PowerPoint too.

In summary, I have presented you with six different options for displaying your data over two time points. There is no right or wrong answer, rather options for you to consider and get inspiration from.

Remember:

Consider who your audience is and the best way to reach them.
Draw focus where it is needed. Push everything else to the back for comparison.
Aid in digestibility by adding that storytelling element to your chart.

A lot to consider, but some options above to help your thinking. Happy designing!

Thank you to my friend and colleague Dr Elena Swift for her advice and assistance in writing this blog.

Let’s talk small multiples

September 29, 2020

Check out this line chart below. As the title suggests it’s looking at AFL club membership from 2016 to 2020. It’s extremely busy with 18 different lines and colours that are awfully similar. It’s hard to gain any insight from the chart (besides that fact that it’s quite busy), let alone focus on any single data series. What if I asked you to tell me how club membership was affected by COVID-19 from 2019 to 2020? Which clubs did well? Which ones lost members? What was the overall trend? It’s virtually impossible which makes the chart pretty useless.

Luckily, we can redo this chart using small multiples instead.

What are small multiples?

The term was popularised by Edward Tufte and as he describes “resemble the frames of a movie: a series of graphics, showing the same combination of variables, indexed by changes in another variable.” They are also referred to as trellis, lattice, grid or panel charts. They are an efficient way to break down a busy chart that allows your audience to walk through one category at a time and make comparisons.

So let’s redo this chart using small multiple line charts.

What have I done?

I’ve created an individual chart for each team showing membership numbers over the last five years. Each chart is to the same scale, which is incredibly important so we don’t mislead our audience. I have decided to colour code the chart with orange used for membership decline over the previous season and blue to indicate membership growth. I have then finished this off with a descriptive title and subtitle at the top which turns my chart from an exploratory chart to an explanatory one (i.e. I’m telling you what my take away message is). Lastly, I have listed my source down the bottom. Has this redesign made it easier for you to understand and interpret the data?

Let’s try another design using small multiples area charts. This time I will incorporate data storytelling.

I’ve pushed everything to the background using grey. I’ve then decided I’ll focus on membership decline once again and highlight that using a bold red colour (a little alarmist, but intentional given the circumstances). I’ve used a complete sentence to highlight my story and colour code it to the chart. Lastly, of course, my source at the bottom.

Depending on where I am using the chart (a report or PowerPoint presentation), I may want to give context that Melbourne was hardest hit by a second wave of coronavirus and teams were moved to hubs predominately in Queensland. Additionally, no Melburnians were able to attend live games within Victoria in 2020.

How have I done these small multiples?

Great news, it’s all in Excel! Highlight and create your first chart with only the one category.

Once you have formatted it the way you want it, right click and save as template. Then copy and paste your chart however many times you need to. In my case, it was 17 more times. Lastly right click on each chart and choose “select data” to populate the new data.

Small multiples can be used for just about any type of chart. My blog on displaying Likert scale data uses a few more examples.

If you need some further inspiration that’s not football related… (I had to, it’s September!), check out these links.

Financial Times: Coronavirus tracker

McKinsey & Company: Tracking US unemployment through the COVID-19 crisis

Happy designing!

Are word clouds dead? The answer is yes, but no.

August 18, 2019

Word clouds were big 9 or 10 years ago, thanks in big part to the online application Wordle.

It only takes a quick google search of ‘word cloud’ to see how popular they were and how we absolutely used them to death. I too, am guilty of it. I’m pretty sure I created one for our annual report back in 2010 (we won’t speak of this again). Now when I see a word cloud used as a visualisation, a cold shiver goes down my spine.

However, there are some exceptions. I will get to this in a moment.

What is a word cloud?

In case you don’t know (or have been cast away on a desert island for the last 10 years… welcome back), word clouds are an image composed of source text that you input into a program or online application (like Wordle). It then creates an image of the text, scaling the words by how frequently they are used. You can then change the font, colour palette and layout.

They were cool, but very overused. People would dump paragraphs of text into these programs without a second thought. They can be hard to pull any meaning from and we only really pay attention to the few big words. Phrases would lose all meaning unless you grouped the words together.

I do however think there is some value left with word clouds. Hear me out…

What are they good for?

They are a good tool for qualitative analysis when you are making comparisons. Before and after, 2018 vs 2019 etc.

We used it to do a qualitative analysis of the Survive and Thrive program, which was an environmental education program delivered by the local CFA to school children in Anglesea. The paper can be found here - https://files.eric.ed.gov/fulltext/EJ1173481.pdf

We used a visual mapping exercise where children were asked a series of questions about what they would do in a bushfire and who would they give information to. This was done over two time points, before and after the CFA training.

We then used word clouds to draw comparisons between the two time points. See image below.

Example of a word cloud to make comparisons with qualitative data.

Why was it a success? Well, we can easily pull meaning from the before and after images. I have kept the same font and colour palette and we are not flooded with words.

This paper was published in 2014, and I would probably do things differently again now if I had the chance. Some of the word clouds used in the published article had words written both horizontally and vertically. The minute we change the angle of the text, the longer it takes to read and process. So I would keep all the words written horizontally.

There is one other occasion where I used a word cloud and that’s during my Design and Data Visualisation Short Course. In the PowerPoint session, I demonstrate some cool interactive tools that make your presentations more engaging. One of those is Poll Everywhere. We use it to ask questions and vote on important things (like - do you like coriander…) but I use word cloud to ask participants to use one word to describe how they are feeling about today’s course. All the answers are anonymous and they begin to appear on the screen in front of them.

Below is a screen shot from my August course. I’m just relieved no one said bored!