Can I make an Excel chart from only one column?
October 20, 2008 3:06 PM Subscribe
How can I create a chart in Excel 2007 showing the number of times a certain value occurs in a column?
This seems like it should be really simple, but I can't for the life of me figure this out.
I have an excel spreadsheet with about 150 rows of marketing data that I'd like be able to chart, but with only a single column of data per chart. Maybe this is possible with Pivot Tables? I can't figure that out.
Here's an example: Column H has the cities everyone lives in. I want a chart showing how many people live in a given city. I could obviously count this out by hand ("Ok, that's 5 for San Diego.."), but I can't help but think Excel should be able to do the heavy lifting for me. I'm fine using macros/VBA, but this seems so simple! Famous last words, I know.
Let me know if I need to clarify this.
This seems like it should be really simple, but I can't for the life of me figure this out.
I have an excel spreadsheet with about 150 rows of marketing data that I'd like be able to chart, but with only a single column of data per chart. Maybe this is possible with Pivot Tables? I can't figure that out.
Here's an example: Column H has the cities everyone lives in. I want a chart showing how many people live in a given city. I could obviously count this out by hand ("Ok, that's 5 for San Diego.."), but I can't help but think Excel should be able to do the heavy lifting for me. I'm fine using macros/VBA, but this seems so simple! Famous last words, I know.
Let me know if I need to clarify this.
Or, if you have the data sorted, you could SUBTOTAL
DATA>>SUBTOTALS>>at each change in city, use function COUNT, add SUBTOTAL to city.
posted by b33j at 3:14 PM on October 20, 2008
DATA>>SUBTOTALS>>at each change in city, use function COUNT, add SUBTOTAL to city.
posted by b33j at 3:14 PM on October 20, 2008
Best answer: You can do this in a Pivot Table. Just put your field (e.g.: City) into the Axis Field box (i.e.: make it a Row Label) and also into the Values box. Change the "Value Field Settings" for your field to "Count", if necessary; if your data was alphabetical instead of numeric, this shouldn't be necessary. Graph as desired.
posted by mhum at 3:21 PM on October 20, 2008
posted by mhum at 3:21 PM on October 20, 2008
countif() is what you're looking for.
By the by, if you're looking for the total number of non-blank cells in a range, use counta().
posted by squorch at 3:22 PM on October 20, 2008
By the by, if you're looking for the total number of non-blank cells in a range, use counta().
posted by squorch at 3:22 PM on October 20, 2008
Response by poster: That's exactly what I was looking for, mhum. I had my Pivot Table, but I couldn't figure out where things went. Thanks.
Countif() would probably work too, except it looks like I'd have to go in a figure out what cities are in their. The Pivot Table sees for itself that San Diego is a possible value, which is a whole lot easier.
Thanks everyone!
posted by niles at 3:27 PM on October 20, 2008
Countif() would probably work too, except it looks like I'd have to go in a figure out what cities are in their. The Pivot Table sees for itself that San Diego is a possible value, which is a whole lot easier.
Thanks everyone!
posted by niles at 3:27 PM on October 20, 2008
It sounds like you're trying to make a histogram (please forgive me if I'm wrong), so check this page out: http://peltiertech.com/Excel/Charts/Histograms.html
posted by yellowbkpk at 5:45 PM on October 20, 2008
posted by yellowbkpk at 5:45 PM on October 20, 2008
« Older What is the name of this novel involving WB Yeats?... | My cat's a nightmare (washing, pooping, moving... Newer »
This thread is closed to new comments.
posted by b33j at 3:12 PM on October 20, 2008