Leave a Reply

    Your email address will not be published.

    From Data Table to Small Multiples in Under Five Minutes

    Updated on: Aug 6th, 2014
    Data Visualization
    , ,
    Data presented in small multiples bar chart with four different colors to show different data results.

    When social scientists like me have lots of data, we tend to put it in tables. There’s nothing wrong with tables. They contain infinite details within a finite and teeny space, and they give the reader freedom to draw his or her own conclusions about which details are worth paying attention to.

    In today’s post I’m teaching you:

    • how to explore your data tables with Excel’s conditional formatting;
    • how clustered bar charts are the worst charts ever;
    • how a small multiples chart can save the day by quickly visualizing an entire table’s worth of details in the same amount of space as the original table; and
    • how to create that small multiples chart in Excel (and it takes less than 5 minutes, I promise).

    The Original: A Data Table

    This example comes from a short and sweet Child Trends brief: www.childtrends.org/wp-content/uploads/2013/07/125_Flourishing_Measures.pdf. This is a pretty typical situation in research and evaluation projects in which we’ve got some interesting variables across the top and multiple demographic breakdowns along the side.
    Microsoft Excel spreadsheet with data.

    Option A: Conditional Formatting

    I wanted to see the patterns and understand what was going on. For starters, I used conditional formatting to color-code any cells that were greater than the overall percentage. For example, in Column B, 93% of children “always” or “usually” were affectionate and tender with their parent. Girls were slightly more likely than boys to be affectionate and tender (95% of girls compared to 93% of children overall) so the 95% is highlighted.

    Now what can I see? Something cool is going on with the kids above poverty and whose parents have higher levels of education.

    This conditional formatting technique is a good start, but it didn’t seem to show me enough patterns, so I kept going.

    Microsoft Excel spreadsheet with data, some highlighted in green.

    Option B: Data Bars

    Data bars–within-cell bar charts–are another underused conditional formatting technique for exploring preliminary patterns from the comfort of your spreadsheet. I teach you how to make them here.

    What’s happening with column C? Why are those bars so short? Why are the kids less likely to bounce back? Now that I can see what’s going on, my brain’s full of questions.

    Microsoft Excel spreadsheet with data in blue bar charts.

    Option C: Clustered Bar Chart

    I made one of these bad boys just for fun, and the result was even more incomprehensible than I could have imagined. The not-so-fun part is that I still see charts like these in research and evaluation reports. If you’re still using these, please, contact me and I’ll help you out. Clustered bar chart.

    Option D: Small Multiples Bar Chart

    I could’ve visualized this data through a million different individual bar charts: a bar chart showing the overall percentages for each of the four indicators (the option selected by the authors in their original report), a dot plot showing the gender differences for one or two or three or four of the indicators, a bar chart focusing on parental education, etc.

    Instead of highlighting one or two patterns, I wanted to see the entire landscape of what was going on. Same real estate as the original data table. The chart could fit on just one page within the body of a report or in an appendix. But now, my brain receives the information through both images and text, rather than just text alone like in the original data table.

    And since my primary goal was to see the big picture, I opted for a generic title, generic subtitle, and emphasis-free color scheme. In other words, my title just states the graph’s content rather than putting forth a “so what?” (it’s modeled after a data table from an appendix, after all). My subtitle describes the data source but doesn’t give my interpretation of what’s going on. My colors are matched to Child Trends’ logo rather than Excel’s default color scheme, but the colors don’t emphasize one category or series of data over another. I’ll describe these distinctions in more detail in a future post.
    Data presented in small multiples bar chart with four different colors to show different data results.
    (You can also view the high resolution image here.)

    More about Ann K. Emery
    Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.


      Leave a Reply

      Your email address will not be published.

      You Might Like

      Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.


      Visualizing 24 School Divisions’ Submissions with a Dashboard in Microsoft Excel

      Amadu Sidi Bah encountered a forgotten folder on the shared drive: “Stakeholder Submissions.” These text-heavy reports had been left to gather dust. He wanted to make sure the stakeholders’ recommendations were used in policy decisions, and he decided to do something about it. In this blog post, you’ll see the Excel-based dashboard that Amadu created from those 24 reports.

      More »

      Inside our flagship dataviz course, you’ll learn software-agnostic skills that can (and should!) be applied to every software program. You’ll customize graphs for your audience, go beyond bar charts, and use accessible colors and text.



      Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.