The MODE guide
=MODE(C2:C13) returns the number that appears most often in the range. It answers "which size, price or score comes up most?", a different question from the average or the median.
The syntax
=MODE(number1, [number2], ...)
Usually one range. MODE.SNGL is the newer name for the same function and gives the same answer. Empty cells, text and TRUE/FALSE in a range are skipped.
Mode, median or average?
For twelve shoes sold in sizes 38 to 44, the average comes out at 40.33 and the median at 40.5. Neither is a size anyone can buy. The mode is 41, the size that sold four times, and that is the one to reorder first. Use the mode when the answer has to be one of the values you have: a size, a product code, a number of seats.
How often?
MODE gives the value, not how many times it appears. Count that with =COUNTIF(C2:C13, F2), pointing at the cell that holds the mode so the count follows it when the data changes.
When MODE returns #N/A
- No value repeats. If every number appears once, there is no mode.
- The range holds text. MODE only counts numbers, so a column of sizes like S, M and L gives #N/A. For the most common text, count each item with COUNTIF in a helper column and take the one with the largest count.
Ties
When two values appear equally often, MODE returns the one that comes first in the range. MODE.MULT returns all of them as a list, in Excel versions with dynamic arrays.
Common mistakes
- Pointing at the text column. MODE on the styles instead of the sizes gives #N/A.
- COUNT instead of COUNTIF. COUNT counts every number in the range; COUNTIF counts the ones that match.
- The count in the mode cell. 4 is how often size 41 sold, not the size.
The other side of "typical": MEDIAN vs AVERAGE.
FAQ
- How do I find the most common number in Excel?
- Use =MODE(range). It returns the number that appears most often. MODE.SNGL does the same.
- Why does MODE return #N/A?
- Either no number appears more than once, or the range holds text. MODE only works with numbers.
- How do I count how many times the mode appears?
- Use COUNTIF on the same range with the mode as the condition: =COUNTIF(C2:C13,F2), where F2 holds the MODE formula.
- What does MODE return when two values tie?
- The one that appears first in the range. MODE.MULT returns every value that ties for most common.



































































