Frequency Count for Top 10 Words

Ella has a worksheet that, in column A, contains a huge number of text values. She would like to determine the top 10 words that are in the column, along with the count for each of those words. A word may be the only thing in a cell, or a cell could contain multiple words, similar to a sentence. This makes it unclear for Ella as to how she should approach the task.

There are a couple of ways you can approach the task. First, if you are using Excel 365 you can use a formula to generate the frequency count:

=LET(
  src, LOWER(TRIMRANGE(A:A)),
  w, TOCOL(
       TEXTSPLIT(TEXTJOIN(" ", 1, src),, " "),
       3),
  u, UNIQUE(w),
  TAKE(
    SORT(
      HSTACK(u, MAP(u, LAMBDA(x, SUM(--(w = x))))),
      2, -1),
    10))

I've tried to split out the lines in this formula so it is easier to understand, but it is still a single formula. The src variable refers to the lowercase version of the cells containing data in column A, the w variable is an array of just the words in that column, and the u variable is the unique words. The portion of the formula beginning with the TAKE function sorts the words by frequency in descending order and displays just the first 10 of the words.

For simple lists, this will work fine, but you need to be aware that if there are punctuation marks following words, then they are considered part of the words they follow. Thus, "abc" (without a comma following it) is different than "abc," (with a comma) or "abc." (with a period).

The bigger issue, though, is that the TEXTJOIN function, used to combine all the source cells into a single string, has an upper limit of 32,767 characters. Thus, if Ella's list in column A is as huge as she indicates, the result of the formula may be an error. The best way to deal with this potentiality—as well as to get a result in older versions of Excel—is to rely on a macro to do the analysis. The following macro provides an example:

Sub Top10Words()
    Dim dict As New Collection
    Dim rSource As Range
    Dim cell As Range
    Dim sIgnore As Variant
    Dim sIg As Variant
    Dim bIgnore As Boolean
    Dim words As Variant
    Dim w As Variant
    Dim i As Long
    Dim j As Long
    Dim arr() As Variant
    Dim cutoff As Long
    Dim s As String
    Dim sTemp As String
    Dim c As String
    Dim v As Variant
    Dim t1 As Variant
    Dim t2 As Variant

    ' Words to ignore
    sIgnore = Array("the", "and", "of", "to", "a", "in", "for", "on", _
      "at", "with", "is", "it", "this", "that", "as", "be", "by", "an", _
      "or", "from", "was", "were", "are", "but", "not", "so", "if", _
      "then", "than", "too", "can", "could", "would", "should", "has", _
      "have", "had", "do", "does", "did", "will", "shall", "may", _
      "might", "must", "also", "just", "about", "into", "out", "up", _
      "down", "over", "under", "again", "still", "only")

    ' Define range in column A
    Set rSource = Range("A1", Cells(Rows.Count, "A").End(xlUp))

    For Each cell In rSource
        If Len(cell.Value) > 0 Then
            words = Split(LCase(CStr(cell.Value)), " ")
            For Each w In words
                s = CStr(w)

                ' Keep letters only
                sTemp = vbNullString
                For i = 1 To Len(s)
                    c = Mid(s, i, 1)
                    If c >= "a" And c <= "z" Then sTemp = sTemp & c
                Next i

                If Len(sTemp) > 0 Then
                    ' Check to see if word should be ignored
                    bIgnore = False
                    For Each sIg In sIgnore
                        If sTemp = sIg Then
                            bIgnore = True
                            Exit For
                        End If
                    Next sIg

                    If Not bIgnore Then
                        ' Check if word is already in list
                        On Error Resume Next
                        v = dict.Item(sTemp)
                        If Err.Number = 0 Then
                            ' Word is in list, so increment count
                            Err.Clear
                            dict.Remove sTemp
                            v(1) = CLng(v(1)) + 1
                            dict.Add v, sTemp
                        Else
                            ' Word not in list, so add it
                            Err.Clear
                            dict.Add Array(sTemp, 1), sTemp
                        End If
                        On Error GoTo 0
                    End If
                End If
            Next w
        End If
    Next cell

    ' Add headers
    Range("C1:D1").Value = Array("Word", "Count")
    If dict.Count > 0 Then
        ' Move collection items to array (word, count)
        ReDim arr(1 To dict.Count, 1 To 2)
        For i = 1 To dict.Count
            arr(i, 1) = dict(i)(0)
            arr(i, 2) = CLng(dict(i)(1))
        Next i

        ' Sort array by count descending
        For i = 1 To UBound(arr, 1)
            For j = i + 1 To UBound(arr, 1)
                If arr(j, 2) > arr(i, 2) Then
                    t1 = arr(i, 1): t2 = arr(i, 2)
                    arr(i, 1) = arr(j, 1): arr(i, 2) = arr(j, 2)
                    arr(j, 1) = t1: arr(j, 2) = t2
                End If
            Next j
        Next i

        ' Output Top 10
        cutoff = 10
        If UBound(arr, 1) < 10 Then cutoff = UBound(arr, 1)
        For i = 1 To cutoff
            Range("C" & i + 1).Value = arr(i, 1)
            Range("D" & i + 1).Value = arr(i, 2)
        Next i
    End If
End Sub

When you run the macro, it puts the words and frequency counts into columns C and D. It also allows you to specify as many words as you'd like that can be ignored. (It is common to ignore words such as "a" or "an.") The macro steps through each cell in column A and each word in those cells. The word is converted to lowercase, has any non-letter characters removed, and is checked against the words you want ignored. Assuming that the word still passes that test, it is compared against the dict collection which is used to aggregate the words.

Once done with all the cells, the macro moves the collection into an array, sorts the array, and then outputs just the top 10 words based on frequency.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10193) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.