Excel Pivot Table Mode: Find the Most Common Value

To find a mode with an Excel PivotTable, put the field you want to analyze in both Rows and Values, set the Values calculation to Count, and identify the item with the largest count. Excel does not offer Mode as a built-in PivotTable summary calculation, so counting each distinct value is the practical PivotTable method.

What mode means in a spreadsheet

The mode is the value that occurs most often in a set of data. In a list of sales regions, for example, the mode might be North if that region appears more often than any other. For numbers, the mode might be the most frequently recorded score, quantity, or price. A dataset can have one mode, several modes tied for the highest frequency, or no repeated value at all.

A PivotTable can show the frequency of every distinct item, which makes the mode visible without calculating it directly. This approach is useful because it also shows the runner-up values and their counts. It is not the same as choosing Sum, Average, or another summary for a numeric field: those calculations describe the values, while Count describes how often they occur.

Why there is no Mode option in PivotTable settings

Standard Excel PivotTables include summary calculations such as Sum, Count, Average, Maximum, and Minimum. Mode is not one of the options in Value Field Settings. The distinction matters: placing a number field in Values usually gives you Sum, which adds the numbers rather than finding the most common number. A mode analysis needs a frequency count first.

For example, if a quantity column contains 2, 2, 3, 3, and 3, the sum is 13, but the mode is 3 because it occurs three times. A PivotTable with the quantity field in Rows and a count of that field in Values displays the frequencies needed to spot 3.

Build a PivotTable that counts each value

Start with a single record per row and a clear column heading, such as Product, Rating, or Quantity. For a reliable source range, convert the data to an Excel table with Insert > Table; a table expands when new rows are added and makes it easier to keep the PivotTable source current. Then create the PivotTable:

Select a cell in the source data, then choose Insert > PivotTable.
Check the proposed table or range, choose where to place the PivotTable, and select OK.
In the PivotTable Fields pane, drag the field whose mode you want into Rows.
Drag that same field into Values as well.
If the Values area shows Sum or another calculation, open its field menu, choose Value Field Settings, select Count, and select OK.

The resulting report has one row for each distinct item and a count beside it. Sort the count column from largest to smallest to put the leading candidate at the top. In current desktop Excel for Windows, you can right-click a count in the PivotTable and choose Sort > Sort Largest to Smallest. The exact context-menu wording or placement can vary slightly across Excel builds.

Example: find the most common category

Suppose the Product column has 12 records: Tea appears five times, Coffee four times, and Cocoa three times. Place Product in Rows and again in Values, and make sure the Values field is Count of Product. The PivotTable reports Tea 5, Coffee 4, and Cocoa 3. Tea is the mode because five is the greatest frequency.

This result is often more informative than a single answer. If the top count is close to the next one, the difference may matter; if several products have the same count, the table makes that tie apparent. To preserve the full frequency picture, leave the table sorted rather than filtering away all but the first row.

Recognize ties and datasets with no mode

If two or more items share the largest count, the dataset has multiple modes. For instance, if ratings 4 and 5 each appear six times and every other rating appears less often, both 4 and 5 are modes. A PivotTable sorted by count shows the tie as adjacent rows. Do not assume the first displayed item is the only mode; compare its count with the other highest counts.

If every distinct value appears exactly once, there is no mode under the usual definition. The PivotTable still works: every count is 1, so no item occurs more frequently than another. Similarly, if all values are blank or there are no records, there is no meaningful most-frequent nonblank item to identify. The frequency table helps distinguish these cases from a single clear winner.

Finding the mode of numeric data with a formula

When you only need the most common numeric value, Excel’s MODE.SNGL function can calculate it without a PivotTable. With numeric observations in B2:B13, enter =MODE.SNGL(B2:B13) in an empty cell. For example, if B2:B13 contains 12, 15, 12, 18, 15, 12, 21, 15, 12, 18, 15, and 15, the result is 15: it appears five times, while 12 appears four times, 18 appears twice, and 21 once.

