Remove All Selected Pivot Table Value Fields With Macro

Remove All Selected Pivot Table Value Fields With Macro

Pivot tables make it easy to work with your data, and see the summaries in different layouts. To add or remove fields from the layout, you can check or uncheck fields in the PivotTable Field list. In large pivot tables, with lots of fields, this macro makes it easy to remove specific fields from the Values area. Just select one or more fields, and run the macro!

Select Pivot Table Values

In the screen shot shown below, there’s a pivot table which has 5 fields in the Values area

  • Qty
  • AvgCost
  • Subtotal
  • Discount
  • Price

To create a smaller report, I’d like to remove 3 of those value fields, and just leave Qty and Price.

pivot table which has 5 fields in the Values area
pivot table which has 5 fields in the Values area

Fields in PivotTable Field List

I could remove the Value fields manually, one at a time, but I’ll use a macro to make this job easier.

That way, I won’t have to scroll through the list of fields in the PivotTable Field List, looking for each field, to uncheck it.

Sometimes you can only see 2 or 3 fields at a time, and you waste time scrolling up and down, looking for a specific field.

PivotTable Field List
PivotTable Field List

Get the Macro to Remove Selected Value Fields

To remove the selected Value fields, I’ll use a macro named PivotRemoveValuesSel

You can get the macro code from the Pivot Table Field Layout Changes page on my Contextures site:

  • Copy the code from the web page, and paste it into a regular code module in your workbook.

Or, on the same page, download the sample Excel workbook, which has the macro in it

  • Scroll down to the Get the Sample Files section
  • Click on the link to download sample file 4  – Remove Value Fields.

Select Value Fields to Remove

To remove three of the Value Fields, I’ll start by selecting at least one cell in each field that I want to remove.

In this example, I’ll select a cell in each of the following fields – AvgCost, SubTotal and Discount

  • First, click on a cell in one column, to select it
  • Then, press the Ctrl key, and click on more cells, to select them

As you can see, the selected cells can be anywhere in the column. You don’t need to select cells that are all in the same row.

3 value cells selected
3 value cells selected

Run Macro to Remove Selected Value Fields

Next, to remove the selected Value fields, I’ll run the macro named PivotRemoveValuesSel

  1. At the top of Excel, go to the View tab and click Macros
  2. In the list of macros, select PivotRemoveValuesSel
  3. Click the Run button

Here’s the same pivot table, after running the macro.

All 3 of the selected Value fields have been removed from the pivot table layout by the macro, and the same 3 cells are still selected.

selected Value fields have been removed
selected Value fields have been removed

Get the Workbook

You can get the macro code from the Pivot Table Field Layout Changes page on my Contextures site.

You’ll find other pivot table layout tips and macros on that page too.

____________________________________

Remove Selected Pivot Fields With Macro

Remove All Selected Pivot Table Value Fields With Macro

_____________________________

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.