Pivot Table Max Not Working? Causes, Checks, and Fixes

If Max is not working in an Excel PivotTable, open the value field’s settings and choose Summarize Values By > Max; then check that the source values are genuine numbers or dates and refresh the PivotTable. Max calculates the largest eligible value within each group, so a text-formatted number, stale source range, filter, or display format can make the result look wrong.

What Max means in a PivotTable

A PivotTable does not automatically show the largest value just because a field is numeric. When you add a field to the Values area, Excel chooses a summary operation, commonly Sum or Count. Max is a separate operation: it returns the greatest value in that field for each combination of row and column labels.

For example, suppose a sales table has Region, Product, and Units columns. If Region is in Rows and Units is in Values with Max selected, the East row displays the largest Units entry among East records. It does not display the total, the most frequently occurring value, or the largest value from every region added together. A grand total using Max is the largest underlying value across the records included in that total.

Max also works with real Excel dates because dates are stored as serial numbers. In a pivot grouped by employee, for instance, Max on a date field can show each employee’s latest recorded date. The result may need date formatting to look like a date rather than a number.

Choose Max in Value Field Settings

In Excel for Microsoft 365 on Windows, use these steps to set the summary operation:

Click a cell in the PivotTable, then make sure the field you want to summarize appears in the Values area of the PivotTable Fields pane. Drag it there if it is currently only in Rows or Columns.
Right-click a value from that field in the PivotTable and choose Summarize Values By > Max.
If that shortcut is not available, right-click the value and select Value Field Settings. On the Summarize Values By tab, choose Max, then select OK.

You can also open Value Field Settings from the Values area: select the field’s drop-down arrow and choose Value Field Settings. Check the field name in the dialog as well as the calculation. A PivotTable can contain several value fields, and changing one field does not change the others. If you have added the same source field twice, each copy can use a different summary.

Excel for Mac has comparable PivotTable field and value settings, although ribbon labels and pane layout can vary by release. Excel for the web provides basic PivotTable controls, but if a setting is missing or the model has advanced features, open the workbook in the desktop app.

Check for numbers stored as text

A column can look numeric while containing text. This often happens after importing a CSV, copying data from a website, or combining entries with inconsistent formats. Values such as a number preceded by an apostrophe, or cells containing spaces, may be treated as text. The PivotTable can then default to Count, exclude those entries from a numeric calculation, or produce results that do not match what you expect.

Check the original source cells, not just the PivotTable display. A common clue is that numbers are left-aligned while other numbers are right-aligned, though alignment alone is not conclusive because formatting can change it. In a blank cell, you can also test a source entry using a conversion formula.

With text-formatted numbers in A2:A5, enter =VALUE(A2) in B2 and fill it down through B5. For example, if A2 contains the text 12, the formula returns the numeric value 12. Use the converted helper column in the PivotTable, or copy the results and paste them as values over a cleaned source column. Check decimal and thousands separators when data comes from a different regional format; a conversion may need an import setting or cleanup if the separators do not match your Excel settings.

Another option for a simple, consistently formatted column is to select the source cells and use Data > Text to Columns, then select Finish. For imported data with locale-specific separators or mixed types, Power Query can provide more control: set the column’s data type deliberately and, where needed, choose the appropriate locale before loading the results.

Refresh the PivotTable and confirm its source

PivotTables use a stored snapshot of their source data. Editing a source cell does not necessarily update the visible result immediately. Click inside the PivotTable and choose PivotTable Analyze > Refresh, or use Data > Refresh All to refresh connections and PivotTables in the workbook. Refresh after converting text to numbers, correcting dates, or changing source records.

Refresh cannot include rows that are outside a fixed source range. To inspect the source, click the PivotTable and choose PivotTable Analyze > Change Data Source. Check that the selected range includes the new rows and the relevant field. For a source that grows regularly, convert the source range into an Excel Table using Insert > Table, then build the PivotTable from that table. Table-based sources expand as rows are added; refresh the PivotTable afterward so it reads the new entries.

If you changed the source column’s data type but Max still behaves as before, verify that the PivotTable points to the cleaned column and refresh again. In a workbook with several similarly named sheets or tables, it is easy to fix one data range while the PivotTable continues to use another.

Look at filters, grouping, and the display calculation

Max is calculated only from the records currently included in a PivotTable’s context. Report filters, slicers, row or column filters, and manually hidden items can exclude the record you expect to be largest. Clear filters temporarily and compare the result with the unfiltered source data. Also check that the record is not excluded by a date or category filter.

