How to return the top or bottom n records without a filter or PivotTable in Excel

There are many methods to return the highest or backside n data from a knowledge set in Microsoft Excel, however the brand new dynamic array capabilities make doing so simpler than ever.

Spreadsheet

Picture: Imam Fathoni, Getty Pictures/iStockPhoto

Returning the highest or backside n data of a knowledge set in Excel is not tough, and there are a lot of methods to take action. You possibly can filter, use expressions, or perhaps a PivotTable; your route would possibly rely on the way you’re utilizing the outcomes. However now, because of the brand new dynamic array capabilities, you should use one expression to return as many columns and rows of the supply information as you want. On this article, I will speak a bit about these capabilities. Then, we’ll apply them to return the highest and backside n data.

SEE: 83 Excel suggestions each consumer ought to grasp (TechRepublic)

I am utilizing Microsoft 365 (desktop) on a Home windows 10 64-bit system. Each dynamic array capabilities on this resolution can be found in Microsoft 365 and Excel 2021, Excel for the net, Excel for iPad and iPhone, Excel for Android tablets and telephones. On your comfort, you may obtain the demonstration .xlsx file. This text assumes that you’ve fundamental Excel expertise, however even a newbie ought to be capable to comply with the directions to success.

Catching up

Prior to now, returning the highest or backside n required a bit of labor and a few specialised information. You could possibly use a sophisticated filter, an expression, or perhaps a PivotTable. The fantastic thing about the brand new dynamic array capabilities is that you do not alter the supply information; these capabilities create a brand new information set. 

In the event you’re not accustomed to the older methods to return the highest or backside n data, you would possibly learn a number of the following articles are on this topic:

They don’t seem to be essentially outdated, particularly if you wish to work with the supply information in place. If you wish to work with a brand new information set, the brand new capabilities are the answer. 

Determine A reveals a easy information set as a Desk. We wish to return the highest and backside n values within the Worth column. A mixture of the capabilities reviewed within the subsequent part will return n data. The sheet’s title is Information; you will want that in a bit.

Determine A

exceltopn-a.jpg

  We’ll use the capabilities within the subsequent part to return the highest and backside n data.

In regards to the capabilities in Excel

We’ll use three capabilities: SORT(), SEQUENCE() and INDEX(). The primary two are dynamic array capabilities and pretty new to Microsoft 365. INDEX() has been round for a very long time, and also you would possibly already be accustomed to it. However first, simply what’s a dynamic array operate?

In the event you’ve ever entered an expression utilizing Ctrl + Shift + Enter, you then’re already accustomed to how Excel used to work with dynamic arrays. Because of the brand new dynamic array characteristic, these kind of expressions may be a lot simpler to create and preserve. The outcomes spill into the cells beneath, filling as many as essential to finish the expression’s calculations. That is referred to as the spill vary. In the event you see a spill error, then the vary wanted to satisfy the operate is not obtainable.

Now, onto the capabilities.

SORT() returns a sorted array utilizing the next syntax:

SORT(array,[sort_index],[sort_order],[by_col])

the place array is the one required argument and identifies the vary to kind. The elective arguments comply with:

  • sort_index: A numeric offset worth that identifies the row or column to kind by
  • sort_order: The #1 for ascending kind or -1 for descending, with 1 being the default
  • by_col: The logical values TRUE for a row kind and FALSE for a column kind, with TRUE being the default

In an effort to return the highest or backside n data, the information set should be sorted, and we’ll use the SORT() operate as an alternative of a handbook route.

SEQUENCE() returns a sequence of values utilizing the next syntax:

=SEQUENCE(rows,[columns],[start],[step])

the place rows is required and specifies the variety of rows to fill. The elective arguments comply with:

  • column: the variety of columns to return
  • begin: the primary quantity within the sequence
  • step: the quantity to increment by

In its easiest kind, you possibly can use this operate to return a sequence of fastened values, however it really shines while you wish to return the entire columns within the supply information set. By combining the 2, you may return a full sorted information set.

The final operate, INDEX(), returns a worth or the reference to a worth from a Desk or vary utilizing the syntax:

INDEX(array, row_num, [column_num])

the place array is required and references a variety or an array fixed. If array accommodates just one row or column, the corresponding row_num or column_num argument is elective. If array has multiple row and multiple column, and solely row_num or column_num is used, INDEX() returns an array of all the row or column in array. The final two arguments could or will not be required:

  • row_num is required except column_num is current. It selects the row in array from which to return a worth. If row_num is omitted, column_num is required.
  • column_num is elective and selects the column in array from which to return a worth. If column_num is omitted, row_num is required.

Now, let’s begin utilizing these capabilities, beginning with SORT()

