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)
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.
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.
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
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.
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,
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
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
Last updated: January 15, 2022 12:14 PM