Pivot Table Issues

How to Change Excel PivotTable Summary Values from Count to Max

Amos Gikunda Oct 21, 2026 868 views

Question details

The user needs to change the summary calculation of Excel PivotTable value fields from the default Count to Max.

How to Change Excel PivotTable Summary Values from Count to Max

Product: Excel
Device & OS: not provided
Scenario: Analyzing data within a PivotTable where the required metric is the maximum value for each category rather than the count of items.
Observed behavior: The PivotTable automatically defaults to displaying the count of items, requiring the user to find a way to switch the summary function to show maximum values instead.

Before you start

Ensure your PivotTable is fully created and properly linked to your source data before attempting to modify the summary calculations.

Solution 1Recommended

Manually Change Value Field Settings

Use Excel's built-in Value Field Settings menu to change the summary calculation for specific fields from Count to Max.

This is the standard approach for updating PivotTable calculations. It works perfectly if you only have a few value fields to modify.

Select the PivotTable

Click any cell inside your existing PivotTable to activate the PivotTable Analyze tab and open the PivotTable Fields pane on the right side of your screen.

Access Value Field Settings

In the PivotTable Fields pane, locate the 'Values' area at the bottom right. Click the drop-down arrow next to the field you want to modify, and select 'Value Field Settings'.

Change Calculation to Max

In the Value Field Settings dialog box, navigate to the 'Summarize value field by' tab. Scroll through the list of calculation types and select 'Max'.

Apply the Changes

Click 'OK' to apply the new setting. The PivotTable will now update to display the maximum value for that specific field.

Updating Multiple Fields: You must repeat these steps individually for each value field you want to change. If you have many fields, consider using the VBA method instead.

Solution 2

Use VBA to Update All Value Fields Instantly

Apply a VBA macro to automatically change the summary function of every value field in your active PivotTable to Max.

Solution 3

Restructure Data Using Power Query

Unpivot your source data in Power Query to simplify field management before generating the PivotTable.

Advanced Data Analysis

Easily Manage PivotTables with WPS Spreadsheet

WPS Spreadsheet provides an intuitive, high-performance interface for building and modifying PivotTables. You can easily switch summary functions from Count to Max, or analyze complex datasets with a few clicks.

1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your raw data.
2. Insert a PivotTable: Highlight your data range, navigate to the 'Insert' tab, and select 'PivotTable'.
3. Configure the Fields: In the PivotTable Field List pane on the right, drag your target numeric column into the 'Values' box.
4. Change Calculation to Max: Click on the inserted field in the 'Values' box, select 'Value Field Settings', choose 'Max' from the calculation list, and click 'OK'.

Free and lightweight spreadsheet applicationFully compatible with Microsoft Excel (.xlsx) formats and PivotTable structuresIntuitive drag-and-drop PivotTable Field ListSeamless processing of large datasets without lagging

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel PivotTable default to Count instead of Sum or Max?

Excel automatically defaults the summary function to Count if it detects any blank cells or text values within the numeric column of your source data. To make it default to Sum (which is closer to Max), ensure the entire column contains strictly numerical data with no blanks.

Can I show both Count and Max for the same field in a PivotTable?

Yes. You can drag the same numeric field into the 'Values' area of the PivotTable Fields pane multiple times. Then, access the Value Field Settings for the first instance to set it to Count, and set the second instance to Max.

How do I change the summary function for multiple fields without using VBA?

Without VBA, Excel does not have a native bulk-update button for Value Field Settings. You must manually click the drop-down for each field in the 'Values' area and change the calculation to Max one by one. If you frequently need to do this, using the provided VBA script is highly recommended.

Excel & Data Problems