how to do it in Excel: emphasizing a data point

Today’s post is a tactical one: how to highlight a data point in Excel. 

When we craft visualizations for explanatory purposes—that is, when there’s a specific finding or recommendation that we want to communicate to someone specific—our goal is to drive action. In those cases,  our visuals should emphasize what’s interesting in the data and what requires attention. Highlighting key points in our graph is an important step in creating successful explanatory communications.

Here are some charts that use this technique effectively, making it clear where an audience is meant to focus. You can check out many more examples by using the Discover feature in the SWD community. 

Let’s get right to how to accomplish this in Excel. The example we’ll be working with today is a graph that shows average revenue by event type for a venue that hosts various public functions. You can download the accompanying data file to follow along with these steps.

Here’s our starting point, which Excel created by default.

First, I'm going to create a clean, uncluttered version of this graph. (You can reference this prior post for steps to reformat Excel graphs.)

Let’s assume my audience is a marketing leader who’s interested in knowing which event type is expected to have the highest average revenue in the coming year. Since the answer is “Fundraisers,” I’ll direct attention to the 2020 forecast for that event type with these steps:

  1. Click once anywhere on the Fundraisers line, then click on just the 2020 data point. Right click on the selected point and choose Format Data Point

2. The Format Data Point menu opens. Under the Fill & Line tab (with the paint bucket icon), click on the button that says “Marker,” and then navigate to Marker Options. Click the Built-in option, then choose a filled circle in the drop-down Type menu. I’ll also adjust the size to 9 (the size you choose should depend on the existing thickness of your lines and your individual needs for using the marker as emphasis).

3. In the Fill menu, I can adjust the color by selecting my preferred choice under Solid fill:

4. I’ll also adjust the outline color in the Border menu below it. I chose no line, but this is my personal preference.

My resulting visual looks like this:

Even if you just quickly glance at this graph, notice how your eyes are drawn to that blue dot. By adding and formatting this particular data point, I’ve made it clear to my audience that the Fundraiser value in 2020 is where I want them to look.

Since the 2020 data point is a forecast, I’d want to visually distinguish it from actual data by varying the line style (making it dotted instead of solid) and the marker style (making it open rather than filled). I’ll also include the numeric data labels for specificity’s sake.

Here’s how we can apply those changes, as we continue to refine our graph:

5. To vary the line style, highlight the data point of interest the same way you did earlier in Step 1. Right-click and choose Format Data Point.

6. In the Fill & Line tab again, instead of clicking on Marker, click into the Line menu, where you can adjust the various settings of the line immediately preceding the data point. To achieve the effect in my example above, my selections are a solid blue line with a width of 2.5 pt and a Dash type of Square dot. You can follow these same steps for the other event types in the graph as well so that all of the lines for 2020 are dashed to indicate a forecast (without changing the color to blue).

7. To add the numeric labels, I’m opting to include them as text boxes so that I have greater control over the formatting. You can read more about the considerations for text vs embedded labels in this prior post.

These formatting changes require a bit of extra effort, but they make your charts far more effective. Don’t assume that your audience knows where to look in your visuals—make that task easier for them through your design choices.

But wait…there’s more! What about all the other data? Isn’t that interesting to the audience? It absolutely could be—I invite you to practice how you’d approach this task in the community exercise emphasize multiple takeaways. You can also check out how I’d walk my audience through a story in the video so what? using story to drive action (open to premium subscribers). 


More Excel how-to’s:

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

Before: intention is unclear

After: action and supporting context is clear

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. 

 
2_original.png
 

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. 

 
3_option1.png
 

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.

 
4_option2.png
 

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.

5_comparison.png

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

alternatives to pies

My disdain for pie charts is well documented. While opinions on their usefulness run the gamut, I am certainly not alone in my contempt. In my workshops, I sometimes get the question, "In what situation would you recommend a pie chart?" For me, the answer is never. There are a number of alternatives, each with their own benefits. It's these alternatives that I'll focus on in this post.

Read More

strategies for avoiding the spaghetti graph

It seems that I have a distaste for any chart type that has food in its title. My hatred of pie charts is well documented. Donuts are even worse. Here's another to add to the list: the spaghetti graph. They look like someone took a handful of uncooked spaghetti noodles and threw them on the ground. There are a few strategies for taking the would-be-spaghetti graph and creating more visual sense of the data. I'll explore three such strategies here.

Read More

no more excuses for bad simple charts: here's a template

If you're using Excel to try to tell a story with data, plotting the data in this application should be the first step in your data visualization process, not your last. It takes time, iterations, and sometimes some brute force to get from the graphing application's defaults to a visual that you're proud to present.

Read More

the waterfall chart

I find waterfall charts to be useful when you are interested in visualizing a starting quantity, positive and negative changes to that quantity, and the resulting ending quantity. I like waterfall charts, so I thought it might be useful to do a post focusing on them: what they are, an example use case, and how to use what I like to consider "brute-force-excel" to create them.

Read More