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
10 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.

  5. Samantha says:

    This is perfect, thank you!! I’m not doing an exact back to back comparison, but learning how to reverse the direction of the bars is useful for a poster I’m designing where I want to position to similar-yet-different graphs next to each other to be more aesthetically pleasing. (PS, I was already designing my graph to be similar to the one on pg. 9 of your book, Presenting Data Effectively, and was happy to see your site come up in the results when Googling for this particular issue–knew it would be helpful!)

  6. Denise Halliday says:

    Excellent work, thanks Stephanie

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>

RT @smfrogers: I just published “Tilegrams: Make your own cartogram hexmaps with our new tool.”

@eagereyes @visualisingdata OMFG another copy literally just arrived in the mail

@visualisingdata @eagereyes I also have 2. Put water on them and they'll keep growing?

RT @StephanieJNeal: Another great post by @evergreendata! Your Data Visualization Secret Weapon #dataviz https://t…

RT @polly: lord grant me the confidence of a middle aged white man who aggregated someone's reporting, added color and then wrote an explai…

RT @infowetrust: Every time I see a network graph I think of this guy:

RT @TheAhmedSherif: What's your point? The Most Important Question in Data Visualization via @evergreendata #dataviz

@maartenzam @visualisingdata I'll be there in May.

Yes it's Kristen ITC, my nemesis!

@esjewett OMG you're right, I think it's worse.