connecting the slide title to the graph

Today’s post outlines one approach to get your message across more clearly: use color to connect the slide title to the graph. 

First, a bit of background. When communicating with data in PowerPoint, your slide title is precious real estate. Your audience is typically looking there first to understand what the slide will display, so we should be using active slide titles to help set their expectations.

Let’s look at an example, adapted from Exercise 5.7 of storytelling with data: Let’s Practice!. The following visual shows a competitive landscape overview for an on-demand printing company.

Consider the slide title. I’d categorize it as an active title because it primes me for what I should see in the forthcoming data. The designer was thoughtful both to put the main point into words and to make the words stand out via their size and placement at the top of the page.

If you’re like me,  then you’re probably now searching for evidence of an increase in XBX Business in the graph. You’ll find it eventually, but there are ways to eliminate the need for this tedious search process altogether.

One option is to use the same color between the data and the text, while simultaneously de-emphasizing the rest of the visual with grey. Check out what a difference this makes: 

This simple change—the power pairing of color and words—ensures that the audience is more likely to immediately understand the results of all the hard work we’ve done. All we have to do is make it easier for them to see in the first place. 

Are there more improvements we can make to this slide? Absolutely—you can download the data and practice improving this visual with me in the SWD community exercise from good to great.  

how to do it in Excel: adding data labels

Today’s post is a tactical one for folks creating visuals in Excel: how to embed labels for your data series in your graphs, instead of relying on default Excel legends.

To illustrate, let’s look at an example from storytelling with data: Let’s Practice!. The graph below shows demand and capacity (in project hours) over time.

There are a few different techniques we could use to create labels that look like this.

Option 1: The “brute force” technique

The data labels for the two lines are not, technically, “data labels” at all. A text box was added to this graph, and then the numbers and category labels were simply typed in manually. This is what we affectionately refer to as “brute-forcing” your tool to make it look the way you want it to, regardless of its defaults. Remember: your audience only sees the end result of your work, even if the behind-the-scenes steps aren’t exactly elegant. 

One benefit of this approach is that I have greater control over the formatting: size, position, and color of the labels. I can easily make them appear how I want them to appear by simply adjusting the formatting, which is much easier to do with a text box than with a genuine data label. The downside is that this method may not scale easily with many graphs, or those that will be frequently updated with new data—as the data changes, the text labels won’t move with them.

Option 2: Embedding labels directly

Let’s look now at an alternative approach: embedding the labels directly. You can download the corresponding Excel file to follow along with these steps: 

Right-click on a point and choose Add Data Label. You can choose any point to add a label—I’m strategically choosing the endpoint because that’s where a label would best align with my design. 

Excel defaults to labeling the numeric value, as shown below.  

Now let’s adjust the formatting. Click the label (not the data point, but the label itself) twice, so that these white boxes appear around it:

Right-click and choose Format Data Label:

In the Label Options menu that appears, you can choose to add or remove fields by checking (or unchecking) the corresponding box under Label Contains. To add the word “Demand”, I’ll check the Series Name box.

The result is this:

The label appeared in all-caps (“DEMAND”) because it’s referencing the underlying data—I could adjust the header in Column M to “Demand” if I didn’t want the entire word capitalized (this is a stylistic choice).

To adjust the number formatting, navigate back to the Format Data Label menu and scroll to the Number section at the bottom. I’ll choose Number in the Category drop-down and change Decimal places to 0 (side note: checking the Linked to source box is a good option if you want the labels to reformat when the formatting of the underlying source data changes).

My resulting visual looks like this:  

From here, I can manually adjust the label alignment by highlighting the graph and making the Plot area smaller so that the label doesn’t overlap the line:

I’ll repeat the same steps to add the Capacity label:

The final thing I’ll do is clean up the formatting of those labels—move the numbers in front of the words, change the number format to be rounded to the thousands place, switch the colors of the labels to match the lines they refer to, and make the font for “24K Capacity” bold.

The benefit of this embedded approach is that as my underlying source data changes, the labels will update accordingly. For graphs that will be refreshed frequently, setting up these steps once will save you the headache of searching for and manually manipulating text boxes.

More Excel how-to’s 

For more tactical instructions, check out these previous posts (and let us know in the comments if there’s something you’d like to see): 

• To depict a range of values: add a shaded band

• To create a frame of reference: embed a vertical line 

• For better virtual presentations, animate text boxes to control what your audience sees 

• For cleaner visual alignment: put elements directly in cells 

• No more excuses for messy graphs: here’s a clean slate template and from data table to story

---

This post was inspired by a recent conversation during our bi-weekly office hour sessions. Do you ever need quick input on a graph or slide, or wish you could pick the SWD team’s brain on a project? Subscribe to premium membership for personalized support and get your questions answered. Our team has enjoyed getting to know many of you during these fun and interactive sessions!

a heatmap makeover

I often describe heatmaps as a good means for getting an initial view of your data. They can help you start to explore and understand where there might be something interesting to highlight or dig into. But once you’ve identified the noteworthy aspects of your data, should you use heatmaps to communicate them?

As often is the case, it depends.

If you are communicating to an audience who likes to see data in tables—applying heatmap formatting can provide a visual sense of the numbers without fully changing the approach (or having it feel like you’ve taken detail away). If you know your stakeholders will want to look up specific numbers (particularly in the case where different stakeholders will care about different numbers) and then understand them in the context of the broader landscape, a heatmap may also work in this scenario.

That said, we can often make things easier by fully visualizing data in a graph. Let’s consider the heatmap at the onset of this post. First, I’ll take some steps to improve it. After that, I’ll explore a couple ways to graph the data. As I walk you through my process, I encourage you to compare the different views and decide which you like best. Why is that?

To start, I’ll change the colors. Red-green color scales can be problematic for colorblind audience members (the most common type of coloblindness is red-green colorblind, where both red and green end up looking brownish, which can be particularly difficult if they are similar in intensity). I played with a few color combinations, using conditional formatting in Excel. The following shows my iterations.

I started with a 3-color scale, setting bright blue for the lowest value, white for the 50th percentile, and orange for the highest value (top left). Because of their concentration (both in intensity and relative proximity), the blues stood out more than I wanted, and the white cells also competed for attention. To adjust, next, I tried lightening the blue and moving from white to grey for the 50th percentile (top right)—this made everything look muddled. For my third combination, I lightened the blue and reverted to white for the 50th percentile (bottom left). I felt good about this one but also tried one more version, a 2-color scale with orange for the highest value and white for the lowest (bottom right). This final view puts clear attention on the sites with higher error rates—something likely worth noting.

Which of the above variations do you prefer? Are there other changes you would make to this heatmap?

In terms of other changes I’d like to make: I would advocate reducing the precision. Not only does this many decimal places make the numbers difficult to talk about (“Site C has the highest monthly error rate over the past six months, averaging oh point three six four percent per month”) but it’s also not likely that this is true precision. If these are manufacturing sites, we’re not likely evaluating every single unit made for errors, but rather some sample, which means there’s natural error to our numbers. Due to both of these reasons, I’ll round to the nearest hundredth (if we go further than that, we’ll lose the ability to differentiate between many of the numbers given their magnitude).

Also, if there’s not a compelling reason to keep the sites ordered in the manner they are currently, I’d be inclined to order them according to the error rates. To direct attention to higher error rates first, I’d start with those and order by decreasing error rates as we move from left to right across the heatmap. The following two views (colored the same as the final two iterations above, respectively) reflect these changes. I also included a screenshot of the settings I used I achieved each via conditional formatting in Excel (to access this screen, go to the Format dropdown and select Conditional Formatting, then click + to set a rule).

We could arguably make some additional changes to the heatmap (for example simplifying the date format or eliminating or lightening borders). But rather than make more modifications to this view, let’s look at some ways to more fully visualize this data.

When I encounter data over time, the first graph type that comes to my mind is a line graph. However, if we graph the error rates for all of these sites, we end up with a tangled mess:

Depending on how we are presenting the data and what we want to highlight, however, it’s possible that we can make this tangled mess work. For example, let’s imagine we are presenting live and want to focus on a single site at a time. I can do this in the line graph and might also pair it with a horizontal bar chart to focus specifically on how error rates across the sites compare as of our most recent monthly data point. Let’s focus first on Site C:

I could cycle through the other lines and bars one at a time. In a written report, this approach of repeating the same graphs with different segments highlighted can also sometimes make sense. For example, I could have a section in my report for each site using a setup like this, perhaps also adding commentary in each section to explain the relevant context.

As another option—and this is one that often works well when faced with a spaghetti line graph like the one above—I might try visualizing the data in a panel chart. Whereas in the last example, I was cycling through the same graph with different points of emphasis, the panel chart shows the same graph for each segment all together in the same view. In the following, the primary comparison that’s easy to make is how error rate for a given site has changed over time. I can also see each site in the context of the rest. This could be an alternate view to meet the needs of a mixed audience, who each wants to concentrate first and foremost on their section of interest, but also understand how it fits into the broader context.

