How to find the most frequent value in a column in Excel

Suppose we have a table of customer order records. We need to find out which city received the highest and lowest number of orders. To solve this task, we will use a formula with search and computation functions.



Finding the Most Repeated Value in Excel

Greatest and Least Repetition of Values

To visually demonstrate the formula's operation, let's use a schematic table of customer order records as an example:

Order Register

Now, let's perform a simple analysis of the most and least repeated values in the "City" column. To do this:

  1. First, find the most frequently repeated city names. Enter the following formula into cell E2:
    2. =INDEX(C2:C13, MATCH(MAX(COUNTIF(C2:C13, C2:C13)), COUNTIF(C2:C13, C2:C13), 0))
    Most Popular Value
  2. After entering the formula, be sure to press the CTRL+SHIFT+Enter keyboard shortcut because it needs to be executed as an array formula.
  3. To calculate the least frequently repeated city name, enter a similar formula into cell F2:
    4. =INDEX(C2:C13, MATCH(MIN(COUNTIF(C2:C13, C2:C13)), COUNTIF(C2:C13, C2:C13), 0))
Least Popular

The results of finding the names of the most and least popular customer cities in the order register are shown in the illustration:

Greatest and Least Repetition of Values

download file Download

If the table contains an equal number of the two most frequently repeated cities or the two least frequently repeated cities in the same column, then the first one encountered will be displayed.

The principle of finding popular values by repetition:

If you look at the formula syntax, you can easily notice that they only differ in the function name: =MAX() and =MIN(). All other formula arguments are identical. The COUNTIF() function counts how many times each city name is repeated within the range of cells C2:C16. This creates a conditional array of values in memory.

The MAX or MIN function selects the greatest or least value from the conditional array. The MATCH() function returns the position number in column C corresponding to the highest or lowest number of repetitions. The obtained value is passed as an argument to the INDEX() function, which returns the final result in the cell.