How to get the most frequently appearing words in a column in Google Sheets

I love it when a Google Sheets blog post generates more curiosity and further questions. Apart from the almost endless source of new material to write about, it is great to see peoples curiosity for a topic deepen.

I wish I could reward you all for this curiosity every time, but sadly I seem to run out of hours of the day.

Fortunately, I have found a little time today to put more details into a question that came from Sue in a recent post Get the Occurrence of a Value in a Range as a Percentage in Google Sheets. She asked:

How would I modify this [the solution in the post] to get the value with the most occurrences?

I hastily wrote a solution but sadly ran out of time to provide any context to the solution. Further, on review of my solution, I discovered two much better approaches. This post answers Sue’s question more fully.

Sue, thanks for the inspiration for this article.

via GIPHY

… in a complete platonic Goat-human interactive way, of course. 🐐

In this tutorial, I will provide two solutions. Both are interesting ways to solve the problem and provide some great insight into how to use certain functions in Google Sheets. You can choose which one makes the most sense for you in your project.

You can use the table of contents below to jump straight to the solution or follow along to learn some cool stuff about the QUERY, FILTER, INDEX, COUNTIF, MAX, ARRAYFORMULA and UNIQUE functions in Google Sheets.

If you are playing along, you can get the sheet with just the data here for you to work on.

Data Sheet. 

Just go to File > Make a copy. So you have your own copy of the sheet to edit.

The problem

Our task is to display the most frequently occurring word or words in a list of words.

Take this list of mystery words, for example.

List of letters to determine most frequently occurring in Google Sheets v2

 

If we were to do a manual count we would discover that the value “puzzling” was the most frequently occurring as it occurs 7 times.

Incidentally, if it was just a list of numbers we could get the most commonly occurring number simply with the MAX function.

So how do we solve this with a Google Sheets formula?

The Solution

As we mentioned earlier, we have two interesting solutions this problem. One we solve primarily using FILTER around a bunch of other functions and one around using the QUERY function on a lovingly hand-crafted array.

The FILTER solution

most frequently occurring word in a column in Google Sheets FILTER solution
Click to Expand!

Here you replace A2:A with the column that you want to find the most frequently occurring cell item in. This can be a single letter or word or phrase.

Simply copy this formula into your Google Sheet and change the range.

To quickly change the range you can select the cell with your new formula in it and hit ctrl + h. The find and replace menu will appear. Let’s say you want to change your range to H4:H200. Your Find and replace menu should look like this:

find and replace a range in Google Sheets
Click to Expand!

You are all done.

But I am sure you are just itching to learn how it all works so you can tweak and change it to other projects. So read on!

Formula Breakdown.

Get a frequency count for each value

Our first task is to get a count of all the times each word appears in each cell. Essentially we want the total count of each word to appear next to the work.

To do this we use the COUNTIF function. This function takes a range as the first argument and a criterion to check as the second argument. If the criterion is true, it will count the value in the cell.

Now, if you have used COUNTIF before you may not have seen another range being added as a criterion. We can apply a criterion as a range because the FILTER function (more on FILTER later) allows for array conditions or ranges of cells to be calculated.

Essentially inside FILTER, this COUNTIF will loop through each cell and conduct a count if the current value in column A matches any other values in the entire range.

Most commonly occurring text in a cell with Google Sheets_Get count of each cell
Click to Expand!

We can now clearly see that “puzzling” is the most frequently occurring word, followed by “cryptic”.

Note! If you are playing along, you might have noticed the inclusion of ARRAYFORMULA in this formula. I’ve used this here to help simulate how the COUNTIF function actually works in the FILTER function when it iterates through the cells in the range. 

If you want to try it out yourself, your formula should look like this: 

Find the max frequency as a value


Next, we need to find the max value of our newly created range of count numbers for each word.

We can do this with the MAX function which conveniently takes a range of values or numbers and return the highest number in that range.

Here we can apply our COUNTIF function we created above so MAX can find the highest value. On ya! Max!

Again, if you are playing along, then you will need to apply the ARRAYFORMUAL function to COUNTIF for it to work on its own in order to simulate it in the FILTER function.

Your result should look like this:

Most commonly occurring text in a cell with Google Sheets_Find the max frequency as a value
Click to Expand!

Filter only those most frequent words in the list

Now we can use these formulas in a FILTER function to only show the word or words that appear the most in your Google Sheets range.

The FILTER function takes a minimum of two arguments:

  1. The range to be displayed.
  2. The conditions that are used to filter the data. The condition starts with a range and then a criterion.

