Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

How to add multiple fields into pivot table?

When we create a pivot table, we need to drag the fields into the Row Labels or Values manually one by one. If we have a long list of fields, we can add a few row labels quickly, but the remaining fields should be added to the Value area. Are there any quick methods for us to add all the other fields into the Value area with one click in the pivot table?

Add multiple fields into Value area of pivot table with VBA code

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

arrow blue right bubble Add multiple fields into Value area of pivot table with VBA code

Unfortunately, there is no checkbox for us to quickly check all the fields in the Pivot Table Field List with one click, but, with the following VBA code it can help you to add the remaining fields to the Values area at once. Please do as follows:

1. After creating the pivot table, firstly, you should add the row label fields as your need, and leaving the value fields in the Choose fields to add to report list, see screenshot:< /p>

doc-add-multiple-fields-1

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Add multiple fields into Value area of pivot tables in active worksheet

Sub AddAllFieldsValues()
'Update 20141112
    Dim pt As PivotTable
    Dim I As Long
    For Each pt In ActiveSheet.PivotTables
        For I = 1 To pt.PivotFields.Count
            With pt.PivotFields(I)
              If .Orientation = 0 Then .Orientation = xlDataField
            End With
        Next
    Next
End Sub

4. Then press F5 key to run this code, all the remaining fields have been added to the Values area at same time, see screenshot:

doc-add-multiple-fields-1

Note: This VBA code is applied to all pivot tables of active worksheet.

Related articles:

How to list all pivot tables form a workbook?

