So, my data sheet looks similar to this:

       Team #  Rank1   Rank2
  1 |  Team 1    1    1,234.567
  2 |  Team 2    2    1,234.567
  3 |  Team 3    2    1,000.000
  4 |  Team 4    3    4,000.000
  5 |  Team 5    0    9,000.000

Is there a way to rank, and subsequently, sort this, where rank 1 is more important than rank 2, so it would end up with something looking like this?

      Team #  Rank1    Rank2     FRank
  1 | Team 4    3    4,000.000     1
  2 | Team 2    2    1,234.567     2
  3 | Team 3    2    1,000.000     3
  4 | Team 1    1    1,234.567     4
  5 | Team 5    0    9,000.000     5

If you want to create a new range, which is a sorted view of the input range, you would use =SORT. The =SORT function can take multiple columns as parameters:

=SORT(area, columnA, ascendingA, columnB, ascendingB)

In your case, you want to sort by column B in descending order, then by column C in ascending order. Given you have your data in column A through C, this should do what you want:

=SORT(A2:C; 2; FALSE; 3; TRUE)

If you want to output the rank of a team in the sorted result set, the easiest way is to use the row number, =ROW.

So =ROW(H2) would yield 2. Knowing that, we could say that the 1-based rank of a row is =ROW(any row in the result) - 1. If we have the sorted result table in H2:J, we can output the rank of each row:


I set up an example spreadsheet to demonstrate, feel free to take a look and/or copy it.
And check the documentation for =SORT and =ROW.

If you want to sort the input range in-place, you can't use a formula - because a formula needs an input range and an output range, which must be different.

For a one-time sort, select the input data (including the header row) and click Tools → Sort range. The dialog that appears has a checkbox for Data has header row - click it.

Specify a sort column (Rank 1), and descending order (Z→A). Then click Add another sort column, and select Rank 2, and ascending (A→Z).

This will sort the range - but only once, so if you alter the data, you will need to repeat the sort procedure.

The third option is to create a Filter view. Select the data, and click _Data → Filter views → Create new Filter view.

Screenshot of the Filter view Give the filter a name. Then, for each column you want to use in your sort, click the down arrow icon (2). Specify the sort order for each column. Close the filter view by clicking the X in the upper right corner.

This will bring you back to your input data (unsorted). To go back to the sorted view, click _Data → Filter views → [your filter view].

Also in this case, you need to repeat the sort procedure when data changes.

  • Looks fine thanks! I didn't know the sort thing could have multiple parameters. – JarheadHME Feb 16 '17 at 12:11
  • @pnuts Hmm, how so? What does the example spreadsheet look like for you? – Vidar S. Ramdal Feb 16 '17 at 16:40
  • So I finally got around to being able to try this, and it looks like it's working perfectly, aside from that it's duplicating the entire thing instead of resorting the thing itself? Is that possible to do? – JarheadHME Feb 17 '17 at 2:25
  • See updated answer. – Vidar S. Ramdal Feb 17 '17 at 7:58

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.