# How to sort a column by occurrence count in Excel?

Let's say I have a column like this:

``````Dog
Bird
Cat
Dog
Dog
Dog
Bird
Cat
Bird
``````

It has 4 Dog, 3 Bird and 2 Cat

I want to sort this column descending by these count numbers.

I mean result should be this when I sort it:

``````Dog
Dog
Dog
Dog
Bird
Bird
Bird
Cat
Cat
``````

How can I do that in Excel 2007?

• Why not just use a pivot table? – Raystafarian Jul 27 '13 at 15:33
• I don't know how to do it. – Someone Jul 27 '13 at 15:34
• Try it out, go to insert - pivot table. Then select your range. Drag your header down to the `sum values` and change it to `count` and then drag the header to `row labels` it will make the table, then change the sorting options. It may not be exactly what you want – Raystafarian Jul 27 '13 at 15:36

1. In the column next to the group insert the following formula

=COUNTIF(A\$1:A\$9, A1)

1. Highlight the cells you want to sort.
2. Click on the Data tab on top of the ribbon
3. In the Sort section, select Sort. In the dialog box select the second column and then from highest to lowest. • Edited the question to make it more clear, it's not about alphabetically. – Raystafarian Jul 27 '13 at 15:32

For me (using Excel 2016 on Win8) this answer doesn't work. It resorts the FORMULAS which now don't refer to the field in the same row...

The way to avoid this is to use `=COUNTIF(Sheet2!\$A\$1:\$A\$100,INDEX(A:A,ROW()))`

Solution explained in Excel Forum - Sorting data containing COUNTIF formulas

For @wbeard52's solution - Excel 2016 on Windows 10, instead of `,` it should be a `;` as delimiter. Like this:

``````=COUNTIF(A\$1:A\$9;A1)
``````

Tip: If you have many rows, write the two first rows like this:

``````=COUNTIF(A\$1:A\$10000;A1)
=COUNTIF(A\$1:A\$10000;A2)
``````

Then copy these two cells, mark cell 3 to 10000 in this case and then paste the code. I did this with 150k + rows and it worked like a charm. However I had to restart Excel after setting the formula for the correct values to show up.