How to add multiple fields into pivot table?
When creating a PivotTable, we usually drag fields into the Row Labels or Values area one by one. If we have a long list of fields, it’s easy to add a few as row labels, but adding the rest to the Values area can be time-consuming. Is there a quick way to add all the remaining fields to the Values area in just one click?
Add multiple fields into Value area of pivot table with VBA code
Add multiple fields into Value area of pivot table with VBA code
Excel currently does not provide a native feature (such as a "select all" checkbox) in the Field List panel that enables you to add all the remaining fields directly to the Values area with just one click. However, you can achieve this efficiently by using a simple VBA macro. This approach is particularly suitable if you need to repeat this process frequently or are handling a large number of data fields. Below are the detailed steps and necessary cautions to help you apply this method:
1. After you have created your pivot table, add the fields you want to use as Row Labels using the usual drag-and-drop method. Leave the other fields—that you intend to summarize as values—unchecked in the Choose fields to add to report section. For reference, see the following screenshot:
2. Press ALT + F11 to open the Microsoft Visual Basic for Applications editor window. Alternatively, you can access it by clicking Developer > Visual Basic from the Excel ribbon. Ensure you are working in the correct workbook.
3. In the VBA window, go to Insert > Module to create a new module. Then copy and paste the following VBA code into 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. Next, press F5 to run the macro, or click the Run button in the VBA editor toolbar. The macro will process all pivot tables in the current worksheet: for each table, it will add any fields that you have not already added as Row Labels or Columns directly into the Values area. As a result, every available data field will appear as a summarized value in your pivot table, making it quick to perform comprehensive analyses. See the following screenshot for reference:
Note: This macro applies changes to all pivot tables present in the active worksheet when you run it. If you have multiple pivot tables and only want to update one, consider temporarily moving that pivot table to a separate sheet. Before running VBA, always remember to save your work, as macros can make irreversible changes. Also, this macro will attempt to add all remaining fields—including those that may not be suitable for numeric summarization—which could result in summary errors or meaningless results for fields with non-numeric data.
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in