Pivot Table Field Layout Changes

How to remove Sum Of in Excel pivot field headings. Move pivot fields to different position. Move all value fields. Remove selected value fields (macro)

Rename Value Field Headings

When you add fields to the Values area, the headings have "Sum of" or "Count of" added to the field name. To see how to change one heading, or multiple headings, watch this short video. Written instructions are below the video.

NOTE: If you have several fields to rename, you can use a macro instead of manually changing the headings.

Rename Values Fields

When you add fields to the Values area, they are renamed. For example, 'Units' becomes 'Sum of Units'. Instead of using these default names, you can change the field names to something shorter, or more descriptive, such as Units Sold.

Note: The typed name can't be the same as the original field name. For example, if the original field name is Quantity, you can't change 'Sum of Quantity' to 'Quantity'. However, you can type the original field name, and add a space character at the end, e.g. 'Quantity ' or at the beginning -- ' Quantity', as shown in the screen shot below.

Change the Value Field Name

To change a single heading, follow these steps.

1. Select the heading cell in the Pivot Table.
2. Type a new heading that is different from the field name in the source data (TIP: Add a space at the beginning or end of the field name, to make it slightly different).
3. Press the Enter key, to complete the renaming.

Or, if you have multiple value fields in the pivot table:

1. Select all the captions
2. Press Ctrl + H to open the Replace Window
3. In the Find What box, type "Sum of" (do not add a space at the end)
4. Leave the Replace With box empty
5. Click Replace All.

Macros to Rename Values Fields

These macros will change the field names to remove the "Sum of", and adds a space character at the end of the field name.

NOTE: If you checked the box to "Add this data to the data model", when creating your pivot table, the pivot table is OLAP-based, instead of a Normal pivot table. For OLAP pivot tables, or to handle both types of pivot tables, use the macros on the Pivot Table Value Heading Macros page.

Selected Pivot Table Only

This macro removes the "Sum of" from Value fields in the selected pivot table

Sub ChangeCaptionsSelPT()
Dim pf As PivotField
Dim pt As PivotTable
On Error Resume Next
Set pt = ActiveCell.PivotTable

If Not pt Is Nothing Then
  For Each pf In pt.DataFields
      pf.Caption = pf.SourceName & " "
  Next pf
Else
  MsgBox "Please select a pivot table cell and try again."
End If

End Sub

All Pivot Tables on Worksheet

This macro removes the "Sum of" from Value fields in all pivot tables on the active worksheet

Sub ChangeCaptionsSheet()
Dim pf As PivotField
Dim pt As PivotTable
For each pt in ActiveSheet.PivotTables
  For Each pf In pt.DataFields
    pf.Caption = pf.SourceName & " "
  Next pf
Next pt
End Sub

All Pivot Tables in Workbook

This macro removes the "Sum of" from Value fields in all pivot tables on all worksheets in the active workbook

Sub ChangeCaptionsWorkbook()
Dim ws as Worksheet
Dim pf As PivotField
Dim pt As PivotTable
For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    For Each pf In pt.DataFields
      pf.Caption = pf.SourceName & " "
    Next pf
  Next pt
Next ws
End Sub

Change Pivot Table Layout

Create a quick pivot table with Excel's Recommended Pivot Tables feature. Then, make simple changes to the layout, to get a different view of your data. This video shows the steps.

Arrange Multiple Data Fields

Watch this short video tutorial, to see how use the Values button, to move the data fields in Excel. Written instructions are below the video

Arrange Multiple Data Fields

In Excel 2007 and later, when you add multiple data fields to the table, by default the data headings are arranged horizontally.

It's easy to change the data to a vertical layout though, which was the default setting in older versions of Excel.

To change the data to a vertical layout, drag the Values button in the Pivot Table Field List, from the Column Labels area to the Row Labels area.

In most cases, the Values button should be positioned below the other fields in the Row Labels area.

After you move the Values label to the Row Labels area, the data fields will be arranged vertically. This pivot table is in Tabular Form layout.

If the Report Layout is Compact Form, the data field headings are indented under the other Row headings.

Remove Selected Values (Macro)

Use this macro to quickly remove several value fields at once. It's a real time saver, when working with big pivot tables.

After you add the macro code to your workbook, follow these steps to run the macro,

1. First, select one or more value field numbers
2. Then, at the top of Excel, go to the View tab and click Macros
3. In the list of macros, select PivotRemoveValuesSel
4. Click the Run button

In this example, cells in 3 of the Values fields are selected

After running the macro, all 3 Value fields have been removed, and the same 3 cells are still selected.

Copy the macro code below, and paste it into a regular code module in your workbook. Or, download the Remove Value Fields workbook, and copy the code from there.

Sub PivotRemoveValuesSel()
'from www.contextures.com
'removes selected value fields
'from pivot table layout
On Error GoTo errHandler

Dim pt As PivotTable
Dim pf As PivotField
Dim pfHide As PivotField
Dim df As PivotField
Dim ws As Worksheet
Set ws = ActiveSheet
 
Dim arrData() As Variant
Dim lCount As Long
Dim c As Range
Dim i As Long
Dim lHide As Long

lCount = Selection.Cells.Count

If ws.PivotTables.Count > 0 Then
  On Error Resume Next
    Set pt = ActiveCell.PivotTable
    Set pf = ActiveCell.PivotField
  On Error GoTo errHandler
  
  If pf Is Nothing Then
    MsgBox "Please select cell in pivot table"
  Else
    Application.ScreenUpdating = False
    ReDim arrData(1 To lCount)
    i = 1
    For Each c In Selection
        arrData(i) = c.PivotField
        i = i + 1
    Next c
     
    On Error Resume Next
    lHide = 1
    For lHide = 1 To lCount
      Set pfHide = Nothing
        Set pfHide _
          = pt.PivotFields(arrData(lHide))
        For Each df In pt.DataFields
          If df.SourceName = pfHide.Name _
              Or df.Name = pfHide.Name Then
            With df
              'item in "Values" field
              .Parent.PivotItems(.Name) _
                .Visible = False
            End With
            Exit For
          End If
        Next df
        'in case it's last (not calc field)
        'and no "Values" field exists
        pfHide.Orientation = xlHidden
    Next lHide
        
    On Error GoTo errHandler
    pt.ManualUpdate = False

  End If
Else
  MsgBox "No pivot tables on active sheet"
End If

exitHandler:
  Set pf = Nothing
  Set pt = Nothing
  Set ws = Nothing
  Application.ScreenUpdating = True
  Exit Sub

errHandler:
  MsgBox Err.Number & ": " _
    & Err.Description
  GoTo exitHandler

End Sub

Move a Pivot Field in Excel

Drag a field button from one area in the Field List to another. For example, drag the Region field from the Rows area to the Columns area.

Before the move, region names are listed in column C.

After the move, region names are across the top of the pivot table, instead of down column C

Get the Sample Files

1. Quick Layout Changes: Get the Pivot Layout Quick Changes workbook to follow along with the video, "Quick Changes to Pivot Table Layout". It has sample food sales data. The zipped file is in xlsx format, and does not contain any macros.
2. Arrange Value Fields: Get the Pivot Layout Stationery workbook to follow along with the video, "Arrange pivot table data vertically". It has sample stationery sales data. The zipped file is in xlsx format, and does not contain any macros.
3. Rename Value Fields: Get the zipped sample file with macros that remove "Sum of" from the value field headings. The workbook is in xlsm format, and contains macros. Be sure to enable macros when you open the file, if you want to test them.
4. Remove Value Fields: Get the Remove Selected Value Fields workbook. The zipped workbook is in xlsm format, and contains macros. Be sure to enable macros when you open the file, if you want to test the macro.

More Pivot Table Tutorials

FAQs - Pivot Tables

"Sum of" Headings - Macros to Remove

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table