Show 1st, 2nd and 3rd Places With a Spreadsheet

This article will explain how to use the SORTN function in Google Sheets to find the top three places with ties.

Sorting out 1st, 2nd, and 3rd place in a spreadsheet can be tricky. While you could sort your data by the column with the values (times, weight, points, etc.), sorting won’t tell you who won first, second, and third place if there are people with the same time/score/etc. In this tutorial, we will concisely list the winners using just one function – SORTN.

We will use race times as the input, so the smallest values rank higher. The table below is our list of times from a recent race.

Run Time	First Name	Last Name
4.77	Ewart	Adelyn
2.97	Boyce	Ayala
2.48	Andre	Duran
2.97	Emmett	Grant
4.50	Vito	Horn
3.54	Carla	Marquez
5.18	Hoyt	Melton
2.48	Arline	Myers
2.97	Winifred	Ramos
3.88	Adam	Steinfurth

Get a copy of the race times spreadsheet with the solutions to follow along.

Top Three Times Ignoring Ties

Let’s start with showing the top three times using the SORTN function. SORTN has a built-in display_ties_mode, which we will change in the subsequent examples to give us different results.

In this first example, we will point the function to the range of A2:C11, which is the unsorted list of race results. We’ll ask for the first three results by specifying 3 for the n parameter. n is the number of results for the function to return.

Formula used in cell E4 =SORTN(A2:C11,3)

The function returns the top three times. But what about Grant Emmett (row 5) and Ramos Winifred (row 10)? They also placed third as their times are tied with Ayala‘s 2.97. Fortunately, the SORTN function handles this situation with the display_ties_mode argument. Let’s look at that next.

The Three Fastest Race Times – Including Ties

Now, let’s find the top racers that got the fastest three times, regardless of how many ties there are. To do this, we will change the display_ties_mode to 3 which shows the top three unique times, including ties.

The report shows the first, second, and third place runners but also the runner with the third fastest time, which won’t place third because of the ties. This list of six runners may be the report you want. However, you are most likely looking for the next option. Let’s change the output to show only the runners that place in the top three spots.

First, Second, and Third Place Including Ties

This last example will show only the runners that place in the top three. It could show more than three runners, depending on the amount of ties. To do this, we will use a display_ties parameter of 1, displaying all duplicates of the third row if there are any.

The report is most likely what you are looking for. You can award first place to Andre Duran and Arline Myers, and second place to Boyce Ayala, Emmett Grant, and Winifred Ramos. There is no third place since at least three runners placed.

Video Tutorial

See a YouTube tutorial on this subject.

Related Pages