Excel – How to condense or summarize a list in Excel?

microsoft excel

I have a excel file with entries that appear multiple times. Example:

  • A
  • A
  • A
  • B
  • B
  • C
  • C

How can I change this to a form that looks like this:

  • A 3
  • B 2
  • C 2

Best Answer

You can do this easily with a pivot table.

First, select your data. Then, from the Insert ribbon, choose Pivot Table. In the dialog that pops up, you will need to tell Excel where to place your pivot table. By default, it is on a new sheet.

enter image description here

After clicking OK, you will see something like this:

enter image description here

In the PivotTable Field List, click and drag the field name you want to summarize ('Category' in my example) to the Row Labels box. Then, click and drag the same field name into the Values box. When you've done this you should see this:

enter image description here

As you can see, this gives you the table you requested.

More information about pivot tables in Excel

Related Question