When Excel Gives You Lemons, Part II: Line Charts

In our last post, we looked at how to take a default column (bar) chart in Excel. Most of the same "clicks" we used can also be applied to a line chart. Keep in mind that these are not hard and fast rules, just some guidelines to help you along.

• Add a title with description and label axes, using simple text/numbers.
• Reduce the data-to-ink ratio by reducing the intensity (e.g lightening up) axis and grid lines, axis labels, and font colors; and, removing tick marks.
• Use color and data labels to emphasize main points.

If we start with the same data set on the ethnicity of Washington K-12 staff and students from 2004 - 2013, we get this as a default line chart:

Who needs eyebleach? Maybe a unicorn chaser?

I don't like a line chart for these data any more than I liked the basic column/bar graph. There's nothing wrong with using a line chart, per se. It shows all the data points---both categorical and over time. But because of the way our brain automatically assigns differences to groups of items vs. individual, it really draws the eye to the "white" data. And if that was the story we needed to tell, that would be okay...but it isn't. We'll get back to this in another post.

So, let's talk about this line chart for a moment. The default colors here are really a problem. If we're going to display staff and students on the same chart (and there are good reasons not to), then even a basic clean up of the line colors can help the audience make some comparisons between staff and students of a particular ethnic group. I also recommend labeling the lines, instead of using a legend. If we do these things (the bulleted list above and these additions), here's what we get:

Ahhh...that's better.

That's not too shabby. While I still don't like a line chart for these data, I do think that it does a good job of showing the relative proportion of the ethnicities and gives a bit more story. The populations of Asian,  Black, and American Indian students have held fairly steady over the last 10 year and kept their rank (by number) over that time. (I.e., Asians have always had the third largest population of students...Native American the smallest.)

Do we need the marker points and all of the data labels? Personally, I don't think it's necessary with this particular chart. Why not? Because we're not trying to make direct comparisons between the groups. It's not the big idea. That being said, if you wanted to add marker points, I wouldn't fuss. If you really need all the data labels and your chart is looking too busy, consider adding a table with the data for those who need the deets.

Meh.

Again, with less than five minutes of work on a basic chart, you can have a big impact with your message. We still have some digging to do. What is the best way to present these data? What is the "So what?" of student populations rising/declining---do we also need to consider professional development needs of teachers, test scores, geospatial data (is the effect the same throughout the state), and so forth? But all of these start somewhere, usually with a data dump like the one we've used here. The more you do to communicate and present your data effectively, the more clearly the next steps will appear.

Bonus Round
You can download my anatomy of the redesign here. Again, it is not intended as a "Thou shalt...," but is offered to provide a reference of where to click and ideas to consider as you build charts of your own.