Excel Pivot Table: Change Multiple Field Settings

To change multiple field settings in an Excel pivot table at once, you can select multiple fields in the Field List, right-click, and choose Field Settings, or use the PivotTable Analyze tab’s Field Settings button. This lets you adjust number formats, subtotals, layout, and other options for several fields simultaneously, rather than editing each one individually.

Why Change Field Settings?

Pivot table fields control how data is summarized, displayed, and formatted. Default settings often show raw sums, but you may need percentages, averages, or custom number formats. Changing field settings lets you:

Alter the summary function (Sum, Count, Average, Max, Min, etc.)
Show values as a percentage of a row, column, or grand total
Adjust number formatting (currency, decimals, thousands separators)
Control subtotals and grand totals for a specific field
Change layout options like inserting blank rows after each item

Without changing settings, your pivot table might display raw counts instead of meaningful percentages or averages, leading to misinterpretation of your data.

How to Select Multiple Fields

In the PivotTable Field List pane, you can select multiple fields using standard Windows selection techniques:

Click the first field, then hold Ctrl and click additional fields to select non-adjacent ones.
Click the first field, then hold Shift and click the last field to select all fields in between.

Once selected, right-click any of the highlighted fields and choose Field Settings. This opens a dialog box that applies changes to all selected fields. Alternatively, with fields selected, go to the PivotTable Analyze tab (or Options in older Excel versions) and click Field Settings.

What Settings Can You Change for Multiple Fields?

The Field Settings dialog has two tabs: Subtotals & Filters and Layout & Print. When multiple fields are selected, the dialog applies the same options to all of them.

Subtotals: Choose Automatic, None, or Custom (e.g., Sum, Average, Max). This is useful when you want consistent subtotal behavior across several fields.
Layout: Set how items are displayed (e.g., Show items in outline form or tabular form). You can also insert blank lines after each item.
Print: Repeat item labels on each printed page, or set print options like “Insert page break after each item.�

Note that the Show Values As and Number Format options are not available in this multi-select dialog. For those, you need to change each field individually or use a different approach (see below).

Changing Number Format for Multiple Fields

If you want to apply the same number format (e.g., currency, decimal places) to several fields, the multi-select Field Settings dialog doesn’t include that option. Instead, you can:

Select the fields in the Field List (using Ctrl or Shift).
Right-click and choose Number Format (this option appears when multiple fields are selected).
Choose the desired format in the Format Cells dialog and click OK.

This applies the number format to all selected fields at once. Alternatively, you can select the data cells in the pivot table and apply formatting directly, but that only affects the visible cells, not the field definitions.

Changing Summary Function for Multiple Fields

To change the summary function (e.g., from Sum to Average) for multiple fields, the multi-select Field Settings dialog does not allow it directly. However, you can:

Right-click a field in the Field List and choose Value Field Settings (this works for fields in the Values area).
In the dialog, go to the Summarize Values By tab and choose the function.
Repeat for each field – there is no multi-select shortcut for this specific setting.

If you have many fields, consider using a pivot table with the same data source but a different layout, or use a macro to automate the changes.

Common Problems and Solutions

Problem: The Field Settings dialog is grayed out.
Solution: Make sure you have selected a field in the Field List. The dialog is only enabled when a field is selected.

Problem: Changes apply to only one field, not all selected.
Solution: Ensure you have selected multiple fields before opening Field Settings. If you click a single field after selection, the selection may be lost.

Problem: Number format changes don’t stick.
Solution: When you refresh the pivot table, number formats can revert. To prevent this, right-click the pivot table, choose PivotTable Options, and on the Layout & Format tab, uncheck “Preserve cell formatting on update.�

Alternatives for Bulk Changes

If you need to change settings for many fields repeatedly, consider these alternatives:

Pivot Table Styles: Use built-in styles to quickly change the look, but they don’t affect field settings like subtotals or number formats.
Format Painter: Copy formatting from one set of cells to another, but this only affects cell formatting, not field definitions.
VBA Macro: Write a simple macro to loop through all pivot fields and apply the desired settings. This is the most efficient for large pivot tables.

For example, a macro could set all value fields to use the number format “#,##0.00� and change their summary function to Average. This requires basic knowledge of VBA but saves time for repetitive tasks.

Frequently Asked Questions

Can I change the summary function for multiple fields at once?

No, the multi-select Field Settings dialog does not include the “Summarize Values By� tab. You must change the summary function for each value field individually. However, you can use a VBA macro to automate this across all fields.

How do I apply the same number format to several pivot fields?

Select the fields in the Field List, right-click, and choose Number Format. This opens the Format Cells dialog where you can set the format for all selected fields at once. Alternatively, you can use the “Preserve cell formatting� option to keep manual formatting after refresh.

Why does my pivot table show “Count� instead of “Sum�?

This happens when the source data contains blank cells in the field, or the field is text. To change it, right-click the field in the Values area, choose Value Field Settings, and select Sum. If the field is text, you’ll need to convert it to numbers in the source data.

Can I set subtotals for multiple fields at once?

Yes, select multiple fields in the Field List, then open Field Settings. On the Subtotals & Filters tab, choose the desired subtotal option (e.g., None, Automatic, or Custom). This applies to all selected fields.

How do I prevent number formats from resetting when I refresh?

Right-click the pivot table, choose PivotTable Options, go to the Layout & Format tab, and uncheck “Preserve cell formatting on update.� This allows your manual number formats to persist after refreshing.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →