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.

Table of Contents

## 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
dict.Add cell.Value, 1
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])….

## Leave a Reply

You must be logged in to post a comment.