MODE.SNGL is for numeric values; it does not find the most frequent text label. It ignores blank cells and text in a referenced range. If the numbers have no repeated value, it returns the #N/A error because there is no single most frequent number. If two or more numbers tie for the highest frequency, MODE.SNGL returns one of the modes rather than listing every tied value. Use a frequency PivotTable when you need to inspect all values and ties.

For category fields such as department or product name, use the PivotTable count method instead. Another option is a helper column that counts how often each category appears, but that adds formulas to maintain and does not remove the need to handle ties. The PivotTable is usually the clearer choice for a reusable frequency summary.

Blanks, duplicates, and data quality

Check the source before interpreting the counts. Spelling differences such as North, north, and North with a trailing space can appear as separate items. Likewise, a number stored as text may not behave like the same number stored as a numeric value. Standardize labels, remove unwanted spaces, and make sure values use consistent data types when those distinctions are accidental.

Blank cells need special attention. A blank may appear as a blank row label, but Count of the same field counts nonblank entries; it does not reliably give you a count of blank records. If counting blank categories is important, place a separate field that is populated on every record, such as an ID, in Values and set it to Count. The blank row label can then be paired with the count of those records. If the source contains formulas that return an empty string, check how those entries appear in the PivotTable rather than assuming they are handled exactly like truly empty cells.

Refresh the report and keep it accurate

A PivotTable is a summary of its source, not a live recalculation of every change to the original range. After editing or adding records, refresh the PivotTable using PivotTable Analyze > Refresh, or right-click inside it and choose Refresh. If new records were added outside the original source range, refresh alone may not include them. An Excel table is a useful source because it expands as records are appended; you may still need to refresh the PivotTable to update its results.

When the source contains multiple columns, make sure each row represents one observation. If the same event is repeated across several rows, the frequency count counts rows, not unique people or transactions. To find the most common value among unique customers, for example, you need a way to count distinct customers for each value; a basic Count of the category field does not do that. The layout and counting question should match what the analysis is intended to measure.

PivotTable or formula: which should you use?

Choose a PivotTable when you want an easy-to-read frequency table, need to compare several categories, or want to filter and rearrange the summary. It is also the better option when ties matter, because it shows the count for every item. Choose MODE.SNGL when the data is numeric and you want one result in a worksheet cell. For text, or for a requirement to return every tied mode, use a frequency table or a formula designed for that specific output.

In Excel for Microsoft 365 on Windows, the PivotTable field arrangement and count method described above are standard. Excel for Mac has PivotTables and the same basic Rows-and-Values approach, although ribbon labels and context menus can differ. Excel for the web also supports PivotTables, but some creation, editing, and advanced options may differ from desktop Excel. If a command is not visible, use the PivotTable field list and the available summary settings rather than expecting a dedicated Mode command.

Frequently Asked Questions

Can an Excel PivotTable calculate mode directly?
No. Mode is not a built-in choice in Value Field Settings. Put the field in Rows and Values, change the Values summary to Count, and use the largest count to identify the mode.

Why does my PivotTable show Sum instead of Count?
Excel commonly defaults to Sum when the Values field contains numbers. Open Value Field Settings for that field and select Count. The Rows area should contain the field whose distinct values you are comparing.

How do I show every mode when there is a tie?
Sort the frequency counts from largest to smallest and include every item with the same highest count. A top-one filter can hide tied results, so inspect the complete frequency table before reporting a single mode.

Can I find the mode of text with MODE.SNGL?
No. MODE.SNGL works with numeric values. For text categories, use a PivotTable with the text field in Rows and Count, or create a frequency calculation that counts each label.

Why is a blank value not counted like the other items?
Count of a field counts nonblank entries in that field. To count records whose category is blank, add a nonblank ID or other populated field to Values and set that field to Count.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.