Excel MODE function

Summary

The Excel MODE function finds the most frequently occurring number in a dataset. It’s a statistical function used to identify the mode, which is the value that appears most often in a data set. This function is particularly useful in data analysis for identifying common trends or values in a collection of data points.
Syntax
				
					=MODE(number1, [number2], ...)
				
			
  • number1: The first number, cell reference, or range in the dataset.
  • number2, …: [Optional] Additional numbers, cell references, or ranges, up to 255 arguments in total.
Return value
The mode of the given numbers. If there is more than one mode, MODE returns the smallest value. If no duplicate values are found, MODE returns an error.

How to use

Input directly numbers or specify cell references/ranges to analyze. The function will calculate and return the value that appears most frequently within this set. It’s important to note that MODE works with numeric data and will ignore text, logical values, and empty cells.

Examples

Simple MODE
Identifying the Most Common Score: To find the score that appears most frequently in a list of student scores.
				
					=MODE(A1:A10)
				
			
If A1:A10 contains a set of scores with one or more scores repeating, MODE returns the score that occurs most frequently. For example, if the scores are 70, 82, 95, 70, and 88, with 70 appearing twice, MODE will return 70 as the most common score.
MODE with Direct Values
Determining the Mode Among Specific Numbers: To calculate the mode of a directly specified set of numbers.
				
					=MODE(12, 15, 12, 18, 21, 15, 12)
				
			
This formula directly evaluates the list of numbers and identifies 12 as the mode, since it appears more frequently than any other number in the set.
MODE across Multiple Ranges
Finding the Mode Across Different Data Sets: To determine the most common value in non-adjacent ranges.
				
					=MODE(B2:B10, D2:D10)
				
			
Assuming B2:B10 and D2:D10 contain numeric data, MODE assesses both ranges and returns the most frequently occurring value across the combined data set.

Additional Notes

  • Excel has introduced two more functions, MODE.SNGL and MODE.MULT, to handle datasets with multiple modes. MODE.SNGL returns a single mode (the smallest value if there are multiple modes), similar to MODE, while MODE.MULT can return an array of all modes in a dataset.
  • If your data doesn’t contain any duplicates, MODE will return an error because it requires at least one number to appear more than once to calculate a mode.

Related Functions

Excel AVERAGE function

The Excel AVERAGE function calculates the mean of numbers, essential for summarizing central tendencies in data sets.

Excel MEDIAN function

The Excel MEDIAN function calculates the middle value in a set, ideal for identifying central tendencies in skewed distributions.

Excel FREQUENCY function

The Excel FREQUENCY function calculates how often values occur within a range of values, aiding in distribution analysis.

Content Navigation