Change Field Names in Pivot Table Source Data

Occasionally, you might need to change a column heading in the pivot table’s source data. For example, you could change a column heading from Qty to Quantity, to make it easier to understand.

Pivot Table Field Name 01

However, when you refresh the pivot table after making the changes, the renamed field will disappear from the pivot table layout. In our example, the Qty field is removed from the layout. It isn’t automatically replaced by the Quantity field, even though it’s in the same column heading cell in the source data.

Pivot Table Field Name 02

If you change the column headings, you’ll have to add the revised field to the pivot table layout again. If you had number formatting in the old pivot field, you’ll have to reapply the number formatting you had previously applied. 

Change Captions Instead

If possible, instead of changing the column headings in the source data, create custom names for the fields in the pivot table instead.

For example, to create a custom name for the Qty field:

  1. In the pivot table, click on the cell that contains the pivot field name, Qty.
  2. Type the custom name, Quantity.
  3. Press the Enter key, to complete the renaming.

Pivot Table Field Name 03

_____________________

For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

_________________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in pivot fields, pivot table tutorial. Bookmark the permalink.

5 Responses to Change Field Names in Pivot Table Source Data

  1. Jenny says:

    Hello,

    Any possiblity to change the pivot table column heading to a dynamic name ie. the report change weekly the column Sum of Qty change from Week 1 Sales Qty to Week 2 Sales Qty, can formula apply to C4?

    Thanks
    Jenny

  2. @Jenny, you can’t use a formula as a pivot table heading. You could use a macro to change the heading text each week, and set it up to run automatically when the workbook opens.

  3. Steve says:

    Hi Debra,

    I was trying to take your advice above and make the change via vba, but was unsuccessful using several variations of the code below. Any ideas on how to get this to work? Thanks!

    Sub test()

    i = Worksheets(“Consolidated”).Range(“M1″).Value ‘the data source header cell that changes

    Worksheets(“Summary”).PivotTables(1) _
    .PivotFields(“Category”).SourceName = i

    End Sub

  4. Johnny says:

    Hi,

    Having some wizz pivot masters around here, and also being beyond basic excel myself, I need some help on the following.

    In the build up of the Pivot table I had a date field on the left comming from a source.
    Then, one of these dates we “abused” and overwrote as “PENDING” in the pivot……now moving along in time this date is becoming an actual date I want to use in the pivot……..so, how do I reverse it back to actually become a date again?
    We tried lots of stuff, but couldn’t work it out…..hope another pivot wizz is reading this (or even pro wizzes making excel) to help solve my issue.

    Thanks!
    Johnny

  5. joeyslaptop says:

    I have the formula “=TODAY()-6″ as my source-data (column) header. Every day I refresh, I have to reformat my Pivot Table. Why can’t the Pivot Table rely on $R$3 rather than on $R$3′s contents?

    It would be wonderful to have the pivot table update on refresh to reflect changed labels.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>