recommended reading: How Charts Lie

 
How Charts Lie Rounded Corners.png
 

Alberto Cairo has penned some of my favorite data visualization books—The Functional Art and The Truthful Art—and he has a new one coming out that I’ve already added to my list of recommended reads: How Charts Lie: Getting Smarter about Visual Information. This book should be in the library of anyone who ever looks at a graph. It should especially be read by everyone who makes graphs, as it is packed with things we should all be aware of and consider when we design data communications for others.

I had an opportunity to get an advanced copy of and to chat with Alberto about it recently. I love talking with Alberto—he is thoughtful, eloquent, and full of inspiring ideas. I found myself nodding during much of our conversation. He raised one point in particular that I found provocative (in a really good way): the idea that we—as the designers of graphs and charts—have a “responsibility to improve the craft,” expanding graphical literacy and innovating to come up with better ways to display and communicate data.

I invite you to listen to our conversation about this and much more related to How Charts Lie (including a number of recommendations for additional reading, which you’ll find listed below).

Summary: If good graphs empower us to pose good questions, what happens on the dark side of data viz? In this episode, Cole talks with Alberto Cairo about his new book, How Charts Lie. Their conversation delves into how information designers and consumers are both to blame and mindfulness as it relates to chart making and interpretation. Tune in also to hear tips to keep from spreading misinformation, why “essay” is a beautiful word, and Alberto’s optimism for the future of data visualization.

Listening time: 1:03:30 | Related links:

how to do it in Excel: a shaded range

in

Today's post is a tactical Excel how-to: adding a shaded region to depict a range of values. 

To illustrate, let’s consider an example from the tourism industry. Suppose a watersports company offers four categories of outings: fishing charters, family rentals, nature cruises and sunset cruises. The graph below shows the monthly volume of passengers for each offering over a year.

 
 

We can see there’s clear seasonality in this business—overall volume is highest in the summer and each outing type generally follows the same monthly pattern. Let’s say you manage the Family rentals and you’d like to compare your monthly volume to what you’re seeing across the entire fleet. 

For the purpose of this tactical illustration, let’s assume the shape of the data—relative peaks and valleys—is more important than the specifics of each category individually. If that’s the case, I can simplify by showing a shaded region to depict the range of absolute passengers each month.

My resulting graph looks like this:

 
Picture23.PNG
 

Creating this shaded region in Excel requires some brute-force formatting utilizing area charts. Here’s a step-by-step overview of how I accomplished this—you can download the file to follow along.

In my Excel spreadsheet, the data graphed above looks like this:

 
Picture3.png
 

The first thing I’ll do is add two new columns calculating the minimum and maximum values for each month. I used a =MIN() and =MAX() function and my resulting series looks like this:

 
Picture4.png
 

To create the shaded region, first I added the Min and Max as new data series and deleted the lines depicting fishing, sunset and nature. Then I adjusted the formatting of Min and Max to create the grey band around family rentals. The following steps show how I accomplished this:

Next, delete the series for fishing, nature and sunset cruises (leaving only family rentals displayed) by highlighting each individual line and pressing delete. 

 
Picture2.PNG
 

Add a new data series for the Maximum by right-clicking the chart and choosing Select Data:

 
 

In the Select Data Source dialog box, click the + button to add a new data series for the Maximum (my Max series is in cells P6:P17 with Name in P5). Click OK when done. 

 
Picture7.png
 

My resulting chart looks like this:

 
Picture8.PNG
 

Reformat the the Max line by changing it to a 2D stacked area: right click the “Max” line, go to “Choose Chart Type” then select “Line”. Scroll down to the 2D area types and select the 2D stacked area chart (It’s the middle one for my version of Excel):

 
Picture9.png
 

My resulting graph looks like this:

 
 

Change the Max 2D stacked area to grey fill by right-clicking the series, choosing Format Data Series: 

 
 

In the Format Data Series dialog box, select Fill and choose the Solid fill option. My selected grey is RGB 191-183-185. 

 
Picture12.png
 

My resulting chart looks like this (note: my Max stacked area chart is set to display as Series 2 although we’ll adjust this in a later step):

 
 

Add a new data series for the Minimum by right-clicking the chart and choosing “Select Data”:

 
Picture14.PNG
 

In the Select Data Source dialog box, click the + button to add a new data series for the Minimum (my Min series is in cells O6:O17 with Name in O5). Click OK when done. 

 
Picture15.png
 

My resulting chart looks like this:

 
Picture15.PNG
 

Reformat the Min line by changing it to an area: right click the “Min” line, go to “Choose Chart Type” then select “Line”. Scroll down to the 2D area types and this time, we’ll select the 2D area chart (mine is the first one in my version of Excel):

 
 

My resulting graph looks like this:

 
Picture17.PNG
 

Reformat the Min 2D area to grey fill by right-clicking the series, choosing Format Data Series: 

 
Picture19.PNG
 

In the Format Data Series box, change to Solid fill with Color = White. Under Border, select No line. 

 
 

The result is this:

 
Picture18.PNG
 

For final formatting changes, I added text boxes for the Max and Min labels and changed Family rentals to render in black for sufficient contrast against the grey band. Depending on where your Max series is displayed, you may also need to ensure that the white Min series is displayed on top if it is not rendering. Highlight the Max series and in the formula bar, ensure the last option is 2. You can also adjust the display order in the “Select Data Series” dialog box. 

 
 

Voila! A shaded region to emphasize a range around my data point of interest:

 
 

Are there other brute-force Excel methods you’re aware of for achieving this effect? Or other considerations with embedding this shaded region? Leave a comment with your thoughts and stay tuned for a new resource coming soon where you can practice and share similar tips!    

Elizabeth Ricks is a Data Visualization Designer on the Storytelling with Data team. She has a passion for helping her audience understand the ’so-what?’ as concisely as possible. Connect with Elizabeth on LinkedIn or Twitter.

#SWDchallenge: improve this table

It’s October! While I have a fondness for the beginning of fall and the changing leaves out my office window are already a beautiful mix of green, yellow, and orange, something even more exciting happens this month: the publication of my new book! Perfect timing to have a challenge taken straight from the pages of storytelling with data: Let’s Practice!

For this month’s #SWDchallenge, we’re going to tackle the first exercise from Chapter 2, which is titled “choose an effective visual.” This is part of the practice with Cole section, which means the exercise is posed, you’re meant to solve it on your own, and then I offer my solution. Not only will this month’s challenge give you a taste of the content and style of the new book, but it will also act as a forcing function for you to complete your practice before turning the page to my solution—there’s no page to turn quite yet, as the book won’t ship for another week or two. I’ll share my solution here when we recap later this month (if you don’t want to wait that long, pre-order Let’s Practice! to be among the first to receive it!).

Here is the challenge (full submission details follow):

Exercise 2.1 | Knaflic, Cole. Storytelling With Data: Let’s Practice! Wiley, © 2019.

Exercise 2.1 | Knaflic, Cole. Storytelling With Data: Let’s Practice! Wiley, © 2019.

 

In case you’d like a refresher of some common visuals used when communicating data in a business setting, here’s the SWD chapter recap that precedes this exercise in the book:

Chapter 2 recap | Knaflic, Cole. Storytelling With Data: Let’s Practice! Wiley, © 2019.

Chapter 2 recap | Knaflic, Cole. Storytelling With Data: Let’s Practice! Wiley, © 2019.

Here’s what you need to do to participate:

CHALLENGE: Download the data and solve Exercise 2.1 using the tool of your choice. Share the visual you create in Step 3, following the submission instructions below.

DEADLINE: Wednesday, October 9th by midnight PST.

SUBMISSION INSTRUCTIONS: upload your visual and related commentary via at storytellingwithdata.com/SWDchallengeSUBMIT. Feel free to also share on social media at any point using #SWDchallenge. For inclusion in the summary recap post, submissions must be officially submitted to us (still a time-intensive process and we aren’t able to scrape Twitter and other social media sites).

Stay tuned for the recap post in the second half of the month, where we’ll share back with you all of the visuals created and shared as part of this challenge—including my solution from Let’s Practice!

For 100+ additional exercises that will help you hone your data storytelling skills, pre-order your copy of Let’s Practice! today. Also check out the #SWDchallenge page for past challenge details and recaps. I’m excited to see your solutions this month, as we all practice together and hone our data storytelling skills!

how it came to be

 
LP! Process.png
 

There is so much more that goes into making a book than we typically recognize when we hold one in our hands. I learned a much greater amount about—and took a more active role in—many parts of the process with Let’s Practice! compared to my first book. I’d like to share some of my experiences in a series of posts here. In this first one, I’ll outline the general steps that took place: from me typing words into my computer to you (soon!) holding the book in your hands.

Writing

Writing a book like this was a little different, because it’s not just words, but also graphs (many, many graphs!). So a big part of the work that I’ve lumped into this section is simply creating all of the graphs. While each example was inspired by a real client scenario, in all cases the visuals were built from scratch using new data and changing details to preserve confidentiality. There was also quite a bit of planning over the course of the writing process: figuring out what fits where and rearranging many times as I worked my way through the content. Speaking of content, if you’d like to know more about exactly what Let’s Practice! is all about, check out this recent post.

Low-tech planning: sticky note storyboards

Low-tech planning: sticky note storyboards

One benefit of a book like this is that it is naturally chunked—for the most part, each exercise is its own contained section. This meant that I could focus on one at a time and complete it. Then I might jump to a totally different part of the book and work on something there if I wanted to redirect my energy or an idea struck me. I’ll dive more into my writing process in a future post and also show some of the transitions from my early rough storyboards into the final product.

Editing

In reflecting on the how-the-book-came-to-be process and visualizing it, one thing that became apparent is that more time and attention was spent on editing than any other step in creating this book. For me, it was also the most frustrating component at times!

