3 Comments

  • […] The best thing I’ve ever written about, and At-a-Glance Patterns with Area Charts | Ann K. Emery […]

  • […] The best thing I’ve ever written about, and At-a-Glance Patterns with Area Charts | Ann K. Emery […]

  • […] Swapping out that default color scheme for customized colors that match your stakeholder’s logo doesn’t hurt either. (P.S. Want to make this yourself? I’ve got a tutorial.) […]

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    At-a-Glance Patterns with Area Charts

    Updated on: Aug 27th, 2014
    Data Visualization
    , , ,
    Handout using small multiples charts to show data that is color-coded and sorted into columns.

    A few weeks ago I showed you how to create small multiples bar charts simply by creating a stacked bar chart in Excel with invisible white segments. In 5 minutes or less! Today’s strategy takes a few minutes longer, but the result is worth it.

    The Original: A Data Table

    The fake scenario: We’re prepping for an upcoming meeting with grantmakers. Like most grantmakers (and humans), they’re inundated with too much data. Our job is to make the data easier to “see” and understand. Rather than expecting them to read a 50+ page report prior to the meeting, we’re going to condense a few key facts into a single handout.

    The fake data: Health indicators in select states where grantmakers have supported community health initiatives over a 20-year time span. Pretend that health indicator 1 is something like fertility rate (births per woman). I realize the numbers are too high to represent actual fertility rates. It’s fake data. But you get the gist of what this dataset might show you.
    Microsoft Excel spreadsheet showing a dataset.

    Option A: Exploring with Sparklines and Sparkbars

    I began exploring the data table with sparklines and sparkbars.
    You’ll need Excel 2010 or 2013 for sparklines, which is why I used my Mac for this post instead of my old-as-dirt-but-love-it-anyway PC with Excel 2007.

    Just highlight the row or column you want to visualize (e.g., C3 through M3), and find the icon that says Insert Sparklines.
    Microsoft Excel spreadsheet with data set and coordinating sparklines.

    Option B: Exploring with Conditional Formatting

    Conditional formatting is the 8th World Wonder. Rather than color-coding cells by hand, you just highlight a row or column (like B3 through M3), click the Conditional Formatting icon (typically in your Home tab), and start clicking on whatever you feel like. You don’t need to know what you’re doing. You won’t break anything.

    In this example, I selected Color Scales. (Data Bars are also a personal favorite–I teach you how to make them here). After creating Color Scales, I adjusted the print settings so that the heat table could serve as a one-page meeting handout in itself. I’ve been using heat tables a lot recently, so I’ll post more examples in future posts.
    Microsoft Excel spreadsheet chart using the color green to highlight data.

    Option C: Visualizing Everything in One Chart

    Raw numbers for indicator 1 + percentages for indicators 2 and 3 = nope. The computer instinctually knows this is a bad idea.
    Microsoft Excel spreadsheet showing data and a chart on top of the data.

    Option D: One State, One Chart

    Again, the raw number plus percentage issue is like comparing apples to oranges.

    The general idea here is that yes, you could narrow down the data table by focusing the reader’s attention on a single state. Maybe our group of fictional grant makers is especially interested in one state over another.
    Microsoft Excel spreadsheet showing data and a chart on top of the data.

    Option E: Three States, Three Charts

    Try to look past the horrid default formatting and the awkward raw number + percentage dilemma. The idea is that you compare each state side by side.
    Microsoft Excel spreadsheet with data on the top and charts below.

    Option F: One Indicator, One Chart

    You might decide most important comparison to share with your fictional grantmakers is an apples-to-apples emphasis on indicator 1.
    Microsoft Excel spreadsheet with data on the top and one chart below.

    Option G: Three Indicators, Three Charts

    I’m optimistic that this layout, when formatted properly, would be extremely easy to understand.
    option-G_three-indicators-three-charts

    Option H: Small Multiples

    In my fictional example, I formed relationships with the fictional grantmakers and deduced that they would benefit from seeing at-a-glance patterns of each of the 9 data series separately, i.e. a small multiples chart which is essentially just a visual depiction of a traditional data table. Their fictional meeting allots 15 minutes to view and discuss the handout that I’m about to create, so I decided that displaying each variable in its own chart would allow them to make their own connections between patterns. This small multiples approach contrasts with Option F (where the layout assumes that comparisons across states are most important) and with Option G (where the layout assumes that comparisons across indicators are most important).

    First, the unformatted small multiples:Microsoft Excel spreadsheet with data on top and nine small multiples charts below.

    Formatted Version H1

    Isn’t it incredible what a little tweaking can do? First, I went through my Data Visualization Design Process article and made sure I covered all the big-picture aspects of the chart. Then, I went through the Data Visualization Checklist that Stephanie Evergreen and I developed to make sure I crossed my t’s and dotted my i’s.

    This first version has states as rows and indicators as columns. The area charts are color-coded to match their indicators. State icons are gray.
    Handout using small multiples charts to show data that is color-coded and sorted into columns.

    Formatted Version H2

    There are always multiple correct ways to display a dataset. This next version still has states as rows and indicators as columns, but the areas charts are color-coded to match their respective states. Now the state-shaped icons are also color-coded instead of being gray.
    Handout using small multiples charts to show data that is color-coded and sorted into columns.

    Formatted Version H3

    I transposed the area charts (i.e., the states are columns and the indicators are rows). Now we can delete redundant vertical axes. In other words, since health indicator 1 ranges from 0 to 8, and all the indicator 1 charts are next to each other, we don’t need to repeat the 0 to 8 labeling in all three area charts. Our readers get more white space and less mental clutter. Without the repeated vertical axes, our small multiples chart is really more of a panel chart, but those nuances are for another day.

    This version’s my personal favorite. How about you?
    Handout using small multiples charts to show data that is color-coded and sorted into columns.

    Here’s how the formatted versions compare:

    Side by side of three different handouts using small multiples charts to show data.

    Your Turn

    Which option would be best for your audience, and why? Would you format the small multiples charts differently, and how?

    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.

    3 Comments

  • […] The best thing I’ve ever written about, and At-a-Glance Patterns with Area Charts | Ann K. Emery […]

  • […] The best thing I’ve ever written about, and At-a-Glance Patterns with Area Charts | Ann K. Emery […]

  • […] Swapping out that default color scheme for customized colors that match your stakeholder’s logo doesn’t hurt either. (P.S. Want to make this yourself? I’ve got a tutorial.) […]

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    You Might Like

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

    Enroll

    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.

    Enroll

    Subscribe

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