How to calculate mode using excel

Use this mode calculator to easily calculate the arithmetic mode of a set of numbers.

Related calculators

What is a mode?

The mode is a descriptive statistic, used as a summary measure for a set of data, and it represents the most often occurring element of the set. For example, in the set of numbers 1, 2, 3, 3, 4, 6, 10 the mode is 3, as it occurs twice and all the other numbers occur just once. A set of numbers can be bimodal or multimodal – have two or more modes, for example the set 1, 2, 3, 3, 4, 6, 10, 10 will have modes 3 and 10 (confirmed by our mode calculator).

The mode is the most frequent value in a set and is one of the three most used statistics, the other ones being the arithmetic mean and median. From the three, the mode is usually the least affected by outliers and extreme values.

How to find the mode?

To find the mode of a set of numbers, follow these three steps:

  1. Order the numbers by value. This will result in all number of the same value being next to each other.
  2. Count how many numbers are there for each distinct value.
  3. Sort the counts from highest to lowest. The highest count is the mode.

Can there be more than one mode?

A complication arises if there are two or more equal counts in which case there can be two or more modes. A set with two modes is called bimodal, a set with more than two modes is called multimodal. Here is an example of a bimodal distribution of numbers:

How to calculate mode using excel

This example set of numbers contains the following numbers (sorted): 1, 2, 2, 2, 2, 3, 3, 4, 4, 4, 5, 5, 5, 5, 6, 6, 7.

Practical applications and examples

Mode calculation makes sense for non-numerical (e.g. categorical) data, like names or family names, professions, car models, etc. Most voting systems in the world have at their core the mode of the set of votes – counting who got the most votes, whose name appears most frequently in the set of votes cast, these are all different ways to describe the mode.

An example from the application of probability theory: if you know that the mode of a set is “5”, and you have to bet on what the value of a randomly drawn element from the set will be, the mode is your best bet. Using an online calculator you can easily calculate the arithmetic mode for even a large dataset.

The MOD function, short for modulo or modulus, divides numbers in Excel. However, unlike regular division, the MOD function only gives the remainder as an answer. Uses for this function in Excel include combining it with conditional formatting to produce alternate row and column shading, which makes it easier to read large blocks of data.

The information in this article applies to Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac.

MOD Function Syntax and Arguments

A function’s syntax refers to the layout of the function and includes the function’s name, brackets, and arguments.

The syntax for the MOD function is:

Number is the number being divided and Divisor is the number by which you want to divide the Number argument. The Number argument can be a number entered directly into the function or a cell reference to the location of the data in a worksheet.

The MOD function returns the #DIV/0! error value for the following conditions:

  • If a zero is entered for the Divisor argument.
  • If a cell reference to a blank cell is entered for the Divisor argument.

Use Excel's MOD Function

Enter data into the cells. To follow along with this tutorial, enter 5 in cell D1 and enter 2 in cell D2.

Select cell E1. This is where the results will display.

How to calculate mode using excel

Select the Formulas tab.

How to calculate mode using excel

Choose Math & Trig to open a drop-down list.

How to calculate mode using excel

Select MOD to open the Function Arguments dialog box.

How to calculate mode using excel

In the dialog box, place the cursor in the Number text box.

How to calculate mode using excel

Select cell D1 on the worksheet.

How to calculate mode using excel

In the dialog box, place the cursor in the Divisor text box.

Select cell D2 on the worksheet.

How to calculate mode using excel

Select OK in the dialog box.

The answer 1 appears in cell E1 (5 divided by 2 leaves a remainder of 1).

How to calculate mode using excel

Select cell E1 to see the complete function, =MOD( D1,D2), in the formula bar above the worksheet.

How to calculate mode using excel

Since the MOD function only returns the remainder, the integer portion of the division operation (2) is not displayed. To show the integer as part of the answer, use the QUOTIENT function.

How to calculate median in a range in Excel?

Sometimes we may need to calculate the median in a range data which may better reflect the median level of the data. Now I am talking about how to calculate the median in a range in Excel.

  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Calculate median in a range

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

There is a formula can help you quickly median the range.

Select a blank cell and type this formula =MEDIAN(A1:C6) (A1:C6 indicates the range you want to calculate median from), press Enter key, and then you can get the median in the cell.

Tip: If the data range is not continuous, you can use this formula =MEDIAN(A1:C3,E1:G3) ( A1:C3 and E1:G3 are the ranges you want to calculate the median from, and the comma indicates and. If you have three discontinuous ranges, you can use formula =MEDIAN(A1:C3,E1:G3, A5:D8)) .

Calculate median excluding zero in a range

Sometimes, if the data is zero, you do not want to calculate the median excluding zero, in this case, you need to use the below formula.

Select a blank cell and type this formula =MEDIAN(IF(A1:C6>0,A1:C6)) (A1:C6 indicates the range you want to calculate median from), press Ctrl + Shift +Enter keys in a meantime, and then you can get the median excluding zero in the cell.