The preceding view (which is nine separate graphs arranged to look like a single visual) could work great to analyze trends across the different sites as we continue to explore our data. Once we’ve done that and have some specific points we want to communicate, I could add framing and sparing emphasis to direct my audience to the important parts. For example, in the following, I categorized each site based on the latest month-to-month change (down = SUCCESS; flat = STATUS QUO; up = WATCH). I also highlighted and annotated some possible areas of interest.

Did you decide which view you like best? I’m partial to this final one, but different scenarios and constraints might call for a different approach.

To practice on your own with this example (you could even try emulating one of the approaches I’ve shared here in your tool of choice), check out the exercise take it beyond the table.

You can also download my Excel file with all of the graphs from this post.

words are your friend—when you choose them wisely

Have you ever looked at a graph and thought, I'm not sure what I'm meant to get out of this? 

When communicating with data, we sometimes forget the importance of words. We might assume that numbers—and the charts that visualize them—speak for themselves. Quite the contrary! Words have a very important place when communicating with data because they help our graphs make sense to your audience (who doesn’t live in your head). 

Here’s an example, excerpted from storytelling with data: a guide for business professionals. Check out how the text makes the data more accessible in the graph on the right compared to the original.

Before: intention is unclear

After: action and supporting context is clear

Let’s turn our attention to a cautionary tale. When we don’t choose our words carefully, they can have the opposite effect—resulting in our audience having to do unnecessary work to understand our graphs. This example is inspired by a recent graph makeover from one of our workshops (details have been changed to preserve confidentiality).

Consider the following visual. Before you study the data, read the headline, and make a note of what you expect to see in the graph. 

If you’re like me, I expected to see a chart depicting a lack of awareness with a corresponding data point showing that 91% of surveyed customers have never used the service. Upon further examination, I figured out that these three charts holistically represent the inverse measures(s)—awareness, consideration, and usage—compared to what’s annotated as the takeaway title. With some mental math, I then reconciled the 91% non-usage rate to the 9% usage rate in June 2020 (far right data bar) but only because I had enough patience and time to undertake this task! 

On a positive note, the designer of this original graph took care to put the main takeaway in words in a prominent place at the top. To further improve, we can alleviate some of the mental effort our audience might encounter with this visual by making a few alternative design choices. 

One option would be to reword the takeaway title to reference usage rates and employ similarity of color to provide a visual cue to the data it describes. 

Another option—particularly if the conversation is better suited towards where we can improve—is to preserve the original title but change the graph to a 100% stacked bar to visually show the magnitude of opportunity.

Both alternatives are shown below. Consider how the words chosen in these two views better enable you to see evidence of what they describe. You can download the data to see how I designed these two visuals in Excel.

This cautionary tale shows that if we don’t word our takeaways carefully, then our efforts (both in the analysis and the communication) might be for naught. In data visualization, words are our friend—but only if we choose them wisely. 

For more examples of using words effectively, check out a power pairing and transforming slide titles. Take it a step further and build your data storytelling muscle with an actual dataset in the SWD community exercise words help data make sense. 

a funnel makeover

Today’s post outlines an alternative approach to using a funnel to visualize a process and related data.

You’ve likely seen funnels like those shown below. The intent is to show how something—customers, products, sales deals—passes through a series of stages until some desired action—conversions, purchases, views—happens. With this overview, you can help others understand the process with a visual representation.

Source: Google image search

While the funnel can be effective at showing a summary of the process, they are not particularly effective when it comes to measuring that process—and how each stage compares to each other in size. Why? Because a generic funnel doesn’t encode the metric it represents.

Let’s look at a specific example.

Imagine you are an HR analyst in a large organization. A new senior leader is coming up to speed on the recruiting process and related metrics. Your teammate put together the following slide for an upcoming meeting. Spend a few moments studying this visual. What observations can you easily make from this slide? What questions do you have?

Let’s first focus on the funnel. I can easily make one broad observation: there are 50 applicants for every 1 hire—and that applicants pass through three stages in between. As I start to intake the volume of applicants in each stage, now it starts to feel like work. I have to mentally picture (I’m a visual learner; others may be processing this differently) 50 applicants going to 17 applicants in the CV review stage, and so forth. This is more effort than necessary if I’m simply desiring to understand where the volume is concentrated. If my audience is a senior leader, I don’t want them to have to work to get a relative sense of the numbers.   

Let’s look at the supporting text at the bottom of the visual. This helps provide a sense of the volume of interviews conducted but it leaves me wondering so what?  Is this indicative of a successful recruiting operation that meets organizations overall hiring needs, or is it a call for action for a better process or more staff? Let me offer one approach to transition from putting numbers and pictures on a slide to creating an integrated visual to more quickly impart understanding.

