Home > Pivot Tables > Format > Layout

Pivot Table Field Layout Changes

How to remove Sum Of in Excel pivot field headings, for one or more columns - manually, or with a quick macro. Quick way to move pivot fields to different position, and change values from horizontal to vertical.

drag Values button to Rows area

Move Pivot Field in Excel

After you create an Excel pivot table, you can move, add or remove the pivot fields, to "pivot" the summarized data.

A quick way to move a pivot field from one part of a pivot table (Filters, Columns, Rows, Values) to another, is by dragging field names in the PivotTable Fields list.

For example, in the screen shot below, the Category and Region fields are in the Rows area.

  • Category names (Bars, Cookies, Crackers, Snacks) are in column B, listed vertically
  • Region names (East, West) appear in column C, listed vertically

You can also see Categry and Region fields in the PivotTable Fields list, at the right side of the screen shot.

In the PivotTable Fields list, I'm ready to drag the Region field button to a different location:

  • from the Rows area
  • to the Columns area

move region field from rows area to columns area

Pivot Field in Different Location

After I dragged the Region field button to the Columns area, the pivot table layout changed on the worksheet.

  • Region names (East, West) have moved to Row 5, listed horizontally, instead of vertically
  • Category names are still in column B, listed vertically

This layout is more compact than the previous layout, and I find it easier to compare the sales for each region and category, in this format.

region field in columns area

Rename Value Field Headings

In Microsoft Excel, when you add fields to the Values area of a pivot table, the headings have "Sum of" or "Count of" automatically added to the field name. You can remove that extra text, like I've done in the screen shot below for the Quantity field.

There's a little trick to it though, so watch the short video below, to see the steps.

rename pivot fields

Video: Remove Sum Of from Pivot Headings

To see how to change one heading, or multiple headings, in a pivot table report, watch this short video. Written instructions are below the video, as well as the full video transcript.

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

Video Timeline:

  • 0:00 Introduction
  • 0:25 Change the heading
  • 0:38 Error message
  • 0:49 View Field Settings
  • 1:11 Change the Custom Name
  • 1:29 Change Quantity heading
  • 1:46 Make columns narrower
  • 2:07 Change multiple headings

Video Transcript

If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript

Video Transcript: Remove Sum Of in Pivot Headings

Here is the full transcript for the Remove Sum Of in Pivot Headings video.

When you add fields to the values area of a pivot table, it automatically puts either Sum of, or Count of, in front of the field name, and that can make those columns a little too wide.

You can change those headings and get rid of that Sum of, or Count of. There's a little trick you have to do though, so that the pivot table will allow you to make the change.

I'll try and change this heading;

• I'll click on that cell

• and in the formula bar, I'll select Sum of,

• and delete everything that's in front of that total price.

When I press enter, Excel shows a message that warns me, PivotTable field name already exists.

• So I'll click, OK

• Then I'll press escape to get out of that cell.

If I take a look at this field, I'll right-click and go to Value Field Settings

We'll see that there is a source name, which is the field name in the source data. And then a custom name, which is Sum of, and then the field name.

The custom name, can't be the same as any source name in the pivot table.

So instead of trying to delete everything, including that space, I'm just going to delete Sum of.

That leaves a space in front of total price.

And when I click OK, now it accepts that name because it's not exactly the same. It's got a space in front of it.

I could do the same thing over here.

I'll select Sum of, but you can see the space is still there.

When I press delete and enter it accepts that name, because again, it's not exactly the same as the source name.

So with that little trick, you can have narrower columns.

The pivot headings look the same as the field names, but with that space at the front, they're different enough that Excel will allow them.

Now I changed each of those individually, but I'm going to take them out of the pivot table and then put them back in. And they get those long names again.

If you want to change several at once:

• you can select them

• then press Ctrl + H to open the find and replace window

• We want to replace Sum of, but don't type a space. We just want to replace the words and leave that leading space.

• We want to replace that with nothing.

• So I'll click Replace All, OK, and Close

and it fixes all the headings at once.

Then you can
• select the columns

• double-click to resize them

and your pivot tables should be much narrower.

For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com

Renaming Pivot Table Values Fields

When you add PivotTable fields to the Values area of a pivot table, they are automatically 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.

Name Restriction

  • The new column heading that you type cannot be exactly the same as the original field name.
  • For example, if the original field name in the source data 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

How to Change 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, to change multiple value field headings 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

Pivot tables are a quick way to summarize your data, and they have another advantage too. After you build the pivot table, it’s easy to change pivot table layout, to get a different type of summary.

After you create a pivot table, take a few minutes to see what happens when you change the pivot table layout.

  • New Ideas: When you experiment, you might find a new pivot table layout that does a better job of summarizing the data
  • No Risk: After trying different layouts, you might not find anything better than the original pivot table layout. To get back to that original pivot table layout, use the Undo command, to reverse the past few steps.

Watch the video below, and get the Excel workbook in the Download section, at the end of this page.

Video: Create Quick Pivot Table and Change Layout

In the "Make Quick Changes to Pivot Table Layout" video below, you'll see the steps to 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.

Video Timeline

  • 0:00 Introduction
  • 3:04 Create an Instant Pivot Table
  • 1:26 Pivot Table Field List
  • 2:12 Add and Remove Fields
  • 2:51 Add a Column Field
  • 3:31 Add Another Value Field
  • 4:11 Move the Value Fields
  • 4:55 Reposition the Value Fields
  • 5:15 Change Your Pivot Table
  • 5:30 Get the Sample File

Arrange Multiple Value Fields

If you have 2 or more fields in the Values area of the pivot table, Excel automatically creates a new field in the layout, called “Values”.

Watch this short video tutorial, to see how use the Values button, to move the data fields in an Excel pivot table.

With these steps, you can change the pivot table layout from horizontal to vertical, or change from vertical to horizontal. Written steps are below the video

This is a slightly longer video, that also shows how to change the pivot table's report type.

Arrange Multiple Value Fields

When you add multiple Value fields to the pivot table, by default the value headings are arranged horizontally, across the top.

In the screen shot below, there are two Values fields in the layout - Units and Total.

Those value fields are arranged horizontally, with the headings Sum of Units, and Sum of Total.

pivot table values in horizontal layout

Move the Values

For some pivot tables, you might want to to change the Values to a vertical layout, to make the pivot table tall and narrow. I use this arrangement sometimes, if a pivot table report needs to be printed in Portrait layout, instead of Landscape layout..

To change the value fields to a vertical layout, follow these steps:

  • Open the PivotTable Field List, if it isn't open already.
  • Next, point to the Values button, in the Column Labels box, in the field list
  • When the pointer changes to a four-headed arrow, drag the Values button to the Row Labels area, below the last Row field.

drag Values button to Rows area

  • Release the mouse button, to drop the Values button in that location

Values button in Rows area

Values Shown Vertically

After you move the Values label to the Row Labels area, the data fields are arranged vertically.

In the screen shot below, the pivot table is in Outline Form layout, and the Value fields are listed vertically in column B.

Values button in Rows area Outline form

If the Report Layout is Compact Form, all the Row labels are listed in a single column.

In the screen shot below, the Value field headings are indented under each of the "Item" Row headings.

Values button in Rows area Compact form

Move Values Above Row Labels

In most pivot tables, I put the Values button below the other Row fields, iif I move Values to the Row Labels area.

But, for a different view of the data, you could move the Values button to the top of the Row Labels fields, to group the data by the Values.

In the screenshot below, the Values are listed first in the Row Labels. That way, it's easy to compare the Sum of Units for all of the items sold, or compare the item totals.

Values button at top of Rows labels in Compact form

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

select values in pivot table

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

selected value fields removed

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

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

Pivot Table Introduction

Grouping Data

Clear Old Items in Pivot Table

Last updated: February 13, 2024 3:30 PM