How to extract top 10 values in excel năm 2024

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.

  1. If we try dragging the same formula down across a column then numbers will increase.
  2. 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.

How to extract top 10 values in excel năm 2024

Steps for finding total

Step 1: Write a formula for sum i.e. = SUM(value1, value2) and press Enter.

How to extract top 10 values in excel năm 2024

Step 2: Try dragging the same formula downward and hence we get the sum of all students.

How to extract top 10 values in excel năm 2024

Step 3: Go to Formula Tab and click on Show Formulas. Now, all the formulas of the given worksheet will appear.

How to extract top 10 values in excel năm 2024

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).

How to extract top 10 values in excel năm 2024

  • 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:

  1. Use $ symbol: Write $ after each and every character of the selected cells.
  2. 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.

How to extract top 10 values in excel năm 2024

Step 2: Now, go to Formulas Tab and click on Show Formulas. All formulas of the given worksheet appear.

How to extract top 10 values in excel năm 2024

Step 3: We observe that while going downward in a row the selected cells remain the same.

How to extract top 10 values in excel năm 2024

Top N values

Given a data set of Students and their Marks. Try finding the highest 3 marks scored by students.

How to extract top 10 values in excel năm 2024

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.

How to extract top 10 values in excel năm 2024

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.

How to extract top 10 values in excel năm 2024

Step 3: You have obtained the highest 3 marks scored by students.

How to extract top 10 values in excel năm 2024

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.

How to extract top 10 values in excel năm 2024

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.