My New Favorite Graph Type: Overlapping Bars

Why have I fallen in love with this graph type? I think its because its such a great way to visualize the comparison between two things, when one is inherently a part of the other. In recent client projects, I’ve used these to show actual v. budgeted amounts. Or individual v. aggregate scores. Or those we’ve surveyed v. the entire population. Progress toward a goal or against a benchmark. How funny my boyfriend thinks I am v. how funny I really am. You get the idea. AND, best of all, making this graph is a piece of cake.

Your data table will look pretty standard:

OverlappingTable
You’ll highlight all of this data and insert a very standard, typical, clustered bar chart. One of these:

Overlapping1
The trick is to get one set of bars on top of the other, kind of nested inside. To do that, you’ll click on the set of bars you want in front. Then right-click and select Format Data Series. In the box that pops open, click the radio button next to secondary axis.

Overlapping2
Moving a series to the secondary axis essentially means it will layer in front of the original series (which is still on the primary axis). The secondary series pretty much occludes your other set of bars, doesn’t it? We want the bars in the back to be wider, so the ones in front look sort of nested inside. Getting the bars wider involves a super geeky technical-sounding Excel move – reduce the gap width. Excel means the width of the gap between two of those blue bars. Why not just call it Make the Bars Wider? I don’t know. Maybe Excel secretly hates you. Regardless, to reduce the gap width, click on the little bit of blue bar you see in the background with the Format Data Series window open. In that same window, you’ll see a slider next to Gap Width. Crank it to the left.

Overlapping3

You’ll just have to eyeball the exact gap width until it looks good to you. See how it made the bars much thicker, kind of making them look like the own the orange bars? That’s the idea! We want the visual representation to reflect the relationship between the variables in the dataset.

Moving those original bars to the secondary axis means we also see a second y axis scale at the top of the graph. Make sure the minimum and the maximum of both y axes match perfectly. Then delete the one at the top. It’s ruling the orange bars but its redundant.

Finally, add a better font, some action color, and a succinct title.

Overlapping4

Overlapping bars show surplus and shortfall so well. (What a tongue twister – good thing this is just a blog post.) 


Read!

Check out even more useful graph types in my latest book, Effective Data Visualization.

You’ll find at least 4 other ways to visualize comparisons against benchmarks, plus my sought-after Chart Chooser handout.

This second edition even includes the largest collection of qualitative visualization options ever published.

Skyrocket Dataviz with Graph Guides!

Graph Guides is a custom-built, year-long sprint through 50 Academy tutorials.

When you enroll, we’ll assess your current data viz skill set, build you a customized learning path, and hold your hand as you blaze your way to new talents.

Enrollment re-opens Spring 2022

Make A Lollipop Graph in Excel

The simplest way to show many types of data is through a column or bar chart, ordered greatest to least. These will work just fine, most of the time. When do they fall short? Well, when the values are all high, such as in the 80-90% range (out of 100%). Then the set of tall columns can be visually aggressive. In that case, try the lollipop graph, which looks like its name – a dot where the value that sits on top of a stick, connected to an axis. Here’s how to hack a lollipop out of Excel.

If you don’t end up loving this method, alter my steps for creating a horizontal dumbbell dot.

The backbone of the lollipop graph is a scatterplot, which means we will need x values and y values. The x values are easy – those are my actual data points, my survey responses in this case. The y values are going to be faked, just inserted to make each lollipop equidistant from each other. So here is my data table, with my actual survey responses and a new column next door where I typed in placeholder data, from 1 to 21. (Response options pulled from Multicultural Alumni survey for SUNY Cortland. Response percentages are dummy.)

Figure5.26_Lollipoptable

Now highlight both columns of numbers and insert a simple scatterplot. Don’t highlight the data labels or the header row, or else the graph won’t work out properly.

Figure5.27_Lollipop1

The scatterplot process created dots – these are the lollipops! It is basically visualizing just the end of the bars in the bar graph. Right now, I suggest you click on the horizontal gridlines and delete them. They can confuse things if we keep them in place any longer.

It’s time to make the stick of the lollipop. We are going to create these using error bars. In Excel 2013, navigate up to the Chart Tools>Design tab and look for the button that says Add Chart Element. Click the dropdown arrow there, hover down to Error Bars, hover on it’s arrow to open another menu, and finally click on More Error Bars Options. In Excel 2010, you should just see a button in the Chart Tools ribbon for Error Bars.

As soon as I clicked on More Error Bars Options, my graph went wacky.

Figure5.28_LollipopWacky

Excel added error bars in all four directions around each dot. We do not need the vertical error bars at all. So in the dialogue box that opened, change the number in the Fixed value box so that it is zero.

