Here, we will learn how to use the Excel dynamic array function: TAKE. Later, we will also learn how to remove the last n rows using TAKE and the ROWS function, that returns the count of rows in the reference.
TAKE function
The Excel TAKE function extracts records from the top or bottom of the given array.
TAKE takes three arguments: array, [rows] and [cols]. The first required argument array will take the range from which to extract records. Both rows and cols arguments are optional, but a numeric value is required for at least one. Both accept positive or negative numbers. Positive numbers will extract records from the start of the array; negative numbers will extract records from the end of the array.
Removing last n rows using TAKE with ROWS
To remove the last n rows from the array, use the TAKE formula and nest the ROWS function in the rows argument of TAKE.
The dataset (shown in the image) contains a list of employees' names, their age and their monthly salary in columns A, B and C respectively. The goal (in E3) is to remove the last 5 rows from the array A3:C18 and extract rest of the records from the top. The formula in G3 is:
=TAKE(A3:C18,ROWS(A3:C18)-E3)
As soon as I press Enter, the formula spills the results into the multiple cells automatically in the range G3:I13.
How this formula works
Since there are 16 rows in the array A3:C18, the expression ROWS(A3:C18) returns 16. The value in E3 is 5, so 16-5 returns 11. Since the number 11 is positive, TAKE extracts 11 rows from the start of the array A3:C18. Cols defaults to zero, returning all the columns.
Note that the same result can be achieved by using the DROP function also. The formula in K3, is:
=DROP(A3:C18,-5)
Here, DROP removes the last 5 rows from A3:C18, as the number 5 is negative for rows.
You have successfully learnt how to use the Excel dynamic array function: TAKE, and how to remove the last n rows using TAKE and the ROWS function! I hope this post helped you.
