Struggling to Count and Analyze Word Frequency in Excel?

QI Macros add-in can count words and phrases for you!

Analyze Text Tables using QI Macros:

  1. Select a table of text.
  2. Click on QI Macros menu > Data and Text Mining > Word Count Wizard.
  3. QI Macros will parse your text into one- and two-word phrases, and sort the counts for you.

QI Macros Word Count Wizards Count Unique Words, Two Word Phrases and More

Most data tracking systems contain note or comment fields for customer service reps, healthcare workers and even customers to input into. These fields contain interesting and valuable data, but it is often hard to analyze.

What are your options? You can spend hours reading through these comments and attempting to tally them. That can be grueling and fatigue will quickly set in. Or you can use a tool like QI Macros Word Count Wizard.

QI Macros Word count function parses the words out of sentences and paragraphs and uses PivotTables to count the occurrences of individual words and two word phrases. Then it displays the totals in a descending order for analysis and creating charts.


Here's how the QI Macros Word Count Wizard Works:

  1. Click and drag over your data to select it (omit the header row):

    count the number of times a word or phrase appears in Excel

  2. Click on the QI Macros menu, select the "Data & Text Mining" drop-down and chose either the "Word Count Wizard" or "Word/Number Count Wizard":

    word count excel menu

  3. QI Macros Word Count Wizard will create two PivotTables. One for single words and one for two-word phrases:

    Word Count Results
    Only counts and displays text

    example of word count results
    Word/Number Count Results
    Counts instances of text and numbers

    example of word and number count results

  4. From here you can get a much better sense of what the data says and you can even use QI Macros to draw a Pareto Chart directly from the PivotTable.
 
Note: In the rare case your workbook file name contains square brackets, rename your file before running the Word Count Wizard (i.e. from "file[1]" to "file-1"). See Excel PivotTable Error for further information from Microsoft.

To Expand the Wizard to Three, Four or More Word Phrases Follow These Steps:

Create the Data

  1. Click on the worksheet tab labeled "Words" and click on cell C2 to view the formula:
  2. word count formula in Excel

  3. Formulas for three and four word phrases are written for you in cells C2 and D2. If you want to count longer phrases then extend the formula to the number of words you want to include. For each word add: & " " & A5 then & " " & A6 then & " " & A7, etc.
  4. Next copy the formula in row 2 down to all of the subsequent rows populated in columns A and B.

Summarize with a PivotTable

  1. Go back to the Word Count worksheet and copy and paste columns D and E into columns G and H.
  2. Change the heading in G1 to "Count of Three-Word Phrases."
  3. Click anywhere in columns G or H and select PivotTable Analyze.

    Change the Data Source to include the column of data (i.e. column C) from the Words worksheet that you want to pivot:

    Excel 2021 - Office 365

    pivottable analyze


    change pivottable data source
  4. Next use PivotTable Tools/ Options/ or PivotTable Analyze to sort the PivotTable in descending order by the count of the phrases in column H:

    sort pivottable in Excel
  5. Now you have your new PivotTable:
  6. multiple pivottables in one spreadsheet

Note: Due to limitations within Microsoft Office, if you add 4 or more PivotTables to a single spreadsheet, Excel will crash.


Stop Struggling with Word Counts!
Start creating your Word Counts in just minutes.

Download a free 30-day trial. Get Word Counts now!

Other Charts Included in QI Macros Add-in for Excel