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
                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])….