Making Back-to-Back Graphs in Excel

Making Back-to-Back Graphs in Excel

Let’s say we’re interested in comparing how two groups – oh, teachers and principals – responded to a survey. One way to visually display that comparison would be a bar graph, where each question had two bars, one for teachers and one for principals. It’s helpful in some ways, but such a display can make it difficult to also see how JUST teachers or JUST principals responded. And thus was borne the need for a back-to-back graph. It looks like this:

back-to-back bar graph in Excel

I made this in Excel. It is not a default chart option. It takes little bit of Excel ninja skills. Here’s how:

1. Make two separate, regular ol’ bar graphs – one for teachers and one for principals.

2. Size them the same and align them.

3. Here’s a pic of the Principal graph:

principals graph just needs the y-axis deleted

This one stays pretty much the same as the default. Just delete the question stems (the y-axis). You click on it to highlight it and then you hit delete.

4. The teacher graph is just slightly trickier:

teacher graph - reverse values on x axis

Here, to make the bars face the opposite direction, right click on the x-axis, select Format Axis, and then check the box that says Values in reverse order. Delete the question stems (y-axis) on this graph, too.

5. Now you’ll need to place your question stems back into the graphs:

insert textboxes over your Excel graphs

This just takes inserting textboxes into your Excel spreadsheet and typing in your labels (I used the rare centered justification here). Then you’ll reposition each textbox so it lines up with the corresponding bars.

6. In this particular graph, I wanted to show questions on which responses were more than 5 percentage points different, so I made them a darker color. I did this by double clicking on the bar I wanted to change and then selecting a new fill color.

So while it seems these are a bunch of different pieces, if you select all of the parts, copy, and then paste as a picture, it will operate as one unit, one fine looking back-to-back graph.

We’ll walk through this example and much more in my upcoming data visualization and reporting online workshops
8 thoughts on “Making Back-to-Back Graphs in Excel
  1. Jon Peltier says:

    Unfortunately, if you want to compare the two sets of data, pointing the bars in opposite directions makes it rather difficult.

    • Stephanie Evergreen says:

      It sure can! Maybe adding data labels would have helped. I know you’re probably thinking the data sets should be next to each other in a single bar graph, as that’s often the most straightforward method of display. But sometimes I actually find it harder to see the overall shape of one dataset when the other’s bars are right next to it. That’s what I was trying to solve here. I guess it depends on what you are trying to accomplish with the display of the data and the context around it.

  2. SAMSON SHAD says:

    Thank.a very critical excellent work.

  3. Balaji Gopalan says:

    Hey Stephanie, this is great. Here’s a suggestion. Instead of using text boxes, make 2 graphs with the same axes labels. Once you reverse the order for the second graph, the axes labels also reverse. Select any one of axes labels and turn them to white (or just delete them if don’t want the axes ticks). Make sure the graph sizes are the same, line them up and bob’s your uncle!!!

    • Balaji Gopalan says:

      To Jon’s point earlier, data labels surely help. I used stacked bar chart and I add data labels because I remove the horizontal axes & gridlines. Makes the chart look cleaner.

    • Stephanie Evergreen says:

      That would work but the labels would be justified to the right or left and would be unintentionally associated with one respondent group. The purpose here is to be politically neutral.

  4. John cullerton says:

    On the basis that exact cause comparison is not what is needed, I think the bars going in different directions is actually clearer than a multi column chart. Nice one.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

@maartenzam @camoesjo @arnicas send them all to the Netherlands. It's where all the #dataviz superheroes go.

RT @RakeshMohanEval: Thrilled to see the work of my @Idaho_OPE referenced in this practical book by @evergreendata. #dataviz #eval #idleg h…

RT @PatrickWhipple: When using graphs, use grey plus an action color to emphasize what is important. @evergreendata #GVEPSIT

RT @VizWizBI: #FixitFriday: Early Leavers from Education | My @FT remake in the style of @evergreendata https://t.c…

RT @JessicaCramer26: Learning how to present data effectively with @evergreendata. So many new tricks!

@visualisingdata congrats! Can't wait to see it.

RT @DrLisaMelchior: Terrific post from @evergreendata How Middle School Math Pays Off #evaluation

RT @VizWizBI: Read this. EVERYONE! It's speaking to you. I promise.

RT @ecgrim: #datanerd Christmas came early this year! Another brilliant read on #dataviz by @evergreendata. #eval