How to check if a pivot table exists in a workbook?

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom

 

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Denis · 7 months ago
    Задача же не всегда стоит в получении списка, хочется допустим поправить названия таблиц или источников данных. Можете реализовать?

  • To post as a guest, your comment is unpublished.
    test · 11 months ago
    Is there a way to edit the macro that would send the fields into the rows label instead of the values?
  • To post as a guest, your comment is unpublished.
    Che · 1 years ago


    If "Values are "0" or "Null" how to suppress Row Values from Displaying
    (ex. Sum of 2743 Difference)


    Pivot "Rows" Pivot "Values"


    Row Labels

    NP9 - Total Unexpended Appropriations

    Sum of OTHER Difference 1,045,355,165.31
    Sum of 3200 Difference 0.00
    Sum of 0108 Difference 12,873,630.29
    Sum of 4586 Difference (33,024,706.93)
    Sum of 0148 Difference (72,046,783.14)
    Sum of 0129 Difference (5,583,891.98)
    Sum of 4598 Difference (929,574.56)
    Sum of 2743 Difference
    Sum of 4041 Difference 0.00
    Sum of 2799 Difference


  • To post as a guest, your comment is unpublished.
    Che · 1 years ago




    Note: Che's error message:

    Sub AddAllFieldsValues()
    'Update 20141112
    Dim pt As PivotTable
    Dim I As Long
    For Each pt In ActiveSheet.PivotTables
    For I = 1 To pt.PivotFields.Count
    End With
    Next
    Next
    End Sub




    Note: Che's trying to add the following fields/snapshot- "Sum of OTHER Difference, Sum of 4096 Difference" over 80 fields to be added


    OTHER Difference
    4096 Difference
    4016 Difference


  • To post as a guest, your comment is unpublished.
    RAP · 1 years ago
    THANK YOU SO MUCH!!!

  • To post as a guest, your comment is unpublished.
    Pr · 1 years ago
    Thanks, helped me a lot

  • To post as a guest, your comment is unpublished.
    Neville · 3 years ago
    Works like a charm. Thanks
  • To post as a guest, your comment is unpublished.
    Zeeshan · 3 years ago
    Hi.

    It gives me a compile error when I click on Run

    Please help
  • To post as a guest, your comment is unpublished.
    Shaun Sheys · 3 years ago
    Does "xlDataField" not work with PowerPivot? I've got the code below, which is used to add all measures to the pivot table. It works with "xlColumnField" and "xlRowField". Any ideas how to make this work?

    Sub AddAllFieldsValues()
    Dim pt As PivotTable
    Dim iCol As Long
    Dim iColEnd As Long

    Set pt = ActiveSheet.PivotTables(1)

    With pt

    iCol = 1
    iColEnd = .CubeFields.Count

    For iCol = 1 To iColEnd
    With .CubeFields(iCol)
    If .Orientation = xlHidden Then
    .Orientation = xlDataField
    End If
    End With
    Next iCol

    End With

    End Sub
  • To post as a guest, your comment is unpublished.
    Sajal · 4 years ago
    This is great! Thanks.
  • To post as a guest, your comment is unpublished.
    sourav · 4 years ago
    Its takiung value as count. how can i convert all count as Sum
    • To post as a guest, your comment is unpublished.
      El · 4 years ago
      You can click on your field (in the area where you drag your fields into : column labels, row labels etc.) then click on "Value field setting" then you will get a dropdown list from where you can chose "Sum" !
      • To post as a guest, your comment is unpublished.
        A · 4 years ago
        VBA code: Change multiple field settings in pivot table
        https://www.extendoffice.com/documents/excel/2354-excel-pivot-table-change-multiple-field-settings.html
  • To post as a guest, your comment is unpublished.
    Nishant · 4 years ago
    Is there anyway if we want to add some selected columns from the base file instead of all.?
  • To post as a guest, your comment is unpublished.
    Shaun · 5 years ago
    Is there a way to run this only for excluded fields, so that when I run it I do not end up with duplicate fields? ie. "Field" and "Field_2"
    • To post as a guest, your comment is unpublished.
      Jeff · 4 years ago
      [quote name="Shaun"]Is there a way to run this only for excluded fields, so that when I run it I do not end up with duplicate fields? ie. "Field" and "Field_2"[/quote]

      Yes- two examples

      Use this to add all remaining unchecked items to ROWS

      Sub AddAllFieldsRow()
      Dim pt As PivotTable
      Dim iCol As Long
      Dim iColEnd As Long

      Set pt = ActiveSheet.PivotTables(1)

      With pt

      iCol = 1
      iColEnd = .PivotFields.Count

      For iCol = 1 To iColEnd
      With .PivotFields(iCol)
      If .Orientation = 0 Then
      .Orientation = xlRowField
      End If
      End With
      Next iCol

      End With

      End Sub

      Use this to add a remaining unchecked items to VALUES

      Sub AddAllFieldsValues()
      Dim pt As PivotTable
      Dim iCol As Long
      Dim iColEnd As Long

      Set pt = ActiveSheet.PivotTables(1)

      With pt

      iCol = 1
      iColEnd = .PivotFields.Count

      For iCol = 1 To iColEnd
      With .PivotFields(iCol)
      If .Orientation = 0 Then
      .Orientation = xlDataField
      End If
      End With
      Next iCol

      End With

      End Sub
      • To post as a guest, your comment is unpublished.
        Valerie · 4 years ago
        Yeah, to echo markus, is there a way to edit the code to only add unchecked fields that have certainly field labels (like with an if contains function)? For example, I have a question that is actually 50 yes/no values by topic and I'd like to move all of those to the values field. They all begin with the same question label q9_[specific question topic].
      • To post as a guest, your comment is unpublished.
        Markus · 4 years ago
        Is there a way to only add some ekstra fields, not the remaining or all of them, but just a pair of those who is not checked already? :)
  • To post as a guest, your comment is unpublished.
    Joe · 5 years ago
    This is fantastic. Can the macro be modified to add all to Row Labels instead of Values?
  • To post as a guest, your comment is unpublished.
    Razvan · 5 years ago
    Wow. It works very very well. Many thanks!
    Nonetheless, I am kinda amazed that Microsoft were not having a built-in feature for a rather simple task...
  • To post as a guest, your comment is unpublished.
    Diogo · 5 years ago
    Really nice and useful macro. However I had already added half of my fields into the Values and when I ran this macro it added all the same fields again. Any idea why?