[Fixed!] Pivot Table Field Name Already Exists (2 Quick Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Different Custom Name to Solve If a PivotTable Field Name Already Exists

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.

Different Custom Name to Solve If a PivotTable Field Name Already Exists

  • Then drag Salesperson to ROWS field and Units to VALUES field.

Different Custom Name to Solve If a PivotTable Field Name Already Exists

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.

Different Custom Name to Solve If a PivotTable Field Name Already Exists

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

Different Custom Name to Solve If a PivotTable Field Name Already Exists

And the Value Field Settings dialog box will appear.

Different Custom Name to Solve If a PivotTable Field Name Already Exists

  • Now change the name from the Custom Name section.
  • Type Units and click OK.

Different Custom Name to Solve If a PivotTable Field Name Already Exists

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


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.

VBA Macros to Solve If a PivotTable Field Name Already Exists

  • 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.

VBA Macros to Solve If a PivotTable Field Name Already Exists

  • Later, drag Salesperson to ROWS field and Units and Sales to VALUES field.

VBA Macros to Solve If a PivotTable Field Name Already Exists

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.

VBA Macros to Solve If a PivotTable Field Name Already Exists

  • 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.


Further Readings

Tags:

Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

1 Comment
  1. Excellent – saved me a lot of time!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo