# How to Find Most Common Value in a Range in Excel

This post will guide you how to find the most common value in a column of data in Excel. How do I find the most frequently occurring text from a list with array formula in Excel. How to extract the most frequent value from a list of data with VBA Macro in Excel.

## 1. Find Most Common Value with Formula

Assuming that you have a list of data in range B1:B9, in which contain product names. And you need to find the most common text value from the range of cells B1:B9. How to do it. You can use an Excel array formula based on the INDEX function, the MODE function and the MATCH function to extract the most common value from a given range of cells. Like this:

``=INDEX(\$B\$1:\$B\$9,MODE(MATCH(\$B\$1:\$B\$9,\$B\$1:\$B\$9,0)))``

Type this formula into a blank cell and press Shift + Ctrl + Enter keys on your keyboard to apply this array formula.

You would notice that the most common value is extracted.

## 2. Find Most Common Value with VBA

You can also use an Excel VBA Macro to achieve the same result of extracting the most common value from a selected range of cells. Here are the steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

``````Function MostCommon(rng As Range) As Variant
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Dim cell As Range
For Each cell In rng
If Not IsEmpty(cell.Value) Then
If dict.Exists(cell.Value) Then
dict(cell.Value) = dict(cell.Value) + 1
Else
End If
End If
Next cell

Dim maxCount As Long
maxCount = Application.WorksheetFunction.Max(dict.items)

Dim key As Variant
For Each key In dict.keys
If dict(key) = maxCount Then
MostCommon = key
Exit Function
End If
Next key
End Function

``````

#5 back to the current worksheet, select the cell where you want the result.

#6 Enter the formula in the formula bar:

`` =MostCommon(B1:B9)``

#7 Press Enter to run the macro and display the most common value.

With these steps, you’ve successfully utilized VBA code to find the most common value in your Excel range.

## 3. Video: Find Most Common Value

This Excel video tutorial where we’ll unravel the secrets of finding the most common value in a range using two distinct methods: the first utilizing INDEX and MATCH functions and the second involving VBA code.

## 4. Related Functions

• Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
• Excel MATCH  function
The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….