Condensing the visual display of comparisons: Data Dashboards

by Stephanie Chasteen on September 13, 2017

I’ve been learning more about effective data visualization lately, and recently was in a wonderful webinar on Data Dashboards (with Ann Emery — whose blog has great posts about data viz, such as using color, and telling stories with data).  It was a wonderfully information-packed session, and I’d recommend it to anybody!  I have a lot to learn in this area, but find it fascinating — particularly since I learn a lot about human psychology and information processing along the way.

What is a Data Dashboard?

It’s a single visual aggregation of all the important information for a project or objective, in one compact display.  For example, you might want to compare all the responses on a series of questions in one display, or show how this year compared to last year on a series of measures, or show progress towards project goals, or show a series of line graphs.  Or, more likely, you may want to do a combination of these things.  First, consider your audience — what information do they need to compare?  What will tell the story that they need to think about?  Dashboards are just one communication tool among many; most useful for pulling together comparisons — across sites, individuals, or time.

In this post I’ll briefly go over some of the types of dashboards I learned about, and how to make some of them.  You really have to just go through it step by step to become familiar with it.  Once you start playing with these, you can get some great ideas.

Types of Dashboards

For more ideas about different types of data displays, see Ann Emery’s “Choose the right chart” posts.  Some other types of visuals she showed us were tracking progress towards goals, and combinations of the chart types below.  All of these are basically add-ons to an existing (nicely organized) table of data values.

1. Comparisons with mini bar charts

This could be used for giving a snapshot of how different sites are performing on a set of measures, for example.  Using Conditional Formatting in Excel, (the “data bars” option), and then being clever with formatting to make it look nice, you can make some nice little bar charts to accompany your data table.  In some cases, you may find it is easier to visually parse this information by using a “heat map” — where the percentage is shown by shading the bar to different degrees.

2.  Comparisons with deviation bar charts

If you want to compare two points in time, or compare one instance to the previous average, deviation bar charts can be really useful.  You could compare Year 1 to Year 4, or Starting to Ending.  This is also done using Conditional Formatting (“data bars”):  The “deviations” are shown as bars that show increases/decreases (e.g., comparing Year 1 to 4)

3. Two or more points in time:  Mini lines

This is a pretty common style, according to Ann, and is pretty easy, allowing you to quickly visualize trends over time.  This is done in Excel using “Sparklines.”

So, how do you do all this?

In the webinar, Ann walked us through doing this in Excel, which was very helpful.  Here are the basic steps.

  1. Create your data table in Excel, and declutter it.  For example, put all your variables in columns (e.g., “Question 1, Question 2) and your groups to compare (e.g., “Workshop 1, Workshop 2”) in the rows.  Give them short names, and organize it into any categories (e.g., “Attitude questions”, “Content questions”) that you want to compare.  Separate those categories with white space.  This isn’t just the data; this will become your dashboard, so make it look nice.
  2. Add your visual.  Detail on creating the visuals above is outlined.  Once you make one, you can copy to your additional rows of data.
    1. To create sparklines (to track data over time):  Highlight the row of data indicating trends over time.  Choose “sparklines.”  You may want to choose to use “Markers” First/last, to make the first and last data point particularly visible.
    2. To make mini bar-charts (to compare groups on similar questions):  Add a blank column between your columns of data.  Using the “=” function in this new column, create a “twin” column of data (i.e., this “twin” column is a copy of your original data).  Use Conditional Formatting on this twin column, choosing “data bars.”  To make it look nice, as in the example, edit the “rule” for that data bar to show the bar only, and choose minimum 0 and maximum 1 (thus the bar will stretch to the max).  To create a grey shading in the background (to make it clearer how far you are from 100%), use the Shading command.  Then outline each bar in white to make it stand out, by using internal and external borders (“more borders”) at the thickest setting.  You can separate the bars from one another by inserting a small blank column.
    3. To make heat maps (to compare groups on similar questions using saturation to indicate higher levels instead of the length of the bar).  As above, but choose Conditional Formatting, and color scales.  It’s best to outline them in white using borders, and try the green/white palette to start.
    4. To make deviation bar charts.  As above (Data Bars), but go into Edit Rule and choose Maximum (Number: 1) and Minimum (Number: 1), filling positive values in blue and negative in red.
  3. Write text to orient your user.  At the top of the dashboard, describe the dashboard. Define any variables. Put the year to the top right.
  4. Use clear text hierarchy.  You want the title to be large, and subtitles to be bold.  What do you want your user to look at first?  What is the most important?  Guide their eye.
  5. Brand the dashboard.  Use fonts and colors which reflect the client.  You can use colors to highlight categories nicely (e.g. the “Listserv” category may use red font, and then red sparklines). Use to avoid colors that are difficult to distinguish for color blind users (such as red and green:  Green and orange or blue and red are fine).  Add a logo.
  6. Print it.  Select the portion of the dashboard you want to print (you may have some notes or other elements off-screen), and choose Print to PDF.  You may wish to play with the size of rows and columns to make it fit nicely to a page. You may wish to change it to Landscape mode too.   Note that if you select all columns and then adjust the size of them, it will adjust the width of all columns in tandem: this is a good trick for ensuring equally sized columns (or rows).

I ended up using these to create a combination dashboard, which used bar charts to show the average question rating, sparklines to show the rating over time, and deviation bar charts to show how this year’s workshop compared to the historical average.  I really like it!  Below is the dashboard that I created for one of my clients.






Leave a Comment

Previous post: