Excel Tip

MODE Function

The MODE function calculates and returns the most frequently used value in a given cell range. This can be useful when you want to determine a trend in your data. Knowing the mode in a given timeframe can help you determine workload predictions and resource allocation.

The Excel 2010 or higher versions offer MODE.SNGL function, which is equivalent to the original MODE function in older Excel versions. The syntax is very simple:

= MODE.SNGL (array)

array - the range of cells from which to extract the most frequently occurring value

The MODE function analyzes a given cell range and allows you to find the most repetitive value used. Two notes worth mentioning are:

  1. MODE.SNGL function only returns one mode. MODE.MULT function can be used to return multiple modes in a data set.
  2. If there are no repetitive values in a data set, Excel will return #N/A which is an error message meaning not applicable.

Jiazhi Ma

Information, Analytics and Systems, UCOP