I am trying to display the 5 most commonly appearing text values in a column in Google Sheets.

The following will give me the most common value in column A:


And this will give me the second most common value in column A, assuming that the formula above is in cell G2 (whose result is being excluded):


How can I get the third, fourth, fifth, etc most common values? Something like this does not work:


Basically, I need to exclude multiple values from that calculation. Any ideas?

1 Answer 1


 "select B,count(B) 
  where B is not null 
  group by B 
  order by count(B) desc 
  label count(B)''", 0), 
 "select Col1
  limit 5", 0)


  • Is there a simple way to extend this to multiple most common values in a block such as A:C?
    – wilsnunn
    Feb 5, 2020 at 10:57
  • @wilsnunn sure: =QUERY(QUERY(FLATTEN(A:C), "select Col1,count(Col1) where Col1 is not null group by Col1 order by count(Col1) desc label count(Col1)''", 0), "select Col1 limit 5", 0)
    – user0
    Jan 14, 2021 at 23:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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