Humans in the western culture tend to see things that trend upward as positive and lines that trend downward as bad. But what if bad is good? And not like my college boyfriend. But in the sense that a decrease is a good thing. Let’s use the example of weight loss.
If you made a line graph of your weight while in an exercise program, it would (hopefully) trend downward but down is usually interpreted as bad, especially when presented in the context of other metrics where down really is bad. Here are three possible ways to handle this scenario.
Possibility #1: Reverse the Variable
This is my favorite suggestion because it’s so simple. Rather than track weight, track cumulative pounds lost. This number will (hopefully) increase over time and follow the up is good interpretive lens.
Such a very easy fix. That said, I’ve suggested this remedy to clients in the past who have literally cringed and/or laughed. Many times folks are not in a position where they can just go transforming variables.
Possibility #2: Use a New Graph Type
This suggestion is best when presenting this metric with several others that trend upward. To make it visually really clear that there is something different about this metric, change the graph type.
While column graphs aren’t normally used to depict trends, it can still work here because it’s purpose is to say Hey this metric is not like the others – and the subtitle tells the story.
Possibility #3: Shade Under the Line
The most Excel ninja suggestion of them all, this one works best when down is good – to a point. Let’s say your weight goal was 165. Down was good until week 7 and then down became bad. We have a benchmark target weight to compare to and we can shade the areas between the trend line and the benchmark to help show the difference.
The ninja skills required for this one are pretty intense but you can read through the logic spelled out by the author, David Merle Montgomery, and download his file and get to playing.
What about changing the order of the y axis so that the trend is essentially reversed? Then the same data would be on a line going up. Muscatello, Searles, Macdonald, and Jorm (2006) ran a randomized controlled trial on graph types and compared a typical line graph where decrease is actually supposed to be good to a line graph with a reversed y axis, so that up was good. They found that accurate interpretation of the trend nearly tripled. Yowza! But I have to wonder how closely respondents looked at the y axis labels when making their judgements because the reversal of the axis is an unusual site. It violates some of our graphing traditions and has led to at least one recent visualization that was targeted as misleading by national news sources. Take your chances?
Huge thanks to my clients at OHA who initially asked me this question (I have the best clients) and my colleague Gavin McMahon for helping me think up solutions (I have the best colleagues).
Slopegraphs are a newer graph type with powerhouse capabilities. They rely on Excel’s line graphing feature but they don’t necessarily have to show change over time. Slopegraphs play into our ability to judge slope fairly well. For this reason, they are perfect for highlighting the story of how just one category decreased when others increased, or to show that one increased at a rate much faster than the others.
Let’s say we are comparing sales in each department of a grocery store, before and after they moved locations. As a side by side bar graph, it would look like this:
Even though humans are good at detecting length, this graph is somewhat difficult to digest. It’s a bit hard to see that sales of cheese are actually down at the new site, for example. It’s easy to miss that just one New Site bar is shorter than its corresponding Old Site bar. A slopegraph will make the story clear.
The table for a slopegraph is probably quite familiar: just two columns of numbers.
You will highlight the rows and columns and insert a line graph. I prefer a line graph with markers, but maybe I’m just obsessed with dots.
It doesn’t quite look right – the departments are on the x-axis and they shouldn’t be. Click your old friend Switch Row/Column.
You can see the beginnings of the slopegraph in this modified line graph already. The differences are minor but interpretively important. Slopegraphs usually have no space between the end of the line and the end of the plot area. The lines are pushed to either side of the graph. To delete that gap, we are going to click a single magic button inside Excel. Really, this is pretty much where it all happens.
Right-click on the x-axis and select Format Axis from that menu. In the Format Axis box that opens, look for the area with the heading Axis position. Under that, pick the radio button next to On tick marks.
By default, Between tick marks is selected but On tick marks will push the lines to the edges of the graph.
From here on out, it’s all formatting baby!
Slopegraphs tend to be long and narrow, unlike line graphs, which are usually wide and short. So resize the graph by stretching the corners. This also puts some distance between points that are nearly on top of one another.
We also need some space on either side of the line to add labels. Currently, there’s a y-axis there. Delete the y-axis (just click on the numbers and hit the Delete key). Do the same for the y-axis gridlines. That buys a little bit of room on the left, but not enough. So click inside the plot area – the white space in the middle of the graph – and drag it’s side handles in on both sides. The graph space will narrow but the overall chart area will stay the same.
At this point, it should already be really obvious that one line is going down. We can also see that another line is increasing moreso than the others. Slopegraphs highlight this story better than any other chart type. As we continue to format the chart, we will use action colors on those two lines to bring attention to them and the other lines will go gray.
Now it’s time to delete the legend and label each point of the line. With the slopegraph, line labelling is a little tedious. You can only add labels to one line at a time. Also, I like to add the label to one side and just the number to the other, so there are a lot of clicks involved. Here’s the most streamlined way to do it and we will alter the colors in the process.
Right click on the top line and select Format Data Series. In the box that opens up, change the line color, marker fill color, and marker border color all to a medium-light shade of gray.
The line should still be highlighted inside your graph. Right-click on it and select Add Data Labels. This will add the dollar values to both markers. Click on the labels and change their color to match the lines. With those labels highlighted, carefully click on just the left label so that it is the only one highlighted. Then right-click on that label and select Format Data Label (it should be singular). New box opens! Check Series Name so that the right grocery department label will be added, but only on the left. Uncheck Show Leader Lines (this will matter more later). Personally, the comma that separates the label from the value offends me. In the Separator drop down menu, pick the option that says (space). Then look for the Label Position choices and pick Left.
Yes, lots of steps there but the payoff is clear labeling and smart color coding that support the story inside this data. Repeat this process for the rest of the lines, choosing a medium dark color for the rapidly increasing line and a dark color for the decreasing line.
As you labeled these lines you probably noticed that the labels for Packaged and Frozen were overlapping each other. To fix that, just click on each label and drag it up or down a little to space them apart. This is why we unchecked Show Leader Lines earlier. With that box checked, each label would have an ugly, cluttering little line tying it to its part of the slopegraph.
Its ok add a great title and stop here. I like to add a final touch by inserting vertical lines that shoot up from Old Site and New Site. Right-click on the x-axis and in that menu, select Add Major Gridlines. BOOM! I also delete the x-axis line. To do so, right click on the x-axis again and select Format Axis. In the new box, look for the line color area and select no line.
The slope of the slopegraph markedly accentuates increases and decreases when comparing two sets of numbers.
This post is an excerpt from my latest book, Effective Data Visualization. It has loads of advice on the best chart type to use and how to make it in Excel.
Skyrocket Dataviz with Graph Guides!
Graph Guides is a custom-built, year-long sprint through 50 Academy tutorials.
When you enroll, we’ll assess your current data viz skill set, build you a customized learning path, and hold your hand as you blaze your way to new talents.
I needed a few new appliances. My old Shark vacuum cleaner bit the dust for the second time and in the same week my washing machine started eating all my towels. So I was interested in the spread from your Good Housekeeping Research Institute, reporting on its expert tests of different washing machines. You’ve had that Seal of Approval since 1909. Sounds credible.
The thing is, I’m a researcher and maybe my standards are just high but I expected more data from a department that does research.
See, the “data display” included a measly 7 washers and the data itself is extremely thin – aside from the picture and the price, there’s only a short description, nothing that would help me rank one over the other.
Consumer Reports, on the other hand, reported on its expert tests of vacuums and was able to fit 22 vacuums and 10 data points on each into roughly the same amount of space (along with pictures and descriptions). This is research I both need and can handle.
The thing that concerns me, stirs me in my gut, and compels me to write this open letter is that – and I’m just gonna come right out with it here – this is some information visualization gender disparity bullshit.
Good Housekeeping is pastels and italics, catering to a largely (87%) female demographic. Consumer Reports is red and black and tabular and square, speaking to a majority (58%) male readership. Even though the Wall Street Journal refers to these two specific magazines when it says “the recommendations of these private, independent watchdogs were based on rigorous testing,” it doesn’t take a degree in feminist studies to recognize that the actual data behind the rigor is deemed as less necessary because you are giving it to women. Thing is, there’s really no need to curtail the amount of information I need to make an informed, data-based decision about my appliances simply because I have a vagina.
Information visualization is real and here and at our fingertips and women need it too.
With Love,
Stephanie
PS. I just revamped my Workshops and Design services pages in case you need some directed guidance and support. ❤
Most of the time, I think radar graph are deployed wrong. They are designed to show percentages along several categories – like a bar graph could – but the axis are distributed around a central point, such that the percentages link together and create a shape. Choosing a radar graph, then, is a decent choice when (1) the categories are all connected in some meaningful way and (2) the resulting shape actually means something. And this is not often the case.
Check this radar I found in an infographic on Visual.ly:The three categories here – work performance, late to work, fall asleep at work (who hasn’t?) – are somewhat related to each other. BUT the resulting shape doesn’t actually mean anything. Shouldn’t be a radar. No. Radar.
By contrast, radar charts are appropriate when the exact values aren’t totally critical for a reader but the overall shape says something. For example, my friend Kylie Hutchinson pointed me to the Program Sustainability Assessment Tool and this handy thing helps consultants figure out whether a nonprofit is set up for sustainability. The tool measures 8 domains (related concepts – check!).
The folks over at PSAT told me that, at the start of the consulting adventure, organizations are usually high in one domain, middle in several, and low in a couple. In other words, when we sort the data from greatest to least at the beginning and plot it on a radar chart, it looks like a crooked teardrop.
Programs that initially don’t look like a crooked teardrop might alert the consultant to something out of the ordinary.
During the consulting adventure, programs usually increase by a point or more in 3 domains, often choosing domains that initially had low to medium scores. By the time the consulting adventure is over, the program’s sustainability doesn’t look like a crooked teardrop anymore. It looks more like how my pancakes tend to turn out.
The shape gives the overall composite picture of how the program’s sustainability shakes out. The shape is a profile. It means something (check!). The same data as a clustered bar graph doesn’t have the same interpretive power.
Most of the time, I think radar charts are misused, contributing to confusion and clouding the data. Generally, when I see one, I’m annoyed because the visualization doesn’t support understanding the data – and that’s the reason I visualize in the first place. Radar charts *can* work, though, when the data combine into a meaningful shape.
I know, that’s how it goes, right? We are so used to seeing trends over time visualized as line graphs that even my 3rd grader can interpret them with ease.
But sometimes it helps to have other options that better fit your data.
Let’s say you are graphing the number of male and female CEOs of Fortune 500 companies over time. Here it is as our reliable line graph:
The number of female CEOs is so small that the line is barely distinguishable from the x-axis. Not good (for several reasons). Whenever my categories are parts of a whole AND one of my categories is really tiny, I prefer stacked columns.
Stacked columns create a visual chunk that is easier for the eye to detect, in my opinion, and this is helpful for those tiny trends. It is as if the area under the line in the line graph is filled in – it’s easier to see (and yes, an area graph would be an alternative but I find that people have a hard time interpreting those).
Add stacked columns to your suite of choices for graphing trends over time. They are a great alternative to show small categories, like college students who are homeowners vs. renters, days of sunshine vs. snowfall in Texas, or times you thought about sending me beer in the mail vs. days you actually did.
Speaking of beer and Texas, I’m headed to Austin soon. Where else will I be? Check out my Upcoming Events and especially look into my public data visualization workshop with Ann Emery. I only hold public workshops every so often, so if you are in Chicago next week, here’s your chance!
Seriously, that’s the most important question to ask when creating a data visualization. And its the first thing I ask a client who sends me data for redesign.
What’s your point?
The answers drive nearly everything about visualizing that data. Here’s how that conversation often goes:
Client: “Thanks for working with us, Stephanie. We have this data from parents and students and right now it is in this bar graph and we are certain it could be displayed better, we just aren’t sure how.”
Me: “I can help with that, Client! What’s your point?”
Client: “Excuse me?”
Me: “What’s the point of showing this data about parent and student perspectives? Right now, it looks like you want people to compare parents and students. Is that your point?”
Client: “Actually, no. And that’s the most clarifying question you could have asked. Our point is that generally we expect students to report higher than parents do on all of these questions, but students have way lower expectations to go to college than their parents have of them. That set off some alarm bells for us.”
And this is when I silently pump my fist in the air because Client answered the most important question and now I know how to better display this data.
Me (after I catch my breath from all that fist pumping): “The first thing we are going to do, then, is take what you just said, and make it the headline of the graph. We are going to replace this generic title with your main point. The next thing we will do is swap out a different graph type, maybe something like a slopegraph, since those are pretty good at highlighting when one thing is decreasing a lot and the rest are going up. Give me a day to play with some ideas and let’s talk tomorrow.”
THE NEXT DAY
Me: “Good morning, Client! What did you think of that slopegraph I sent you?”
Client: “It really does say exactly what we originally thought we needed to show. But I talked to my colleagues more after our call yesterday and asked them ‘What’s the point?’ and we decided that the real bottom line point was that so few students have expectations to go to college. Forget the parents – that’s a secondary issue right now. ‘What’s the point?’ really helped us hone our thinking.”
Me: “Ah well in that case, you have other options for showing that point.” *telepathically sends new visual possibilities* “Maybe one of these?”
Client: “These are all right to the point. We will choose one today.”
Asking the most important question sharpens the thinking and the messaging surrounding the data and, in doing so, reveals the best way to visualize the data. When you get stuck with your graph, keep asking “What’s the point?”
Visualize Branching Questions with Nested Area Graphs
I got this idea from the smart people at Innovation Network. And it is super amazing at visualizing the sequence of survey questions that branch. The kind where it says “if you said Yes, proceed to question 32” and stuff like that. Also pretty well suited for visualizing funneled data.
See how it works? In this case, 25% of the survey respondents said no (and they skipped to the next section). 75% of the respondents said yes. Of that 75%, 81% reported “daily.” And of those, 64% said they use software. You get it? It’s actually pretty darned intuitive.
What makes it work:
Listing the n’s alongside the main heading in each section. This will help people who are having a hard time wrapping their brains around the visualization because they can see how the n’s carry down into each section.
This is graphing by area – which is tricky. Humans are bad at interpreting area. So the rule is that the area must be proportionate to the data its representing. As in, a reader should be able to put down a ruler and calculate the area of the “daily” square and it had better be 81% of the “yes” square.
And it is. I started by calculating the total rectangle area by just throwing out some dimensions – I said 6” x 5.63”, or 33.78 square inches, would represent all 500 respondents. The dark blue “Yes” rectangle had to be 75% of that size, or .75 multiplied by 33.78 square inches. That’s 25.34 square inches and I chose to lay that out as 4.5” x 5.63”. I inserted a rectangle in PowerPoint and followed Drawing Tools>Format>Size and punched in those dimensions I just calculated.
To get to the blue “daily” rectangle, I calculated 81% of 25.34 square inches, which is 20.52 square inches. I chose to lay this out as 3.75” x 5.47”. So I inserted another rectangle and sized it up properly. And so on.
Sounds complicated but it really isn’t too hard. You just pick an area to start with for your total set of respondents and divide it up in proportion to the data it needs to represent.
It’s the smartest way I’ve been to display data that is nested in nature. Have you seen other ways to show nested data? Speak up in the comments!
You can find a lot more step-by-step instruction on how to make awesome visuals in my Evergreen Data Visualization Academy. Video tutorials, worksheets, templates, fun, and community. Excel, Tableau, and R. Come join us.
More and more, organizations are on board with the idea of becoming “data-driven.” Collecting data on key indicators is a big step ahead from where many organizations used to be. But being able to interpret that data and share it with others in an intelligent way is equally as important and most organizations don’t focus here.
We’ve talked previously about how that data shapes the culture and conversations inside your organization. We (Gavin and Stephanie) were clarifying our thinking on this topic recently, in between data visualization workshops for Verizon.
What we first noticed, as we’ve seen in many organizations, is that there’s almost TOO MUCH data. The rush to be data-driven didn’t come with a handy interpretation guide. So they ended up with slides full of tables with text so tiny it wasn’t legible. They were producing slides like this (totally not real data at all):
Geoff Walls, VP, Product Marketing and Communications at Verizon, put it this way:
“Our challenge at Verizon is that we’ve got more data and more information on what’s going on, or investments we’re making, or customer purchase patterns. Ultimately we have to take all that data and synthesize it into something that we can make decisions on.”
Everyone who looked at the slides, including those who made them, had trouble distinguishing the important stuff – the signal from the noise. And as a result, Verizon was wasting time and money.
“It was a struggle and always required rounds and rounds of interaction in the business with the folks that have the data in hands, to come to conclusions via the data they were sharing.” Geoff disclosed.
Many businesses are producing spreadsheets that track every movement in the company, but management often lacks the ability to interpret the data and present it in a way that supports actionable decision-making. They lack intentional reporting. There are different reasons for this difficulty with numbers. Sometimes it’s part of the culture to try to impress people with numbers. Sometimes it’s not wise to be the bearer of bad news, or lacking the courage to do so, and sometimes it’s simply not knowing how to tell a story with your data. Clear data visualization supports truth-telling. It helps decision-makers connect dots, engage in precise discussion, and make straightforward decisions. And that’s where culture is sculpted.
We didn’t radically overhaul anything for Verizon. We took them through the Presenting Data workshop and gave them basic tools to find insights in their data and present them effectively. We added simple visualizations to their tables of numbers. Things like sparklines to visualize their quarterly reporting. Bullet graphs to show how close they are to their target. And, where possible, we encouraged them to ditch tables in favor of line graphs and bar graphs. Nothing too radical:
What happened? Following one Presenting Data workshop, these baby steps toward better visualization were tested. It was good news and bad news.
One of the managers told us, “Our feedback on the weekly call was [Geoff, the boss] liked the new charts. He didn’t like what they said (because we are going down) but he liked that they identified the issue clearly.”
In other words, he was excited, not that the data pointed in the wrong direction, but the graphs clearly showed performance. Geoff could anticipate results and take immediate action.
Geoff reports, “We’re starting to see better decisions, more clarity, less top down, more true interaction between the levels of the organization which is critical for our team culture.”
Data visualization and intentional reporting are two of the most important tools to shape organizational culture.
Presenting data effectively changes the kinds of conversations that can happen inside organizations. Better presentations shape an improved culture of decision-making. Let me tell you about a recent example of this.
Late 2012 I got a call from an evaluation officer who was working at the Walton Family Foundation. (Yep – *those* Waltons. Whatever your opinion of Walmart, the foundation is doing some good work. While I can’t tell you the details of their data, you’ll just have to trust me that their investments in environmental initiatives are truly impressive.) She said her group was ready for change, ready to learn ways to present better.
We had a thought-provoking workshop together and at the end they were ready to overhaul their reporting. You see, they had been submitting their data to their board of directors like this:
with the expectation that the board would make actionable decisions based on it. Yeah, that wasn’t happening. Rows and rows of numbers are extremely hard to make sense of. It’s very difficult for our brains to compare that many digits and pull out any kind of pattern, let alone gain insight that can lead to strategy. I suggested we swap out all tables for graphs, but Karen at Walton said “Uh, no the board really wants to see all the raw numbers.” Inside my head I said “Suuuuuuure they do” and out of my mouth I said, “No problem, let’s keep them all in there and dashboard this thing.” Here is the redesign:
We really only made 3 small changes.
I added a trendline to show the row of numbers preceding it, so that the overall pattern is clear. That green dot at the end of the trendline? That’s the target they are aiming for in 2019.
The target is listed numerically in the column next door. And in that target cell, you’ll see that some have a red dot. I used conditional formatting to set up a formula which calculates their predicted value in 2019 based on their current data and the red dot shows up when they aren’t going to meet their target as things stand without some serious intervention. Talk about being able to make actionable decisions!
And then the modified bullet graph shows progress to date.
So only 3 changes, really. Plus some light graphic design in terms of colors and such.
Walton actually took another one of my recommendations from our first workshop, which was to hire a graphic designer. Maskar put their final touches on the dashboard so now it looks like this:
Karen and her team showed this dashboard to their board, who saw it as a real improvement. In late 2014 we have been dashboarding even more. So, good for me and my business but let’s talk about what happened inside the Walton Family Foundation.
The culture changed. Karen recently told me that the drive toward better design “really impacted everyone at Walton Family Foundation.” She went on to say, “Dipping our toe in the waters of better data visualization with the dashboards has set off a chain reaction. Our entire organization is really poised to improve how we present information to our Board and publicly. Data visualization helps us tell a story about the foundation’s impact and leads to improved decision-making across the organization.”
And *that’s* the kind of difference that can be made by presenting data effectively.
Few things scream “I didn’t try very hard” more than using the Excel default color schemes. Good news is that changing them up isn’t really very hard at all.
First, decide what other colors you would like. Maybe pick something from your organizational brand. I used ColorBrewer to come up with the color scheme here. I chose a diverging color scheme, where darker colors are on the ends and a neutral color is in the middle – because that fits my data, a 5-point Likert scale. Whatever color scheme you choose, make sure you copy down the RGB color codes that make up each color. (At ColorBrewer, you probably have to switch it away from HEX to RGB. At your office, there’s someone, I promise, who knows your RGB color codes, so just ask around.)
RGB stands for Red Green Blue and the 3 numbers you get there are the precise mix of red and green and blue needed to make the specific color you are staring at. Every color has a 3 number RGB color code. Write them down. Do you have your color codes?
Then you create your data visualization as usual. I chose a simple 100% stacked bar here (though there are other options).
Now I’ll click on the teal bars that represent Strongly Disagree so they are all activated. Then I right-click and in that drop down menu, click Format Data Series.
In Excel 2013, the dialogue box that pops up looks like this. I want the paint bucket icon. In 2010, click on Fill.
In the color menu I see there, I want to switch it away from Automatic to Solid. Then we need to customize that solid color so click on the arrow by the word Color and select More Colors.
It’ll open up a new dialogue box. Make sure you are in the Custom tab and WHAM. That’s where you type in your RGB. I typed in the dark red color I found in a diverging scheme at ColorBrewer. Dark red because, in this culture, we tend to associate reds with warning and danger. I’ll use blues, which we see as more positive, on the agree side of this Likert data.
Click ok and your stacked bar is one step away from looking default. Repeat this process for each of the segments in the stacked bar graph and you end up here:
Why do we go through this process? So that (1) you don’t look like a lazy schmuck, (2) people are better able to mentally group your Likert scale responses and interpret your data, and (3) you communicate more effectively. For about 10 minutes’ worth of work, those are some mighty amazing benefits.
You can find a lot more step-by-step instruction on how to make awesome visuals in my Evergreen Data Visualization Academy. Video tutorials, worksheets, templates, fun, and community. Excel, Tableau, and R. Come join us.
Want biweekly tips and tricks on better data visualization & reporting? Get my super helpful newsletter right in your inbox. We'll never share your information with anyone else. That would be gross.
Almost done! Check your inbox and confirm your subscription.
Let's Hang Out