more Americans are tying the knot

The Pew Research Center reports on some fascinating data. But I tend to be underwhelmed with the way they illustrate this data visually. The graphs aren't horrible. They look nice. They are well-labeled and on topic when it comes to the stories and reports in which they are found. But they still get under my skin. Because in many cases, some relatively minor modifications would transform the graphs from "not horrible" to great.

The following graph caught my eye as I was scrolling through my Twitter feed last week:

Take a moment to study this graph. What information does it reveal? What data points do you focus on? What comparisons does it enable you to make?

It's not a horrible graph. But it could be so much better. This prompted me to take a look at the full article in which this graph was contained. I had the same reaction to every visual display of data that was included. In all cases, the data in the graphs can help add visual evidence to the story that is being told (and Pew Research gets high marks from me when it comes to clearly articulating a story), but the graphs aren't structured in a way that facilitates that as well as they could.

By choosing the right type of graph and being more strategic with color, we can transform these graphs from not horrible to great. Let's take a look at each, in context of the stories that they are meant to help tell.

Story & Visual #1: Newly Married Adults

The new data show that 4.32 million adults (ages 18 or older) were newlywed in 2012, a 3% increase over the 4.21 million adults married in 2011.

Here's a quick overview of the changes I made:

• Shift from bars to a line graph: Yes, you can show time in a bar chart, but they don't tend to allow the audience to see trends as easily as the connected points in a line graph do. Also, years ordered descending downward isn't as intuitive as increasing years from left to right. 
• Use color more strategically: Don't use color just to use color. Rather, use it to draw your audience's eye to where you want them to look. In this case, if the point we're making is about 2012, let's use color there (and only there) to help reinforce the story that we want to tell (in this case we could have possibly even made the last line segment between 2011 and 2012 that same shade of green, it's that slope that shows the 3% increase referenced in the article; we'll look at another example using this approach momentarily).
• Related thought - decimal places: I originally wanted to reduce the number of decimals to one, but that leaves points that don't appear to be the same labeled the same, which can be confusing (for example, the 2011 point in the line graph appears slightly lower than 2010, but if we reduce to a single decimal point, both data labels would be 4.2). If the values look different, make sure the data labels are set to a format that doesn't appear to contradict this.
• Related thought - axis range: The rule is that bar charts must have a zero-baseline because of the way our eyes compare the endpoints (I'm not positive that this was the case in the original). With line graphs, you can get away with the minimum value on your y-axis being something other than zero, but you have to be cautious about over-zooming and making relatively small changes appear more significant than they are. In fact, when I first plotted this data in Excel, the program automatically zoomed way in:

Don't let your graphing application pick your axis range!

Story & Visual #2: New Marriage by Education

Almost the entire increase in new marriages from 2011 to 2012 is accounted for by the college educated.

This is the graph that originally caught my eye in my Twitter feed. In this case, the comparison we want the reader to make is between the Bachelor's degree or more series and the other series over time. We want to draw special emphasis to the increase over time for this group from 2011 to 2012, to help make the point that this group accounted for nearly all of the overall increase in new marriages.

The original chart isn't constructed in a way that makes this easy. Again, I'd recommend a line graph. In this case, the data works well (lines aren't overlapping, creating a spaghetti graph) and it's easier to compare the relative heights of the lines when they are all oriented against the same yearly x-axis (rather than repeat the years for each category, as was done in the original graph). Since the main point is about the Bachelor's degree or more series, we can call the reader's attention there through use of color. We can emphasize the 2011 to 2012 increase by using a darker shade of the same color. I rounded the figures, as decimal places weren't needed here (and can actually result in a false sense of precision, since I believe these figures are based on a survey sample, so not the entire US population).

Story & Visual #3: New Marriage by Age

The prime age for getting hitched is 25 to 34.

A similar approach can be taken for the third visual in the article, which was designed the same as the second visual, but focused on marriage rate by age. Typically, I would suggest leveraging the natural ordering of the categories (keeping the age groups in order from lowest to highest, as was done in the original), however in this case I think we can break that guideline and still have a chart that's easy to read because of the clear labeling of the various series. Again, this design (line chart, aligned by common x-axis, using color to highlight the series of interest) allows the reader to make the comparison we want - between 25-34 year olds and other ages - more easily than the original. Again, I rounded the figures shown in the data labels.

Note in this case, given the story (the prime age for getting hitched is 25-34 years), we could have potentially reduced the data shown to just the 2012 figures (perhaps in this case using a horizontal bar chart to compare across the various age groups - with that approach, I'd suggest keeping the age groups in numerical order). There are some benefits to retaining the historical context, however. First, it helps to put the 2012 figures into perspective. We also leverage the fact that our audience is familiar with this chart design (and how to read it), since we used the same approach previously. Whether to limit the data to only the pieces that directly support the story or showing additional context is always a question to debate when determining what to show (and the answer will change depending on the situation).

Story & Visual #4: Staying Married

It is one thing to get married, it is another thing to stay married. In spite of the recent uptick in newlyweds since 2011, it is still the case that fewer adults were currently married in 2012 (50.5%) than in 2011 (50.8%). The share of adults presently married peaked around 72% in 1960. 

It's probably no surprise that I stuck with the pattern of transforming a bar chart into a line chart here. My biggest issue with the original visual in this case isn't the chart type (though from a clutter/cognitive load standpoint, the single line is much cleaner than the multiple bars), but rather the discrepancy in time over the x-axis. In the bar graph, we start off in decades - 1920, 1930, and so on. Until the year 2000. After that, we jump to 2006. And then the figures are reported annually from 2006 through 2012. But all the bars appear visually the same, width- and spacing-wise. This is a big no-no. 

In the remake on the right, I've plotted the decade figures through 2010 and connected them with a line graph. Then I separately (on another graph that's layered over the first - this is a true example of brute-force-Excel) plotted only the actual dates for which there were values (on a scale that started off 1920, 1921, 1922, etc.), including the annual data points leading up to 2012. I colored only the points of interest - leading up to and 2012 to reinforce that the percentage currently married is at an all-time low, and the peak that happened way back in 1960.

The meta-point here is: if there is a specific story you want to tell, don't simply show relevant data, but rather display it in a way that makes it clear to your audience where to look for the evidence of the story you're telling. Choose a graph type that enables your audience to easily make the comparisons you want them to. Use color strategically to draw their eye to where you want them to focus their attention.

For those who are interested, the Excel file containing the above makeovers can be downloaded here.

slopegraph template

I've found myself increasingly using slopegraphs as of late. They can be useful when you have two time periods of data and want to quickly see increases/decreases between the two periods (example below; see second half of this post for more discussion and another example).

From a formatting standpoint, however, they are annoying. They take a lot of time to set up because basically everything is different from graphing application defaults. I realized as I was making a recent one that I make the exact same changes every single time and may actually leverage a template for this (I say "may actually" because I thought that would be the case once before, but it didn't happen, though I've heard from others that they do use it).

In case you find yourself wanting to use a slopegraph (or quickly see whether one will work given the specifics of your data), you can download the Excel template I created here (screenshot below).

"animation" with power point

Let me begin this post by stating clearly that the only types of animation in Power Point (or substitute your presentation application of choice) that I endorse are: appear, disappear, and (sparingly) transparency. Please steer clear of any bouncing, flying, or fading in/out (as well as any other "slick" animations by which you may be tempted). To use an analogy, flashy animation is to presentation software as 3D is to a graph: unnecessary at best, and distracting at worst.

But that's slightly off topic. Today, I want to show you how you can use Excel and Power Point together with some simple screencasting with QuickTime Player to simulate a fully animated video.

When I solicited examples for a recent workshop, one participant sent me a graph they had created, along with this explanation:

This graphic summarized the key finding of the LAC (Latin America & Caribbean) middle class flagship we just launched. It is clearly not difficult to understand but my frustration was knowing that it could be more effective as an animated chart that could tell in a few seconds how far LAC has come. I tried to no avail to find someone who could do it for us. At the end, The Economist did what I would have liked to have done for our launch (link). 

Could we have done this ourselves? Or who could have done this for us and for how much? What kind of skills are required?

General consensus when I asked around was that The Economist's version was probably done using D3. I don't have any experience with this, and when I read the first part of the summary on the site ("D3 allows you to bind arbitrary data to a Document Object Model (DOM), and then apply data-driven transformations to the document." ...that's a mouthful!), my inclination to use tools more familiar to my audience was confirmed.

What I'll show you here should probably be considered a brute-force approach. There are certainly more eloquent solutions out there, but in case you aren't familiar with those (or don't have time or interest to learn the tools that would allow for them), this is a workable solution using good old Excel and Power Point, together with QuickTime Player.

My approach was to build the final graph in Excel, and then make a number of copies of it, eliminating some of the data elements from each so that I could focus on one component at a time to tell the story. Then I copied and pasted each of these into Power Point (making sure the visual was in exactly the same place on each slide). I pasted onto separate slides, so you see the progression as you move through them. You could also do this on a single slide with animation in Power Point (appear/disappear), which just takes a little more time and patience to set up. Patience is key throughout this process - it's a little painstaking to set up, but in the end I think achieves the desired result. Finally, after my slides were created, I used QuickTime Player to record my computer screen and voice.

Here's my resulting video:



The Excel file used to create the graphs can be downloaded here.
The Power Point file used to create the video above can be downloaded here.

This was my first time using QuickTime Player to create a screencast. I found it to be pretty straightforward; the instructions I followed can be found here.

Note how you could use this same approach in Power Point to focus your audience's attention in a live presentation as well.

plotting a value within a range

I often refer to my method of creating graphs as "brute force Excel," meaning that you can make almost anything work in Excel, but it sometimes means getting a little creative. The method described in this post can be considered a prime example of brute force Excel.

This example was taken from a recent workshop, where I've anonymized the details. The goal was to understand the cost of various high cost items in a given region relative to some comparison regions. The hypothesis was that the region of interest (Region 1, in the example below) had higher cost items than the other regions. The original visual was a table that looked something like this:

The table in this case might be helpful for the exploratory analysis, but it isn't well-suited for explanatory analysis, because there is simply too much to try to take in to form a conclusion. So I knew going in that I wanted to make the data more visual. The question was: how?

While I was considering this, I sketched out an idea:

Rather than show the individual values for each of the regions, my idea was to plot the range of prices across the various regions, from the minimum value to the maximum value (irrespective of which region has the min or max price, since it didn't seem like that was critical for what we are trying to show here) and then show the specific value for Region 1, the region of interest, within that range.

The next challenge was how to create this visual in Excel. I'll take you through how I did this. The brute force in this case is a couple of stacked bar charts, with some of the series made to be invisible. Here's the step-by-step:

STEP 1: Plot the minimum price and range (the difference between the maximum value and the minimum value across the various regions) as a stacked bar chart on the primary axis.

STEP 2: Make the blue series (Min) invisible by formatting it so there is no fill color, no line, and no shadow (I will never understand why Excel adds a shadow in the first place!).

STEP 3: Plot the Region 1 price and Marker (for this, I just put a value of 100 across all of the items - just big enough for it to show up on the graph - this will be what we'll use to show where Region 1 falls within the range) on the secondary axis. At this step, you must set the secondary axis maximum to $16,000 (overwriting the default of $10,000) so that it lines up with the primary x-axis.

STEP 4: Add data labels to the green series (Region 1) and format them so they are placed at "inside end" of the bar. Then make the green series invisible (no fill, no line, no shadow).

STEP 5: Format, format, format! The base visual looks like what we want. Now it's about playing with the details of the visual so that the information is as straightforward to consume as possible.

In this step, I think about getting rid of clutter, using color strategically to draw my audience's eye to where I want them to pay attention, and adding titles, footnotes, etc. to describe and explain my visual. A couple of notes on changes I made in this step:

• The item names and horizontal borders are in the cells directly (vs. part of the graph); I find it easier to format the visual this way, you just have to be sure that you've sized the graph so it lines up to the labels. 
• I reversed the order of the y-axis so the items range from min to max (vs. max to min as shown in the above steps) to leverage how our eyes tend to take in information (from left to right).

The above visual still takes a little processing to figure out what's going on. But I find the data much faster to process here than it was in the original table. Now, we can see relatively quickly that the price in Region 1 is mostly on the lower end of the ranges when compared to other regions (with a couple exceptions).

The final step would be to put the story around this visual: what it shows, why that's interesting, and what action should be taken (hard to do with the generalized example, but there was an interesting story here in the original version).

The Excel workbook with the progression outlined above can be downloaded here.