Figure5.29_LollipopVerticalError

This will eliminate one set of error bars. To get to the other set, click on the little arrow next to Error Bar Options and then switch from Series 1 Y Error Bars to Series 1 X Error Bars.

Figure5.30_LollipopPickHorizontal

You’ll see the same basic options, now just for the horizontal set of error bars. Here is where the magic happens. You’ll want to adjust every bit of this dialogue box. We really only want the error bars that shoot out to the left, so in the Direction area, switch from Both to Minus.

Figure5.31_LollipopHorizontalAdjustment

In End Style, get rid of the cap. In Error Amount, click the radio button by Percentage and type in 100%. This will fix your x axis so it starts at 0%, like it should, and will extend the lollipop stick from the lollipop head to the y axis.

Figure5.32_Lollipop2

The essential pieces of the lollipop graph are in place.

Give yourself a fist pump because that was awesome.

Next up, let’s fix that y axis, shall we? Right-click on it and adjust the maximum to 22 – that’s one more than the highest number we have in our fake data. If, in doing so, Excel adjusts your minimum, just change it back to zero. Now you can delete the y axis labels altogether.

Let’s get the data labels in place now, so we know which lollipop represents what. You could just do this by inserting textboxes. Or you can do it like ninjas do. Ninjas only, read on.

We are going to insert another series of fake data so that we can use it’s labels. Since we are still working with a scatterplot, the y values for the data are already here – we will use the same y values in the Lollipop spacing column. But the x values will be new. So add another column of data and this time, just fill it up with zeroes. This way, the new series of data will be a set of dots all the way over on the left side of the graph and the labels we eventually put there will look like regular, standard, proper labels for the lollipops. The table will now look like this:

Figure5.33_LollipopTable2

Right-click on the graph and click Select Data. In the dialogue box that opens, click on Add in the Legend Entries section. Use the cell picker icons in the new box to select Label holders as your series name, all those zeroes at the Series X values, and all the numbers underneath Lollipop spacing as your Series Y values.

Figure5.34_LollipopTable3

Click OK and your graph will have a second set of dots going up the left side of the graph. Sweet! We are going to add data labels to the dots! But there’s no room for the data labels yet. So click inside the graph, on the white background (Excel calls this the plot area). It’s border will become active and you should see a little square white box on the left, amidst your new series of dots. Click on that baby and drag it over to the right so that you are shrinking the plot area and making room for your labels.

Figure5.35_LollipopDrag

Now that there’s room to breathe, right-click on the new set of dots and select Add data labels. Excel is going to give you the y axis values, which are a secret. But click on each label and link it to the spreadsheet cell with the label you actually want. Here’s how you create that link. Click on a single label and type an equals sign in the formula bar. Then go click in the spreadsheet, in the cell with your true label. Hit Enter and your actual label will populate in the graph, Ninja.

You may need to readjust your plot area here or enlarge your entire graph to make room for all those labels (I did both).

Labels are in place, lollipop and stick are ready to go. It’s time to get rid of the second set of dots that our labels are attached to. Right-click on those dots and select Format Data Series. In the Marker Options window, select None to eliminate the marker dots altogether.

The last step here is to add an awesome title and make any color adjustments.

Figure5.36_LollipopFinal

Go ahead and compare this lollipop graph to a bar graph of the same data. Less visually demanding, isn’t it? Even though it took us several more steps to make the lollipop, we put in that elbow grease in order to make life easier for our readers. Plus you learned about the secret power of error bars in this process!

Yummy enough to eat, I know! Lollipops are decent alternative to standard bar chart when the visualization is overpowering due to its massive ink because the lollipop focuses attention at the value.

This tutorial and others like it are featured in my book, Effective Data Visualization. Order now.


Skyrocket Dataviz with Graph Guides!

Graph Guides is a custom-built, year-long sprint through 50 Academy tutorials.

When you enroll, we’ll assess your current data viz skill set, build you a customized learning path, and hold your hand as you blaze your way to new talents.

Enrollment re-opens Spring 2022

Make a Slopegraph in Excel

Slopegraphs are a newer graph type with powerhouse capabilities. They rely on Excel’s line graphing feature but they don’t necessarily have to show change over time. Slopegraphs play into our ability to judge slope fairly well. For this reason, they are perfect for highlighting the story of how just one category decreased when others increased, or to show that one increased at a rate much faster than the others.

Let’s say we are comparing sales in each department of a grocery store, before and after they moved locations. As a side by side bar graph, it would look like this:

Figure3.3_GrocerySidebySide

Even though humans are good at detecting length, this graph is somewhat difficult to digest. It’s a bit hard to see that sales of cheese are actually down at the new site, for example. It’s easy to miss that just one New Site bar is shorter than its corresponding Old Site bar. A slopegraph will make the story clear.

The table for a slopegraph is probably quite familiar: just two columns of numbers.

Figure3.4_GroceryTable

You will highlight the rows and columns and insert a line graph. I prefer a line graph with markers, but maybe I’m just obsessed with dots.

Figure3.5_GrocerySetUp

It doesn’t quite look right – the departments are on the x-axis and they shouldn’t be. Click your old friend Switch Row/Column.

Figure3.6_GroceryGraph1

You can see the beginnings of the slopegraph in this modified line graph already. The differences are minor but interpretively important. Slopegraphs usually have no space between the end of the line and the end of the plot area. The lines are pushed to either side of the graph. To delete that gap, we are going to click a single magic button inside Excel. Really, this is pretty much where it all happens.

Right-click on the x-axis and select Format Axis from that menu. In the Format Axis box that opens, look for the area with the heading Axis position. Under that, pick the radio button next to On tick marks.

Figure3.7_GroceryAxisFormat

By default, Between tick marks is selected but On tick marks will push the lines to the edges of the graph.

Figure3.8_GroceryGraph2

From here on out, it’s all formatting baby!

Slopegraphs tend to be long and narrow, unlike line graphs, which are usually wide and short. So resize the graph by stretching the corners. This also puts some distance between points that are nearly on top of one another.

We also need some space on either side of the line to add labels. Currently, there’s a y-axis there. Delete the y-axis (just click on the numbers and hit the Delete key). Do the same for the y-axis gridlines. That buys a little bit of room on the left, but not enough. So click inside the plot area – the white space in the middle of the graph – and drag it’s side handles in on both sides. The graph space will narrow but the overall chart area will stay the same.

Figure3.9_GroceryPlotShrink

At this point, it should already be really obvious that one line is going down. We can also see that another line is increasing moreso than the others. Slopegraphs highlight this story better than any other chart type. As we continue to format the chart, we will use action colors on those two lines to bring attention to them and the other lines will go gray.

Now it’s time to delete the legend and label each point of the line. With the slopegraph, line labelling is a little tedious. You can only add labels to one line at a time. Also, I like to add the label to one side and just the number to the other, so there are a lot of clicks involved. Here’s the most streamlined way to do it and we will alter the colors in the process.

Right click on the top line and select Format Data Series. In the box that opens up, change the line color, marker fill color, and marker border color all to a medium-light shade of gray.

Figure3.10_GroceryLineChange

The line should still be highlighted inside your graph. Right-click on it and select Add Data Labels. This will add the dollar values to both markers. Click on the labels and change their color to match the lines. With those labels highlighted, carefully click on just the left label so that it is the only one highlighted. Then right-click on that label and select Format Data Label (it should be singular). New box opens! Check Series Name so that the right grocery department label will be added, but only on the left. Uncheck Show Leader Lines (this will matter more later). Personally, the comma that separates the label from the value offends me. In the Separator drop down menu, pick the option that says (space). Then look for the Label Position choices and pick Left.

Figure3.11_GroceryLabels

Yes, lots of steps there but the payoff is clear labeling and smart color coding that support the story inside this data. Repeat this process for the rest of the lines, choosing a medium dark color for the rapidly increasing line and a dark color for the decreasing line.

Figure3.12_GroceryAlmostDone

As you labeled these lines you probably noticed that the labels for Packaged and Frozen were overlapping each other. To fix that, just click on each label and drag it up or down a little to space them apart. This is why we unchecked Show Leader Lines earlier. With that box checked, each label would have an ugly, cluttering little line tying it to its part of the slopegraph.

Its ok add a great title and stop here. I like to add a final touch by inserting vertical lines that shoot up from Old Site and New Site. Right-click on the x-axis and in that menu, select Add Major Gridlines. BOOM! I also delete the x-axis line. To do so, right click on the x-axis again and select Format Axis. In the new box, look for the line color area and select no line.

Figure3.13_GrocerySlopegraph

The slope of the slopegraph markedly accentuates increases and decreases when comparing two sets of numbers.

This post is an excerpt from my latest book, Effective Data Visualization. It has loads of advice on the best chart type to use and how to make it in Excel.


Skyrocket Dataviz with Graph Guides!

Graph Guides is a custom-built, year-long sprint through 50 Academy tutorials.

When you enroll, we’ll assess your current data viz skill set, build you a customized learning path, and hold your hand as you blaze your way to new talents.

Enrollment re-opens Spring 2022

The Easiest Way to Make Bullet Charts in Excel

