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.
How to find top 10 values in a column in Excel?
To find the top 10 values in a column in Excel, you can use the LARGE function. Use the LARGE function 10 times to find the top 10 values in the column. The LARGE function compares all the numeric values in a range supplied for the 1st argument [array] and returns the largest value based on the position specified in the 2nd argument [k].
How to find top 10 values based on criteria in Excel?
You can use the following formulas to find the top 10 values in Excel based on criteria: Method 1: Find Top 10 Based on One Criteria This formula finds the top 10 values in the range C2:C20 where the value in the range A2:A20 is equal to “Value.” Method 2: Find Top 10 Based on Multiple Criteria
How to extract and display top 10 values efficiently in Excel?
Using formulas like LARGE, SMALL, INDEX, and MATCH can help extract and display the top 10 values efficiently. The sort and filter features in Excel can be utilized to reorder and hide rows for better data visualization. Removing blank rows from the dataset is important to ensure accurate results when identifying the top values.
How do I find the top 3 values in Excel?
To find the top 3 values in Excel, copy the formula to 3 cells. The same approach applies for top 5 values [copy the formula to 5 cells] or top 10 values [copy the formula to 10 cells]. Here's how the formula works:
How do I get top 10 products in Excel?
Click on the arrow to the right of the Order ID drop down box and select Value Filters > Top 10 from the popup menu. When the Top 10 Filter [Order ID] window appears, select Top, 10, Items, and Sum of Quantity in the respective drop downs. Then click on the OK button.
How do I filter top 10 data in Excel?
Show the top or bottom 10 itemsRight-click an item in the selection, and then click Filter > Top 10 or Bottom 10. In the first box, enter a number. In the second box, pick the option you want to filter by.