FILTER on first or last n values

FILTER first or last n values

To FILTER and extract the first or last n values in Excel, use a formula based on the TAKE, FILTER, INDEX, and SEQUENCE functions.

How to filter the first n values in Excel?

  1. Click on cell D3.
  2. Type =TAKE(FILTER(B3:B14, B3:B14<>0), 5)
  3. Press Enter.
  4. This formula filters the blank cells and takes the first 5 values.

How to filter the last n values in Excel?

  1. Click on cell F3.
  2. Type =TAKE(FILTER(B3:B14, B3:B14<>0), -5)
  3. Press Enter.
  4. This formula filters the blank cells and takes the last 5 values.

Example

Let us see an example! We have a column “Range” containing a list of states. The goal is to extract the first five and last five non-blank entries from the list. The formula in cell D3 extracts the first five entries using the TAKE and FILTER functions, which filter out blank cells and return the specified number of entries from the top. Similarly, the formula in cell F3 extracts the last five entries from the dataset.

Formulas:

=TAKE(FILTER(B3:B14, B3:B14<>0), 5)

and

=TAKE(FILTER(B3:B14, B3:B14<>0), -5)

FILTER first or last n values

The result shows the first and last five non-blank states from the original range. These formulas will ensure that any zeros are filtered out before selecting the specified number of items from the start or end of the range.

Explanation

Evaluate the formula from the inside out:

=FILTER(B3:B14, B3:B14 <>0):

The FILTER function is designed to extract data from a specified range based on a given condition. The first argument is the range contains the list of states in column B from cells B3 to B14. The second argument is the condition ((B3:B14<>0). This condition checks each cell in the range B3:B14 to see if it is not equal to 0 (or if it’s non-empty).

Since <> is the “not equal to” operator, B3:B14<>0 returns TRUE for cells with non-zero values (or non-empty cells) and FALSE for any cells with a zero (or empty). The FILTER function uses this condition to return an array of only the non-empty cells from B3:B14.

FILTER range extract first or last n values

=TAKE(FILTER(B3:B14, B3:B14<>0), 5):

The TAKE function is designed to return a specified number of items from the beginning or end of an array. The first argument is the array, which is the result of FILTER(B3:B14, B3:B14<>0) is the array passed to TAKE. This array includes all non-empty cells from B3:B14.

The second argument is the number of rows you want to extract. In the example, the number 5 tells TAKE to return the first five items from this filtered list. As a result of the formula takes the first five non-empty items from the filtered array and displays them in the target cell(s).

Using INDEX, FILTER and SEQUENCE functions

Okay, let’s try another formula. In the example, you want to extract the first n values from a range.

Formula:

=INDEX(FILTER(B3:B14,B3:B14<>””),SEQUENCE(5,1,1,1))

Using INDEX FILTER and SEQUENCE functions

Explanation:

FILTER(B3:B14,B3:B14<>””): This part of the formula uses the same logic as the previous example.

SEQUENCE(5, 1, 1, 1): SEQUENCE generates a list of numbers in a defined pattern.

  • 5: The number of rows in the sequence (we want the first five results).
  • 1: The number of columns in the sequence (a single column array).
  • 1: The starting value for the sequence.
  • 1: The increment between each number in the sequence.

The result of the SEQUENCE function is an array {1; 2; 3; 4; 5}, which represents the positions of the first five items in the filtered list.

INDEX(…, SEQUENCE(5, 1, 1, 1)): The INDEX function returns the value at specific positions within an array. The first argument is the array from FILTER(B3:B14, B3:B14<>””), which includes only the non-empty cells from B3:B14. The row numbers argument is SEQUENCE(5, 1, 1, 1) provides the row numbers {1; 2; 3; 4; 5}, so INDEX retrieves the first five items from the filtered array. The INDEX formula returns the first five non-empty values from the filtered range.

Filter last n values

You can use an advanced formula to filter and extract the last n values in a range.

Formula:

=INDEX(FILTER(B3:B14,B3:B14<>””),SORT(SEQUENCE(5,1,SUM(–(B3:B14<>””)),-1)))

Here, SEQUENCE generates the last five positions by counting backward from the total count of non-empty cells, providing similar functionality without using SUM.

filter and extract the last n values in a range

The main difference is that the TAKE formula is a more concise and efficient way to retrieve the last five non-empty items directly, while the INDEX and SEQUENCE formula involves extra steps (counting and sorting) to achieve the same result. In cases where you only need the last items and no additional ordering or manipulation, TAKE is the preferable choice.

Download the practice file and play with the formulas.