There are lots of ways to make bullet charts, some easier than others, some better suited for specific visualization contexts. Hell, there are plug-ins you can purchase that make it a snap. Except when my plug-in broke and I had to remake about a bazillion bullet graphs in several dashboards. :/ doesn’t even begin to express it. So yeah, we could use yet another method. This is the easiest way I have found to do it right inside Excel. It comes from this book, which gets useful at the end.

The backbone behind this version is an set of overlapping column charts in Excel. Except we are going to leverage a totally amazing and misunderstood feature of Excel.

Here is how I set up my table. The first three rows are the performance areas that will become the background of the bullet. The value row is my actual value and will ultimately show up as a black column in my bullet chart. The target value will become a red line.

BulletChartTable

To make the chart, highlight everything and insert a stacked column chart. Slow your roll – not the 100% stacked column, just the regular stacked column. It’ll come out looking like this:

BulletChartA

Not cool, Excel. This isn’t what I want. Right-click on the chart and click Select Data. In the box that pops up, take care of some business: Switch Row/Column. Click ok and now you should see this:

BulletChartB

Well ok, so that’s not really doing it for you, either, is it? Let’s start by making the target into a line instead of a bar. Right-click on the target slice of this stacked bar and select Change Chart Type. (In Excel 2010, this is a button in your banner, not part of your right-click menu.) In the box that pops up, select Stacked Line with Markers.

HOLD UP! Don’t close the box. While here, we are going to execute some Excel ninja skills. Right now everything is stacked as one bar (and a line) but we really want the performance areas as one stacked column in the background and our value and target line in the foreground. Do you know how we shift them to the foreground? It’s by moving them to the secondary axis. Yeah! That crazy thing we always see in Excel! It actually has a purpose! So in that dialogue box you should also see a checkbox that says Secondary Axis. Do it! Click now! (In Excel 2010, right-click again on the target part of the graph and select Format Data Series. You should see a radio button there for Secondary Axis.)

Then right-click on the target line and select Format Data Series. Adjust the marker option so it is a Built-in marker type and select the dash. Make it big, like a size 15. Change the fill color to red and in the Border area, select No Border. It should end up looking like this.

BulletChartC

Yeah? You with me so far? That was the hardest part. Almost there.

Let’s move Value so it’s also on the secondary axis. Right-click on the Value slice of the stacked bar and select Format Data Series. In the window that pops up, you’ll see an option to move it to the secondary axis. CLICK! It’ll look super weird so BEFORE YOU CLOSE THAT WINDOW move the slider bar on the Gap Width up to like 250% so the Value bar gets skinny. Then you’ll be able to see the others behind it. While in the formatting stuff, change the fill color of Value to black. Hopefully your graph looks a lot like this:

BulletChartD

It’s all formatting from here on out, baby. You’ll want to adjust both axis so they stop at 100%.

BulletChartEAdjust the colors of the performance areas so they are shades of gray. Delete the right axis.

BulletChartF

Let’s pause here for a quick sidenote: This color scheme is a great example of how we can design such that the colors will still hold up when reprinted in black and white. Putting the lightest gray on the top will let the target lines show up even if the Value bar is short and the grays are all still light enough that the black column will maintain its contrast. That’s scoring full points on the Data Visualization Checklist item “Color is legible when printed in black and white.” Back to your regularly scheduled programming.

Now suppose your boss says you also need to report on Regions B-D. Go through that whole process again? Uh, no. Just click on the graph such that the original data table is highlighted in blue. Drag the corner handle so it covers all of your data.

BulletChartG

Boom, the chart:

BulletChartH

Add an informative title. Add data labels to show the value on your black columns. Coming up with solution like this just takes thinking through the backbone of the graph and what Excel default graphs are available to make it happen.

BulletChartFinal

This method of bullet chart construction takes no extra plug-ins, no crazy math, just a teeny bit of ninja skill to lean on the secondary axis. So super simple.


Skyrocket Dataviz with Graph Guides!

Graph Guides is a custom-built, year-long sprint through 50 Academy tutorials.

When you enroll, we’ll assess your current data viz skill set, build you a customized learning path, and hold your hand as you blaze your way to new talents.

Enrollment re-opens Spring 2022

Adding a Benchmark Line to a Graph

This simple line packs so much power. Adding a benchmark line to a graph gives loads of context for the viewer. Here’s how to make one right inside Excel. It’s so easy you might pass out.

My data table looks like this:TargetLine1

I have my data and I have the benchmark value listed next to each.

Highlight the group names and their data and insert a simple bar graph:TargetLine2

 

Then right-click on the graph and click Select Data. In that box that pops up, click the Add button to add a new series. In *that* dialogue box, select your Benchmark data. It’ll look like this, but don’t freak out:

