How to find Top or Bottom N values in Excel?
Functions help find top or bottom N values in excel very easily. The functions used to find Nth largest and Nth smallest number are = LARGE(array, k) and = SMALL(array, k) respectively.
Cell Reference
Before understanding the LARGE and SMALL functions. We first need to have a fine knowledge of cell references. Cell reference is of two types:
- Relative Reference
Relative reference is the reference that changes with cells.
- If we try dragging the same formula down across a column then numbers will increase.
- If we try dragging the same formula side across a row, then alphabets will increase.
Here is an example for better understanding: Consider a data set of Students with their Physics and Math marks. Calculate the total marks.
Steps for finding total
Step 1: Write a formula for sum i.e. = SUM(value1, value2) and press Enter.
Step 2: Try dragging the same formula downward and hence we get the sum of all students.
Step 3: Go to Formula Tab and click on Show Formulas. Now, all the formulas of the given worksheet will appear.
Step 4: We find in range E3:E9 that the values inside the functions are increasing as we go downward in a row. For Example: in cell E3, the formula is =SUM(C3, D3) and in cell E4, the formula is =SUM(C4, D4).
- Absolute Reference
The absolute reference remains static even if the rows or columns are changing. There can be two ways to make a selected cell with absolute reference:
- Use $ symbol: Write $ after each and every character of the selected cells.
- Use F4: Press Fn + F4 on your keyboard to make absolute reference to a selected cell.
Consider the same data set as above. Try using the same formula for total but with absolute reference.
Steps of finding total with absolute reference
Step 1: Considering the same data set and write the function with absolute reference.
Step 2: Now, go to Formulas Tab and click on Show Formulas. All formulas of the given worksheet appear.
Step 3: We observe that while going downward in a row the selected cells remain the same.
Top N values
Given a data set of Students and their Marks. Try finding the highest 3 marks scored by students.
Steps for top N values
Step 1: Use = LARGE(Array, k) function to have kth largest number in an array. Press Enter.
- Array: It is the first argument of the LARGE function. We need to provide an absolute reference for this array.
- k: It is the second argument of the LARGE function. It specifies which largest number you want. We will provide a relative reference to this number.
Step 2: You will get the kth largest number from the array. Now, drag down till the N numbers you want. For example: drag down to 3 cells for the given data set.
Step 3: You have obtained the highest 3 marks scored by students.
Bottom N values
Consider the same data set of Students and their Marks. Try finding the lowest 3 marks obtained by students.
Steps for bottom N values
Step 1: Use = SMALL(Array, k) function to have kth smallest number in an array. For example, if the value of k is 1, then the function will return the smallest number in the array. If the value of k is 2, then the function will return the second smallest number in the array. Press Enter.
- Array: It is the first argument of the SMALL function. We need to provide an absolute reference for this array.
- k: It is the second argument of the SMALL function. It specifies which smallest number you want. We will provide a relative reference to this number.
Step 2: You will get the kth smallest number from that array. Now, drag down till the N numbers you want. For example: drag down to 3 cells for the given data set.
Step 3: You have obtained the lowest 3 marks scored by students.