I was editing, in some capacity, throughout the writing process and beyond. Most of this was done electronically, but I also printed the manuscript on three separate occasions and edited by hand (it’s amazing how much more you catch this way!). And I definitely wasn’t the only one reviewing things; numerous people were involved at different points to correct, offer suggestions, and give feedback through varied lenses. The book is absolutely a better product as a result of the time spent and input received and incorporated. I’ll dive into this more in an upcoming post and share some specific learnings and resources that others may find useful.

My hand-edits on printed copy #1 (of 3)

My hand-edits on printed copy #1 (of 3)

Illustration & design

It struck me part way into the writing process that it would be neat for the SWD chapter recaps I was planning for the onset of each chapter to be super visual. I also knew I wanted this book to feel less formal than a typical book to encourage people to write in it, get it messy, and really use it—to practice with abandon and without feeling like things need to be perfect.

Hand-drawn illustrations seemed like a great way to meet both of these needs and add some fun to the overall book. I knew one specific person who would be awesome for this: Catherine Madden. I had met Catherine at Tapestry Conference in 2017, when she drew live visual notes for each of the presentations (here’s the one she created for mine). I was thrilled when she agreed to take on this project and had a ton of fun working with her over the course of it. I continue to be in awe of her superpower for taking words and ideas and making them visual. Here’s an example of one of the illustrations Catherine created—this one leads into Chapter 8, “practice more on your own,” and alerts people to some common myths in data visualization:

Chapter 8 “common myths” recap | Knaflic, Cole. Storytelling With Data: Let’s Practice! Wiley, © 2019.

Chapter 8 “common myths” recap | Knaflic, Cole. Storytelling With Data: Let’s Practice! Wiley, © 2019.

The design and layout of a book is typically done by the publisher. But I decided to go a different route this time. Knowing how much iterating it can take to thoughtfully design a book with so many visuals and to allow for flexibility with the use of illustrations, I wanted more control over this part of the process. Catherine brought in Matt at Flight Design Co, who did the heavy-lifting here, designing the book and laying out all of the text and graphics (and being incredibly patient through the numerous iterations to get everything right). Here’s an example 2-page spread leading into Chapter 4 on focusing attention:

Chapter 4 outline | Knaflic, Cole. Storytelling With Data: Let’s Practice! Wiley, © 2019.

Chapter 4 outline | Knaflic, Cole. Storytelling With Data: Let’s Practice! Wiley, © 2019.

Catherine and Matt were both super amazing to work with—stay tuned for a forthcoming post, where they’ll share their process and perspectives.

Cole at Quad.jpg

Printing

I had the distinct pleasure of visiting Quad Graphics World Headquarters in Sussex, Wisconsin two weeks ago to see my book printed and again last week to watch it be assembled and bound. I was able to take some of the first copies home with me! This was a fascinating experience and I took a lot of pics that I’ll soon pull together into a follow up post to share.

LP being boxed.jpg

Distribution

I know least about this part of the process, which is underway now. The gist is: books go from the printer to Wiley warehouses to the various booksellers to you! Don’t forget to pre-order your copy to be among the first to receive it: Amazon, Barnes & Noble, Books-A-Million, or (best) order from your favorite independent bookseller.

For the international audience, there will be a simultaneous publication in Europe and books can be pre-ordered through Amazon UK (hopefully, publication in other languages will follow; since this gets done through deals with other publishers, translations can take a year or more to become available).

The other stuff

This is a simplified overview—I’ve skipped some things. For starters, the gap between having an idea and writing. There is a decent chunk absent from the timeline that begins this post, which is the time between the kernel of the idea and actually beginning to write. I’m a percolator, things mix around and need to reach a boiling point before I’m ready to start. But once I’m ready, I go. That said, I now have absolutely no recollection of how long that time gap was. I suppose it doesn’t matter now.

Another item I’ve skipped is the legal mumbo jumbo and contract negotiations. That took longer and cost a lot more in lawyer fees than I would have liked, but it ended in a good spot. That’s enough about that.

Marketing is the other major workstream that I didn’t include above. My publisher, Wiley, undertakes some of this (optimizing things on Amazon and adding the title to their roster of books to generally promote). Often, authors hire publicists at this point to help bring awareness, but this is a piece we are handling directly. Some of our efforts to date have included offering early sample content to university instructors and arranging special bulk pricing for our clients (which I’ve just officially made available to everyone reading this, too!). Our workshops also act as great ongoing advertising—our final public workshop this year coincides with the official book launch and we have added a new half-day custom workshop to our offerings based on the new book.

I hope you enjoyed this overview, and that you’ll find the forthcoming deep dive posts interesting, too!

Finally, don’t forget to pre-order storytelling with data: Let’s Practice! to be among the first to hold it in your hands—and hopefully now that you’ve read this, you’ll be fully appreciative of all the effort—by so many people—that went into it!