Excel Pivot Table Min and Max Values: How to Show Them

Charts & pivot tables · Updated November 18, 2026

To show a minimum or maximum in an Excel PivotTable, put a numeric or date field in the Values area, right-click one of its values, and choose Summarize Values By > Min or Max. To display both, add the field to Values twice and set one copy to Min and the other to Max.

What Min and Max Mean in a PivotTable

A PivotTable’s Min and Max summaries return the smallest or largest underlying value in each group. If a source table has sales by region, for example, putting Region in Rows and Sales in Values lets you show the lowest and highest sale for each region. The result is not the smallest or largest displayed subtotal; it is calculated from the source records that contribute to that group.

This makes Min and Max useful when a total or average would hide an important boundary. You might want the earliest order date by salesperson, the lowest recorded temperature by location, or the largest transaction in each product category. The summary changes as you change the PivotTable’s layout or filters.

Min and Max are summary calculations, not separate worksheet functions built into the PivotTable. They do not change the source data, and they do not identify which record produced the result. If you need the full row associated with the maximum sale, for instance, you will need an additional lookup or filtering step.

Set Up a Min or Max Summary

Start with a clean, tabular source range: one header row, one record per row, and a column for the values to summarize. In Microsoft 365 for Windows, select a cell in the source data and choose Insert > PivotTable. Confirm the table or range and where to place the PivotTable, then select OK. If the source is an Excel Table, new rows added to that table are generally included when the PivotTable is refreshed.

  1. In the PivotTable Fields pane, drag a category such as Region or Product into Rows.
  2. Drag the numeric or date field, such as Sales or Order Date, into Values. Excel may initially choose Sum, Count, or another summary.
  3. Right-click a displayed value in the PivotTable and select Summarize Values By > Min or Summarize Values By > Max.
  4. Check that the result makes sense for the group and, if necessary, format the field as a number, currency, or date.

You can also open Value Field Settings for the value field and choose Min or Max on the Summarize Values By tab. The right-click route is usually the quickest. On Excel for Mac, right-click or Control-click a PivotTable value and use the summary options; ribbon placement can vary by release. Excel for the web has PivotTable controls too, although some commands and layouts differ from the desktop app.

Show Minimum and Maximum Side by Side

One Values field can use only one summary calculation at a time. To show both boundaries in the same report, drag the source field into Values a second time. Excel creates two value fields based on the same source column. Set the first to Min and the second to Max using the steps above.

Rename the two value fields so the report is easy to read. For example, change their custom captions to Lowest Sale and Highest Sale. Right-click a value field, open Value Field Settings, and enter a clearer name in the Custom Name box. Excel may reject a caption identical to the source field name, so use a distinct label.

You can also place another category in Columns to compare groups across the page. For example, put Region in Rows, Sales twice in Values, and Year in Columns. This produces minimum and maximum sales for each region-year combination. Keep an eye on the width of the report: duplicating value fields across many column groups can make a PivotTable wide.

How Groups, Filters, and Grand Totals Affect Results

Each row or column grouping creates its own calculation. If Region is in Rows and Product is nested beneath it, Excel reports a minimum and maximum for each product within each region. Removing Product rolls the calculations up to region level. Moving a field between Rows and Columns changes the layout, not the basic meaning of Min or Max.

Report filters, slicers, and timeline selections can change which source records are included, so the displayed minimum or maximum may change when you use them. A region filter, for example, removes other regions from the report. A date filter may leave a different earliest order date or largest sale. This is expected: the summary describes the records currently included in that PivotTable view.

A grand total for a Min or Max field represents the minimum or maximum across the records covered by the total, not an average of the group results. If the highest sale in any region is 900, the overall maximum should be 900 even if the regional maxima are 500, 900, and 700. Do not add or average minimum and maximum values to infer an overall boundary.

Numbers, Dates, Blanks, and Zero Values

Min and Max are most straightforward when the source column contains consistently typed numbers or dates. A date is stored by Excel as a serial number, so Min can return the earliest date and Max the latest. If the PivotTable displays a date as a number, change the value field’s number format to a date format; the underlying result may be correct even when its display is confusing.

Zero is a real numeric value and can be the minimum. A blank cell is not the same as zero; blank source entries generally do not supply a numeric minimum or maximum. If blank entries are meaningful, decide whether they should remain blank or be replaced with an explicit value before interpreting the report. Do not replace blanks with zero automatically, because doing so can make zero appear to be the minimum even when no zero was recorded.

Numbers stored as text, error values, mixed units, or inconsistent date formats can produce results that are surprising or hard to interpret. For example, a value entered as text may not behave like a number in a numeric summary. Clean the source column, convert text-formatted numbers to actual numbers, and use one unit and data type per field. A PivotTable is a summary, not a data-cleaning tool.

Worked Example and a Useful Check

Suppose a Sales column contains 120, 0, 85, 210, a blank, and 140. With Region in Rows and Sales in Values summarized by Min, a region containing the zero has a minimum of 0; the blank does not make the minimum zero. Its maximum is 210 if that is the largest sale in that region. If the zero belongs to a different region, each region’s result reflects only its own records.

For a quick independent check on a simple, unfiltered list, worksheet functions can calculate the limits directly. With sales values in C2:C7, including the example values 120, 0, 85, 210, a blank, and 140, enter =MIN(C2:C7) in an empty cell; it returns 0. With the same values in C2:C7, enter =MAX(C2:C7) in another empty cell; it returns 210. These formulas summarize the entire specified range, not a PivotTable’s filtered groups, so their results may differ from a filtered PivotTable.

If you need to confirm a PivotTable number, compare it with the source records for that group and the filters currently applied. Checking only the full source column is not enough if the report is restricted to one year, region, or product.

Refresh and Troubleshoot the Result

PivotTables do not always recalculate immediately after source cells change. Right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. If you add rows outside the original source range, refreshing may not include them. Using an Excel Table as the source helps the source expand with appended records; check the PivotTable data source if new records are still missing.

  • The field shows Count instead of Min: Open Value Field Settings and select Min. If Min is unavailable or results look wrong, check whether the source field contains numbers or dates consistently rather than text.
  • The displayed minimum is zero: Look for actual zeros, formulas returning zero, or imported values that were converted to zero. Blank cells alone are not proof of a zero.
  • A date appears as a large number: Apply a date number format to that value field through Value Field Settings.
  • The result ignores new records: Refresh the PivotTable and verify that the source range includes the added rows.
  • The maximum changes unexpectedly: Review slicers, report filters, row or column filters, and the records included in the selected group.

When checking filters, inspect both the visible labels and the source data. A filter can hide a category or date period while leaving the PivotTable’s layout looking otherwise familiar. Clearing the filters temporarily is a useful way to distinguish a data issue from a scope issue.

PivotTable Min and Max vs. Worksheet MIN and MAX

Use PivotTable summaries when you want boundaries organized by categories and easy to rearrange, filter, or compare. A single PivotTable can calculate a minimum per region, product, and year without writing separate formulas for every group. It can also be refreshed when the source data changes.

Use worksheet MIN and MAX when you need a direct result for a known range, a formula that updates as cells change, or a calculation embedded in another formula. Those functions do not automatically understand the PivotTable’s categories or slicer selections. For filtered or grouped results, the PivotTable is often more convenient; for a single fixed range, a worksheet formula may be simpler.

Neither approach automatically tells you the complete record responsible for the result. If the question is which transaction had the maximum value, filter the source data to the relevant group and inspect the matching records, or build a separate lookup solution that accounts for ties. Multiple records can share the same minimum or maximum.

Frequently Asked Questions

Can one PivotTable show both Min and Max?

Yes. Add the same source field to the Values area twice, then set one copy to Min and the other to Max. Rename the two value fields to make their meanings clear.

Why does my PivotTable show Count instead of Min?

Excel may choose Count when the source field contains text or mixed data types, or the summary may simply have been set to Count. Open Value Field Settings and select Min; then check the source for numbers stored as text or other inconsistent entries.

Does Min ignore blank cells?

Blank source cells generally do not count as numeric values for a minimum or maximum, while zero does count. Verify how imported or formula-generated entries are stored before treating an apparent blank as missing data.

Can a PivotTable show the row that contains the maximum?

Not from the Max summary alone. It returns the maximum value, not the associated record. Filter the relevant source group for that value or use a separate method to retrieve the matching row; check for ties if several records have the same maximum.

Why did my PivotTable Min or Max not update?

Refresh the PivotTable after changing source data. If you added records beyond the original source range, update the source or base the PivotTable on an Excel Table that expands as records are added.

Related

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.