Md. Sourov Hossain Mithun
While adding a field to the Pivot Table data area then it will automatically set custom names like Sum of Units or Count of Units. We are able to rename a Pivot Table data field manually or using VBA Macro. Instead of “Sum of Units”, we may want to set the name to “Units”, so that it becomes easier to read and the column gets narrower. But if we rename the cell typing Units then we’ll get an error message- “Pivot Table field name already exists.” It happens when we try to set a custom name that’s similar to a field name in the source data. This article will guide you with two easy ways to solve the problem with sharp steps and vivid illustrations.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
2 Ways to Solve If a Pivot Table Field Name Already Exists
To explore the article I’ll use the dataset given below which contains some salespersons’ sales, units, and corresponding regions.
1. Using Different Custom Name
Here in this article, we’ll use a different custom name manually for a Pivot Table Field if the name already exists. Actually, we’ll use space here with the name to avoid the problem.
Steps:
- Select any data from the dataset.
- Then click as follows: Insert > Pivot Table.
And soon after, a dialog box named Create PivotTable will appear.
It will select the data range automatically, you can change the range according to your wish.
- Then select the worksheet- New worksheet/ Existing Worksheet. I’ve chosen the New worksheet option.
- Now, click OK.
Pivot Table Fields will open up.
- Then drag Salesperson to ROWS field and Units to VALUES field.
After that, your Pivot Table will look like the image below. Take a look that Excel has automatically changed the unit 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’.
And the Value Field Settings dialog box will appear.
- Now change the name from the Custom Name section.
- Type Units and click OK.
It will then show- “Pivot table field name already exists”. Because we have the same name in our dataset.
- 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 is changed successfully.
Read more: [Fix] The Pivot Table Name Is Not Valid
Similar Readings
- Pivot Table Field Name Is Not Valid: 9 Causes and Corrections
- Excel Table Name: All You Need to Know
- Pivot Table is Not Picking up Data in Excel (5 Reasons)
- How to Refresh All Pivot Tables in Excel (3 Ways)
2. Using VBA Macros
If we face the same problem for many sheets then it will consume time to change the title name by typing space manually. So we can use VBA to solve the problem quickly and smartly by changing all the title names at a time.
Steps:
- Like the previous method, select any cell of the dataset.
- Then click as follows: Insert > Pivot Table.
- Then, after appearing 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.
- Later, drag Salesperson to ROWS field and Units and Sales to VALUES field.
Then your Pivot Table will look like this and if we try to change the title name Sum of Units to Units and Sum of Sales to Sales then we’ll face the problem- “Pivot table field name already exists” as we used these names in our dataset.
- Now to open the VBA window right-click on the sheet title and click the View Code option from the context menu.
- After that write the following codes given below. This code will change all the source field names with a space after the names.
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- Now click the Run icon to run the codes. A Macros dialog box will open up.
- Select the Macro as we specified in the codes.
- Finally, just press Run.
Now you will see that all the source names are changed successfully without facing any trouble.
Read more: How to Use an Excel Table with VBA
Conclusion
I hope the procedures described above will be good enough to solve the ‘PivotTable field name already exists’ issue. Feel free to ask any question in the comment section and please give me feedback.
Excellent – saved me a lot of time!