Home > Pivot Tables > Format > Layout

Pivot Table Field Layout Changes

Quick way to move pivot fields to different position, and change values from horizontal to vertical. Or, use a macro to quickly remove selected Value fields.

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

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"
    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
  MsgBox "No pivot tables on active sheet"
End If

  Set pf = Nothing
  Set pt = Nothing
  Set ws = Nothing
  Application.ScreenUpdating = True
  Exit Sub

  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: July 1, 2024 3:33 PM