2 Methods to Find the Largest or Smallest N Items in Your Excel Worksheet

Sometimes you need to find the largest N items or the smallest N items in a range in your worksheet. Here we will provide two effective methods for you.

Finding the largest N items or the smallest N items in a range is a very ordinary task in your work. In our previous article How to Highlight the Top or Bottom N Items by Conditional Formatting in Excel, we have introduced the methods of using conditional formatting. And in the following part, we will discuss about the two other methods in detail.

Method 1: Excel Functions

Excel has also provided functions for you to finish this task. Suppose now you need to find the largest 5 numbers or the smallest 5 numbers in a given range.

Input the number 1 in a blank cell in the worksheet. Here we choose the cell A16.
And then input number 2 into the cell below the above cell.
Now select the two cells.
After that, click the fill handle and drag downwards to fill other cells until there are 5 numbers in this column.
Now click another blank cell in the worksheet. We will use the cell B16 as an example.
Input the following formula into the cell:

=LARGE($B$2:$F$13,A16)

The first argument is the reference of the target range where you need to find the numbers. And the second argument is the rank of the target number in this range.

After that, press the button “Enter” on the keyboard. And you will see the largest number in this cell.
In this step, click the cell with formula again.
And then double click the fill handle of this cell. Therefore, the formula will be filled in other cells in this column. And the largest N items will appear in this range.

On the other hand, if you need to find the smallest N items in this range, you need to use another function.

Click a blank cell in the worksheet. Here we click the cell C16.
Now input the following formula into the cell:

=SMALL($B$2:$F$13,A16)

After that, press the button “Enter” on the keyboard. Therefore, you will get the first number.
And then click this cell C16 again.
Next double click the fill handle of this cell. Hence, you will get the result in the range.

Therefore, the result will be list in the new range. And it is easy for you to know the exact numbers in this worksheet.

Method 2: VBA Macros

Except for the Excel functions, here you can also use VBA macros to finish this task.

Press the shortcut keys “Alt +F11” on the keyboard to open the Visual Basic editor.
And then insert a new module in the editor.
In this step, copy the following codes into the new module:

Sub FindNLargestItems()
  CountItems = InputBox("Input the numbers of the items that you need")
  If CountItems = "" Then Exit Sub
  For RowReference = 1 To CountItems
    Range("E15").Offset(RowReference, 0) = Application.WorksheetFunction.Large(Range("B2:F13"), RowReference)
  Next
End Sub

In this macro, you will need to input the numbers that you need. And then we will output the result in a range in the worksheet. Actually here you will still use the worksheet function. In addition, you can also change the target range according to your need. Other elements in the codes can also be modified.

After that, press the button “F5” on the keyboard to run this macro.
And then you will see the input box pop up. Input the numbers that you need to know about the items.
After that, click the button “OK” in the window.

And now come back to the worksheet, you will see the result in the target range.

As for the smallest N items, you need to change the function “LARGE” into “Small” in the codes. The new codes will be like this:

Sub FindNSmallestItems()
  CountItems = InputBox("Input the numbers of the items that you need")
  If CountItems = "" Then Exit Sub
  For RowReference = 1 To CountItems
    Range("F15").Offset(RowReference, 0) = Application.WorksheetFunction.Small(Range("B2:F13"), RowReference)
  Next
End Sub

We have changed certain elements in this macro. In addition, you need to modify it according to your actual worksheet.

After you run this macro, you will also get the result easily in a range.

However, you may find that you can only get the numbers in another range. What if you need to know the exact cell references? Here we have also changed the codes to fulfill this requirement.

To the references of the largest N items in a range, you can use the following macro:

Sub FindReferenceofLargestNItems()
  Dim objRange As Range
  CountItems = InputBox("Input the numbers of the items that you need")
  If CountItems = "" Then Exit Sub
  For SecondArgument = 1 To CountItems
    For Each objRange In Range("B2:F13")
      If objRange = Application.WorksheetFunction.Large(Range("B2:F13"), SecondArgument) Then
        objRange.Interior.Color = vbYellow
          End If
    Next objRange
  Next SecondArgument
End Sub

And then press the button “F5” on the keyboard to run this macro. Still you will be asked to input the numbers of items that you need.
Next come back to the worksheet, you will see the result. Those cells with the largest numbers in the range will be formatted with a different color.

You will see that there are 6 cells. That’s because two cells contains the same value “785” in this range.

When you need to find the smallest N items in the range, you can use the following codes:

Sub FindReferenceofSmallestNItems()
Dim objRange As Range
  CountItems = InputBox("Input the numbers of the items that you need")
  If CountItems = "" Then Exit Sub
  For SecondArgument = 1 To CountItems
    For Each objRange In Range("B2:F13")
      If objRange = Application.WorksheetFunction.Small(Range("B2:F13"), SecondArgument) Then
        objRange.Interior.Color = vbGreen
      End If
    Next objRange
  Next SecondArgument
End Sub

You can also get the result easily with this macro. To make a difference, we change the color into green.

The next time if you need to find largest N items or smallest N items in a range, you can use the macros according to your actual need.

A Comparison of the Methods

Combined with the method of conditional formatting in the article How to Highlight the Top or Bottom N Items by Conditional Formatting in Excel, we will compare the three methods in a comprehensive way.

Comparison	Excel Functions	VBA Macros	Conditional Formatting
Advantages	The arguments of the two functions are very easy. Every user can understand this method and apply it in worksheet.	You can either get the numbers or the cell references by using different macros. And with only several steps, you can get the result quickly.	By using this methods, you can also know the exact cell references of the largest N items or the smallest N items.
Disadvantages	When you need to get the exact cell references, you still need to find them in the range. This can be troublesome.	If you are not familiar with VBA codes, you will find it hard to modify it. Besides, you will probably meet with problems when using this method.	Every time you need to find the items, you need to perform the whole process. This will cause a lot of time and energy.

Until now, you will have a deep understanding of the three methods. You will know which method is the most suitable one for you.

Protect the Private Data in Your Excel Files

Your Excel files will certainly contain important private data and information. And you will certainly protect them. The anti-virus software is a must in your computer. Once you find errors in your Excel files, using a recovery tool to repair Excel xlsx file problem. If you let the problem go, you are sure to suffer a lot. What’s worse, the result of data disaster can be disastrous.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word doc file and outlook repair software products. For more information visit www.datanumen.com