baby steps

Today’s quick post is an example of an incremental improvement to an existing graph that makes it easier for the audience to read. When practicing being a better communicator with data at work, it’s easy to become consumed by the desire for perfection and talk ourselves into thinking it’s an all-or-nothing approach. Rather, the opposite is true: incremental improvements are achievable baby steps we can take to become better data communicators. 

Consider the following visual and imagine it’s part of a semi-annual update to senior leadership. The data displayed is total dollar volume of an organization’s funding amongst its various initiatives. Within each initiative, the stacked bars break down the dollar volume into three stages: distributed, pending and funded.

 
Picture1.png
 

Assume a stakeholder asks you for an updated version by the end of the day. Given the tight deadline you don’t have time to make big sweeping changes—however, you can use this as an opportunity to make an incremental and impactful improvement. Study the visual and ask yourself: what is one change that would make this graph easier to read?

Do you have your change in mind? Read on to see what mine is!

My incremental improvement is to reposition the words to the top. More specifically, I’d choose to left-align the chart title and supporting subtext while moving the legend and x-axis labels to the top of the graph. The benefit is that my audience sees how to read the graph before they get to the data! You can see this change reflected below.

 
 

There are certainly more modifications I’d make given ample opportunity but time constraints are real. Continually evaluating our graphs for readability and implementing small incremental changes sets us up for improved future iterations and, ultimately, success. You can download the Excel file to see how I made these changes. 

What was the incremental change you’d make given limited time? Leave a comment with your thoughts! Now, consider your own work: what baby step could you apply to make an existing graph easier to read?

how to do it in Excel: a shaded range

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.

forty-five pie charts? never say never

Here at storytelling with data, we have been known to say things like, “The only thing worse than a pie chart…is two pie charts.” And yet, believe it or not, we’ve found a data visualization that we think succeeds in using not one, not two, but forty-five pie charts. How could this possibly be?

Read More

the accidental misdirect

A friend of mine, Mark Bradbourne, recently posted a picture to Twitter showing a bar chart that his local utility company included in his most recent bill. He entitled the picture “Let’s spot the issue!” 

So as to protect the utility company in question, I’ve recreated the chart below, as faithfully as possible. (There are, of course, many changes I would make in order to render this a storytelling with data-esque visualization, but for the purposes of this discussion it’s important that you see the chart as close to its original, “true” form as possible.)

The chart from Mark’s utility bill, recreated from the original photograph as posted on Twitter.

The chart from Mark’s utility bill, recreated from the original photograph as posted on Twitter.

The internet immediately latched onto the seemingly absurd collection of months portrayed in this chart. The bill, dating from June of 2019, included 13 prior months of usage from as early as August of 2016, as recently as March of 2019, and in a random order.

Soon, our non-U.S.-based friends pointed out that the dates made even less sense to them, as (of course) their convention is not to show dates in MM/YY format, but in YY/MM format.

And with this, the truth of the matter became obvious: the dates were in neither MM/YY format nor YY/MM format; they were in MM/DD format, and excluded labeling the year entirely. 


Whenever we run across these kind of so-called “chart fails,” it helps to keep in mind that whoever created the chart wasn’t setting out to be confusing or deceptive. The utility company clearly wanted its customers to be aware of their recent usage, and went so far as to show that usage in a visual format so that it would be more accessible.

The danger, though, is in the assumptions we make when we are the ones creating the chart. Specifically, in this case, there were likely assumptions made about how much information needed to be made explicit versus how much could be assumed.

The energy company likely thought:

The chart says that it’s showing monthly usage; and, since it shows 13 bars, the homeowner will know, or at least assume, that the bars represent the last 13 months in chronological order.

And in general, yes: that is what our first assumptions would be, if there had been no labels whatsoever. 

In this case, the company chose to label the bars with a MM/DD convention, excluding the year—probably to denote what specific day the meter was last read, or on what specific day the last water bill was issued. But we very rarely see dates in MM/DD format when they cut across two different years. We’re trained to see date formats in the style of XX/YY being representative of months and years, not months and days. To interpret the chart correctly, we would have had to ignore and resist our personal experience with this convention.

So on the one hand, logic told us that the chart showed the last 13 months; on the other hand, our experience and the direct labels told us that it was mistakenly showing us 13 random months. What other elements of the chart, or other design choices, could have nudged us towards one of these interpretations over the other?

Perhaps if the chart had been a line chart rather than a bar chart, we would have been nudged into thinking that the data was being shown over a continuous period of time; this could have been enough to make the chart more easily interpreted.

The original chart recreated as a line, rather than a bar.

