Word Frequency In Excel

[Giorgio Aguilar]

MicrosoftExcel is a fantastic tool for working with numbers, but it can become challenging when it comes to counting specific words in your spreadsheets. If you've ever struggled with Excel's complex formulas for this task, there's an effective solution - creating a custom function that makes text frequency analysis a breeze.

Excel offers various approaches to retrieve a list of unique values and get the count of specific text within the target range. However, these methods often require the use of intricate Excel functions, which might not be intuitive and user-friendly for everyone.

Tip. If you plan to use this function frequently, consider storing it in an add-in file rather than your current workbook. This way, you can access the function from any Excel file. Here are the detailed instructions on creating and using add-ins to store custom functions in Excel.

Tip. Unfortunately, Excel doesn't display tooltips for custom function arguments like it does for built-in functions. However, there's a little trick to display argument names. Simply enter the name of the custom function and then press the Ctrl + Shift + A shortcut. For more details, see how to display tooltips for custom VBA functions in Excel.

In Dynamic Array Excel, FreqWord behaves like a dynamic array function, i.e. you enter a formula in one cell and it returns an array of values into neighboring cells.Just make sure there are enough empty cells down and at least one empty column to the right to output the results, otherwise you may get a #SPILL error.

Using FreqWords function in Excel 2019 - 2007In pre-dynamic versions of Excel, you need to use the FreqWords function as an array formula, which requires a slightly different approach:

For more information, see How to enter an array formula in Excel.Now that you understand how to use the FreqWords function, let's look at some real-life scenarios where it can simplify your Excel data work.

How to count frequency of words in ExcelLet's assume you have a list of addresses in A2:A25. To find out which words most often occur in this list, enter this formula in C3:

After hitting Enter, you will receive a list of all unique words and the number of times each word appears in the source range. The words will be displayed in column C, and their corresponding frequencies will be in column D.In Excel 365 and Excel 2021, the formula returns an array of values in multiple cells. However, in Excel 2019 and earlier versions, a normal formula will return just a single value. To obtain a list of values, be sure to enter it as an array formula as explained above.

How to do word frequency analysis with custom delimiterOften, when importing data into Excel from other programs or CSV files, values are not separated by spaces but by special characters such as commas, semicolons, colons, slashes, and more.

The result of this calculation is a frequency table of words based on your specified delimiter:How to count frequency of text within one cellIn case you need to count text frequencies within a single cell, simply supply that cell address for the first argument of the FreqWords function:

Let's consider a scenario where you have two lists with similar items but in different orders. Moreover, the items in these lists may have different word arrangements, such as "chocolate milk" and "milk chocolate". The question is: are these lists identical or different?The solution involves two simple steps.

By applying these formulas, you obtain the word count for each list. Examining the results, as shown in the screenshot below, you can conclusively determine that the two lists are different.Step 2: Compare word frequencies in two lists

To accomplish this task, you need to place the word frequencies side by side. In our example, since List 2 contains more unique words, we'll consider it the main list and pull the frequencies from List 1 to the adjacent column using the XLOOKUP function.

To make the differences even more apparent, you can add labels such as "match" and "don't match" in a separate column. This can be achieved using the following IF statement that compares the word counts in columns I and J:

As a result, you'll get the same word frequencies highlighted in both lists, allowing you to visually distinguish matches and differences.To wrap it up, analyzing text frequencies in Excel doesn't have to be tough anymore. With the custom FreqWords function and the Compare Tables tool, it becomes super simple! Whether you're working on textual analysis, data mining, or simply trying to figure out the patterns in your spreadsheet, these tools together will make your tasks smoother and more understandable. Just give them a try and see the difference they can make.

Alexander. In your FreqWords() function you only remove special character in the If Delimiter = "" Or Delimiter = " " Then Delimiter = " " portion of the IF statement but not the ELSE portion. I don't understand why special characters would be dependent on the delimiter or is there something I'm not understanding.

Thanks