In our FILTER formula we want to display A2:A. So that goes in the first argument.

Then in the next argument, we want to compare the count of each cell against the max value.

Our FILTER will then look like this:

Which would result in displaying all the times that the most frequently occurring cell, well… occurs.

Click to Expand!
What if there is more than one Frequently occurring cell value?

I’m glad you asked.

The great part of this formula is that it will take this into account and include any cell value that is also the most common in the range.

If you have a good memory, you might have noticed that the word, “cryptic” occurs 6 times. Let’s add another “cryptic” to cell A20 and see what happens.

Most commonly occurring text in a cell with Google Sheets_Filter only those most frequent words in the list more than one word
Click to expand!

Cool, hey?

Only show each most frequent word once

We don’t want to see repetitions of the words. We get it. They are the most common. Let’s do something about that with the UNIQUE function.

UNIQUE takes a range and displays any value that occurs in the range only once.

It is pretty cool in that it doesn’t discriminate if it is a number, date or text. It is also case sensitive.

Our final formula would now look like this (Keeping in mind that we have added “cryptic” now as a most frequent value).

Most commonly occurring text in a cell with Google Sheets_Only grabbing the values once
Click to expand!

Bonus! Sorting your results

Having come this far I have now realised that my example is woefully ill-prepared to present the sorting of our results alphabetically because it is, well… already that way.

So for the sake of this short example, let’s go a little wild and sort from Z to A.

We can do this by wrapping our formula in the SORT function.

Sort takes at least 3 functions:

  1. The range to sort – This will be the result of our formula.
  2. The column to sort by – There is only one column for us. So this will be ‘1’.
  3. Whether ascending or descending – Let’s go Z to A here.

Our formula will now look like this:

Which will look like this spectacularly topsy-turvy hellscape:

Most commonly occurring text in a cell with Google Sheets_Sort
Click to Expand!

Quickly now! Change the sort value to TRUE and we will speak no more of it.

The QUERY solution

Most commonly occurring text in a cell with Google Sheets with QUERY
Click to Expand!

Here you replace the A2:A with the range you want to run the formula. You will need to do this each of the three times this range occurs in the formula.

For a shortcut to do this check out the example in The FILTER solution chapter.

This approach has the benefit of being able to quickly display headers or even the count for each max value with simple changes to the query. More on this in the bonus material at the end.

Now I am sure that you are barely containing yourself to find out how this formula works so let’s get cracking.

Formula Breakdown.

This formula uses the QUERY Google Sheets function to show the most commonly occurring cell item in a range. To do this we build our own range based on the original A1:A range and a range generated from the total count of each cell value in the A1:A range as our second column.

We then dynamically insert the largest number of occurrences from this second column to only show those values that are most frequently used, before using the UNIQUE function to shot those values only once.

Let’s break it down into steps…

Get a frequency count for each value.

Our first task is to get the number of times each cell repeats in our list.

We did the same thing here as we did in the FILTER versions.

You can click on the link for the previous explanation.

Make an array containing both the mystery word list and the total count for each word

Next, we need to create a new array containing our original list of mystery words and their corresponding count. In Google Sheets, we can create an array or range of data with curly braces ({}). We create two columns by putting our data between a comma (,).

Here is what our array is going to look like:

Which will produce this result in your Google Sheet:

most frequently occurring word in a column in Google Sheets QUERY solution_get the array
Click to Expand!

Find the max frequency as a value

Just like in our FILTER version we need to get the highest value from our list frequency count for each cell value in the list.

Check out the explanation in the FILTER portion of this tutorial.

QUERY the newly built array where column two is equal to 7

We can finally get into the query. The QUERY function takes two mandatory arguments:

  1. The range to query. This is the two-column array we created.
  2. The query. This will be the query we will write below.

QUERY uses the Google Visualisation API Query Language. You can find some good use-case examples in the main docs.

Being a new language to learn it can take a little while to get the hang of, but one of the beauties of using the query language is that is pretty close to plain English to read.

So, let’s dive in.

Your first step is to ask what you want from the data. For us, we want to display the words in our first column where the total frequency count of the word is the highest.

In query language, our statement would look like this:

Here we select all cells in column 1  where column 2 (our frequency sum col) is equal to 7.

Why 7? This is just a placeholder for our dynamic max count value we created earlier. We know that our current max count is 7 for our most frequently occurring word. We will update this in the next step. For now, it is just easier to read this way.

