How to Change the Default Custom Name of a PivotTable Value Field
Question details
Users want to rename a PivotTable value field to match the original source field name, but Excel automatically adds prefixes like "Sum of" and prevents using the exact source name.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Customizing PivotTable headers for cleaner presentation without getting a duplicate-name error.
- Observed behavior
- Excel automatically generates a long default name (e.g., "Sum of Sales") and throws an error if the user tries to rename it to the exact source field name (e.g., "Sales").
Ensure you have inserted a PivotTable and added the desired numeric field to the Values area in the PivotTable Fields pane.
Rename the Value Field by Adding a Trailing Space
Bypass Excel's duplicate name restriction by adding a single invisible space character at the end of your desired header name.
Because PivotTables require unique field names, you cannot use a name that exactly matches a column header in your source data. The simplest and most widely used workaround is to add a space at the end of the custom name.
Click on the cell in your PivotTable that contains the default value field name (e.g., "Sum of Sales").
Type the name exactly as you want it to appear, typically matching your source field (e.g., "Sales").
Press the Spacebar once to add a trailing space (e.g., "Sales "), then press Enter. The PivotTable will accept the new name without triggering an error.
Use the Data Model to Create Explicit Measures
For advanced control over field names without space tricks, use the Data Model (Power Pivot) to create explicit DAX measures.
Easily Manage and Rename PivotTable Fields with WPS Office
WPS Spreadsheet provides a highly compatible and intuitive interface for creating and managing PivotTables. You can easily rename value fields using the identical trailing-space method found in Microsoft Excel, while enjoying a lightweight and completely free office suite.
- 1. Open your Workbook in WPS Spreadsheet: Launch WPS Office, open your spreadsheet file, and select the target PivotTable.
- 2. Access Value Field Settings: Right-click the value field header or click the field in the Values area of the sidebar, then select 'Value Field Settings'.
- 3. Apply Custom Name: In the 'Custom Name' text box, type your preferred name followed by a single space (e.g., "Revenue "), then click OK to save your clean, customized header.
Frequently Asked Questions
Why does Excel add "Sum of" to my PivotTable fields?
Excel automatically prepends the aggregation function (like Sum, Count, or Average) to clearly distinguish the calculated summary field from the raw source data column it is based on.
Can I permanently change the default PivotTable naming format?
No, Excel does not have a built-in global setting to permanently change or disable the "Function of Field" default naming convention. You must rename the fields manually or use explicit Data Model measures.
What happens if I type the exact source field name without a space?
Excel will display an error message stating "PivotTable field name already exists." This happens because the PivotTable requires a strictly unique name for every field, and it cannot share the exact same identifier as the source data column.
How do I change the aggregation function from Count to Sum?
Right-click any value in the PivotTable's data area, select "Summarize Values By" from the context menu, and choose "Sum" from the list of available calculation types.
