How to Fix If a Pivot Table Field Name Already Exists (2 Methods)

The dataset below contains some salespersons’ sales, units, and corresponding regions.

Method 1 – Using Different Custom Names

Steps:

Select any data from the dataset.
Click as follows: Insert > Pivot Table.

A dialog box named Create PivotTable will appear.

Select the data range; you can change the range according to your wish.
Select the worksheet- New worksheet/ Existing Worksheet. I’ve chosen the New worksheet option.
Click OK.

Pivot Table Fields will open up.

Drag Salesperson to the ROWS field and Units to the VALUES field.

Your Pivot Table will look like the image below. Excel has automatically changed the unit’s name to Sum of Units, but our headers’ name was Units. Let’s try to change the name.

Double-click on the title ‘Sum of Units’.

The Value Field Settings dialog box will appear.

Change the name from the Custom Name section.
Type Units and click OK.

It will then show, “Pivot table field name already exists,” because our dataset has the same name.

The simple solution is to type a space before or after the name. Excel will accept it.
Click OK.

Now take a look that the name has been changed successfully.

Method 2 – Using VBA Macros

Steps:

Select any cell of the dataset.
Click as follows: Insert > Pivot Table.

After appearing in the Create PivotTable dialog box, select the data range and select New worksheet or Existing worksheet as you desire. I selected New worksheet.
Press OK, and you will then get the PivotTable Fields dialog box.

Drag Salesperson to ROWS field and Units and Sales to VALUES field.

Your Pivot Table will look like this. If we try to change the title names Sum of Units to Units and Sum of Sales to Sales, we’ll face the problem—“Pivot table field name already exists”—as we used these names in our dataset.

Open the VBA window.
Right-click the sheet title and click the View Code option from the context menu.

Enter the following formula:

Sub PivotTable_AlreadyExists()
Dim Pfield As PivotField
Dim PTable As PivotTable
Set PTable = ActiveSheet.PivotTables(1)
For Each Pfield In PTable.DataFields
    Pfield.Caption = Pfield.SourceName & " "
Next Pfield
End Sub