If you have tried out QUERY before you might be scratching your head over the ‘Col’ word instead of say, a column letter like A, B, C etc. We use the ‘Col#’ syntax when we are not referencing a ‘physical’ range on the sheet but a generated range like our array.

Let’s put our QUERY together now:

Which will result in:

most frequently occurring word in a column in Google Sheets QUERY solution_the basic query
Click to Expand!

Adding the max frequency formula inside the query

One of the cool things about Google Sheets is that we can add dynamic values to strings of text in a cell. To insert a value or formula result in a string of text we use the ampersand character “&” .

Let’s go ahead and add our dynamic max frequency formula to our query.

most frequently occurring word in a column in Google Sheets QUERY solution_the basic query with dynamic input
Click to Expand!

Complete the formula by only selecting UNIQUE values

Finally, we can use the UNIQUE function to only display the words once.

most frequently occurring word in a column in Google Sheets QUERY solution_full formula result
Click to Expand!

BONUS! Expanding QUERY views for kicks

Another benefit of using QUERY is that we can quickly change how the data is displayed by making a few changes to the formula.

First off, for all of these examples, we will need to change our ranges to A1:A instead of A2:A. Go ahead and change this now (See if you can do it with find and replace).

All done? Let’s make some mods to our QUERY.

Add a header

The QUERY function has an optional third parameter that allows you to have a header. We can simply add one to our formula and we now have a header – keeping in mind that we have adjusted all of our ranges to start at A1, not A2.

And the result is:

most frequently occurring word in a column in Google Sheets QUERY solution_bonus header
Click to Expand!
Add the header and frequency count to the formula

Now that we have our headers in place, let’s add the frequency column so that we know how many times these max values have appeared.

All that we need to do here is to add Col2 to our SELECT stage. Don’t forget to separate the columns by a comma.

Which will now look like this:

most frequently occurring word in a column in Google Sheets QUERY solution_bonus header and col2
Click to Expand!

Those headers don’t look great. Especially the frequency column. Let’s do something about that.

Add the header and frequency count to the formula with header LABELS

We can use the query language LABEL clause to create custom labels for any column.

Let’s change column one’s header to read ‘Top Mystery Word/s’ and column two to read ‘Frequency’. To do this we add the LABEL clause followed by the column and then in single quotation ('   ') we add our new label. We separate each label group with a comma.

Our formula will look like this now.

And our end result is this:

most frequently occurring word in a column in Google Sheets QUERY solution_bonus header and col2 with labels
Click to Expand!

Isn’t it pretty?

Conclusion

If you would like the complete Google Sheet with all the examples you can get it here:

Get the most common words in a column – complete examples.googleSheet

If you have found the tutorial helpful, why not shout me a coffee? I'd really appreciate it.

I really love hearing how you use these projects or build on them in the comments below.

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

 

~Yagi

Update a range of dropdown lists in a Google Sheet dynamically based on a corresponding dropdown choice

I don’t often do this*, but I recently got a question on my YouTube tutorial, Update dropdown list in Google Sheets dynamically based on previous dropdown choice: Data Validation, about whether or not this process can be applied to a column range.

The short answer is yes. The long answer is that it is a bit ugly, but it works.

Let’s first clarify the problem.

*obviously not procrastinating before starting another big project 🤣🐐.

Continue reading “Update a range of dropdown lists in a Google Sheet dynamically based on a corresponding dropdown choice”

New course 🎉🎉🎉 Google Sheets: Learn the Essentials with Three Detailed Projects & 60% off 😃

It’s the big day!

I’m very excited to introduce you to my new course:

Google Sheets: Learn the Essentials with Three Detailed Projects

Whether you are looking to skill up, make your work-life easier or impress your boss then this course will take you from Google Sheets beginner to producing corporate-level Google Sheets with three detailed projects.

And because this is the opening week, I am offering a whopping 60% off the course for the next 7 days only.

Just use the Coupon Code…

NEWCOURSEDISCOUNT

…on the checkout page and you can start the course today.

CHECK OUT THE COURSE!

Google Sheets Project Main Page
Click to Expand!

First 5 Chapters Free

I have also opened up the first 5 Chapters of the course for you to try completely FREE.

Go on and give it a try now!

CLICK HERE!

Share the love

If you are already a Google Sheets legend, you know how important it is to learn how to master spreadsheets. If you know someone transitioning industries, just starting out or simply needing to boost their skillset please share this post.

This is a whopper of a discount and not one to miss.

