How to Find the Most Common Text Value in Google Sheets

How to Find the Most Common Text Value in Google Sheets - Hero

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.
    MODE.MULT Formula Entry
  2. The most commonly occurring value appears in the cell. In this case, it returns two values: 20 and 21.
    MODE.MULT Formula Result

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.
    INDEX Formula Entry
  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.
    INDEX Formula Results

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.



  1. Zelda

    February 28, 2024 at 5:28 pm

    Brian,Jeff and Adam all had 3 entrys… they were all “the most common”.

    Brian just happened to be the first name on the list with the matching 3 count of entries.

    • Jeff Butts

      February 29, 2024 at 8:58 am

      Hmmm good catch. It looks like INDEX only returns one value, the first one, even though MODE.MULT will return multiple values if there’s a tie. I’ll have to see if there’s some way to improve upon that.

      Thanks for bringing it to my attention!


Leave a Reply

Your email address will not be published. Required fields are marked *


To Top