The new functions TAKE and DROP allow you to pick from the top or the bottom rows (and columns) of a group of cells.
If you have a sorted list and only want to see the top of your list, this is easy with TAKE.
In the picture below we have a list of players, sorted by age, and then the top 3 listed to the side.
Using the TAKE function we have selected just the top three. I have included the headings as part of the values that are returned, but you don’t need to do it that way. Like all formulas, it is dynamic, so as your data in table changes, the results of the formula change automatically.
The TAKE function requires two inputs. The group of cells you are referencing and how many rows you want returned. Here I have chosen to reference the whole table, including the headers, and return 4 rows, so the headers, plus three players.
You can easily change to get the last items on the list by changing the 4 to a negative 4.
Of course, now the headers don’t come as part of the values returned and we get the last 4 rows from the table.
The TAKE function also has an optional input. Note the square brackets on the columns input.
=TAKE(array, rows,[columns])
This means we can keep the columns that we want. So if we didn’t want to show the ages of the players in the top three, we can just keep the first three columns.
Or, if we didn’t want the ID column, we can use the negative value in the column indicator to only take the last three columns.
DROP and TAKE
DROP has the same inputs as TAKE, and works in the opposite way. So instead of taking the top values, it will drop the top values and return all the rest. The negatives work the same, so will drop the bottom values. And the column input also works the opposite way to TAKE.
