How-To

How to Find the Most Common Text Value in Google Sheets

By Jeff Butts

Updated February 29, 2024

If you ever wanted to know the most common text value that appears in your spreadsheet, Google Sheets can help you do that easily.

Spreadsheets are awesome tools for data analysis, whether you’re using Apple’s Numbers, Microsoft Excel, or Google Sheets. You can quickly get sums and averages, calculate percentages, and countless other calculations. You can even count how many checked or unchecked checkboxes you have in your worksheet. Did you know, though, that you can also find the most common text or other value used in Google Sheets? If you want to know how to find the most repeated text or number in your Google Sheets spreadsheet, read on.

Tracking What Value You Use the Most in a Google Sheets Spreadsheet

Through the power of the MODE function and its derivatives, you can quickly see what number or text value shows up the most frequently in your Google Sheets worksheet. MODE is a premade function that will return the most common value or values.

In its basic form, MODE.SNGL, the function returns the single most common value. It’s important to note that if all values occur only once, MODE will return an error. The syntax for using MODE is:

MODE(value1, [value2, ...])

In practical use, value1 is the first value or range you want the function to evaluate, and the rest is optional. These values will typically be numbers but could include averages calculated from other cells in your spreadsheet. The MODE function can normally take up to 30 arguments for values or ranges, but Google Sheets supports more than that.

Let’s dive into using this function to quickly learn the most commonly appearing values in our worksheet.

How to Use MODE to Find the Most Commonly Occurring Value

In our example data, we’ve got a spreadsheet of game winners and their winning scores. We want to see what the most common winning score was, reflected by the values in column B. We’ll use MODE.MULT, just in case there’s a tie in the frequency of winning scores.

1. In the field we want the most commonly occurring value to appear, enter the function =MODE.MULT(B2:B11). As you can see, this includes all of the entered scores. Press Enter/Return.
   
2. The most commonly occurring value appears in the cell. In this case, it returns two values: 20 and 21.
   

If you get an error or the result appears strange (like a time instead of a numeral), make sure your cell is formatted for the right type of data. When I first entered this function, Google Sheets thought the cell I used should be in a date/time format because the rest of the column included dates.

How to Find the Most Common Text Value in Google Sheets

If you want to use MODE to find the most common text value, you have to nest it within the INDEX function, along with the MATCH function. Google Sheets supports a wide variety of functions, and nesting them is easy (albeit sometimes confusing).

1. In your worksheet, click on the cell where you want the result to appear. Enter the function =INDEX(A2:A11,MODE.MULT(MATCH(A2:A11,A2:A11,0))) and press Enter/Return.
   
2. As you can see from our sample data, Brian was the most frequent winner of our little games.
   
   Note: Unlike when using MODE.MULT on its own, this formula doesn’t return multiple values. If two or more text values are tied for appearing most frequently, only the first will appear.
   

Harnessing the Power of Google Sheets

As you can see, Google Sheets is a robust spreadsheet alternative that is capable of so much more than just adding up numbers. You can do the basics, sure, but the app is also prepared to help you with just about any other form of data analysis you want to do based on the values in your spreadsheet.