Pivot Table Slicer for Value Groups

Click a Slicer, to quickly show and hide groups of values in a pivot table. Click the Function slicer to change summary function and headings. This technique saves time and space, when there are many values to show in a pivot table

Slicer for Value Groups

This animated screen shot shows how this pivot table technique works. Click a Slicer, to quickly show and hide groups of values in a pivot table. Click the Function slicer to change summary function and headings. This technique saves time and space, when there are many values to show in a pivot table.

Data with Value Groups

In this example, there is a named Excel table with work order data.

The first 8 columns have descriptive fields that can be used in the Row, Column or Filter areas in a pivot table.

The remaining 9 columns have numeric fields, and those can be added to a pivot table's Values area. The numeric fields have values that fall into 4 groups:

• Travel -- Kms, Km Fee
• Labour -- Lbr Hrs, Lbr Cost, Lbr Fee
• Parts -- Parts Cost, Parts Fee
• Total -- Total Cost, Total Fee

Pivot Table Values

If all the numeric fields are added to the pivot table's Values area, the report is crowded, and hard to read.

To focus on one group of costs, you could manually add and remove the Value fields, using the PivotTable Field List. However, that takes time, and might be difficult for people who aren't familiar with pivot tables.

Instead, this example shows how to use a Slicer to quickly add and remove groups of values from the pivot table. There is also a Slicer to change the function that each value field uses.

Create Value Groups

To make it easy to add and remove values from the pivot table, this example has a list of the source data's numeric fields. In the next column, each field is assigned to one of the four value groups - Travel, Labour, Parts or Total.

This is a formatted Excel table, named tblFields.

Value Groups Pivot Table

On the PivotLists sheet in the sample workbook, there is a pivot table, named ptGroup, which is based on the named table, tblFields.

The Group field is in the Filters area, and there's nothing else in the pivot table.

The filter's drop down list shows the four Group names, in alphabetical order.

Value Groups Slicer

There is a Slicer connected to this pivot table, and the Slicer is on the WO_Pivot worksheet.

Fields for Selected Group

On the PivotLists sheet, in cell M4, there is a dynamic array formula, to create a list of fields for the selected group. The list spills down column M, as far as necessary, to show all the matching fields.

=SORT(FILTER(tblFields[Field],tblFields[Group]=K3))

NOTE: If your version of Excel doesn't have dynamic array formulas, such as FILTER, use the INDEX/MATCH functions to create a list of matching items.

Selected Fields List Name

The dynamic list of matching fields is named SelFields, and you can see that name, and its Refers To formula, in the Name Manager.

SelFields:  =PivotLists!$M$4#

The number sign at the end of the formula is the Spill operator, so the SelFields range will include all of field names that are returned by the dynamic array formula in cell M4.

Value Groups Macro

When you click a button on the pivot table Slicer:

• it updates the pivot table named ptGroup
• the pivot table's filter selection changes, to show the Group selected in the Slicer.
• in cell M4, the dynamic array formula automatically updates, to show fields for the selected group

We also want the main pivot table, on the WO_Pivot sheet to update, and show the selected groups fields in the Values area. That happens with programming - code runs automatically when the ptGroup pivot table is updated.

To see the code, right-click the PivotLists sheet tab, and click View Code. The code checks which pivot table was updated, and then runs a macro to update the main pivot table.

Private Sub Worksheet_PivotTableUpdate _
  (ByVal Target As PivotTable)
On Error Resume Next
Application.EnableEvents = False

Select Case Target.Name
  Case "ptGroup"
    PTShowCategory
  Case "ptFn"
    PTChangeFn
End Select

Application.EnableEvents = True
End Sub

PTShowCategory Macro

When the ptGroup updates, a macro named PTShowCategory runs. To see that macro's code, right-click on the macro name, and click Definition.

• First, the macro hides all of the Value fields (DataFields) that are currently in the main pivot table.
• Then it checks the Function slicer, to see which function is selected.
• Next, it goes through the SelFields range, and adds those fields to the main pivot table's Values area.
• Finally, it changes all the Value fields to a number format with a comma separator, and no decimal places

Sub PTShowCategory()
Dim wsPL As Worksheet
Dim wsP As Worksheet
Dim ptW As PivotTable
Dim c As Range
Dim df As PivotField
Dim myFn As Long
Dim myDesc As String
Dim SelFn As String

