Skip to main content

How to add multiple fields into pivot table?

Author: Xiaoyang Last Modified: 2014-11-19

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

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>


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
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:


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?

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


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!


Comments (23)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I am running this code. How much time does it take to complete? It's now 45 minutes it's still running. Please help me. By the way, my total field columns are 3600.
This comment was minimized by the moderator on the site
Hi, is there a way to add only the columns unseleted ones? thanks. 
This comment was minimized by the moderator on the site
Задача же не всегда стоит в получении списка, хочется допустим поправить названия таблиц или источников данных. Можете реализовать?
This comment was minimized by the moderator on the site
Is there a way to edit the macro that would send the fields into the rows label instead of the values?
This comment was minimized by the moderator on the site
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

Sum of OTHER Difference 1,045,355,165.31

Sum of 3200 Difference

Sum of 0108 Difference

Sum of 4586 Difference

Sum of 0148 Difference

Sum of 0129 Difference

Sum of 4598 Difference

Sum of 2743 Difference

Sum of 4041 Difference

Sum of 2799 Difference
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations