Understand the MODE Function in Google Sheets

Find the most frequently occurring number in your spreadsheet

man drinking coffee while looking at Sheets on computer screen.

Jetta Productions / Blend Images / Getty Images

Google Sheets is a web-based spreadsheet that's easy to use and makes your documents available wherever you can access the internet. Because it isn't tied to a single machine, it can be accessed from anywhere and on any type of device. If you are new to Google Sheets, you'll need to learn several functions to get started. Here, we look at the MODE function, which finds the most frequently occurring value in a set of numbers.

Find the Most Frequently Occurring Value With the MODE Function

For the number set:

 1,2,3,1,4 

the mode is the number 1 since it occurs twice in the list and every other number appears only once.

If two or more numbers occur in a list the same number of times, they are, collectively, the mode.

For the number set:

 1,2,3,1,2 

both the numbers 1 and 2 are the mode since they both occur twice in the list, and the number 3 appears only once. In the second example, the number set is "bimodal."

To find the mode for a set of numbers when using Google Sheets, use the MODE function.

The MODE Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators and arguments.

The syntax for the MODE function is: =MODE (number_1, number_2, ...number_30)

  • number_1 – (required) the data included in calculating the mode
  • number_2 : number_30 – (optional) additional data values included in the mode calculation. The maximum number of entries allowed is 30.

The arguments can contain:

How to Use the MODE Function in Google Sheets

Open a new blank Google Sheets document and follow these steps to learn how to use the MODE function.

The MODE function only works with numerical data.

  1. Enter your data into a Google spreadsheet, and then click the cell you want to insert the MODE function into.

    Google Spreadsheet MODE function
  2. Type, "=MODE(" to start the formula.

    Google Sheets MODE function
  3. Select the cells with the data you want to analyze.

    You can select a range of cells by clicking each of them or clicking and dragging. To use an entire column whole column, either click on the column heading or type "[column label]:[column label]."

    Google Spreadsheet MODE function
  4. Close the parentheses once you've finished selecting cells, and then press Enter. The mode of the data you highlighted will replace the formula in the cell.

    If no value appears more than once in the selected range of data, an #N/A error appears in the function cell.

    Google spreadsheets MODE function
  5. The formula will update if you change the original set of data and it changes the mode.

How to Find Multiple Modes

The data you're analyzing may be polymodal – multiple numbers "tie" for appearing most frequently. If you use the MODE function, it will only return one of these numbers: the one closest to the top of the spreadsheet. Google Sheets has another formula that will pick out all of the modes. Here's how to use it.

  1. Instead of "=MODE(," type, "=MODE.MULT(" to begin your formula.

    You can also modify the formula by clicking on the cell and changing it in the entry bar at the top of the screen.

    Google spreadsheets MODE function
  2. Select the cells as usual, and then close the parentheses to complete the function.

    Google spreadsheet MODE function
  3. Now, when you press Enter, all of the modes in the set will appear on a separate line starting with the one in which you entered the formula.

    Google spreadsheets MODE function
  4. Google Sheets won't let you delete the entries in cells that don't contain the formula. But if you clear the cell with the function, it will remove all of the other modes.