How to find the second most common/frequent number or text in Excel?

This solution leverages an array formula to identify the second most common number in a specified range. Array formulas are suitable when you want a direct result with a single formula, but they only work reliably for numeric values. For text values, a different method is required. Note that array formulas may require Ctrl + Shift + Enter (instead of just Enter) depending on your Excel version (pre-Office 365 and Excel 2019).

Select a blank cell where you want the result to be displayed. Type the following formula:

=MODE(IF(A2:A31<>MODE(A2:A31),A2:A31,""))

Press Ctrl + Shift + Enter at the same time to confirm the formula. Upon successful entry, Excel will automatically add curly braces around the formula, indicating that it's been entered as an array formula.

The second most common or frequent number from your range will then appear in the cell. This approach is efficient for numeric lists but does not work for text values, nor does it account for unique tie-breaking logic if several values have the same frequency.

Tips & Notes: Double-check that your range (A2:A31 in the example) matches your actual data. If there are less than two unique numbers in the list, this formula may return either an error or unexpected results. Also, if working with Office 365 or Excel 2019 or newer, you may be able to use dynamic array formulas and just press Enter.

This method will guide you to batch count the occurrences of each item which you will find out the second common or frequent items from by the Advanced Combine Rows utility of Kutools for Excel, and then sort the occurrences to find out the second most common/frequent text value.

1. Highlight the text or number column you want to analyze, plus a blank adjacent column for marking occurrences. Then go to Kutools > Content > Advanced Combine Rows.
Note: For clarity, assign a header to the helper column such as "Occurrence".

2. In the Advanced Combine Rows dialog box, set your chosen column as the Primary Key, and in the helper column, set it up to Count occurrences. Click Ok to confirm.

3. Select the column displaying the occurrence numbers, then go to Data > Sort Largest to Smallest to arrange the values by their frequency descending.

4. If a Sort Warning appears, make sure to choose Expand the selection and click Sort. This will organize your rows so the second most common item appears on the third row.

Advantages: This method is excellent for both text and numbers, and allows you to check not only the second but also third, fourth, etc. most common values.

Sorting values by frequency using built-in tools or add-ins such as Kutools for Excel is one of the simplest and most visual methods to find the second most frequent value, especially when working with large datasets.

1. Select your target data column, then open Enterprise > Advanced Sort.

2. Configure the Advanced Sort dialog: pick your column from the Column drop-down, set Frequency as the criterion to sort by, and set the order to Z to A.

3. Click OK. The values are quickly sorted from most to least frequent, so you can straightforwardly identify the second most common value by looking at the sorted list. This method works seamlessly for both numbers and text, simplifies comparison, and requires no formulas.

For users who prefer automating repetitive tasks or wish to avoid manual setup, a custom VBA macro can efficiently return the second most frequent value from any range, regardless if it contains numbers, text, or a combination. VBA solutions are especially practical if you need to apply this analysis regularly across different worksheets, or prefer not to use helper columns.

1. On the Excel ribbon, click Developer Tools > Visual Basic. In the newly opened Microsoft Visual Basic for Applications window, choose Insert > Module, then copy and paste the code below into the module:

Sub SecondMostFrequentValue()
    Dim dict As Object
    Dim rng As Range
    Dim cell As Range
    Dim freqArr As Variant
    Dim i As Integer
    Dim j As Integer
    Dim keyArr As Variant
    Dim tempF As Variant
    Dim tempK As Variant
    
    Set dict = CreateObject("Scripting.Dictionary")
    xTitleId = "KutoolsforExcel"
    
    On Error Resume Next
    Set rng = Application.Selection
    Set rng = Application.InputBox("Range", xTitleId, rng.Address, Type:=8)
    On Error GoTo 0
    
    If rng Is Nothing Then Exit Sub
    
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            dict(cell.Value) = dict(cell.Value) + 1
        End If
    Next
    
    If dict.Count < 2 Then
        MsgBox "Not enough unique values.", vbExclamation, xTitleId
        Exit Sub
    End If
    
    freqArr = dict.Items
    keyArr = dict.Keys
    
    For i = 0 To dict.Count - 2
        For j = i + 1 To dict.Count - 1
            If freqArr(j) > freqArr(i) Then
                tempF = freqArr(i)
                tempK = keyArr(i)
                freqArr(i) = freqArr(j)
                keyArr(i) = keyArr(j)
                freqArr(j) = tempF
                keyArr(j) = tempK
            End If
        Next j
    Next i
    
    MsgBox "Second most frequent value: " & keyArr(1) & vbCrLf & "Times: " & freqArr(1), vbInformation, xTitleId
End Sub

2. Click the button (the green triangle) to execute. A dialog box will prompt you to select your target range. Select your data and confirm, after which the second most frequent value along with its occurrence count will be displayed.

A Pivot Table is a highly flexible and user-friendly way to summarize and analyze large datasets in Excel. It allows you to count occurrences of each value, sort them, and quickly locate the first, second, or even third most common value, regardless of whether your data is numeric or text. This method is ideal for users who prefer built-in Excel features without entering formulas or using add-ins.

1. Select any cell in your dataset, then click Insert > Pivot Table. In the dialog, set your desired range and choose where to place the Pivot Table (e.g., a new worksheet).

2. Drag your data column (e.g., “Product Name” or “Values”) into both the Rows and Values areas of the Pivot Table Fields pane. Change the “Values” area  to “Count of [Column Name]”.

3. Click the drop-down arrow on the “Row Labels" column within the Pivot Table, and sort it from Largest to Smallest. The most frequent value appears at the top, the second most frequent on the next row.

Tips: This approach makes it easy to visualize the distribution of frequencies. You can also filter or refresh the table as your data changes. Note that blank entries will be counted as well, so you may want to filter those out for more accurate results.

Comparison: Unlike formula-based solutions, the Pivot Table method does not require complex syntax or advanced Excel skills, and is excellent for working with dynamic, growing datasets.

Find most common value from a list in Excel

Find the mode for text value from a list/column in Excel