LARGE & SMALL Array Functions: Return Top 8 and Bottom 8 Values

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how to use LARGE and SMALL functions to display Top 8 and Bottom 8 values

LARGE Function returns the k-th largest value in a data set

LARGE Syntax                    =LARGE(array, k)

SMALL Function returns the k-th smallest value in a data set

SMALL Syntax                    =SMALL(array, k)

In this example we want to return the Top 8 and Bottom 8 Total Sales in column N

To return the Top 8 Total Sales, select P10:P17

Execute the formula in the selection: =LARGE(N4:N1042,{1;2;3;4;5;6;7;8})

Then press CTRL + SHIFT + ENTER

In the caption above, we have returned the Top 8 Total. To verify whether we are correct, we will sort the Total from Largest to Smallest.

To sort the Total, right-click any cell in the Total and select Sort from Largest to Smallest

We are correct.

To return the smallest Total Sales, select cells Q10:Q17 and execute the formula:



We have returned the bottom 8 Total Sales. To verify, we will sort the Total from smallest to largest

Correct. That is how to use the two array functions






Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *