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
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.
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:
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.
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.
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.
There is a Slicer connected to this pivot table, and the Slicer is on the WO_Pivot worksheet.
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.
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.
The dynamic list of matching fields is named SelFields, and you can see that name, and its Refers To formula, in the Name Manager.
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.
When you click a button on the pivot table Slicer:
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
When the ptGroup updates, a macro named PTShowCategory runs. To see that macro's code, right-click on the macro name, and click Definition.
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
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.
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
When the ptFn updates, a macro named PTChangeFN runs. To see that macro's code, right-click on the macro name, and click Definition.
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
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):
And this formula calculates the labour cost, in column L (Lbr Cost):
To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:
and much more!
Last updated: August 10, 2020 12:49 PM