How to fake a two directional bar chart in Excel


Excel has lots of charting options. Line charts, bar charts, scatter plots, pie charts and oh, so much more. But it doesn’t have this chart.

This is a nice chart for showing two values for many different variables. It’s easy to read with the labels listed vertically and it fills a page nicely without being overwhelming.

Here are the tricks of the trade. You can see everything that I’ve done in the image here so it’s easy to follow along.
Two sided bar chart in excel

First, put together the three columns of data – the labels, the first score, the second score. Second, copy out the equation that you see in the Excel menu bar

=REPT(“g”,D5*2)

REPT: Excel will repeat a value a certain number of times

“g”: The value Excel is going to repeat. Notice that the font I’m using in that particular cell is webdings. You might have to use a different font to generate the same result but you’re looking for any character in any font that is a really wide fat box.

D5*2: This is how many times Excel is going to repeat the value. In the case of the highlighted cell, Excel is going to duplicate the g value 4.4 times 2. If you find your bar is too long, instead of multiplying by 2, trying dividing by 2.

Now, align right the first column and align left the second column. To remind your reader of what’s going on, colour in green the column that reflects the good thing and colour in red the column that reflects the bad thing. Well, now that I look at it, perhaps the yum column ought to be in green!

At this point, you can either copy paste the cells directly into powerpoint or word, or create a screencapped image and paste it anywhere. Enjoy!

About these ads

6 responses

  1. I could not find a “fat box font that worked with out gaps between the characters

    Also this chart is misleading look under Tasty. 4.3 and 4.4 have the same length, but 4.4 and 4.5 have a different length. I think this would be fine if the number are integers but not good for decimals

    Also the directions leave out that you need to align the text right or left to get it to work.

    1. Ah yes, I should have mentioned the alignment. I’ll add that.

  2. What version of Excel and PowerPoint do you use? Are you happy with them? My 2007 versions hold an error when charting, but there’s no way to fix it. I need to upgrade to 2010, but am hoping not to have the same issues. Have you had this or any other issues? Love the Excel tips!

    1. Glad you like them! I have the newest versions now and haven’t yet noticed any major problems. I find the ribbon wastes a lot of space but you can turn it off.

  3. Thank you, thank you, THANK YOU for this! This will give me a great way to graph some of my research results for an article I’m working on!

    1. You’re very welcome :D

Enter your email address to subscribe

Join 9,579 other followers

LoveStats on Twitter

All Top

Featured in Alltop
Follow

Get every new post delivered to your Inbox.

Join 9,579 other followers

%d bloggers like this: