How to Find Top 5 Values in Excel [9 Methods]

ByJames Corrigan

Do you need to display Top N values in your Excel Report or dashboard? Excel is a highly effective program for analysing data. In this blog post, you will learn different methods to Find Top 5 Values in Excel.

Most of the time, the stack holders are expected to report the Top N values of the data, which helps them to better understand the data and predict the result.  

In the report or dashboard, you should always highlight the popular products or customers with the highest sales are most important. To find the Top N values, you have to sort the data into descending order and then filter Top N Values.

You can use excel in-built functions LARGE, INDEX & MATCH combinations, or SORT & FILTER feature to find the Top N Values. XLOOKUP, SORT & FILTER functions, or Analyze Data features will help you to find the Top N Values if you are working with office 365.

Also, you have Power Query or Power Pivot tools in excel to find Top N Values. Suppose you need this functionality in your automation, you can use either VBA or Office Scripts method.

Assume that you have received the below sales data table, it has the customer’s name and the sales values. In your report, you need to show only the top 5 sales with the respective customer’s name.

Get your copy of the example workbook used in this blog post to follow along!

Find Top 5 Values in Excel with LARGE function

LARGE function will give you the kth largest value from your data set.  In this section, you will learn how to find top 5 sales in your data set using the LARGE function.

LARGE( array, k )

Create a support field Rank in column D, Type the numbers 1 to 5 in the range of cells D2:D6.

Go to cell E2, type the formula =LARGE( $B$2:$B$16, D2 ), and Press Enter. Drag the fill handle to the range E2:E6 to copy the formula.

Find Top 5 Values in Excel with INDEX & MATCH function

The above method returns only the top 5 values. But you have to show the respective customer’s name in column F.  Use INDEX & MATCH combination with LARGE function to show the respective customer’s name in your report.

INDEX( array, row_num, [column_num] )

INDEX returns a value from a table or range.

MATCH( lookup_value, lookup_array, [match_type] )

MATCH function finds a certain item in a range of cells and then returns the position. 

Create a support field Rank in column D, Type the numbers 1 to 5 in the range of cells D2:D6.

Go to cell F2, type the formula =INDEX( $A$2:$A$16, MATCH( LARGE( $B$2:$B$16, D2), $B$2:$B$16, 0 ) ), and Press Enter. Drag the fill handle to the range F2:F6 to copy the formula.

If you use the above formula to find Top 7 values, you will see the same sales value in cells E7 & E8, and notice that the above formula returns the same customer name in cells F7 & F8. 

You can try the below formula to find Top 7 values for the duplicate data. Now you can see the proper name in cells K7 and K8.

=INDEX( $A$2:$A$16, MATCH( 1, ( $B$2:$B$16 = LARGE( $B$2:$B$16, I2 ) ) * ( COUNTIF( K$1:K1, $A$2:$A$16 ) = 0 ), 0 ) )

Find Top 5 Values in Excel with Excel 365 functions

You have a number of new functions in office 365 and online Excel. In this section, you learn how to use XLOOKUP and a combination of SORT & FILTER functions to find Top 5 values.

These functions will not work in the earlier versions of excel.

Using XLOOKUP function

=XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

XLOOKUP function finds the text in the lookup_array and then returns the item from the return_array.

Create a support field Rank in column D, Type the numbers 1 to 5 in the range of cells D2:D6. Go to cell F2, type the formula =XLOOKUP( LARGE( $B$2:$B$16,D2 ), $B$2:$B$16, $A$2:$A$16 ), and Press Enter. Drag the fill handle to the range F2:F6 to copy the formula.

Using SORT & FILTER Combination of function

This section explains to you how to find Top 5 Values using the Office 365 SORT and FILTER functions. The FILTER function filtered the Top 5 Values of your data set and then use the SORT function to sort the filtered data by sales column.

=FILTER( array, include, [if_empty] ).

FILTER function filters an array.

=SORT( array, [sort_index], [sort_order], [by_col] )

SORT function returns the sorted array of lists.

Go to cell D2, type the formula =SORT( FILTER( A2:B16, B2:B16 >= LARGE( B2:B16, 5 ) ), 2, -1 ), and Press Enter. It returns the Top 5 sales with the customer’s name as a two-dimensional table.

Find Top 5 Values in Excel with SORT & FILTER Command

Excel has a Sort & Filter command in the Home Tab. Now you learn how to use this command to your data set and find Top 5 Values.

Please follow the below steps to use Sort & Filter command on your data.

  1. Select the entire range A1:B16 of your data.
  2. Go to the Home Tab.
  3. Click the Sort & Filter command and choose the Filter item.

Now you can see the Filter dropdown in your data header rows.

  1. Click the drop-down icon in the Sales header to expand the filter menu.
  2. Select Number Filters and Choose Top 10… – to open the Top 10 AutoFilter dialog box.
  1. Make sure the first dropdown is selected Top, the second input box enters 5 and the last input box is selected Items.
  2. Press OK.
  1. Click the drop-down icon in the Sales header to expand the filter menu.
  2. Select the option Sort Largest to Smallest.

Find Top 5 Values in Excel with Analyze Data

Excel 365 has a new Analyze Data command in the Home Tab, which is an AI-enabled feature. It allows you to analyze your data, create charts, and can be used to identify KPIs quickly and easily.

Select the command Analyze Data in the Home Tab. Analyze Data Pane will appear.  Type the question Top 5 Sales in the Ask a question about your data input box and then Press Enter.

