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

2 years ago 218

There are tons of ways to instrumentality the apical oregon bottommost n records from a information acceptable successful Microsoft Excel, but the caller dynamic array functions marque doing truthful easier than ever.

Returning the apical oregon bottommost n records of a information acceptable successful Excel isn't difficult, and determination are galore ways to bash so. You tin filter, usage expressions, oregon adjacent a PivotTable; your way mightiness beryllium connected however you're utilizing the results. But now, acknowledgment to the caller dynamic array functions, you tin usage 1 look to instrumentality arsenic galore columns and rows of the root information arsenic you like. In this article, I'll speech a spot astir these functions. Then, we'll use them to instrumentality the apical and bottommost n records.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

I'm utilizing Microsoft 365 (desktop) connected a Windows 10 64-bit system. Both dynamic array functions successful this solution are disposable successful Microsoft 365 and Excel 2021, Excel for the web, Excel for iPad and iPhone, Excel for Android tablets and phones. For your convenience, you tin download the objection .xlsx file. This nonfiction assumes that you person basal Excel skills, but adjacent a beginner should beryllium capable to travel the instructions to success.

Catching up

In the past, returning the apical oregon bottommost n required a spot of enactment and immoderate specialized knowledge. You could usage an precocious filter, an expression, oregon adjacent a PivotTable. The quality of the caller dynamic array functions is that you don't change the root data; these functions make a caller information set.

If you're not acquainted with the older ways to instrumentality the apical oregon bottommost n records, you mightiness work immoderate of the pursuing articles are connected this subject:

How to show the apical n records successful an Excel PivotTable utilizing built-in options
How to item the apical n values successful a Microsoft Excel sheet
Sum the apical n values successful a scope utilizing dynamic array behaviour successful Excel
We request a amended solution: How would you extract the apical n records successful Excel?

They're not needfully outdated, particularly if you privation to enactment with the root information successful place. If you privation to enactment with a caller information set, the caller functions are the solution.

Figure A shows a elemental information acceptable arsenic a Table. We privation to instrumentality the apical and bottommost n values successful the Value column. A operation of the functions reviewed successful the adjacent conception volition instrumentality n records. The sheet's sanction is Data; you'll request that successful a bit.

Figure A

About the functions successful Excel

We'll usage 3 functions: SORT(), SEQUENCE() and INDEX(). The archetypal 2 are dynamic array functions and reasonably caller to Microsoft 365. INDEX() has been astir for a agelong time, and you mightiness already beryllium acquainted with it. But first, conscionable what is simply a dynamic array function?

If you've ever entered an look utilizing Ctrl + Shift + Enter, past you're already acquainted with however Excel utilized to enactment with dynamic arrays. Thanks to the caller dynamic array feature, these types of expressions tin beryllium overmuch easier to make and maintain. The results spill into the cells below, filling arsenic galore arsenic indispensable to implicit the expression's calculations. That's called the spill range. If you spot a spill error, past the scope needed to fulfill the relation isn't available.

Now, onto the functions.

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

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

where array is the lone required statement and identifies the scope to sort. The optional arguments follow:

sort_index: A numeric offset worth that identifies the enactment oregon file to benignant by
sort_order: The fig 1 for ascending benignant oregon -1 for descending, with 1 being the default
by_col: The logical values TRUE for a enactment benignant and FALSE for a file sort, with TRUE being the default

In bid to instrumentality the apical oregon bottommost n records, the information acceptable indispensable beryllium sorted, and we'll usage the SORT() relation alternatively of a manual route.

SEQUENCE() returns a bid of values utilizing the pursuing syntax:

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

where rows is required and specifies the fig of rows to fill. The optional arguments follow:

column: the fig of columns to return
start: the archetypal fig successful the sequence
step: the magnitude to increment by

In its simplest form, you could usage this relation to instrumentality a bid of fixed values, but it genuinely shines erstwhile you privation to instrumentality each of the columns successful the root information set. By combining the two, you tin instrumentality a afloat sorted information set.

The past function, INDEX(), returns a worth oregon the notation to a worth from a Table oregon scope utilizing the syntax:

INDEX(array, row_num, [column_num])

where array is required and references a scope oregon an array constant. If array contains lone 1 enactment oregon column, the corresponding row_num oregon column_num statement is optional. If array has much than 1 enactment and much than 1 column, and lone row_num oregon column_num is used, INDEX() returns an array of the full enactment oregon file successful array. The past 2 arguments whitethorn oregon whitethorn not beryllium required:

row_num is required unless column_num is present. It selects the enactment successful array from which to instrumentality a value. If row_num is omitted, column_num is required.
column_num is optional and selects the file successful array from which to instrumentality a value. If column_num is omitted, row_num is required.

Now, let's commencement utilizing these functions, starting with SORT()

How to usage SORT() successful Excel

To instrumentality the apical oregon bottommost n values of immoderate information set, you request a sorted information set. Thanks to SORT() a manual benignant isn't necessary. To spot however SORT() works, let's usage it to instrumentality a sorted information acceptable of the illustration information acceptable (Figure A). First, you request to transcript the file headers to different country of the sheet, oregon adjacent different sheet. I'm going to usage different sheet, truthful you tin spot however easy this is achieved. Copy the header cells B2:F2 into a 2nd sheet. Then participate into B3 (of the 2nd sheet) the pursuing function:

=SORT(Data!B3:F13,2)

Figure B shows the root information sorted by the Value field. Here's however the arguments successful the SORT() relation work:

Data! is the expanse sanction wherever the root information is.
B3:F13 is the archetypal information set.
2 is the scale value, which specifies the Value column—the 2nd file successful B3:F13. By changing the scale worth from 2 to 1, 3, 4, oregon 5, you tin show 1 oregon each of the columns.

Figure B

Wasn't that easy?

If you're selecting these references, Excel volition show operation referencing due to the fact that the root information is simply a Table. You'll privation to format the results due to the fact that the SORT() relation doesn't. That's a spot of a nuisance erstwhile trying to usage this successful a dashboard setting. You'll privation to adhd a VBA process that applies the formatting. This is the 1 shortcoming I've tally into.

That's a elemental sort, but to execute a apical oregon bottommost information set, you'll request to adhd SEQUENCE() and INDEX().

The solution

The SORT() relation tin easy instrumentality the afloat information acceptable successful ascending oregon descending order. We privation to besides bounds the fig of records returned, truthful we'll adhd SEQUENCE() and INDEX() successful the form

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

The arguments for the archetypal SEQUENCE() relation volition beryllium input values, truthful the idiosyncratic tin alteration the fig of records returned. That means we request input cells. Use Figure C arsenic a usher to acceptable up the apical and bottommost ranges and the input compartment range. Because I inserted rows astatine the apical to accommodate the input cells, the scope references you've been seeing volition beryllium different, truthful don't fto that confuse you.

Figure C

Let's commencement with the apical information set, which requires a descending sort, expressed by -1 successful the SORT() function's sort_order argument. In H6 participate the function

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

It volition instrumentality a calculation mistake due to the fact that there's nary worth successful I2—the input cell. Enter 3 successful I2 to instrumentality the information acceptable shown successful Figure D. You already cognize however the SORT() relation works; successful this case, it serves arsenic the INDEX() function's array argument. SEQUENCE(I2) is the row_number statement and returns 3 due to the fact that the input worth successful I2 is 3. Consequently, the returns a descending information acceptable with 3 rows. SEQUENCE(1,5) specifies columns 1 done 5 successful the root information set. All enactment unneurotic SORT() returns a afloat sorted information set, but the 2 SEQUENCE() functions bounds it to 3 rows and includes each 5 columns.

Figure D

The bottommost relation works similarly, but it omits the sort_order statement due to the fact that ascending (1), is the default. In addition, it references I3 arsenic the input cell. You could usage lone 1 input compartment and person them some notation it, but this mode some information sets tin beryllium a antithetic fig of rows.

In N6 participate the relation

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

The nuts and bolts are fundamentally the same. The default sort_order argument, not entered, returns an ascending order, truthful you get the lowest values astatine the apical of the sort. By referencing I3, you find however galore rows to return. Figure E shows the input worth of 4. Consequently, the combo relation returns each 5 columns for the archetypal 4 rows—the bottommost 4 values successful Value.

Figure E

At first, everything seems a small much analyzable that you mightiness like, but erstwhile you go acquainted with the caller dynamic array functions, the easier your solutions volition beryllium for you.

Worth mentioning

Earlier I mentioned that these functions mightiness instrumentality an mistake if the spill scope isn't available. If this happens, prime the afloat spill scope and region everything—data, formats, everything and spot if that doesn't help. Of peculiar enactment is merged cells. For immoderate crushed removing them doesn't wholly wide them. You whitethorn person to determination the full works to a caller sheet.

Stay tuned

The lone regulation I've noticed is the functions' nonaccomplishment to support formatting. In a aboriginal article, I'll stock a VBA process that applies the due formatting for you erstwhile you alteration an input cell.