After adding fields to a pivot table, move them to a different position, or change their headings
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.
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
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.
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.
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.
These macros will change the field names to remove the "Sum of", and adds a space character at the end of the field name.
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
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
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
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.
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.
Last updated: September 6, 2016 8:03 AM