Set wsPL = Sheets("PivotLists")
Set wsP = Sheets("WO_Pivot")
Set ptW = wsP.PivotTables("ptWO")
SelFn = wsPL.Range("SelFn").Value

For Each df In ptW.DataFields
  df.Orientation = xlHidden
Next df

Select Case SelFn
  Case "Sum"
      myFn = xlSum
      myDesc = " Total"
  Case "Count"
      myFn = xlCount
      myDesc = " Count"
  Case "Avg"
      myFn = xlAverage
      myDesc = " Avg"
  Case "Min"
      myFn = xlMin
      myDesc = " Min"
  Case "Max"
      myFn = xlMax
      myDesc = " Max"
  Case Else
      myFn = xlSum
      myDesc = " Total"
End Select

For Each c In wsPL.Range("SelFields")
    ptW.AddDataField _
      ptW.PivotFields(c.Value), _
      c.Value & myDesc, myFn
Next c

For Each df In ptW.DataFields
  df.NumberFormat = "#,##0"
Next df

End Sub

Function Slicer

There is another Slicer on the WO_Pivot sheet, where you can select a function to use for all the value fields.

The function names are listed in a table named tblFn, on the PivotLists sheet.

There's also a pivot table, named ptFn, which is based on that table.

It has one field, Function, in its Filter area, and cell E3 is named SelFn.

Function Slicer Macro

When you click the Function Slicer, it updates the pivot table named ptFn, and the Worksheet_PivotTableUpdate code runs automatically.

This section of the code applies to the ptFn pivot table

  Case "ptFn"
    PTChangeFn

PTChangeFN Macro

When the ptFn updates, a macro named PTChangeFN runs. To see that macro's code, right-click on the macro name, and click Definition.

• First, the macro checks the named range, SelFn, to see which function is selected.
• Based on that selection, a function code and description are assigned
• Each Value field is changed to the selected function, its heading is changed, and a number format is applied

SubSub PTChangeFn()
Dim wsPL As Worksheet
Dim wsP As Worksheet
Dim ptW As PivotTable
Dim c As Range
Dim df As PivotField
Dim myFn As Long
Dim myDesc As String
Dim SelFn As String
Dim myFmt As String

Set wsPL = Sheets("PivotLists")
Set wsP = Sheets("WO_Pivot")
Set ptW = wsP.PivotTables("ptWO")
SelFn = wsPL.Range("SelFn").Value
myFmt = "#,##0"

Select Case SelFn
  Case "Sum"
      myFn = xlSum
      myDesc = " Total"
  Case "Count"
      myFn = xlCount
      myDesc = " Count"
  Case "Avg"
      myFn = xlAverage
      myDesc = " Avg"
  Case "Min"
      myFn = xlMin
      myDesc = " Min"
  Case "Max"
      myFn = xlMax
      myDesc = " Max"
  Case Else
      myFn = xlSum
      myDesc = " Total"
End Select

For Each df In ptW.DataFields
  df.Function = myFn
  df.Caption = df.SourceName & myDesc
  df.NumberFormat = myFmt
Next df

End Sub

Work Order Formulas

NOTE: This information is not related to the Slicer setup or macros. It just explains how the work order rows are calculated.

To calculate the work order costs and fees, there are standard rates entered on the AdminLists sheet.

Values are typed in the green cells, and the green cells are named. Each cell's name is shown in column B.

Those names are used in formulas on the WorkOrders sheet, to calculate the costs and fees. For example, this formula calculates the travel fee, in column J (Km Fee):

• =IF([@Kms]<MaxKm,0,([@Kms]-MaxKm)*KmCost)

And this formula calculates the labour cost, in column L (Lbr Cost):

• =IF([@Techs]=2,Rate02,Rate01)*[@[Lbr Hrs]]

Download the Sample File

1. Download the Pivot Table Slicer for Value Groups sample file. The file is zipped, and is in xlsm format. The file contains macros which run when the slicers are clicked. Be sure to enable macros when you open the workbook, if you want to test the Slicers.

Related Links

Pivot Table Slicers

Summary Functions

Report Filters

Source Data

Remove Pivot Fields