Dear Alexander, thanks for the post. It works perfectly for 1 word analysis, but in my case, I analyse ingredient lists.

I have 1 ingredient per cell. Each ingredient can be 1 word (i.e. salt) or 2 words (i.e. white sugar).

Would it be possible to do this without removing the space between "white" and "sugar"? I am just guessing that if I delete spaces between words and prepare the table without spaces (i.e. "WhiteSugar", it should work. But the result is less user-friendly.

This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. Excel 365 has many new functions that are really useful, I am using the REDUCE function combined with the LAMBDA and VSTACK functions here.

Hello Oscar,

first of all MERRY CHRISTMAS AND HAPPY NEW YEAR 2012- also Thank you very much for Word Frequency function.

I would like to ask if it is possible to convert it to a VBA Macro subroutine . I mean is it possible to use Freqwords function with in a sub(). I am novice to programming . please help.

Thank you very much sir.

Srinivas

One more thing i would like to ask is that

How can we identify any protected excel file before opening using macro VBA (e.g. i have a folder with multiple excel files i need to find out using macro how many files are password protected before opening and it should give result on separate workbook.)

is it possible using excel vba macro

My requirement is to copy data from ms excel and paste into ms word on specific position

>Both the files should get open automatically specially ms word

>macro should identify the user define cursor position in ms word file

>then paste the data where user wants to

>In this way i want to copy tables data, text, from excel into ms word on user define posion.

Hi great formula!

Quick question - my apologies in advance if it is dumb I typically do not work in Excel.

When I define the array for the results return I am assuming I know the number of unique words I have (in your example you knew there are 27 unique values?). But what happens if I don't know the number of unique words? I selected as an array the whole column but I get many #N/A and then it very inconvenient because I cannot delete these (it tells me I cannot change the array).

Is there a way for it automatically to decide the size of the array?

Much like Julie, I mod'ed the formula for a single column, removed the array because I am using Excel 365 and am getting the #NAME? error. I double checked the formula call to the function name and it reads the same. Not sue what else to do...

I am wanting to export a search of frequency list of words into an excel file to then make notecards. When I try to copy and paste from the analysis tab it doesn't copy correctly into an excel spreadsheet. Any suggestions?

I assume you mean it does't place the Greek words in a separate cell from the rest of the content? If so, this is tweaked in 9.6 (currently in beta) and should work better. I just tested with your example (or something similar to your example) and get the following:

As a tip (if you didn't already know), when using the count command on a custom range of verses, be sure to use the [RANGE ] command, and not the drop down menu of your custom ranges. Using the latter will constrain the hits to only that range, and not the entire GNT, which is usually what most people want with this type of search.

I have a question similar to brenshaw833's. First of all, what are the search commands to export the list of words like you did? Second, is there a way to have Accordance sort the list by the number of times each word occurs? For example, first list all the words that appear once, then twice, and so on.

If you open the Analysis Word Counts after your search, you have the option of sorting by count up or count down (Brenshaw's initial post in this thread shows alphabetical, but you can use the pull down menu to select other options).

If you are interested in lower frequency words only (i.e. only those that occurs 1-30 times in the NT) then just change the count accordingly. This is where the type of range you use becomes important as mentioned by Rick. If you use the range command [COUNT 1-99999] [RANGE Jn] it uses the frequency of the word across the NT. If you use the range drop down it uses the frequency in that particular selected range. These therefore give different results.

I couldn't find a solution for this calculation. I have one column with product titles as values. Now I have to count the frequency of repeating words. It can be limited to the top 100 repeated words. Every month new product titles are added with new or existing words. Therefore the output has to be dynamic. The product titles are all different. Example:

@PhilipTreacy wow, appreciate your effort to help and the solution you created, thank you! This would work yes. Unfortunately the sum of words in titles in the future will be in million range. So the solution with a column for words would cause performance issues. I'll try the word cloud and see how this works. But at least your solution could help in a future smaller case.

3a8082e126