TargetLine3Now it’s time to move that benchmark data from bars to a line.

In Excel 2013, I right-click on the orange benchmark bars and click Change Chart Type and then choose Line. You can do this in 2010, too, just click on the benchmark bars and then click the Change Chart Type button in your Layout tab and select a line graph. (This is a good time for me to mention that if I ever open a pub for data nerds, I’m going to call it The Benchmark Bar.)

TargetLine6

I also added the word “Benchmark” to the line by adding a data label to just the left most point. Of course, at first Excel tried to give me the value of the point, but just right-click on the point again and click Format Data Label and then select Series name and unselect Value.

So this is cool but if you’d like your benchmark line to be a bit longer, you can just fiddle with the data table a little and select blank cells to add some space to the set of bars and associate the benchmark 65% with each blank spot. Did that sentence make any sense? It was heavy on the nerd-speak. Just make it look like this table here:

TargetLine7

And now the graph is WAY more powerful! We’ve added so much interpretation to the data visualization, helping the viewer understand how far the first two groups are from the target and by how much Group C exceeded it. See a benchmark line in the wild in this dashboard report from Oregon Health Authority (scroll to, like, page 10). And think of other ways you could use this line, such as for targets, averages, national standard, yes keep dreaming!

This is *exactly* the kind of thing we were referring to in the Data Visualization Checklist when we said “Contextualized or comparison data are present.” Yer viewers want to be able to interpret the data – is it good? bad? the worst in history? best in the nation? A benchmark line gives that necessary context. BOOM!

This post is an excerpt from my latest book, Effective Data Visualization, where I even SIMPLIFIED these instructions. It has loads of advice on the best chart type to use and how to make it in Excel.


Skyrocket Dataviz with Graph Guides!

Graph Guides is a custom-built, year-long sprint through 50 Academy tutorials.

When you enroll, we’ll assess your current data viz skill set, build you a customized learning path, and hold your hand as you blaze your way to new talents.

Enrollment re-opens Spring 2022

How to Make Horizontal Dumbbell Dot Plots in Excel

In case it wasn’t clear, I freakin love dot plots. They are amazingly easy to read, beautifully simple in their display. I was making these babies for some clients a little while ago, before and after dots for about 25 variables in one graph. And they said “Uh, hey yeah Stephanie? Could you, like, draw a tiny line between the pair of dots on each line?” >.< That was my face when I imagined painfully inserting 25 lines, perfectly aligned between the dot pairs. But I love challenges like this. Could I find a way to make Excel do this for me?

Hell yes I could.

Read below for my old instructions. I vastly simplified this process, though, and posted updated instructions here. Use this extra time you now have to go back the world a better place, eh?

Ultimately, it looked like this:

A regular dot plot is made with a basic scatterplot as its backbone. To make the dots connected, like tiny dumbbells, the backbone is just a connected scatterplot. But the construction is a little bit different. My data table is as so:

dumbbelldotplotdatatableI need a set of y values to accompany each of my x values (the stuff I really care about displaying). Note that I ordered the post scores from least to greatest. Then for the pre and post y values (columns D and E) I typed in values that were .5 apart from one another. This will force each pair of dots to appear on its own line.

So insert a connected scatterplot without selecting any data and then right-click on the empty space and click Select Data. It’ll open up this window:

dumbbelldotplotdataseriesEach pair of dots will need to become its own series. So you’ll click on Add to make a new series.

dumbbelldotplotaddseries

The little window you see above will open up. For Series name, click on the name of the group (Group B). For Series X values, select Group B’s pre and post scores. For Series Y values, select Group B’s pre y values and post y values (Columns D and E). Click OK and then repeat this for each group until they are all displayed on your graph.

It’ll look funky at first.

dumbbelldotplotfunkyYou’ll have to go in and carefully change each marker to a circle. Right-click on the markers and click Format Data Series. Up in here, select the Built-in option and choose the one that looks like a sunburst (it’s really a circle) and increase the size to 20:

dumbbelldotplotmarker

Change the color of each marker to correspond to your pretest color and your posttest color. Add the labels in the center of each dot. Finally, change the line color away from the Excel defaults. Right-click on either marker and select Format Data Point. In this window:

dumbbelldotplotlineSelect Line Style from the choices on the left and adjust the width of the line (I ended up using 2 pt). Select Line Color and pick gray or black or something unassuming.

I thought we were good to go but my clients said they’d prefer if we brought better attention to the places where the scores actually decreased. While it would appear that one could simply change the line style in the window shown above such that it began or ended with an arrowhead, in actuality the arrowhead is obscured by this awesome size 20 dot. So I manually inserted a tiny triangle, which wasn’t too painful, especially since we only applied it to a small portion of the dumbbell dot plot pairs.

