How to find the mode for text value from a list/column in Excel?

As you know, we can apply the MODE function to quickly find out the most frequent number from a specified range in Excel. However, this MODE function does not work with text values. Please do not worry! This article will share two easy methods to find the mode for text values from a list or a column easily in Excel.

Mean, median and mode are all measures of central tendency in statistics. In different ways they each tell us what value in a data set is typical or representative of the data set. The mean is the same as the average value of a data set and is found using a calculation. Add up all of the numbers and divide by the number of numbers in the data set. When the total number of supplied numbers is even, the median is calculated as the average of the two numbers in the middle. Cells that contain text, logical values, or no value are ignored. Numbers can be supplied as numbers, ranges, named ranges, or cell references that contain numeric values. The arithmetic means, mode and median are the base of any statistical analysis of data. We have already discussed how we can calculate MODE and MEDIAN in Excel. In this tutorial, we will learn how to calculate mean in excel. Mean is nothing but the average of data. A given set of data is added and divided by total numbers.

• Find the mode for text values from a list /column with Kutools for Excel

Find the mode for text values from a list/column with formula

This method will introduce an array formula to find the mode for text values in a list in Excel. Please do as follows:

Select a blank cell you will place the most frequent value into, type the formula =INDEX(A2:A20,MODE(MATCH(A2:A20,A2:A20,0))) (A2:A20 is the list where you will find out the most frequent (mode for) text value from) into it, and then press the Ctrl + Shift + Enter keys.

Now the most frequent (mode for) text value has been found and returned into the selected cell. See screenshot:

Find the mode for text values from a list /column with Kutools for Excel

If you have Kutools for Excel installed, you can apply its Find most common value formula to quickly find out the most frequent (mode for) text values from a list without remembering any formulas in Excel.

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Free Trial Now!

1. Select a blank cell where you will place the most frequent (mode for) text value, and click Kutools > Formula Helper > Lookup & Reference > Find the value that appears most often. See screenshot:

2. In the opening Formula Helper dialog box, please specify the list where you will look for the most frequent text value into the Range box, and click the Ok button.

And then the most frequent (mode for) text value has been found and returned into the selected cell.

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Get It Now

Demo: Find the mode for text values from a list /column

Related articles:

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

• To post as a guest, your comment is unpublished.
  the names list in text format, text to column does not work. i tried bunch of stuff. please help.

The median is one of the three main measures of central tendency, which is commonly used in statistics for finding the center of a data sample or population, e.g. for calculating a typical salary, household income, home price, real-estate tax, etc. In this tutorial, you will learn the general concept of the median, in what way it is different from arithmetic mean, and how to get median in Excel.

• How to find median in Excel

What is median?

In simple terms, the median is the middle value in a group of numbers, separating the higher half of values from the lower half. More technically, it is the center element of the data set arranged in order of magnitude.

In a data set with an odd number of values, the median is the middle element. If there are an even number of values, the median is the average of the middle two.

For example, in the group of values {1, 2, 3, 4, 7} the median is 3. In the dataset {1, 2, 2, 3, 4, 7} the median is 2.5.

Compared to the arithmetic mean, the median is less susceptible to outliers (extremely high or low values) and therefore it is the preferred measures of central tendency for an asymmetrical distribution. A classic example is a median salary, which gives a better idea of how much people typically earn than an average salary because the latter may be skewed by a small number of abnormally high or low salaries. For more information, please see Mean vs. median: which is better?

Excel MEDIAN function

Microsoft Excel provides a special function to find a median of numeric values. The syntax of the MEDIAN function is as follows:

Where Number1, number2, … are numeric values for which you want to calculate the median. These can be numbers, dates, named ranges, arrays, or references to cells containing numbers. Number1 is required, subsequent numbers are optional.

In the recent versions of Excel 2016, 2013, 2010 and 2007, the MEDIAN function accepts up to 255 arguments; in Excel 2003 and earlier you can only supply up to 30 arguments.

4 facts you should know about Excel Median

• When the total number of values is odd, the Excel MEDIAN function returns the middle number in the data set. When the total number of values is even, it returns an average of the two middle numbers.
• Cells with zero values (0) are included in calculations.
• Empty cells as well as cells containing text and logical values are ignored.
• The logical values of TRUE and FALSE typed directly in the MEDIAN function's arguments are counted. For example, the formula MEDIAN(FALSE, TRUE,2,3,4) returns 2, which is the median of the numbers {0, 1, 2, 3, 4}.

How to calculate median in Excel - formula examples

MEDIAN is one of the most straightforward and easy-to-use functions in Excel. However, there are still some tricks, not obvious to beginners. Say, how do you calculate a median based on a condition? The answer is in one of the following examples.

Excel Median formula

For starters, let's see how to use the classic Median formula in Excel to find the middle value in a set of numbers. In a sample sales report (please see the screenshot below), supposing you want to find the median of numbers in cells C2:C8. The formula would be as simple as this:

=MEDIAN(C2:C8)

As shown in the screenshot above, the Excel Median formula works for numbers and dates equally well since in terms of Excel dates are also numbers.

Excel Median If formula - median based on one criterion

Regrettably, Microsoft Excel does not provide any special function to calculate a median based on a condition like it does for the arithmetic mean (AVERAGEIF and AVERAGEIFS functions). Luckily, you can easily build your own Median If formula in this way:

In our sample table, to find a median amount for a specific item, input the item name in some cell, say E2, and use the following formula to get the median based on that condition:

=MEDIAN(IF($A$2:$A$10=$E2, $C$2:$C$10))

The formula tells Excel to include in calculation only those numbers in column C (Amount) for which a value in column A (Item) matches the value in cell E2.

Please pay attention that we use the $ symbol to create absolute cell references. It is especially important if you intend to copy your Median If formula to other cells.

Finally, since you want to check every value in the specified range, make it an array formula by pressing Ctrl + Shift + Enter. If done correctly, Excel will encapsulate the formula in curly braces like this:

Excel Median Ifs formula - median based on multiple criteria

Taking the previous example further, let's add one more column (Status) to the table, and then find a median amount for each item, but count only orders with the specified status. In other words, we will be calculating median based on two conditions - item name and order status. To express multiple criteria, use two or more nested IF functions, like this:

With criteria1 (Item) in cell F2 and criteria2 (Status) in cell G2, our Median Ifs formula takes the following shape:

=MEDIAN(IF($A$2:$A$10=$F2, IF($D$2:$D$10=$G2,$C$2:$C$10)))

Finding Mean Median And Mode In Excel For Mac Free

Since it's an array formula, remember to hit Ctrl + Shift + Enter to complete it correctly. If all done properly, you will get a result similar to this:

This is how you calculate median in Excel. To have a closer look at the formulas discussed in this short tutorial, you are welcome to download the Sample Median Excel workbook. I thank you for reading and hope to see you on our blog next week!

Mean Median And Mode For Kids

You may also be interested in