Instead of the funnel, we could utilize a square area graph. For reasons outlined in the current #SWDchallenge, we don’t use a lot of area graphs. They’re ink-heavy and our eyes aren’t great at comparing areas. However, in the use case where you’d like to communicate numbers of varying magnitudes, a square area graph gives us an additional dimension: the width of a square (in contrast to a bar chart, where we only have height or width). The second dimension of a square area graph allows us to visualize more information in less space.

Check out the difference between the funnel and the same data visualized in a square area graph below. Each square represents an applicant and the volume of applicants in each stage is encoded by color. This design allows me to see the number of applicants in each stage.

NOTE: Alternately, I could have designed my graph with 100 squares and rescaled the numbers so they could be expressed as relative percentages. However, I intentionally chose to use 50 squares and keep the same volume of people in each stage to emphasize the flow to make 1 hire. 

Next, let’s turn our attention to answering, “so what?” My redesign of the slide might look similar to what you see below. I can use the square area graph—paired with the supporting context from the original slide—to achieve the desired outcome. Our senior leader has to do less work to understand what is being communicated.

In conclusion, a square area graph can be an alternate choice for a funnel when you want to visualize and compare numbers along different stages of a process. You can download the accompanying Excel file to see how I created this visual. 

See the following for additional examples of square area graphs:

• Your potential return on investing $1,000 with Warren Buffett

• #SWDchallenge: square area graphs

Have you seen instances of funnels used effectively? Leave a comment with your thoughts. If you’d like to try a more traditional area graph, flex your data storytelling muscles with this month’s #SWDchallenge.

---

how to make a better pie chart

A friend called me recently and started our conversation with: “I know you dislike pie charts, but…can you help me create one?” 

Spoiler alert: I don’t hate pie charts. They’ve received a bad rap over the years and with good reason—they are very commonly used when another chart type would be better suited. The appropriate use case for a pie chart is expressing a part-to-whole relationship. Their limitation is that it can be difficult to accurately judge the relative size of and compare the segments. To see examples of the correct use case for pies, check out what is a pie chart? and you can read more about the ongoing debate with these related articles: the great pie debate and an updated post on pies. 

My general recommendation on pies is that if you can clearly articulate why a pie chart is a more effective choice than another type of graph, then you’ve appropriately considered that it will satisfy your scenario. In today’s post, I’ll highlight a specific use case for a pie chart—and show how you can create an improved one. 

Let’s use my friend’s scenario to illustrate. She is a sales manager for a mid-size equipment manufacturer and is preparing for an upcoming sales meeting with her team. She wants to communicate the degree to which her salespeople are losing sales deals. She used her tool to create a chart similar to the one shown below.

Spend a minute studying this visual: what can you easily conclude about the data? What changes might you make? 

I came up with five changes I would make to the existing chart. I’ll address those momentarily, but first we had an in-depth discussion on why she was sold on a pie chart. Her rationale was that she wanted her audience—salespeople at her organization—to understand that they were losing nearly two-thirds of their deals for two reasons: not qualified and timing. We discussed the various limitations of pie charts and the trade-offs and she’d decided she’d be comfortable with them. One question I posed: how important was it that her audience have a sense of the volume of deals—that they be able to accurately judge the magnitude of the categories (lost reasons) relative to each other?

After a thorough discussion, we both felt comfortable that the pie chart’s part-to-whole relationship worked for what she needed—to convey that a large percentage of deals were being lost for two specific reasons and to focus her audience’s attention on identifying a possible solution.

With this intent in mind, I walked her through the five steps I’d take to design a more effective pie than what her tool created by default. There may be others you considered; I’ll focus here on those that sufficed given the context of her scenario:

1. Sort meaningfully: In this case, this means ordering the data so that the largest categories (not qualified and timing) appear at the top of the chart. 

2. Eliminate the legend: Labeling the categories directly reduces the work of going back and forth between the legend at the top and the data below.

3. Specify what is being shown: I’ll include a more specific chart title and a descriptive subtitle specifying the metric being graphed (% of total deals lost with the volume of deals lost for context).

4. Add a takeaway and call to action: I’ll add annotations near the data to answer the question, “so what?”

5. Use color sparingly: I’ll use color thoughtfully to direct the audience’s attention.

Check out the impact of these changes in the visual below. You can download the file to see how I created this in Excel. 

Broadly speaking, these five changes greatly improve this default pie chart—but they’re actually not specific to pies. Rather, these are steps I find myself taking nearly every time I design a chart. Consider my changes together with those you may have identified: where might you apply the same to your own work?

For more on improving pies, practice flexing your data storytelling muscle with the alternatives to pies exercise in our SWDcommunity.

---

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.