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.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.