Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a spreadsheet that is filled by a form.
It contains values like the following:

Timestamp           Person  Category
2012-02-13 10.31.22 a       x
2012-02-13 11.06.37 b       y
2012-02-13 11.34.32 c       x
2012-02-14 09.22.35 d       z
2012-02-14 09.24.01 e       w
2012-02-14 11.06.20 f       x
2012-02-14 22.39.33 g       y

I would like to make a bar chart that shows a bar for each category with the value of the number rows per category.

Should I calculate new columns in a new table before creating the chart, or could it be done in one step? And how? :)

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

This is an excellent time to use a pivot table.

Select columns B and C, then click on DataPivot table report...

In the Report Editor column on the right side:

  • In the Columns section, add the Category field. Uncheck Show totals if you do not want the Grand Total column.
  • In the Values section, add the Category field and summarize by COUNTA.

Now click on InsertChart..., select the Column chart style and click the Insert button.

New form submissions will update the pivot table and the updated pivot table will update the chart. This is what the pivot table and chart look like using the example data you provided in your question:

Pivot report and chart

share|improve this answer
add comment

I would put the count column in a new sheet that way if you start sorting the rows of data you won't mess up the columns with the data for the chart.

I am assuming that you are using countif to make the data for the bar chart.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.