Contextures

Excel Pivot Table Field Layout

After adding fields to a pivot table, move them to a different position, or change their headings

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.

pivot data horizontal 2010

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.

pivot data drag 2010

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

pivot data row labels 2010

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.

pivot data vertical tabular 2010

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

pivot data vertical compact 2010

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.

rename pivot fields

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.

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

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 Row Labels area to the Column Labels area.

move a pivot field demo

Download the Sample File

Download the zipped sample file for this tutorial. The workbook is in xlsm format, and contains macros that remove "Sum of" from the value field headings.

More Pivot Table Tutorials

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table

Search Contextures Sites

 

DVMSP

 

 

Free pivot table tools

 

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: November 8, 2016 7:29 PM