# How to sort unique cells in order of appearance on google sheets?

So, I know there is a easy way to sort unique values in alphabetic order using

`SORT(UNIQUE(),1,TRUE)`

but i'm trying to order based on how many times a item has appeared.

exemple, if had a column like

1. Apple
2. Apple
3. Banana
4. Banana
5. Banana
6. Lemon

I would like a return like:

1. Banana
2. Apple
3. Lemon

With your list in A1:A6 you can try:

``````=unique(sort(A1:A6,countif(A1:A6,A1:A6),0))
``````

N.B I've just realised you've tagged the question with Excel as well as Google Sheets - this formula only works in Google Sheets as the Excel implementation of SORT is different.

• This works great thanks! (i tagged excel because a answer in excel could solve my current problem as well) Jan 24 at 23:13
• Consider marking the question as answered 😉 Jan 25 at 5:29

One way is to use the query() function:

`=query(A1:A, "select A, count(A) group by A order by count(A) desc", 0)`

This has the benefit that it also shows the counts per value.

If the column has a header row, you can show that header on top of the results by replacing the `0` with a `1`.

In the event the column includes blank values, you can leave out that count like this:

`=query(A1:A, "select A, count(A)` `where A is not null` `group by A order by count(A) desc", 0)`