How to chart the count of items?

Question

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 the number of 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?

Answer 1

This is an excellent time to use a pivot table.

Select columns B and C, then click on Data → Pivot table...

In the Pivot table editor sidebar 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 select the pivot table data on the sheet and click on Insert → Chart. Select Column chart for the Chart type.

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:

Answer 2

This is possible using the following steps:

1. Select column C
2. Open the Chart Editor from the menu Insert > Chart
3. The chart should be correctly setup, if not, the settings are:
   • Stacking: None
   • Data range: C1:C1000
     customize to your needs
   • X-axis: Category
   • Aggregate: checked
   • Use row 1 as headers: checked

You can reverse the order of the X-axis using Customise > Horizontal Axis > Reverse axis order

Answer 3

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.