How one can use SORT() in Excel

To return the highest or backside n values of any information set, you want a sorted information set. Because of SORT() a handbook kind is not essential. To see how SORT() works, let’s use it to return a sorted information set of the instance information set (Determine A). First, you must copy the column headers to a different space of the sheet, and even one other sheet. I’ll use one other sheet, so you may see how simply that is achieved. Copy the header cells B2:F2 right into a second sheet. Then enter into B3 (of the second sheet) the next operate:

=SORT(Information!B3:F13,2)

Determine B reveals the supply information sorted by the Worth discipline. This is how the arguments within the SORT() operate work:

  • Information! is the sheet title the place the supply information is.
  • B3:F13 is the unique information set.
  • 2 is the index worth, which specifies the Worth column—the second column in B3:F13. By altering the index worth from 2 to 1, 3, 4, or 5, you may show one or the entire columns. 

Determine B

exceltopn-b.jpg

  SORT() returned a sorted information set on one other sheet. 

Wasn’t that straightforward? 

In the event you’re deciding on these references, Excel will show construction referencing as a result of the supply information is a Desk. You may wish to format the outcomes as a result of the SORT() operate does not. That is a little bit of a nuisance when attempting to make use of this in a dashboard setting. You may wish to add a VBA process that applies the formatting. That is the one shortcoming I’ve run into. 

That is a easy kind, however to attain a prime or backside information set, you will want so as to add SEQUENCE() and INDEX(). 

The answer

The SORT() operate can simply return the complete information set in ascending or descending order. We wish to additionally restrict the variety of data returned, so we’ll add SEQUENCE() and INDEX() within the kind

=INDEX(SORT(array,sort_index,sort_order),SEQUENCE(no_records),SEQUENCE(first_column,last_column))

The arguments for the primary SEQUENCE() operate can be enter values, so the consumer can change the variety of data returned. Meaning we want enter cells. Use Determine C as a information to arrange the highest and backside ranges and the enter cell vary. As a result of I inserted rows on the prime to accommodate the enter cells, the vary references you have been seeing can be completely different, so do not let that confuse you.

Determine C

exceltopn-c.jpg

  You want a variety for prime and backside data and enter cells.

Let’s begin with the highest information set, which requires a descending kind, expressed by -1 within the SORT() operate’s sort_order argument. In H6 enter the operate

=INDEX(SORT(B6:F16,2,-1),SEQUENCE(I2),SEQUENCE(1,5))

It would return a calculation error as a result of there is no worth in I2—the enter cell. Enter 3 in I2 to return the information set proven in Determine D. You already understand how the SORT() operate works; on this case, it serves because the INDEX() operate’s array argument. SEQUENCE(I2) is the row_number argument and returns 3 as a result of the enter worth in I2 is 3. Consequently, the returns a descending information set with three rows. SEQUENCE(1,5) specifies columns 1 by means of 5 within the supply information set. All put collectively SORT() returns a completely sorted information set, however the two SEQUENCE() capabilities restrict it to 3 rows and consists of all 5 columns.

Determine D

exceltopn-d.jpg

  The combo operate returns the variety of data laid out in I2.

The underside operate works equally, however it omits the sort_order argument as a result of ascending (1), is the default. As well as, it references I3 because the enter cell. You could possibly use just one enter cell and have them each reference it, however this manner each information units could be a completely different variety of rows. 

In N6 enter the operate

=INDEX(SORT(B6:F16,2),SEQUENCE(I3),SEQUENCE(1,5))

The nuts and bolts are basically the identical. The default sort_order argument, not entered, returns an ascending order, so that you get the bottom values on the prime of the type. By referencing I3, you establish what number of rows to return. Determine E reveals the enter worth of 4. Consequently, the combo operate returns all 5 columns for the primary 4 rows—the underside 4 values in Worth.

Determine E

exceltopn-e.jpg

This operate returns the underside n data. 

At first, all the things appears somewhat extra advanced that you just would possibly like, however when you grow to be accustomed to the brand new dynamic array capabilities, the better your options can be for you. 

Value mentioning

Earlier I discussed that these capabilities would possibly return an error if the spill vary is not obtainable. If this occurs, choose the complete spill vary and take away all the things—information, codecs, all the things and see if that does not assist. Of particular word is merged cells. For some purpose eradicating them does not fully clear them. You’ll have to maneuver all the works to a brand new sheet. 

Keep tuned

The one limitation I’ve observed is the capabilities’ failure to take care of formatting. In a future article, I will share a VBA process that applies the suitable formatting for you while you change an enter cell. 

Additionally see

Recent Articles

spot_img

Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox