In my sheet, there are 5 columns of data. Some rows are fully filled and some rows have some empty cells. Now I want to sort my sheet from most-filled row to least-filled row. How can I do it?

  • can you share a copy of your sheet?
    – user0
    Sep 1, 2018 at 17:29
  • did I? was my solution somewhat not working for you?
    – user0
    Sep 2, 2018 at 18:44

2 Answers 2


Simply use =COUNTA(range) in a new column, and then use that column to sort.

  X      X      X      X      X      =COUNTA(A1:E1)
  X      X             X      X      =COUNTA(A2:E2)
  X                                  =COUNTA(A3:E3)
  X             X             X      =COUNTA(A4:E4)
  X             X                    =COUNTA(A5:E5)
  X                           X      =COUNTA(A6:E6)

Results in

  X      X      X      X      X      5
  X      X             X      X      4
  X                                  1
  X             X             X      3
  X             X                    2
  X                           X      2

Then use column F to sort your data.


you will need +1 column to generate sorting sequence used in =QUERY which will return your data

assuming your dataset somehow looks like:

paste this formula in cell F1:


and then paste this formula wherever you need to:

=QUERY(A1:F; "select A, B, C, D, E where F is not null order by F desc"; 0)

if overall visual requires so, you can easily hide the whole F column:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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