How to select top 5 of values in Excel

How to select top 5 of values in Excel

Financial work involves sorting of data. Excel is a very potent tool for sorting and organizing data.

You can sort your data in either ascending or descending order of magnitude. The sort icons are available on the DATA tab for you to use for sorting data manually. But it is only but a permanent solution which you have to repeat the procedure whenever you want to sort your data. Sorting of data is majorly used for comparison.

Excel makes sorting work easy; it has a function that will sort your data within a blink.

There is no static formula for sorting data in excel, but you can dynamically create workarounds for sorting your data.

Now, let us see how we can select the top 5 values in a set of data. This tutorial will shed light on this.

Sorting the top 5 numbers means arranging the numbers in descending order. You can create a simple dynamic formula for this. It is called a LARGE function and has an expanding range.

The syntax is given below:

=LARGE(data,ROWS(exp_RNG))

Now, if the formula is in cell C5;

=LARGE(data,ROWS($B$5:B5))

How the LARGE function works:

That LARGE value extracts the largest range of value from a set data. The process is automated, and it sorts the values whenever you enter the formula.

Selecting the top 5 values in Excel

Now, let’s consider a spreadsheet with values within the range A2: A16. Follow the steps below to select or sort the top 5 values.

Firstly, identify the cell that contains the data in this case B2.

 

 

 

 

 

 

 

 

 

 

Check the window menu, locate the formula bar on the menu bar, and copy and paste the formula below:

=LARGE(A$2:A$16,ROWS(B$2:B2))

 

 

 

 

 

 

 

 

Note that there are five columns between B2 and B6, so the top 5 values in the list will be displayed.

Ensure that you enter the formula correctly. Proceed by pressing the enter button.

We aim to select the top 5 numbers from the list, so locate the cell, B2. Select and drag the fill handle down to cell B6.

 

 

 

 

 

 

 

 

 

 

 

 

It is straightforward and quick. I hope that this tutorial is comprehensive enough for your understanding. Please share it with your friends. Thanks for reading.