The original chart recreated as a line, rather than a bar.

Or, if the labels had used abbreviations for the months, rather than numbers, we almost certainly would  have seen the orderly progression of months more clearly.

The original bar chart, but with the months on the horizontal axis labels shown with three-letter abbreviations instead of numbers.

The original bar chart, but with the months on the horizontal axis labels shown with three-letter abbreviations instead of numbers.

Another solution, one which would have almost certainly eliminated all confusion, would have been to include the actual year in the labels, or as super-categories below the existing labels.

With super-categories for the years along the horizontal axis, confusion is likely minimized.

With super-categories for the years along the horizontal axis, confusion is likely minimized.


We could also ask the question: Do we need to be so precise with our X axis labels that the specific day of the month is shown at all? 

It doesn’t seem like it; especially considering that the data on the Y axis has most likely been rounded off, and is presented to the audience at a very general level. 

Look at the level of granularity on the Y axis; although it ranges from 0.1 to 0.7 (in 1000s of units), every bar is shown at an exact increment of 0.1. It’s unlikely that a homeowner’s actual monthly utility usage is always an exact multiple of 100. 

In this case, the labeling of the specific date on the X axis implies a specificity of data that the Y axis does not support. 

Bar chart with more consistency of specificity between the horizontal and vertical axes.

Bar chart with more consistency of specificity between the horizontal and vertical axes.

The bottom line, though, is that the creator of the chart made assumptions about what they needed to show versus what they could exclude; and in making those assumptions, they inadvertently misled their audience in a manner that was very confusing.


It is important to focus your audience’s attention on your data in your visualizations, and to remove extraneous clutter and distracting elements—including redundant information in labels. This case, however, highlights the danger of taking your assumptions too far, and inadvertently adding confusion rather than clarity.

Sometimes we get so familiar with our own work, and our own data, that we lose track of what is, or isn’t, obvious to other people. During your design process, it can be valuable to get input from people who aren’t as close to your work. This helps to identify, and avoid, situations like this one, where familiarity with the data led to design choices that were confusing, rather than clarifying. 

Putting yourself in the mind of your audience, and soliciting feedback from other people who aren’t as close to your subject, will help you to avoid these kinds of misunderstandings in your own work.


Mike Cisneros is a Data Storyteller on the SWD team. He believes that everybody has a story to tell, and he is driven to find ways to help people get their data stories heard. Connect with Mike on LinkedIn or Twitter.




power pairing: color + words

What is one thing you’ll do differently after learning the storytelling with data lessons?

At the end of our workshops, participants are often prompted to reflect on this question. The resulting discussion usually evolves into things that can be easily integrated into the day-to-day work already being done. One piece of advice we frequently give may surprise you—there are two easy actions that don’t require complicated technical skills! First, adopt the habit of stating your takeaway in words. Second, develop the practice of using color sparingly. Today’s post is a quick illustrative example that puts these tips to use. 

At a recent client workshop, we discussed a visual similar to the one below. It is a snapshot of an organization’s current accounts payable (AP) by vendor at a point in time. At a basic level, the graph is fine. It’s cleanly designed with a left-aligned chart title, data labels incorporated into the bars, and no clutter of gridlines or chart border. The bar chart is easy for me to read—I can quickly see that AP is highest for Microsoft and how incrementally larger it is compared to the other vendors because of the consistent baseline (the y-axis). 

 
Picture1.png
 

What I can’t easily see is what I should take away from this chart. At client workshops, we often don’t have this important context—because of this, we often show multiple approaches for highlighting different potential takeaways. Below you’ll see several strategies for employing color and words in this visual. In each of these, notice how the words set up your expectations for what’s emphasized in the graph and color used sparingly indicates where to look in the visual. 

If the audience is interested in the highest spend, I could emphasize the largest vendor:

 
 

Perhaps the audience will be more curious where AP is concentrated. I could instead focus attention on the top vendors:

 
 

What if the conversation is about expectations—is this spend surprising or unsurprising? I might add additional context with super-categories—useful if the audience is unfamiliar with these vendors’ services—grouping and employing similarity of color and position to visually tie the text to the data it describes. 

 
 

Practice pairing color and words in your visuals to be more effective when communicating for explanatory purposes with data. Bonus: you don’t need fancy tools to do either of these things!

Download the file for a peek at how I created these visuals in Excel. 


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

three tips for storytelling with qualitative data

Do you find yourself needing to communicating with qualitative data? This post discusses three best practices when communicating with qualitative data—effectively using color, reducing text and considering if audience needs quantitative context—and illustrates through example.

Read More