Add in a sweet title and some textboxes with labels and now we are talking about one heavy-lifting data visualization. Check out a recent report by OHA that uses dumbbell dot plots (I consulted on the design).

AND FOLKS! This is a great example of choosing a graph type that is appropriate to the data. Dot plots are awesome for showing comparisons between two (or sometimes more) points. This would score a 2 on the Data Visualization Checklist item “The type of graph is appropriate for data.” 

This is a very early draft of a section now in my latest book, Effective Data Visualization. It has loads of advice on the best chart type to use and how to make it in Excel. Video help is available at the Academy and in our Graph Guides program.


Skyrocket Dataviz with Graph Guides!

Graph Guides is a custom-built, year-long sprint through 50 Academy tutorials.

When you enroll, we’ll assess your current data viz skill set, build you a customized learning path, and hold your hand as you blaze your way to new talents.

Enrollment re-opens Spring 2022

Easy Bullet Charts in Excel

Here is what a normal bullet chart looks like:

BulletChartExample

There are usually areas of performance in the background (acceptable/unacceptable, in this case), a target line, and an actual bar the represents your real value. Bullet charts kick ass for showing part-to-whole relationships for single data points, especially in long lists of metrics, like dashboards.

Sure, there is a bullet chart tutorial here, but I found it pretty complicated. And one here that’s fairly straightforward but, as an Excel graph, it takes some extra steps to resize or copy the graph with the data. There’s also a plug-in that makes bullet charts for you! So why write another blog post on it? Well, I was working with these methods until two things happened (1) I upgraded to Excel 2013 and the plug-in quit working and (2) my client needing something different – and my clients’ needs come first.

My client said, “Yo, we don’t need those gray shades in the background. These metrics don’t have performance levels. You either meet the target or you don’t [Ed Note: Sound familiar??] And furthermore, we don’t need a target line. The target should be the end of the graph, so that when the actual bar hits the end of the graph, we know we have met our target.”

And you know what? That way of visualizing the bullet chart made So. Much. Sense. At least for me, in this case and several others I’ve worked with since then.

And BONUS: this method uses in-line graphing with Excel, so the graphs don’t move out of alignment or require extensive size and positioning, like the others I mentioned above. We’re using conditional formatting to make these, peeps (and it works in Excel 2010, too!).

Here’s how:

1. Copy your actual values into a column to the right of your metrics. Here we are graphing Kalamazoo’s Census facts, in comparison to Michigan’s average, which will serve as our target in this case. So I copied Kalamazoo’s column of data and pasted it again on the right. (I eventually filled these cells with a medium gray color too.) This is where the bullet charts will go.

ConditionalBullet1

2. Put your cursor in the first cell where you want the bullet chart. So here, that’ll be D3. Then click on the Conditional Formatting button in the ribbon at the top and pick New Rule. It’ll open up this dialogue box.

ConditionalBullet2By default, the highlighted drop down menu in the middle will say 2-color scale, so click the arrow and select Data Bar.

3. Adjust the settings in the dialogue box.

ConditionalBullet3Let’s walk through this bit-by-bit.

Check the box that says Show Bar Only, which will replace your copied data with just the viz.

Where it says Minimum, you’ll usually want to change the type from Automatic to Number. The Value box will populate with a 0. Generally, leave that be.

Where it says Maximum, you’ll want to point Excel to your target value, since that’s the way we are conceptualizing the bullet graph here. To do so, change the type from Automatic to Formula. In the Value box, type in the cell that contains your target value. Note that you need to have an equals sign (=) at the front and dollar signs before the letter and number ($).

Where it says Bar Appearance, you can change the fill color to match your brand. By default there will be no border but I actually gave it a gray border, which matches my cell background color, so the bar ended up looking a little skinnier.

Finally, under Bar Direction, select Left-to-Right. And click OK.

BOOM! Bullet graph! It’s embedded in the cell, so it won’t jump around on the screen like regular graphs can. It’ll copy into new spreadsheets with ease. And it’s pretty freaking easy to set up, compared to some of the other methods I’ve seen.

Optional 4. Handle negative actual values. I made up this data about Crime Rate at the bottom, but let’s pretend the rate since 2008 has decreased in Kzoo. In this situation, adjust the Minimum for the bullet graph by selecting Lowest Value.

ConditionalBullet4

Then click on this gray button at the bottom of the dialogue box that says Negative Value and Axis. It’ll bring up a new dialogue box that looks like this:

ConditionalBullet5Here is where you can change the color of the bars when they are negative (red is default but it was to bright for my branded color scheme so I chose a dark gray). Note it will make an axis line appear, too, which better visualizes that the value is negative.