Use the MODE function in Excel to find the most frequently occurring number in a list of numbers. Use MODE.MULT to find multiple modes.

1. The MODE function below returns the most frequently occurring number (8).

How to calculate mode using excel

2. The new MODE.SNGL function (SNGL stands for single) produces the exact same result.

How to calculate mode using excel

3. Change the value in cell A2 to 5. In this example, there are multiple modes (5 and 8). MODE and MODE.SNGL always return a single mode.

How to calculate mode using excel

4. If you have Excel 365, simply use the MODE.MULT function to find multiple modes.

How to calculate mode using excel

Note: the MODE.MULT function, entered into cell A8, fills multiple cells. Wow! This behavior in Excel 365 is called spilling.

If you don’t have Excel 365, execute the following steps to find multiple modes.

5. Select multiple cells.

How to calculate mode using excel

6. Enter the MODE.MULT function.

How to calculate mode using excel

7. Finish by pressing CTRL + SHIFT + ENTER.

How to calculate mode using excel

Note: the formula bar indicates that this is an array formula by enclosing it in curly braces <>. To delete this array formula, select the range A8:A10 and press Delete. In this example, there are only two modes (5 and 8) so we could have selected two cells at step 5.

8. Change the value in cell A6 to 3.

How to calculate mode using excel

9. The MODE function only works with numbers. Use INDEX, MODE and MATCH to find the most frequently occurring word in Excel.

When analyzing numerical data, you may often be looking for some way to get the “typical” value. For this purpose, you can use the so-called measures of central tendency that represent a single value identifying the central position within a data set or, more technically, the middle or center in a statistical distribution. Sometimes, they are also classified as summary statistics.

The three main measures of central tendency are Mean, Median and Mode. They all are valid measures of central location, but each gives a different indication of a typical value, and under different circumstances some measures are more appropriate to use than others.

How to calculate mean in Excel

Arithmetic mean, also referred to as average, is probably the measure you are most familiar with. The mean is calculated by adding up a group of numbers and then dividing the sum by the count of those numbers.

For example, to calculate the mean of numbers <1, 2, 2, 3, 4, 6>, you add them up, and then divide the sum by 6, which yields 3: (1+2+2+3+4+6)/6=3.

In Microsoft Excel, the mean can be calculated by using one of the following functions:

    – returns an average of numbers. – returns an average of cells with any data (numbers, Boolean and text values). – finds an average of numbers based on a single criterion. – finds an average of numbers based on multiple criteria.

For the in-depth tutorials, please follow the above links. To get a conceptual idea of how these functions work, consider the following example.

In a sales report (please see the screenshot below), supposing you want to get the average of values in cells C2:C8. For this, use this simple formula:

To get the average of only “Banana” sales, use an AVERAGEIF formula:

=AVERAGEIF(A2:A8, “Banana”, C2:C8)

To calculate the mean based on 2 conditions, say, the average of “Banana” sales with the status “Delivered”, use AVERAGEIFS:

=AVERAGEIFS(C2:C8,A2:A8, “Banana”, B2:B8, “Delivered”)

You can also enter your conditions in separate cells, and reference those cells in your formulas, like this:

How to calculate mode using excel

How to find median in Excel

Median is the middle value in a group of numbers, which are arranged in ascending or descending order, i.e. half the numbers are greater than the median and half the numbers are less than the median. For example, the median of the data set <1, 2, 2, 3, 4, 6, 9>is 3.

This works fine when there are an odd number of values in the group. But what if you have an even number of values? In this case, the median is the arithmetic mean (average) of the two middle values. For example, the median of <1, 2, 2, 3, 4, 6>is 2.5. To calculate it, you take the 3rd and 4th values in the data set and average them to get a median of 2.5.

In Microsoft Excel, a median is calculated by using the MEDIAN function. For example, to get the median of all amounts in our sales report, use this formula:

How to calculate mode using excel

To make the example more illustrative, I’ve sorted the numbers in column C in ascending order (though it is not actually required for the Excel Median formula to work):

In contrast to average, Microsoft Excel does not provide any special function to calculate median with one or more conditions. However, you can “emulate” the functionality of MEDIANIF and MEDIANIFS by using a combination of two or more functions like shown in these examples:

How to calculate mode in Excel

Mode is the most frequently occurring value in the dataset. While the mean and median require some calculations, a mode value can be found simply by counting the number of times each value occurs.

For example, the mode of the set of values <1, 2, 2, 3, 4, 6>is 2. In Microsoft Excel, you can calculate a mode by using the function of the same name, the MODE function. For our sample data set, the formula goes as follows:

How to calculate mode using excel

In situations when there are two or more modes in your data set, the Excel MODE function will return the lowest mode.

Mean vs. median: which is better?

Generally, there is no “best” measure of central tendency. Which measure to use mostly depends on the type of data you are working with as well as your understanding of the “typical value” you are attempting to estimate.

For a symmetrical distribution (in which values occur at regular frequencies), the mean, median and mode are the same. For a skewed distribution (where there are a small number of extremely high or low values), the three measures of central tendency may be different.

Since the mean is greatly affected by skewed data and outliers (non-typical values that are significantly different from the rest of the data), median is the preferred measure of central tendency for an asymmetrical distribution.

For instance, it is generally accepted that the median is better than the mean for calculating a typical salary. Why? The best way to understand this would be from an example. Please have a look at a few sample salaries for common jobs:

  • Electrician – $20/hour
  • Nurse – $26/hour
  • Police officer – $47/hour
  • Sales manager – $54/hour
  • Manufacturing engineer – $63/hour

Now, let’s calculate the average (mean): add up the above numbers and divide by 5: (20+26+47+54+63)/5=42. So, the average wage is $42/hour. The median wage is $47/hour, and it is the police officer who earns it (1/2 wages are lower, and 1/2 are higher). Well, in this particular case the mean and median give similar numbers.

How to calculate mode using excel

But let’s see what happens if we extend the list of wages by including a celebrity who earns, say, about $30 million/year, which is roughly $14,500/hour. Now, the average wage becomes $2,451.67/hour, a wage that no one earns! By contrast, the median is not significantly changed by this one outlier, it is $50.50/hour.

Agree, the median gives a better idea of what people typically earn because it is not so strongly affected by abnormal salaries.

This is how you calculate mean, median and mode in Excel. I thank you for reading and hope to see you on our blog next week!

You can use the following formulas to find the mean, median, and mode of a dataset in Excel:

It’s worth noting that each of these formulas will simply ignore non-numeric or blank values when calculating these metrics for a range of cells in Excel.

The following examples shows how to use these formulas in practice with the following dataset:

How to calculate mode using excel

Example: Finding the Mean in Excel

The mean represents the average value in a dataset.

The following screenshot shows how to calculate the mean of a dataset in Excel:

How to calculate mode using excel

The mean turns out to be 19.11.

Example: Finding the Median in Excel

The median represents the middle value in a dataset, when all of the values are arranged from smallest to largest.

The following screenshot shows how to calculate the median of a dataset in Excel:

How to calculate mode using excel

The median turns out to be 20.

Example: Finding the Mode in Excel

The mode represents the value that occurs most often in a dataset. Note that a dataset can have no mode, one mode, or multiple modes.

The following screenshot shows how to calculate the mode(s) of a dataset in Excel:

How to calculate mode using excel

The modes turn out to be 7 and 25. Each of these values appears twice in the dataset, which is more often than any other value occurs.

How to calculate mode using excel

This tutorial demonstrates how to use the Excel MODE Function in Excel to calculate the most common number.
MODE Function Overview
The MODE Function Calculates the most common number.

To use the MODE Excel Worksheet Function, select a cell and type:

(Notice how the formula inputs appear)

MODE function Syntax and inputs:

array – An array of numbers.

What is the mode?

The mode is the most repeated number in a group of numeric data. It also represents the central tendency of a data set, similar to mean or median.

MODE function in Excel

The MODE function takes a range of data and returns the most frequently occurring number.

How to calculate mode using excel

If there is no mode in a group of data, the MODE function will return #N/A

How to calculate mode using excel

Empty Cells or Cells With Text

The MODE Function ignores cells that are empty. However, it will return #N/A error if there are non-numeric data in the group.

How to calculate mode using excel

Important: The MODE Function will return the same error for numbers stored as text. To use the MODE Function with numbers stored as text, first, use the VALUE Function to convert the numbers stored as text to actual numbers.

Let’s say you want to find out the most common number of bird species sighted in a sample of bird counts at a critical wetland over a 30-year time period, or you want to find out the most frequently occurring number of phone calls at a telephone support center during off-peak hours. To calculate the mode of a group of numbers, use the MODE function.

MODE returns the most frequently occurring, or repetitive, value in an array or range of data.

Important: This function has been replaced with one or more new functions that may provide improved accuracy and whose names better reflect their usage. Although this function is still available for backward compatibility, you should consider using the new functions from now on, because this function may not be available in future versions of Excel.

For more information about the new functions, see MODE.MULT function and MODE.SNGL function.

Syntax

The MODE function syntax has the following arguments:

Number1 Required. The first number argument for which you want to calculate the mode.

Number2. Optional. Number arguments 2 to 255 for which you want to calculate the mode. You can also use a single array or a reference to an array instead of arguments separated by commas.

Remarks

Arguments can either be numbers or names, arrays, or references that contain numbers.

If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

Arguments that are error values or text that cannot be translated into numbers cause errors.

If the data set contains no duplicate data points, MODE returns the #N/A error value.

The MODE function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:

Average which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.

Median which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.

Mode which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

For a symmetrical distribution of a group of numbers, these three measures of central tendency are all the same. For a skewed distribution of a group of numbers, they can be different.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.