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 in Excel and Google Sheets
Topic:AVERAGE & central tendencyExcel & Google Sheets
=MODE(B2:B6)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
- 1For a numeric range, type =MODE(B2:B6) and press Enter.
- 2MODE returns the most frequently occurring number; if all values are unique it returns #N/A.
- 3To find the most common text value, use: =INDEX(A2:A6,MATCH(MAX(COUNTIF(A2:A6,A2:A6)),COUNTIF(A2:A6,A2:A6),0)) as an array formula.
- 4In Excel 365+, =MODE.MULT returns all modes if there are ties.
- 5In Google Sheets, MODE works identically for numbers.
Tips
- MODE only works on numbers. For text, use the INDEX/MATCH/COUNTIF array formula.
- MODE.SNGL is the Excel 2010+ equivalent of MODE and behaves identically.
- MODE.MULT returns all tied modes as a vertical spill array in Excel 365.
- If all values are unique, MODE returns #N/A — handle with IFERROR.
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
How do I find the most common text value?
Use =INDEX(A2:A6,MATCH(MAX(COUNTIF(A2:A6,A2:A6)),COUNTIF(A2:A6,A2:A6),0)). In Excel 365, this is entered normally; in older Excel, press Ctrl+Shift+Enter.
What if there are multiple modes (ties)?
MODE/MODE.SNGL returns the first mode encountered. Use MODE.MULT in Excel 365 to get all tied modes.
More on AVERAGE & central tendency
See all →How-to
How to calculate an average
Use the AVERAGE function to calculate the arithmetic mean of a range of numbers in Excel or Google Sheets.
How-toHow to average ignoring zeros
Use AVERAGEIF with a "<>0" criterion to calculate an average that excludes zero values from the calculation.
How-toHow to average ignoring blank cells
AVERAGE naturally ignores blank cells; use AVERAGEIF with "<>" to also ignore text placeholders like dashes.
How-toHow to average with a condition
Use AVERAGEIF to compute the mean of values in one column where a corresponding column meets a specified criterion.
How-toHow to calculate a moving average
Compute a rolling average for each row using AVERAGE with a fixed-window OFFSET or INDEX formula anchored to each row.
How-toHow to find the median of a range
Use the MEDIAN function to find the middle value in a dataset — the value that half the data falls above and half below.