Note from Beth: I just knew that I was going to start obsessing about charts and graphs after my Excel spreadsheet obsessions started. I thought if I set up a tumblr blog curating great nonprofit spreadsheets, but the next logical step is create visualizations of your data. What better way than in Excel. I got into a wonderful conversation with Stephanie Evergreen, another nonprofit datanerd who loves spreadsheets who offered to write up this guest post about how to create the perfect graph.
Six Steps to Great Graphs By Stephanie Evergreen
Low budget? No programming skills? Me, too! Great data visualizations don’t necessarily require an expensive software package or a programmer on staff. Here is how you can work with what you already own, Excel, to increase the impact of your data visualizations. Let’s start with one basic dataset – a count of the number of followers, advocates, and donors for a non-profit over the last 6 years – and rock the graph so it is clear and compelling.
Step 1: Which Chart is Best?
Excel provides all sorts of default chart types to choose from, but the truth is, keep it as simple as possible. If your data adds up to 100%, you might choose a pie chart. It’s difficult to interpret area, so if you use a pie, restrict the number of wedges to 4 or fewer. People are better at judging length, so bar charts are a decent option also useful for comparisons. People are even better at judging points on a line, but box plots aren’t yet a default option in Word (here’s a tutorial on how to force one out of Excel, though
Since we’re working with the defaults, here is how our social media data looks as a bar graph.
The bar graph is okay, but when trying to look at change over time, line graphs are a more appropriate chart type.
Ah, so much better! Now the trends are much easier to see at a glance. The choice in chart type should be driven by the relationships in the data we want to visualize. For more help, check out my favorite chart chooser tool.
Step 2: Use Color to Emphasize
Excel’s default colors are so equally dark that it can be difficult to tell the graph’s main point, without some serious cognitive effort. Changing the color can help bring attention where we want it. Of course, that means we have to know where we want it. So in this case, I’m suggesting we pay attention to the advocates, who used to be followers, and some of whom will become donors. I’m going to change the line color for advocates to green, my action color, and change the others to a shade of gray.
Your action color will likely be something from your brand. Use a color-picking tool to find out the exact color and navigate to the custom color area in Excel to match your shade.
Step 3: Delete What You Don’t Need
Little things in this chart still make it feel cluttered and distract from the data. Most of the time, we can do without the tick marks along each axis. Just right-click on each axis, and in the format area, change the tick mark option to None.
We can also lighten up the gridlines. They support estimation of the data values, but the default is so dark it can compete with the actual data lines. Right-click on them and change their color to a light gray. If you are going to put the number labels on your data lines, delete the gridlines altogether. Just a few tweaks there cleans up the data visualization.
Step 4: Directly Label
Our brains make better sense of the data when we replace the legend with direct line labels. Just click on the legend and hit your delete key. It will feel good.
Then right-click on each line and select Add Data Labels. This will give you numbers. So right-click again for select Format Data Labels. Uncheck Values and check Series. Now each data point will have its appropriate label, like “Advocates.” But you only need the label at the end of the line, so click on the others and hit that happy delete key again. This way, interpretation of which line stands for what is very obvious.
Step 5: Save as a Template
Whew – sounds like a lot of work, right? Make this process easier in the future by saving the chart as a template. Look in the chart types area of Excel and you’ll see a space to save what you have made up to this point. Name it something you’ll remember. Then the next time you need to make a three-line graph, click on your customized template and BAM – instant great graph.
Step 6: Annotate
Let’s get back to the original reason we visualize data – we’re looking for a pattern. We graph our social media impacts because we need to see how launching new platforms has influenced our clients. So now that we have taken lots of things out of the graph, let’s add back in some thoughtful annotation to help make the patterns clear.
Several pieces were added: I inserted icons along the timeline to illustrate when the nonprofit launched each social media platform. Based on that, we can see a series of changes in the data. After each launch, followers increase, and after a lag the advocates and eventually the donors increase as well. Let’s point out that pattern using the title. I left-justified the title and changed the text from something generic to a clear take-away point. No reader can mistake the message. I added a subtitle to further elaborate.
You can use Excel to do more than create awesome line graphs. Are you inserting this graph into a slideshow for your Board? Try the slow reveal. Once you have mastered clean graph redesign, pull several together into a 1-page dashboard, like my annual report. Or entice your annual meeting invitees with scratch-off graphs.
Six steps help us tweak Excel’s defaults into rockstar data visualizations that help us understand our work and better communicate it to others.
Stephanie Evergreen blogs, tweets, and trains on how to make awesome graphs, slides, and reports. Her forthcoming book, Presenting Data Effectively, is coming soon, better early your copy now!