From here, we can order from greatest to least (without having to reshuffle lots of graphs!), add more data and maybe other in-cell indicators like sparklines and icons, and make it ten thousand times easier for our viewers to get a sense of performance at a glance.

THE MAJOR CON, IN CASE YOU HADN’T NOTICED: when the actual value exceeds the target, we’d expect to see a blue bar that goes past the gray background but Excel has these stupid in-cell margins, so there’s always a tiny sliver of gray that shows up, such that it could look like the actual values haven’t met the target yet even when they have. You’ll see this in rows 6 and 10, as examples. That makes this visualization method a little crude but again, it might be the best solution for your client’s circumstances.

Now, this post accidentally published last week and in the 45 minutes it was available for viewing, my friend Jon Schwabish jumped on it, saying these can’t be called bullet charts. He pointed out that Stephen Few, inventor of bullet charts, said they must have shaded areas of performance in the background. Well, I say maybe the definition of a fairly new method of visualizing should be broadened. Or maybe they don’t need to be called bullet charts at all. That’s okay with me too. My point, no matter what we call it, is to visualize data so it is more useful.


I’m talking a lot about dataviz these days. See if I’m coming into your orbit in my Upcoming Events and make sure we high five in-person!

You can find a lot more step-by-step instruction on how to make awesome visuals in my Evergreen Data Visualization Academy. Video tutorials, worksheets, templates, fun, and community. Excel, Tableau, and R. Come join us.

Adding Standard Deviation to a Dataviz

Do you need to communicate your standard deviations to your audience? No, I mean, really DO YOU? Think hard about that because chances are your audience doesn’t give a crap. They want to know you calculated your standard deviations. They want to know you were meticulous in your number crunching. But most likely they do not want you to actually tell them the standard deviation. Tuck that ish in an appendix or something.

But in the rare circumstance where your audience really does want to see your standard deviations, here’s a pretty easy way to visualize them in your graph inside Excel. It represents the standard deviation via a gray band behind the mean scores, using a stacked area chart. Sounds complicated, I know, but it’s actually pretty simple and these are the step-by-step instructions. In this example, we are visualizing mean reading scores for schools across the state.

Step 1, we calculate means and standard deviations for each of the 5 years we are reporting on.

StdDev

 

Step 2, add the standard deviation to the mean to calculate the upper end of the standard deviation range. And subtract the standard deviation from each mean to get the lower end of the standard deviation range.StdDev2

Step 3, the difference between the upper range and the lower range is the full standard deviation range, so calculate that by subtracting the lower range from the upper range.StdDev3

Step 4, graph. We want to create a stacked area chart, using this guy:

StdDevType

So highlight the values in the lower range and the full range, click the stacked area graph, and get this:

StdDevStacked

 

As my notes suggest, change the default colors of the stacked areas so that the lower range is no fill or white and the actual standard deviation range is gray. That’s your gray background, yo!

Step 5, add the mean values. First, right-click on the graph, click on Select Data, and add your mean values as a new series. It’ll look like this:

StdDev6

You don’t want it to look like that, but don’t freak out. Just keep going.

Step 6, click on that new area representing your means so that it is highlighted. Then change the chart type and select a line.

StdDev8

It’ll look like this:

StdDev9

Whew! That’s more like it.

Step 8, format! Add an explanatory title and a legend-like subtitle to convey what standard deviation actually means.StdDev10And there you have it. But wield this with care, okay? Standard deviation, while important to us datanerds, can elicit deer-in-the-headlight stares from many audiences.


You can find a lot more step-by-step instruction on how to make awesome visuals in my Evergreen Data Visualization Academy. Video tutorials, worksheets, templates, fun, and community. Excel, Tableau, and R. Come join us.

How to Make a Diverging Stacked Bar Chart in Excel

Last week my friend Ann Emery posted a dataviz challenge on something I’d been wanting to figure out for a long time: how to make a diverging stacked bar chart in Excel (I’d also heard of them as sliding bar charts, but getting our dataviz terminology on the same page is another blog post). Diverging stacked bar charts are great for showing the spread of negative and positive values, such as Strongly Disagree to Strongly Agree (without a Neutral category) and because they align to each other around the midpoint, they handle some of the criticism of regular stacked bar charts, which is that it is difficult to compare the values of the categories in the middle of the stack.

Ann will post answers to her challenge, but here is how I figured it out. It was approximately 8 billion times easier than I expected.

The secret is hidden values at either end of the bars.

Here is what my data table looks like:

DivergingStackedData