The offer expires on Sunday the 9th May 2021.

DON’T MISS OUT!

Why I built the course

This has been a project I wanted to build for a long time. The best way to learn something is by getting hands-on and building a project.  That’s how I know I learn best.

I wanted to build a Google Sheets beginners to pro course that I wanted when I first started in the world of Google Sheets. Something with real-life examples and a set of projects that I can build and maybe even modify and reuse in the future. Something that not only gives me the how-toos but the why I should be building and designing my spreadsheets in a certain way.

Go on, check out the link to find out more about the course and try out the first 5 chapters on me. I know you are going to get a lot out of it.

FIND OUT MORE!

This offer expires on Sunday 9 May 2021.

~Yagi 🐐

Why can’t I edit this Google Sheet that was shared with me??!!! 😠

You’ve probably come to this page feeling pretty frustrated that you’ve received a link to a Google Sheet, Google Slide or Google Doc in an email or found a link to one of these documents in a website or even one of my tutorials, but you just can’t edit it. You can’t update the text and even half of the menu bar is greyed out!

Naaarggghhhh!!!!

What’s going on?

It means that you have been given only View permissions to this document.

Why?

Generally, it’s because any changes in a Google Workspace Document, (Sheets, Slides, Docs) will be displayed for anyone who has access to the document. So if you add or delete something in a document, then other editors, commentators and people with view-only access will see the change in near real-time.

Google Sheets Beginners: Sharing your Sheet (20)

There are three main reasons why an owner of a document will provide View-only access to users:

1. It might have only been meant for you to read-only.

Maybe you get an email from another department from work with a new policy that they have. You don’t really need to make edits to this. You just need to read it, right?

If you think this might be the case, but you want to be able to access the document in your personal folder, you can go to File > Add a shortcut to Drive. This will save a short cut to the file in your own Google Drive to help you to better organise your documents.

You still won’t be able to edit the document, but you don’t need to.

Also, the owner or the editors of the documents can also make copies of the document after you have made the shortcut if they need to.

2. It might be an example document or template.

In my tutorials, I often provide links to templates or example documents. Here again, I only ever provide view access.

However, you can create your own copy of these documents easily, by going to File > Make a copy. You will then be prompted to move the copy into a desired folder in your Google Drive.  Once done, a new window will appear in your browser. You will then have your very own copy of the document that is owned by you. You can now edit it and it will not affect the original document, because they are two separate documents.

3. The owner may have forgot to make you an editor.

Maybe the owner of the document was meant to give you edit permission but may have forgot.

Now before you get too excited, there is one thing you might want to check to save yourself some embarrassment. Go up to the top of your sheet and check to see if you are using the correct account for this sheet. You can do that in the top-right corner of the document and confirm you are using the right avatar.

Perhaps the owner has given you edit permission to the document in another one of your accounts, like a work account.

If you are in the wrong account, simply change to your correct account and check to see if you have edit permission.

If you are in the correct account and you are certain you should have edit permission, then you can select the View Only button and then click Request view access. An email will be sent to the owner and they can decide if you need edit access.

Why so much build-up to this last option? Well, you can imagine that it can be a little frustrating to get a bunch of emails from people who don’t actually need edit access.

Fortunately, there is a workaround to receiving a bunch of emails requesting edit access unnecessarily. Check out the tutorial below:

How to stop getting Google document requests to edit from users outside your organisation from publicly viewable files.

Check out the video:

 

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

Google Sheets Beginners: Getting Help (44)

One of the biggest revelations that I came across that really changed my whole view of learning was that learning how to ask the right questions or know where to get help completely transforms how you learn.

Knowing how to get help quickly and efficiently in Google Sheets is really going to cut away a lot of the frustration of finding the right information and get you there in a much more efficient way.

In the video below I will go over how to use all of Google Sheets help tools effectively and then discuss how you can research to find out how to build more complicated functions and processes in Google Sheets.

Check out the video below!

00:00 Intro
00:27 The help menu
01:13 Keyboard shortcuts
01:30 The help search bar
02:08 The Sheets Help tool
02:44 Understanding the help tool for functions
04:03 Preparing complex formulas with the Function List
05:14 How to ask the right questions

This is the last video in the Google Sheets beginners Youtube course. This is the first online course I have created. It was a long and rewarding journey. I’ve learned a lot along the way and I hope you got a lot out of the course.

If you enjoy the videoes and want to learn more about how to use Google Sheets, check out the Youtube Playlist:

Google Sheets Shorts: Beginners

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi