The first dataviz challenge was a hit! A few of you wanted to participate but weren’t ready to jump into a challenge that required so much Excel elbow grease. So, this second challenge is geared towards beginners like you. Once I build your foundational skills, we’ll move on to advanced charts.
I’m giving an Ignite presentation tomorrow at the Nonprofit Technology Conference (#13NTC). I’m going to show nonprofit leaders how to make four easy-to-read bar charts by simply using a little #ExcelElbowGrease:
First, let’s take a closer look at default Excel bar charts. The “before” bar chart is shown below. In my example, we’re examining how a nonprofit has grown over the past six years. This is a simple metric so we don’t need anything more complicated than a basic bar chart.
You’ve probably seen these exact charts before. There’s nothing technically wrong with this default bar chart. It doesn’t distort the data. It doesn’t mislead the readers. Default charts are fine for internal use like informal staff meetings. However, a few tweaks can really improve the formatting.
The “after” chart is shown below. Now, my brain sees the pattern immediately. There are fewer distractions — no border, no tick marks, no grid lines. In other words, this bar chart passes the Squint Test — when you squint your eyes, and everything gets a little blurry, you should still be able to detect the overall shape of the data. There shouldn’t be extra ink like borders, tick marks, or grid lines getting in the way.
I also infused a few of my personal preferences into the chart’s formatting. My eyes do better with the larger font. Being a data nerd, I prefer having data labels on my bars so I can read the exact values (rather than having to estimate values in the default chart). The white space and custom color palette make the chart look like something a graphic designer might create. I purposefully focused attention on the 2012 bar by using a darker color.
Like I mentioned, these last few adjustments aren’t required. Passing the Squint Test is more important than satisfying everyone’s personal preferences. There are multiple correct ways to improve chart formatting.
The dataviz challenge: Re-create the “after” version of the basic bar chart in Excel or R. No expensive software allowed. You can re-create this exact bar chart, or you can tweak the color scheme and numbers to fit an example from your own work. The goal is to practice manipulating the settings in Excel and to create a basic bar chart that’s easy to read and understand. When you’re finished, email me or tweet a screenshot to @annkemery.
Bonus: Beginners can probably make this bar chart in 30 minutes or less (for example, during your lunch break). You’ll get faster with practice. I bet that advanced Excel users can enter the data, insert the chart, and adjust the chart in 3 minutes or less. Time yourself. Advanced users, how fast are you?
The prize for playing: Beer or coffee, my treat, the next time you’re in DC; a professional development opportunity; and bragging rights.
I’ll post the how-to guide in two weeks, on April 24, 2013. Happy charting!
Angie Kyzer 
Sam Held 
Ethan 
Mike 
19 Comments
Hi, Ann!
Could you explain for me how you created the “back to back” chart? I tried recreating one of these “tornado” or “butterfly” charts a few weeks ago, but I could not find instructions on how to get the labels in the center (where you have years listed). The only instructions I found were for Excel 2003 and older. I love the look of this and could use it often!
Thanks for your help!
Hi Angie,
Great question. I got the idea for back to back bar charts from Stephanie Evergreen’s blog post: http://stephanieevergreen.com/making-back-to-back-graphs-in-excel/ You’ll see that she uses text boxes to add the labels in the center. This is one approach, and definitely the easiest. Start there and let me know how you do!
Ann
Ann, this is great! With the link you gave me, I was able to create the back to back bar chart with no problem! Appreciate your help!
Angie, Glad to hear that you figured it out! Stephanie has a lot of great resources on her blog.
I gave it a shot – just over 4 minutes – embarrassed by what slowed me down. Had problems getting the apostrophe in front of the dates. Ultimately, did a workaround with a space in front of each one to get excel to recognize it as text. Even with the column set to text, excel was automatically changing ’07 to just 07, ignoring the leading apostrophe. Suggestions? Thanks for the challenge. I cheated slightly too. Matched the colors with pixie, but didn’t ensure font matching. http://ow.ly/i/1RClP
Hi Susan,
Thanks for entering the challenge, and great work! Great speed too. I have to admit that I purposely added the apostrophes in front of the years because this is an extra trick in Excel. I don’t want to give away the secret just yet, but I’ll share it in the how-to guide on April 24. It’s very, very, very easy and you might even figure it out on your own before the 24th.
Thanks again,
Ann
Oooh. I can’t wait! Always love a good Excel tip. Was weird. Once I added the space, it rendered properly – not taking into consideration the space (basically, the space didn’t throw off the alignment).
The following works =”’07”. The reason it causes a problem is remembering that Excel is a front to a database. Single quotes are important components of database queries, so Excel protects itself by removing them automatically. Also protects from stack overflows.
Hi Ann,
This is a clever challenge; thanks for offering it up. But, I have to take issue with charts 2, 3, and 4–I think the way they are currently laid out distort the data. Take, for example, the side-by-side bar chart (#3). Cutting off the axis on the ‘Maryland’ series gives a visual perception that the two series are more equal in size than they truly are because of the chart border. Here’s my remake of your chart: http://www.flickr.com/photos/24074397@N00/8639452862/in/photostream
and here’s the remake if you keep the horizontal space equal for both series: http://www.flickr.com/photos/24074397@N00/8639452790/in/photostream.
When the horizontal space is the same for both series in that second version, the visual perception differs such that the Maryland series appears smaller.
Hi Jon,
Thanks for reading, and good catch! I’m very careful with axes and often double-check them in my work. The charts themselves had correct axes, but then I used a snipping tool to take screenshots of the four charts and compile them on Slide 5. I accidentally cut off part of the charts when snipping them, if that makes sense. Another detail to add to my list of graphical elements to double-check…
Thanks again for your careful check!
Ann
Hmmm, I can get everything but the (pretty!) whitespace between the bars and the chart title; and the thinness of the axis line. So far not too hard (longer than 4 minutes, I admit!) but I had absolutely no idea that this could be done in Excel; delightful. Thank you!
Hi Ethan,
Terrific job for stepping out of your comfort zone and trying something new in Excel. When I post the how-to guide on April 24, I’ll make sure to address white space between bars, the chart title, and the axis line.
(In the meantime… try right-clicking anywhere and everywhere in Excel. You can right-click on the axes, on the bars, etc. Try scanning through all the options in the pop-up windows that appear, and you might be able to figure it out.)
Thanks for participating in the challenge! Ann
[…] ask a tricky question to get people thinking and engaging a little more. A good example of this is Ann Emery’s Microsoft Excel challenges (although she doesn’t offer a prize other than bragging […]
I understand the goal of the challenge is to get users to improve chops with excel and bar charts in particular, but why not use a trend line instead of a bar chart to display this type of data (link here: http://i.imgur.com/UsPoONY.jpg)? Bar charts can show change over time, but are used more for comparison of groups or categories.
Hi Mike,
Good thinking! You’re right, you could definitely use a line chart instead of a bar chart to display this same data. Line charts are very common when showing patterns over time.
A key principle in data visualization is that charts need to be tailored for your project, audience, context, etc. In my work, I typically create charts for non-data and non-evaluation people. They often feel most comfortable with bar charts. My goal is to communicate results in a way that matches their dataviz literacy level. In your work, it might make more sense to use line charts. Bottom line: there is rarely a one-size-fits-all approach, so that’s why it’s great to have conversations like these and think through the contextual details a bit more.
Again, good thinking. Great job producing your line chart! Ann
Mike, I like the idea of the line chart. When I used to teach stats I would have said that a line chart was the way to go to show the continuity of the data (or, if you were going to use a bar chart, then the bars should be snug up to one another so that categorical data was not implied). One thing we lose with the line chart though is the capacity to emphasize a particular point, as Ann has done with the darker 2012 bar. Changing the color of a single dot doesn’t have the same emphasis. Do you have ideas for elegant emphases in line charts?
[…] Two weeks ago, I challenged readers to re-create the “after” version of a basic bar chart. You can read the full post here. […]
[…] to everyone who participated in the first and second dataviz challenges! I hope these challenges give you a chance to practice and build upon your Excel […]
[…] Two weeks ago, I challenged readers to re-create the “after” version of a basic bar chart. You can read the full post here. […]