Grouping changes what each displayed result represents. If a date field is grouped by month, Max returns the largest date or number within each month group, not one result for the entire year. If categories are nested, each row’s Max is calculated within that row’s particular group. Expand the relevant labels or remove a grouping temporarily to see whether the unexpected result comes from the grouping level.

There are two different settings that can be confused. Summarize Values By chooses the underlying aggregation, such as Max or Sum. Show Values As changes how that summary is displayed, for example as a percentage or difference. In Value Field Settings, check the Show Values As tab and select No Calculation if you want to see the plain Max result rather than a transformed display.

Check dates, blanks, and number formatting

A date that looks correct in the source may actually be text. Text dates do not behave like genuine Excel dates in all PivotTable calculations. If Max returns an unexpected date, confirm that the source cells contain dates Excel recognizes, rather than strings with inconsistent day/month order or hidden spaces. Convert the source consistently, refresh, and compare the result with the source records.

Formatting can also disguise a correct result. A date is stored as a serial number, so a PivotTable value may show a number such as 45,000 instead of a calendar date if its number format is General. In Value Field Settings, choose Number Format and apply a date format. Setting the format there is often more reliable than formatting a single visible cell, because the PivotTable can reapply its own formatting on refresh.

Blank cells are not the same as zero. If some records have no value, Max generally finds the largest available value rather than treating a blank as a numeric zero. But a real zero can be the maximum when all included numbers are zero or negative. Decide whether blanks mean missing data or should be interpreted as zero, and clean the source accordingly. Do not replace blanks automatically unless that matches the meaning of the data.

When Max is unavailable or still gives the wrong answer

First confirm that the intended field is in the Values area. A field placed only in Rows or Columns labels records; it does not create a summarized value. If the field is in Values but Max is absent or disabled, inspect the source type and how the PivotTable was created. PivotTables based on an external OLAP source or the Data Model can expose different choices from a standard range-based PivotTable, because some calculations are controlled by the model or connection.

For a Data Model PivotTable, check the model’s available measures and aggregation options rather than assuming the regular range-based menu will behave identically. If the model does not offer the required calculation, the model owner may need to provide an appropriate measure or adjust the source. In a normal worksheet-based PivotTable, a clean numeric or date field in Values should generally allow a standard Max summary.

Also make sure you are inspecting the intended value field and group. A field caption may have been renamed, and a PivotTable can show multiple summaries of the same source column. Compare the displayed result with a small set of source records after clearing filters. This helps distinguish a calculation problem from a mismatch in field, grouping, or included records.

A quick troubleshooting sequence

When the cause is unclear, work through the checks in order rather than rebuilding the PivotTable immediately:

Open Value Field Settings and verify that Max is selected under Summarize Values By.
Confirm the field is in Values and that you changed the correct copy if it appears more than once.
Check the source column for numbers or dates stored as text, inconsistent entries, and blanks.
Clear filters and slicers temporarily, then review any grouped row or column fields.
Check Show Values As and the number format, especially when the result looks like a percentage or serial date.
Refresh the PivotTable and confirm Change Data Source includes the complete data range.

If a small hand-check of the included source records agrees with the PivotTable, Max is likely working and the surprise is due to grouping or interpretation. If it does not agree, compare data types and included rows first; those are more common causes than a broken Max calculation.

Frequently Asked Questions

Why does my PivotTable show Count instead of Max?
Excel may have chosen Count when the source field contains text or mixed types. Open Value Field Settings and choose Max if it is available, then clean any numbers stored as text and refresh. Also verify that the field is in Values.

Why is Max greyed out in Value Field Settings?
The field or PivotTable source may not support the same summaries as a standard worksheet range. Check that the field is in Values and inspect whether the PivotTable uses an external OLAP connection or the Data Model. Those sources can offer model-controlled calculations.

Does PivotTable Max ignore blanks?
Blank entries are not numeric values for a maximum calculation, so they do not act like zero. A numeric zero is a real value and can be the maximum when the included values are zero or negative.

Why does my maximum date appear as a number?
Excel stores dates as serial numbers. The maximum may be correct but displayed with a General or numeric format. Open Value Field Settings, choose Number Format, and apply a date format.

Will Refresh fix a PivotTable Max result?
Refresh updates the PivotTable from its current source, so it can incorporate corrected values and records already inside that source. It will not automatically expand a fixed source range to include rows outside it; check Change Data Source or use an Excel Table as the source.

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.