Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to find the most common value (mode) in a column in Excel and Google Sheets
Excel & Google Sheets
=MODE.SNGL(B2:B7)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select an empty cell where you want the mode to appear.
- 2Enter the formula =MODE.SNGL(range) in Excel or =MODE(range) in Google Sheets, replacing 'range' with your data range.
- 3Press Enter to calculate the most common value in the specified range.
Tips
- Ensure your data range does not include text or error values as these can cause errors.
- In Excel, use MODE.SNGL for a single mode; use MODE.MULT for multiple modes.
- Check for updates in Excel or Google Sheets as functions may evolve over time.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if there are multiple modes in my data?
In Excel, use MODE.MULT to return all modes. In Google Sheets, MODE will return one of the modes.
Can I use this formula on non-numeric data?
No, MODE functions work only with numeric data. For text data, consider using other methods like COUNTIF.
What happens if all values are unique?
The MODE function will return an #NUM! error in Excel and #DIV/0! in Google Sheets.