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