How to Find the Mode of a Data Set in Excel
Finding the most common value in a data set is a frequent task in data analysis, and Excel has powerful built-in functions designed to do exactly that. Learning how to calculate the mode helps you quickly understand patterns, like which product is your bestseller, the most common survey response, or the typical score on an exam. We'll walk you through the simple functions Excel offers to find single or multiple modes in your data instantly.
What is the Mode, Anyway?
In statistics, the mode is simply the value that appears most frequently in a set of data. It's one of the three measures of central tendency, alongside the mean (average) and the median (the middle value). Unlike the average, the mode identifies the most popular or common occurrence, which can be incredibly useful.
Your data can have:
- One Mode (Unimodal): A single value appears more often than any other. For example, in the data set {2, 4, 6, 6, 6, 8, 9}, the mode is 6.
- Multiple Modes (Multimodal): Two or more values share the highest frequency of appearance. In the data set {2, 2, 4, 6, 6, 8}, both 2 and 6 are modes. If there are exactly two modes, it's called bimodal.
- No Mode: All values appear with the same frequency. In the data set {1, 2, 3, 4, 5}, there is no mode because every number appears just once.
Understanding which type you're dealing with will help you choose the right Excel function for the job.
How to Find a Single Mode with MODE.SNGL
If you suspect your data has only one mode, or if you only need to find the first mode in a multimodal set, the MODE.SNGL function is your best tool. As its name implies, it's designed to return a single mode.
This function works with numbers and ignores any text or blank cells in your selected range.
Free PDF · the crash course
AI Agents for Marketing Crash Course
Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.
The Syntax for MODE.SNGL
The formula structure is straightforward:
=MODE.SNGL(number1, [number2], ...)Here, number1 is the first number or range you want to analyze, and [number2] and any subsequent arguments are optional additional numbers or ranges.
Step-by-Step Example
Imagine you have a list of t-shirt sizes sold in a day and you want to find the most popular size sold. Your data is in cells B2 through B11.
The Data:
Step 1: Start the Formula Click on an empty cell where you want your result to appear (let's say D2). Type:
=MODE.SNGL(Step 2: Select Your Data Range Highlight cells B2 to B11. Excel will fill in the range:
=MODE.SNGL(B2:B11)Step 3: Close and Enter Finish the formula with a parenthesis:
=MODE.SNGL(B2:B11)Press Enter.
Excel will calculate and display the mode, which is 34, as it appears four times—more than any other size.
What if There is No Mode?
If every item in your data set appears only once, the MODE.SNGL function will return the #N/A error. This isn't a mistake! It's Excel telling you that no value has duplicates.
How to Find Multiple Modes with MODE.MULT
What if your data is bimodal or multimodal? For instance, what if sizes 34 and 36 sold in equal quantities? Using MODE.SNGL would only return one of these (the first it finds). For these cases, you need MODE.MULT.
MODE.MULT is a dynamic array formula, available in Excel 365, Excel 2021, and later. It returns a list of results that "spill" into the cells below the formula cell, helping you find all modes.
The Syntax for MODE.MULT
It looks like this:
=MODE.MULT(number1, [number2], ...)Step-by-Step Example
Suppose sizes 34 and 36 both sold very well, creating two modes. The data in column B now looks like this:
Both 34 and 36 appear four times. To find them using MODE.MULT:
Step 1: Choose an empty area, e.g., cell D2, and ensure the cells below D2 are clear to allow spill.
Step 2: Enter the formula with your data range:
=MODE.MULT(B2:B12)Step 3: Press Enter. Excel will list 34 in D2 and 36 in D3, showing all modes.
If you click on any cell in the spill range other than D2, you'll see the formula in the formula bar grayed out, indicating it's part of a dynamic array result.
The Legacy MODE Function
In older Excel versions (prior to 2010) or legacy spreadsheets, the original MODE function was used. Since Excel 2010, it has been replaced by MODE.SNGL and MODE.MULT for clarity and expanded functionality.
The old MODE behaves like MODE.SNGL, returning only the first mode found. It's still available but better to use the newer functions if possible.
Troubleshooting Common Issues
Even though these functions are simple, errors can occur:
The #N/A Error
Means no mode exists (all values are unique). Check your data for typos or inconsistencies.
Free PDF · the crash course
AI Agents for Marketing Crash Course
Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.
The #SPILL! Error
Occurs with MODE.MULT if there's not enough space below the formula cell. Clear the target spill range to fix this.
Functions Ignore Text
Mode functions only work with numbers. Text entries are ignored, e.g., in {10, 20, "None", 10}, the mode is 10.
Other Ways to Find the Mode in Excel
1. Sort and Scan
- Highlight data column
- Data tab > Sort A-Z
- View which values appear most frequently
2. Use a PivotTable
- Insert > PivotTable
- Put data field in Rows and Values
- Change Value setting to Count
- Sort counts descending to identify the most common value
Final Thoughts
Excel's MODE.SNGL and MODE.MULT simplify frequency analysis, helping you quickly find the most common values in your data. Pairing these with PivotTables or sorting enhances your dataset understanding. Manual methods are practical for quick checks, but leveraging Excel's functions streamlines analysis, especially for recurring reports.
Manually calculating stats in Excel or Google Sheets is practical for one-off analyses, but it's often part of a larger, repetitive reporting process that consumes hours each week. We built Graphed because we believe your time is better spent on strategy than on spreadsheet wrangling. It replaces manual reporting by connecting directly to your live data sources and letting you build the dashboards you need with simple, natural language. This way, you get the insights instantly, without ever exporting another CSV.
Related Articles
Facebook Ads for Pest Control: The Complete 2026 Strategy Guide
Learn how to run effective Facebook ads for pest control companies in 2026. This comprehensive guide covers campaign setup, targeting strategies, cost benchmarks, and best practices for generating quality leads.
Facebook Ads for Carpet Cleaners: The Complete 2026 Strategy Guide
Learn how to run Facebook ads for carpet cleaning businesses in 2026. Get proven strategies for targeting, creative formats, retargeting, and budget that actually convert.
Facebook Ads For Personal Trainers: The Complete 2026 Strategy Guide
Learn how to effectively use Facebook ads for personal trainers in 2026. This comprehensive guide covers targeting strategies, ad creative, budgeting, and optimization techniques to help you grow your training business.