Click the + Insert Button. The Analyze Data generates the Top 5 Sales table in a new worksheet named Suggestion1.

Find Top 5 Values in Excel with Pivot Table

Pivot Tables are easy to use and allow you to sort, filter, and calculate your data. You can quickly extract meaningful information from your data. Slicers offer buttons and you can click to filter tables or PivotTables, but they also show the current filtering state, making it easy to understand what is being displayed.

Please follow the below steps to insert the Pivot Table in Excel.

  1. Select the range of cells A1:B16 in the Data Tab
  2. Choose the PivotTable command in the Insert Menu
  3. Click From Table/Range option – to open the PivotTable from table or range dialog box.
  1. Select the option Existing Worksheet. You can select the New Worksheet option if you need the pivot in a new worksheet.
  2. Choose the cell B6, location to insert the pivot table.
  3. Press OK

Now you can see an empty pivot table is created in cell B6 in the PivotTable Sheet.

  1. Drag the fields Customer Name to Rows Pane and Sales to Values Pane
  2. Click the small drop-down icon on the right side of the Row Labels header.
  3. Select the Value Filters option and Click Top 10… – to open the Top 10 Filter (Customer Name) dialog box.
  1. Make sure the first dropdown is selected Top, the second input box enters 5, the third dropdown is selected Items and the last dropdown is selected Sum of Sales, since you are filtering by Sales.
  2. Press OK
  1. Click the Right mouse button on any cell in the Sum of Sales column in the pivot table, and then click Sort.
  2. Select the option Sort Largest to Smallest.

Find Top 5 Values in Excel with Power Query

Excel Power Query is a powerful data processing and transformation tool. It allows you to connect different data sources. Using the Power Query options, you can clean, transform, process, and format the connected data for your reports and analysis.

Steps to find Top 5 values using Power Query

  1. Select the range of cells A1:B16 in the Data Tab.
  2. Choose From Table/Range option in Data Menu– to open the Power Query window.
  1. Click the drop-down in the Sales Column, and then select the Sort Descending option.
  1. Go to the Home Tab in the Power Query Editor, Select the Keep Rows command and then Click Keep Top Rows to open the Keep Top Rows dialog box.
  1. Enter the number 5 in the input box, since you are finding the Top 5 values, and Press OK.
  1. Go to the Home Tab, Select the Close & Load command and Press Close & Load To…
  1. The above steps open the Import Data dialog box. In that, you can select the sheet and the location to insert the transformed Power Query table in your worksheet.

Find Top 5 Values in Excel with VBA

VBA (Visual Basic for Applications) is a programming language that allows you to automate repetitive tasks, create custom formulas, and reports in Excel. You will use the below VBA script in your automation to find Top 5 Values.

Sub Top5Sales()
'Declare variable
Dim selectedRange As Range

'Assign worksheet and selected range to the respective variables
Set selectedRange = ActiveSheet.Range(Selection.Address)

'Sort your data by sales
selectedRange.Sort key1:=selectedRange.Range("B2"), order1:=xlDescending, Header:=xlYes

'Keep only top 5 rows and clear all other data in the sorted table
selectedRange.Range("A7:B" & selectedRange.Rows.Count).Clear

End Sub

Add the above code to your module in the VBE.

Macro declares the variable and assigns the currently selected range to the variable selectedRange. Sort the data by sales, sales data are in the second column B. Finally, macro keeps top 5 rows and clears the rest of the data.

Steps to Run the Top5Sales macro.

  1. Select the range of cells A2:B16
  2. Press Alt + F8, the keyboard shortcut to open the Macro dialog box.
  3. Choose the Top5Sales Macro and then Press Run.

Find Top 5 Values in Excel with Office Scripts

Office Scripts is a scripting language that helps you to create automated tasks and macros in Excel. You can use office script to create custom formulas and data manipulation.

function main(workbook: ExcelScript.Workbook) {

  //getselected range
  let rng = workbook.getSelectedRange();

  //Sort your data by sales
  rng.getSort().apply([{key:1,ascending:false}],false,true,ExcelScript.SortOrientation.rows);

  //Keep only top 5 rows and clear all other data in the sorted table
  rng.getCell(6, 0).getExtendedRange(ExcelScript.KeyboardDirection.right).getExtendedRange
(ExcelScript.KeyboardDirection.down).clear(ExcelScript.ClearApplyTo.all);
  
}

The above script assigns the selected range to the variable rng. getSort().apply() method sorts the data by Sales in descending order. In the final step keep only top 5 rows. The script selects all the rows and columns from the first cell in row 66th cell in the first column and clears all.

Follow the below steps to execute the Top 5 Values Office script in your excel.

  1. Select the range of cells A2:B16.
  2. Go to Automate menu.
  3. Select the Top 5 Values Script – to open the Code editor on the right side of the excel window.
  4. Press Run. 

Conclusions

Finding Top N values in the data will give you the data patterns, outliers, and the most important values within the data set. It is also helpful for predicting the result.

INDEX & MATCH, XLOOKUP and SORT & FILTER functions work very well for the dynamic data. You can use AI-enabled Analyze Data to find Top 5 Values if you work with office 365.  Suppose, you like to find Top 5 Values using command or keyboard shortcuts, Pivot Table and Sort & Filter commands will be user-friendly.

Using Power Query and Power Pivot to find Top 5 Values are the most effective for working with large datasets. VBA or Office Scripts can be helpful to find Top 5 Values in your automaton or dashboard.

Do you know any other method to Find Top 5 Values in Excel? Let me know in the comments section!

Similar Posts