Can I make an Excel chart from only one column?

October 20, 2008 3:06 PM

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.

posted by niles to computers & internet (7 answers total)

=COUNTIF(A1:A10,"SAN DIEGO")
posted by b33j at 3:12 PM on October 20, 2008

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

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

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

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

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

Ack, sorry: Link
posted by yellowbkpk at 5:45 PM on October 20, 2008