Excel offers numerous functions and features to simplify complex tasks. Identifying the top values within a dataset is a common requirement for Excel users. Whether you are looking to find the highest sales figures, the top performers in a competition, or the most popular items in a list, Excel provides several methods to help you find the top 5 values.
Use LARGE Function to find Top 5 Values
You can use the Excel LARGE function to find the top 5 values. By entering the formula =LARGE(range,1) for the highest value, =LARGE(range,2) for the 2nd highest, and so on.
Type the formula =LARGE($B$2:$B$26, 1) in cell E2 to find the highest value, =LARGE($B$2:$B$26, 2) in cell E3 to find the 2nd highest value, =LARGE($B$2:$B$26, 3) in cell E4 to find the 3rd highest value, and so on.
How to use Conditional Formatting to highlight Top 5
You can use the Top 10 Items option in the conditional formatting menu to highlight the top 5 values with a specific color or format. Conditional formatting is a useful feature in Excel. It can help you visually identify the top values in a dataset.
Select the entire data set e.g. A1:B26, and Click Conditional formatting in the Home menu. Choose Top 10 Items… in the Top/Bottom Rules
Change the number 5 in the input cells, the default value is 10, since you are finding Top 5 values. Select Green Fill with Dark Green Text in our example.
Use the Sort feature to find Top 5 Values in Excel
Sorting data in Excel is a basic way to find the top values in a dataset. Simply select the data range, and click on the Sort button in the Data menu.
Choose the Sales column, and select Cell Values for Sort On input item. For Order, select Largest to Smallest, to arrange the values from highest to lowest. You can easily locate the top five values at the top of the list.
Use the RANK Function in Excel to find Top 5
The RANK function in Excel allows you to assign a rank to each value in a dataset based on its position.
Syntax: =RANK(cell, range, 0)
Go to cell C2 and type the formula =RANK(B2,$B$2:$B$26,0). Drag and apply the formula to the entire dataset.
In the formula, B2 is a number to find rank in the dataset, B2:B26 is your data set and the last parameter is 0 for Descending Order.
Conclusion
Obtaining the highest values in Excel can be a straightforward process if you have the appropriate techniques and functions to use whether you prefer using formulas LARGE, Conditional Formatting, or Pivot tables.
I hope this blog post has helped you to understand how to find the Top 5 Values in Excel. If you have any questions or feedback, please leave a comment below.