Let’s walk through it a bit. In the middle, in the black text, are my actual data values, what I ultimately want to show the audience. On either side I have purple Buffer columns. THESE ARE THE SECRET COLUMNS!!! In gray you can see my notes. So. Yo. Listen up. From “Disagree” over to the right, those values need to add up to 100%. Just mentally sum your Disagree and Strongly Disagree values and put the remainder in the Buffer column. Same thing for Agree over to the left.

Then select all the headings and values, from Buffer to Buffer, and create a 100% stacked bar chart. Mine looked like this:

Diverging1Not cool. I should see 6 segments in the stacked bar, not six bars. So right-click, choose Select Data, and click on Switch Row/Column. Now it looks like this:

Diverging2Bingo! So now it’s time to make the two Buffer categories white. Just right-click directly on the Buffer bars and select the white fill color. When you’re done it should look like this:

Diverging3Look at that! Now the only bars with color are the ones that encode our values. Still, it looks like crap, right? So delete the legend (we’ll add it back in later), delete the gridlines, and delete the y axis. Delete the x axis too BUT FIRST! Adjust the axis values so the max stops at like 70%. We want more of the chart area taken up by our actual values.

I also added data labels and changed the colors so that the positive were blue-er and the negative were red-er:

Diverging4To finish it off, I just need to add back my legend (across the top, using textboxes), give it a descriptive chart title, and add in my survey questions as data labels:

DivergingFinal

POW! That’s right! It takes just a little forethought with some basic math to better represent diverging survey data and circumvent some of the issues with a basic stacked bar. Rock on, people!

Tutorials just like this are in my latest book, Effective Data Visualization


Skyrocket Dataviz with Graph Guides!

Graph Guides is a custom-built, year-long sprint through 50 Academy tutorials.

When you enroll, we’ll assess your current data viz skill set, build you a customized learning path, and hold your hand as you blaze your way to new talents.

Enrollment re-opens Spring 2022

Dashboard Icons in Excel

We don’t just report the facts, ma’am. We use a set of values to make judgments about the data, like which of these results is good or needs improvement, etc. We set benchmarks and cut points so our clients can understand when action needs to be taken and where. Yet our presentation of those values and judgments is often unclear. We tend to present just an array of numbers and assume the reader will be able to mentally dig in and figure out what’s good and what isn’t. We can and should present those numbers – we’re into transparency, after all. But the addition of a set of judgmental icons can be a helpful way to quickly communicate where focus is needed.

So let’s say I’m working with a high school Math teacher with way too many students. She keeps track of student scores on homeworks and exams and so forth in a spreadsheet but, as the school’s internal evaluator, she told me she needed something that would jump out at her if students were failing, or on the edge of doing so.

Lucky for us, Excel 2010 has built in a set of judgmental icons that we can use for this purpose:

Conditional formatting icon sets in ExcelTwo problems, though. First of all, most of the predesigned icon sets are in the red/yellow/green spectrum, which isn’t helpful to the colorblind. And its kinda ugly (views are my own). That’s why I chose the red to black set of icons. The other thing is, putting an icon on everything in a spreadsheet or dashboard is a sure way to make things overwhelming, fast. Here’s how to fix it.

You select the cells in your spreadsheet for which you’d like to have the icons applied. Then you click you fave set of icons here. Then you Manage Rules. This box pops up:

Popup box to manage rules for conditional formatting icon sets in ExcelAnd its here that I adjust the scope of icon set and assign the cut points or values where they will apply. I selected no icon for the students with passing grades. I adjusted the range of values so that students near the failing mark (less than 60 and greater than or equal to 50) get a light red circle and those who are failing (less than 50) get a bright red circle.

Applied to the Math teacher’s spreadsheet, it shakes out like this:

Dashboard in Excel with icons

Now the icons highlight the average student scores that need attention. I also added in sparklines to show each student’s progression of grades over time. It’s a lot to consider, but the sparklines can give more informative detail than just the average score. Student ID 20, for example, has a bright red mark, indicating failing. But that student’s sparkline shows a generally improving trend since the first assignment. A different remediation approach might be warranted than, say, Student 10, who has stayed flat.

So the strategy here was to add simple, limited indicators to show our evaluative cut points and spur action. As with most things in Excel, the defaults are nice but insufficient and some tweaking will be needed to maximize their benefit.


And speaking of using dataviz in the education world, have you read Sheila Robinson’s recent blog post on this topic? She writes about a cool study that we need to see more of!

I’m hitting the road soon! Check out my upcoming events to see if I’ll be near you. If so, let’s grab a coffee and talk shop. If not, bring me out, why don’t ya?

You can find a lot more step-by-step instruction on how to make awesome visuals in my Evergreen Data Visualization Academy. Video tutorials, worksheets, templates, fun, and community. Excel, Tableau, and R. Come join us.

From the blog