Join 3,514 readers in helping fund MetaFilter (Hide)


Creating simple bar graph in Excel 2007
March 28, 2014 9:05 AM   Subscribe

Hey guys, what I'm trying to do is very simple I just never really use Excel. I have two unrelated fields, categories, or columns, that I'd like to depict in a bar graph showing the amount of records that have been completed out of the total. The data are values, in my case "Yes", not numbers. I used the countif() formula to count every cell that has "yes". But I'm not really sure what I have to do next. Do I need to create another row showing what percentage this number is from the total amount of records, 136/633? And then create a bar graph from just that cell? When I just highlight all the cells for both columns and click bar graph it doesn't even fully show me a graph so I'm obviously not doing it right. Ideally, I'd like the y-axis to just show 0-100% and the total number of records(633) with marks maybe at 25% intervals. The x-axis to have my two column names and then the bars showing the percentage of records that have "yes" for these columns out of the total with a count included also. If anyone could help me out that would be great. Thanks a ton in advance.
posted by mr.coffee to Computers & Internet (6 answers total)
 
So assuming something like this:
  |    A     |      B       |        C          |      D
------------------------------------------------------------
1 | Question | Percent Yes  |   Total Yeses     | Total responses
------------------------------------------------------------
2 | Q1 title | =(C2/D2)*100 | X                 | Y 
------------------------------------------------------------
3 | Q2 title | 37.5         | 75                | 200
------------------------------------------------------------
In Excel 2010 I then highlight columns A and B, and go to the Insert tab, then click Column and 2D Column. This inserts a bar chart with the Percent Yes values for each question.
Then double click on the Y axis to open Axis Options.
Axis Options >
set Maximum to Fixed, 100
set Major Unit to Fixed, 25
Click Close.
posted by EndsOfInvention at 9:37 AM on March 28


Thanks for your response EndsOfInvention. My table is currently set up with all of our records and then just a row at the bottom with the subtotal that I created with the countif function. So I should create a new table or spreadsheet like the one you've created above in order to create the graph? So if I wanted the percent yes, total yeses, and total responses to be illustrated in the graph would I highlight columns A, B, C and D? I guess I could always go back and just edit the labels on the axes to include the counts, assuming it allows me.
posted by mr.coffee at 10:08 AM on March 28


EndsOfInvention, alright I created the graph based on a table like the one above by just highlighting Columns A and B like you suggested and it looks great. What is the easiest way for me to get the count and total to appear on the graph? Thanks again!
posted by mr.coffee at 10:57 AM on March 28


Those are called Data Labels and you can get them by right clicking on part of the bar and selecting Add Data Label in 2010. Follow the link in my first sentence if that does not work in 2007.
posted by soelo at 11:29 AM on March 28


Ok, great I got it to work thanks! My boss is now asking for a stacked column chart. He wants to see how our progress compares with the total. Is there a way to do this simply with the table you've described above? I've tried selecting all of them again, and just the "Total Yeses" and "Total responses" column with no luck. All of the examples I'm finding on the internet are a bit different than what I'm after. Thanks again!
posted by mr.coffee at 7:30 AM on March 31


Sounds like you'll need to add another column with a calculation in it.

You want one column with the yes votes, and another with the no votes. If you graph yes and total you'd be counting yes twice?

To get the no percentage just enter 1-B2 in E2

Then select columns A, B and E (hold control to select separate ranges). Then go Insert > Stacked Column. Then change the axis as before.

If you need more help feel free to send me a mail on here.
posted by chrispy108 at 5:10 AM on April 2


« Older Where in the world should we t...   |  I have never in my life bought... Newer »

You are not logged in, either login or create an account to post comments