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.

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.

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

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.

NOTE: There is no sample file specifically for this example. You can try this technique with any pivot table.

Download the Sample Files

1. Quick Layout Changes: Download the Pivot Layout Quick Changes workbook to follow along with the first 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: Download 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 Values Fields: Download 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.

Pivot Table Tools

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

• copy the formatting from one pivot table, and apply it to another pivot table.
• change all the values from Count to Sum
• remove the "Sum of" from all the headings

and much more!

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