In this chapter, you will:
Find out how pivot tables evolved
Build a pivot table
Use advanced pivot table features
Filter a data set
Use the Data Model in Excel
Use other pivot table features
Compare VBA to TypeScript
Pivot tables are the most powerful tools that Excel has to offer. The concept was first put into practice by Lotus, with its Improv product.
I love pivot tables because they help you very quickly summarize massive amounts of data. The name pivot table comes from the ability you have to drag fields in the PivotTable Fields list and have them recalculate. You can use a basic pivot table to produce a concise summary in seconds. However, pivot tables come in so many varieties that they can be the tools of choice for many different uses. You can build pivot tables to act as the calculation engine to produce reports by store or by style or to quickly find the top 5 or bottom 10 of anything.
I don’t suggest that you use VBA to build pivot tables for a user; rather, I suggest that you use pivot tables as a means to an end—to extract a summary of data that you can then take on to better uses.
As Microsoft invests in making Excel the premier choice in business intelligence, pivot tables continue to evolve. They were introduced in Excel 5 and perfected in Excel 97. In Excel 2000, pivot table creation in VBA was dramatically altered. Some new parameters were added in Excel 2002. A few new properties, such as PivotFilters
and TableStyle2
, were added in Excel 2007. These are some of the changes Microsoft has made in the most recent five versions:
Excel 2010 introduced slicers, Repeat All Item Labels, Named Sets, and several new calculation options: xlPercentOfParentColumn
, xlPercentOfParentRow
, xlPercentRunningTotal
, xlRankAscending
, and xlRankDescending
. These do not work in Excel 2007.
Excel 2013 introduced timelines, the xlDistinctCount
function, and the Power Pivot Data Model. You can add tables to the Data Model, create a relationship, and produce a pivot table, but this code does not run in Excel 2010 or earlier.
Excel 2016 introduced AutoGrouping for dates. Although this is automatic, it does not affect pivot tables built with VBA.
Excel 2019 introduced PivotTable defaults. This does not affect pivot tables built with VBA.
Recent changes in Microsoft 365 include the ability to format an intersection of labels in a certain color (for example, West region sales of Bananas).
Because of all the changes from version to version, you need to be extremely careful when writing code in Excel that might be run in other versions.
As I mentioned earlier, this chapter does not mean to imply that you should use VBA to build pivot tables to give to your clients. Instead, the purpose of this chapter is to remind you that you can use pivot tables as a means to an end: You can use a pivot table to extract a summary of data and then use that summary elsewhere.
In the first part of this chapter, the data set is an eight-column by 5,000-row data set, as shown in Figure 12-1. The macros create a regular pivot table from the worksheet data. Near the end of the chapter, an example shows how to build a pivot table based on the Data Model and Power Pivot.
In Excel 2010 and later, you first create a pivot cache object to describe the input area of the data:
Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim Prange As Range Dim FinalRow As Long Dim FinalCol As Long Set WSD = Worksheets("PivotTable") ' Delete any prior pivot tables For Each PT In WSD.PivotTables PT.TableRange2.Clear Next PT ' Define input area and set up a pivot cache FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row FinalCol = WSD.Cells(1, Columns.Count).End(xlToLeft).Column Set Prange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol) Set PTCache = ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=Prange, _ Version:=xlPivotTableVersion14)
After defining the pivot cache, use the CreatePivotTable
method to create a blank pivot table based on the defined pivot cache:
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, _ FinalCol + 2), TableName:="PivotTable1", Version:=xlPivotTableVersion14)
In the CreatePivotTable
method, you specify the output location and optionally give the table a name. After running the preceding code, you have a strange-looking blank pivot table like the one shown in Figure 12-2. You need to use code to drop fields onto the table.
You can now run through the steps needed to lay out the pivot table. In the .AddFields
method, you can specify one or more fields that should be in the row, column, or filter area of the pivot table.
The RowFields
parameter enables you to define fields that appear in the Rows area of the PivotTable Fields list. The ColumnFields
parameter corresponds to the Columns area. The PageFields
parameter corresponds to the Filter area.
The following line of code populates a pivot table with two fields in the row area and one field in the column area:
' Set up the row & column fields PT.AddFields RowFields:=Array("Region", "Customer"), _ ColumnFields:="Product"
To add a field such as Revenue to the Values area of the table, you change the Orientation
property of the field to be xlDataField
.
When you are adding fields to the data area of a pivot table, there are many settings you should control instead of letting Excel’s IntelliSense decide. For example, say that you are building a report with revenue that you will likely want to sum. If you don’t explicitly specify the calculation, Excel scans through the values in the underlying data. If 100% of the revenue columns are numeric, Excel sums those columns. If one cell is blank or contains text, Excel decides on that day to count the revenue, which produces confusing results. Because of this possible variability, you should never use the DataFields
argument in the AddFields
method. Instead, change the property of the field to xlDataField
. You can then specify the Function
to be xlSum
.
Although you are setting up the Data field, you can change several other properties within the same With…End With
block. For example, the Position
property is useful when you are adding multiple fields to the data area. Specify 1
for the first field, 2
for the second field, and so on.
By default, Excel renames a Revenue field to have a strange name like Sum of Revenue. You can use the .Name
property to change that heading back to something normal.
You are not required to specify a number format, but doing so can make the resulting pivot table easier to understand and takes only one extra line of code:
' Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0" .Name = "Revenue " End With
Your pivot table inherits the table style settings selected as the default on whatever computer happens to run the code. If you want control over the final format, you can explicitly choose a table style. The following code applies banded rows and a medium table style:
' Format the pivot table PT.ShowTableStyleRowStripes = True PT.TableStyle2 = "PivotStyleMedium10"
If you want to reuse the data from the pivot table, turn off the grand totals and subtotals and fill in the labels along the left column. The fastest way to suppress the 11 possible subtotals is to set Subtotals(1)
to True
and then to False
, like this:
With PT .ColumnGrand = False .RowGrand = False .RepeatAllLabels xlRepeatLabels ' New in Excel 2010 End With PT.PivotFields("Region").Subtotals(1) = True PT.PivotFields("Region").Subtotals(1) = False
At this point, you have a complete pivot table like the one shown in Figure 12-3.
Listing 12-1 shows the complete code used to generate this pivot table.
Sub CreatePivot() Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim Prange As Range Dim FinalRow As Long Set WSD = Worksheets("PivotTable") ' Delete any prior pivot tables For Each PT In WSD.PivotTables PT.TableRange2.Clear Next PT ' Define input area and set up a pivot cache FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row FinalCol = WSD.Cells(1, Application.Columns.Count). _ End(xlToLeft).Column Set Prange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol) Set PTCache = ActiveWorkbook.PivotCaches.Create( _ SourceType:= xlDatabase, _ SourceData:=Prange.Address, _ Version:=xlPivotTableVersion14) ' Create the pivot table from the pivot cache Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _ Cells(2, FinalCol + 2), TableName:="PivotTable1") ' Set up the row and column fields PT.AddFields RowFields:=Array("Region", "Customer"), _ ColumnFields:="Product" ' Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0" .Name = "Revenue " End With 'Format the pivot table PT.ShowTableStyleRowStripes = True PT.TableStyle2 = "PivotStyleMedium10" With PT .ColumnGrand = False .RowGrand = False .RepeatAllLabels xlRepeatLabels End With PT.PivotFields("Region").Subtotals(1) = True PT.PivotFields("Region").Subtotals(1) = False WSD.Activate Range("J2").Select End Sub
Although pivot tables are incredible, they have annoying limitations; for example, you cannot move or change just part of a pivot table. Try to run a macro that clears row 2. The macro comes to a screeching halt with the error 1004, as shown in Figure 12-4. To get around this limitation, you can copy the pivot table and paste as values.
Knowing the size of a pivot table in advance is difficult. If you run a report of transactional data on one day, you might or might not have sales from the West region, for example. This could cause your table to be either six or seven columns wide. Therefore, you should use the special property TableRange2
to refer to the entire pivot table.
PT.TableRange2
includes the entire pivot table. In Figure 12-5, TableRange2
includes the extra row at the top with the field heading Revenue. To eliminate that row, the code copies PT.TableRange2
but offsets this selection by one row by using .Offset(1, 0)
. Depending on the nature of your pivot table, you might need to use an offset of two or more rows to get rid of extraneous information at the top of the pivot table.
The code copies PT.TableRange2
and uses PasteSpecial
on a cell four rows below the current pivot table. At that point in the code, your worksheet looks as shown in Figure 12-5. The table in J2 is a live pivot table, and the table in J12 is the copied results.
You can then eliminate the pivot table by applying the Clear
method to the entire table. If your code is then going on to do additional formatting, you should remove the pivot cache from memory by setting PTCache
equal to Nothing
.
The code in Listing 12-2 uses a pivot table to produce a summary from the underlying data. At the end of the code, the pivot table is copied to static values, and the pivot table is cleared.
Sub CreateSummaryReportUsingPivot() ' Use a pivot table to create a static summary report ' with product going down the rows and regions across Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim Prange As Range Dim FinalRow As Long Set WSD = Worksheets("PivotTable") ' Delete any prior pivot tables For Each PT In WSD.PivotTables PT.TableRange2.Clear Next PT WSD.Range("J1:Z1").EntireColumn.Clear ' Define input area and set up a pivot cache FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row FinalCol = WSD.Cells(1, Application.Columns.Count). _ End(xlToLeft).Column Set Prange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol) Set PTCache = ActiveWorkbook.PivotCaches.Create( _ SourceType:= xlDatabase, _ SourceData:=Prange.Address, _ Version:=xlPivotTableVersion14) ' Create the pivot table from the pivot cache Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _ Cells(2, FinalCol + 2), TableName:="PivotTable1") ' Set up the row fields PT.AddFields RowFields:="Product", ColumnFields:="Region" ' Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0" .Name = "Revenue " End With With PT .ColumnGrand = False .RowGrand = False .NullString = "0" End With ' PT.TableRange2 contains the results. Move these to J12 ' as just values and not a real pivot table. PT.TableRange2.Offset(1, 0).Copy WSD.Cells(5 + PT.TableRange2.Rows.Count, FinalCol + 2). _ PasteSpecial xlPasteValues ' At this point, the worksheet looks like Figure 12-5 ' Delete the original pivot table and the pivot cache PT.TableRange2.Clear Set PTCache = Nothing WSD.Activate Range("J12").Select End Sub
The code in Listing 12-2 creates the pivot table. It then copies the results and pastes them as values in J12:M13. Figure 12-5, which was shown previously, includes an intermediate result just before the original pivot table is cleared.
So far, this chapter has walked you through building very simple pivot table reports. Pivot tables offer far more flexibility, though. The sections that follow present more complex reporting examples.
In this section, you use the detailed transactional data to produce a series of reports for each product line manager. This section covers the following advanced pivot table steps that are required in these reports:
Group the daily dates up to yearly dates.
Add multiple fields to the Values area.
Control the sort order so the largest customers are listed first.
Use the ShowPages feature to replicate the report for each product line manager.
After producing the pivot tables, convert each pivot table to values and do some basic formatting.
Figure 12-6 shows the report for one product line manager to give you an idea of the final goal.
The report has three fields in the Values area: # Of Orders, Revenue, and % Of Total Revenue. Anytime you have two or more fields in the Values area, a new virtual field named Data becomes available in your pivot table. In Excel today, the Data field appears as Σ Values in the PivotTable Fields list. When creating your pivot table, you can specify Data as one of the column fields or row fields. The position of the Data field is important: It usually works best as the innermost column field.
When you define a pivot table in VBA, you have two column fields: the Date field and the Data field. To specify two or more fields in the AddFields
method, you wrap those fields in an array function.
Use this code to define the pivot table:
' Set up the row fields PT.AddFields RowFields:="Customer", _ ColumnFields:=Array("Date", "Data"), _ PageFields:="Product"
This is the first time you have seen the PageFields
parameter in this chapter. When you are creating a pivot table for someone to use, you should know that the fields in PageFields
allow for easy ad hoc analysis. In this case, the value in PageFields
is going to make it easy to replicate the report for every product line manager.
So far, the .Function
property of the Data fields has always been xlSum
. A total of 11 functions are available: xlSum
, xlCount
, xlAverage
, xlStdDev
, xlMin
, xlMax
, and so on.
Count
is the only function that works for text fields. To count the number of records, and hence the number of orders, add a text field to the data area and choose xlCount
as the function:
With PT.PivotFields("Region") .Orientation = xlDataField .Function = xlCount .Position = 1 .NumberFormat = "#,##0" .Name = "# of Orders " End With
Pivot tables have the amazing capability to group daily dates up to months, quarters, and years. In VBA, this feature is a bit annoying because you must select a date cell before issuing the grouping command.
There are seven choices for grouping times or dates: Seconds, Minutes, Hours, Days, Months, Quarters, and Years. Note that you can group a field by multiple items. To do so, you specify a series of True
/False
values corresponding to Seconds, Minutes, and so on.
For example, to group by Months, Quarters, and Years, you would use the following:
PT.PivotFields("Date").LabelRange.Group , Periods:= _ Array(False, False, False, False, True, True, True)
If you want to group by week, you group only by day and use 7
as the value for the By
parameter:
PT.PivotFields("Date").LabelRange.Group _ Start:=True, End:=True, By:=7, _ Periods:=Array(False, False, False, True, False, False, False)
Specifying True
for Start
and End
starts the first week at the earliest date in the data. If you want to show only the weeks from Monday, January 1, 2018, to Sunday, January 2, 2020, use this code:
With PT.PivotFields("Date") .LabelRange.Group _ Start:=DateSerial(2018, 1, 1), _ End:=DateSerial(2020, 1, 4), _ By:=7, _ Periods:=Array(False, False, False, True, False, False, False) On Error Resume Next .PivotItems("<1/1/2018").Visible = False .PivotItems(">1/2/2020").Visible = False On Error Goto 0 End With
Excel 2016 briefly introduced the concept of AutoGrouping for dates. Excel 2016 had built-in rules that analyzed the span of dates and decided whether dates should be grouped by month or by month, quarter, and year. It was generally hated and removed by Excel 2019. But, the VBA still exists:
PT.AutoGroup
For this report, you need to group only by year, so the code is as follows:
' Group daily dates up to years PT.PivotFields("Date").LabelRange.Group , Periods:= _ Array(False, False, False, False, False, False, True)
After you group daily dates to years, the new Year field is still called Date. This might not always be the case. If you roll daily dates up to months and to years, the Date field contains months, and a new Year field is added to the PivotTable Fields list to hold years.
Excel offers 15 choices on the Show Values As tab of the Value Field Settings dialog box. These calculations enable you to change how a field is displayed in the report. Instead of showing sales, you could show sales as a percentage of total sales. You could show a running total. You could show each day’s sales as a percentage of the previous day’s sales.
All these settings are controlled through the .Calculation
property of the pivot field. Each calculation has its own unique set of rules. Some, such as % Of Column, work without any further settings. Others, such as Running Total In, require a base field. Others, such as Running Total, require a base field and a base item.
To get the percentage of the total, specify xlPercentOfTotal
as the .Calculation
property for the page field:
.Calculation = xlPercentOfTotal
To set up a running total, you have to specify a BaseField
. If you need a running total along a date column, use this:
' Set up Running Total .Calculation = xlRunningTotal .BaseField = "Date"
With ship months going down the rows, you might want to see the percentage of revenue growth from month to month. You can set up this arrangement with the xlPercentDifferenceFrom
setting. In this case, you must specify that the BaseField
is "Date"
and that the BaseItem
is something called “(previous)"
:
' Set up % change from prior month With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Caption = "%Change" .Calculation = xlPercentDifferenceFrom .BaseField = "Date" .BaseItem = "(previous)" .NumberFormat = "#0.0%" End With
Note that with positional calculations, you cannot use the AutoShow
or AutoSort
methods. This is too bad; it would be interesting to sort the customers from high to low and see their sizes in relation to each other.
You can use the xlPercentDifferenceFrom
setting to express revenues as a percentage of the West region sales:
' Show revenue as a percentage of California With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Caption = "% of West" .Calculation = xlPercentDifferenceFrom .BaseField = "State" .BaseItem = "California" .Position = 3 .NumberFormat = "#0.0%" End With
Table 12-1 shows the complete list of .Calculation
options. The second column indicates the compatibility of the calculation with earlier versions of Excel. The third column indicates whether you need a base field or a base item.
TABLE 12-1 Complete list of .Calculation
options
Calculation | Version compatibility | BaseField/BaseItem |
---|---|---|
| All | Both required |
| All | Neither |
| All | Both required |
| All | Both required |
| All | Neither |
| 2010+ |
|
| 2010+ | Both required |
| 2010+ | Both required |
| All | Neither |
| All | Neither |
| 2010+ |
|
| 2010+ |
|
| 2010+ |
|
| All |
|
After that long explanation of the .Calculation
property, you can build the other two pivot table fields for the product line report.
Add Revenue
to the report twice. The first time, there is no calculation. The second time, calculate the percentage of total:
' Set up the data fields – Revenue With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 2 .NumberFormat = "#,##0" .Name = "Revenue " End With ' Set up the data fields - % of total Revenue With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 3 .NumberFormat = "0.0%" .Name = "% of Total " .Calculation = xlPercentOfColumn End With
If you have some customers who were new in year 2, their sales will appear blank in year 1. Anyone using Excel 97 or later can replace blank cells with zeros. In the Excel interface, you can find the setting for this on the Layout & Format tab of the PivotTable Options dialog box. Select the For Empty Cells, Show option and type 0 in the box.
The equivalent operation in VBA is to set the NullString
property for the pivot table to “0”:
PT.NullString = "0"
The Excel interface offers an AutoSort option that enables you to show customers in descending order, based on revenue. The equivalent code in VBA to sort the product field by descending revenue uses the AutoSort
method:
PT.PivotFields("Customer").AutoSort Order:=xlDescending, _ Field:="Revenue "
After applying some formatting in the macro, you now have one report with totals for all products, as shown in Figure 12-7.
As long as your pivot table was not built on an OLAP data source, you now have access to one of the most powerful, but least-well-known, features in pivot tables. The command is called Show Report Filter Pages, and it replicates your pivot table for every item in one of the fields in the Filters area.
Because you built the report in this example with Product as a filter field, it takes only the following code to replicate the pivot table for every product:
' Replicate the pivot table for each product PT.ShowPages PageField:="Product"
After running this code, you have a new worksheet for every product in the data set. From there, you have some simple formatting and calculations to do. Check the end of the macro, shown in Listing 12-3, for these techniques, which should be second nature by this point in the book.
Sub CustomerByProductReport() ' Use a pivot table to create a report for each product ' with customers in rows and years in columns Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim PT2 As PivotTable Dim WS As Worksheet Dim WSF As Worksheet Dim Prange As Range Dim FinalRow As Long Set WSD = Worksheets("PivotTable") ' Delete any prior pivot tables For Each PT In WSD.PivotTables PT.TableRange2.Clear Next PT WSD.Range("J1:Z1").EntireColumn.Clear ' Define input area and set up a pivot cache FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row FinalCol = WSD.Cells(1, Application.Columns.Count). _ End(xlToLeft).Column Set Prange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol) Set PTCache = ActiveWorkbook.PivotCaches.Create( _ SourceType:= xlDatabase, _ SourceData:=Prange.Address, _ Version:=xlPivotTableVersion14) ' Create the pivot table from the pivot cache Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _ Cells(2, FinalCol + 2), TableName:="PivotTable1") ' Set up the row fields PT.AddFields RowFields:="Customer", _ ColumnFields:=Array("Date", "Data"), _ PageFields:="Product" ' Set up the data fields – count of orders With PT.PivotFields("Region") .Orientation = xlDataField .Function = xlCount .Position = 1 .NumberFormat = "#,##0" .Name = "# of Orders " End With ' Group daily dates up to years PT.PivotFields("Date").LabelRange.Group , Periods:= _ Array(False, False, False, False, False, False, True) ' Set up the data fields – Revenue With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 2 .NumberFormat = "#,##0" .Name = "Revenue " End With ' Set up the data fields - % of total Revenue With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 3 .NumberFormat = "0.0%" .Name = "% of Total " .Calculation = xlPercentOfColumn End With ' Sort the customers so the largest is at the top PT.PivotFields("Customer").AutoSort Order:=xlDescending, _ Field:="Revenue " With PT .ShowTableStyleColumnStripes = True .ShowTableStyleRowStripes = True .TableStyle2 = "PivotStyleMedium10" .NullString = "0" End With ' Replicate the pivot table for each product PT.ShowPages PageField:="Product" Ctr = 0 For Each WS In ActiveWorkbook.Worksheets If WS.PivotTables.Count > 0 Then If WS.Cells(1, 1).Value = "Product" Then ' Save some info WS.Select ThisProduct = Cells(1, 2).Value Ctr = Ctr + 1 If Ctr = 1 Then Set WSF = ActiveSheet End If Set PT2 = WS.PivotTables(1) CalcRows = PT2.TableRange1.Rows.Count – 3 PT2.TableRange2.Copy PT2.TableRange2.PasteSpecial xlPasteValues Range("A1:C3").ClearContents Range("A1:B2").Clear Range("A1").Value = "Product report for " & ThisProduct Range("A1").Style = "Title" ' Fix some headings Range("b5:d5").Copy Destination:=Range("H5:J5") Range("H4").Value = "Total" Range("I4:J4").Clear ' Copy the format Range("J1").Resize(CalcRows + 5, 1).Copy Range("K1").Resize(CalcRows + 5, 1). _ PasteSpecial xlPasteFormats Range("K5").Value = "% Rev Growth" Range("K6").Resize(CalcRows, 1).FormulaR1C1 = _ "=IFERROR(RC6/RC3-1,1)" Range("A2:K5").Style = "Heading 4" Range("A2").Resize(CalcRows + 10, 11).Columns.AutoFit End If End If Next WS WSD.Select PT.TableRange2.Clear Set PTCache = Nothing WSF.Select MsgBox Ctr & " product reports created." End Sub
There are many ways to filter a pivot table, from using the slicers, to the conceptual filters, to simply selecting and clearing items from one of the many field drop-down menus.
When you open a field heading drop-down menu and select or clear items from the list, you are applying a manual filter (see Figure 12-8).
For example, say that you have one client who sells shoes. In the report showing sales of sandals, he wants to see just the stores that are in warm-weather states. The code to hide a particular store is as follows:
PT.PivotFields("Store").PivotItems("Minneapolis").Visible = False
This process is easy in VBA. After building the table with Product
in the page field, loop through to change the Visible
property to show only the total of certain products:
' Make sure all PivotItems along line are visible For Each PivItem In _ PT.PivotFields("Product").PivotItems PivItem.Visible = True Next PivItem ' Now – loop through and keep only certain items visible For Each PivItem In _ PT.PivotFields("Product").PivotItems Select Case PivItem.Name Case "Landscaping/Grounds Care", _ "Green Plants and Foliage Care" PivItem.Visible = True Case Else PivItem.Visible = False End Select Next PivItem
Excel 2007 introduced conceptual filters for date fields, numeric fields, and text fields. Open the drop-down menu for any field label in the pivot table, and you can choose Label Filters, Date Filters, or Value Filters. The date filters offer the capability to filter to a conceptual period, such as last month or next year (see Figure 12-9).
To apply a label filter in VBA, use the PivotFilters.Add
method. The following code filters to the customers that start with the letter E:
PT.PivotFields("Customer").PivotFilters.Add _ Type:=xlCaptionBeginsWith, Value1:="E"
To clear the filter from the Customer field, use the ClearAllFilters
method:
PT.PivotFields("Customer").ClearAllFilters
To apply a date filter to the date field to find records from this week, use this code:
PT.PivotFields("Date").PivotFilters.Add Type:=xlThisWeek
The value filters enable you to filter one field based on the value of another field. For example, to find all the markets where the total revenue is more than $100,000, use this code:
PT.PivotFields("Market").PivotFilters.Add _ Type:=xlValueIsGreaterThan, _ DataField:=PT.PivotFields("Sum of Revenue"), _ Value1:=100000
Other value filters might enable you to specify, for example, that you want branches where the revenue is between $50,000 and $100,000. In this case, you specify one limit as Value1
and the second limit as Value2
:
PT.PivotFields("Market").PivotFilters.Add _ Type:=xlValueIsBetween, _ DataField:=PT.PivotFields("Sum of Revenue"), _ Value1:=50000, Value2:=100000
Table 12-2 lists all the possible filter types.
TABLE 12-2 Filter types
Filter type | Description |
---|---|
| Filters for all dates before a specified date. |
| Filters for all dates on or before a specified date. |
| Filters for all dates after a specified date. |
| Filters for all dates on or after a specified date. |
| Filters for all dates in January. |
| Filters for all dates in February. |
| Filters for all dates in March. |
| Filters for all dates in April. |
| Filters for all dates in May. |
| Filters for all dates in June. |
| Filters for all dates in July. |
| Filters for all dates in August. |
| Filters for all dates in September. |
| Filters for all dates in October. |
| Filters for all dates in November. |
| Filters for all dates in December. |
| Filters for all dates in Quarter 1. |
| Filters for all dates in Quarter 2. |
| Filters for all dates in Quarter 3. |
| Filters for all dates in Quarter 4. |
| Filters for the specified number of values from the bottom of a list. |
| Filters for the specified percentage of values from the bottom of a list. |
| Sums the values from the bottom of the list. |
| Filters for all captions, beginning with the specified string. |
| Filters for all captions that contain the specified string. |
| Filters for all captions that do not begin with the specified string. |
| Filters for all captions that do not contain the specified string. |
| Filters for all captions that do not end with the specified string. |
| Filters for all captions that do not match the specified string. |
| Filters for all captions that end with the specified string. |
| Filters for all captions that match the specified string. |
| Filters for all captions that are within a specified range of values. |
| Filters for all captions that are greater than the specified value. |
| Filters for all captions that are greater than or match the specified value. |
| Filters for all captions that are less than the specified value. |
| Filters for all captions that are less than or match the specified value. |
| Filters for all captions that are not within a specified range of values. |
| Filters for all dates that are within a specified range of dates. |
| Filters for all dates that apply to the previous month. |
| Filters for all dates that apply to the previous quarter. |
| Filters for all dates that apply to the previous week. |
| Filters for all dates that apply to the previous year. |
| Filters for all dates that apply to the next month. |
| Filters for all dates that apply to the next quarter. |
| Filters for all dates that apply to the next week. |
| Filters for all dates that apply to the next year. |
| Filters for all dates that apply to the current month. |
| Filters for all dates that apply to the current quarter. |
| Filters for all dates that apply to the current week. |
| Filters for all dates that apply to the current year. |
| Filters for all dates that apply to the current date. |
| Filters for all dates that apply to the next day. |
| Filters for all dates that apply to the previous day. |
| Filters for all dates that do not match a specified date. |
| Filters for all dates that match a specified date. |
| Filters for the specified number of values from the top of a list. |
| Filters for the specified percentage of values from the top of a list. |
| Sums the values from the top of the list. |
| Filters for all values that do not match the specified value. |
| Filters for all values that match the specified value. |
| Filters for all values that are within a specified range of values. |
| Filters for all values that are greater than the specified value. |
| Filters for all values that are greater than or match the specified value. |
| Filters for all values that are less than the specified value. |
| Filters for all values that are less than or match the specified value. |
| Filters for all values that are not within a specified range of values. |
| Filters for all values that are within one year of a specified date. |
Excel 2010 added a Search box to the filter drop-down menu. Although this is a slick feature in the Excel interface, there is no equivalent magic in VBA. Whereas the drop-down menu offers the Select All Search Results check box, the equivalent VBA just lists all the items that match the selection. To achieve the same results in VBA, use the xlCaptionContains
filter described in the code that precedes Table 12-2.
The Top 5 Customers report actually contains two snapshots of a pivot table. After using the AutoShow feature to grab the top five markets with their totals, the macro went back to the pivot table, removed the AutoShow option, and grabbed the total of all customers to produce the Total Company row.
Excel 2010 introduced the concept of slicers for filtering pivot tables. A slicer is a visual filter that you can resize and reposition. You can control the color of a slicer and control the number of columns in it. You can also select or unselect items from a slicer by using VBA.
Figure 12-11 shows a pivot table with two slicers. Both of the slicers have been modified to show multiple columns.
Slicers work only with pivot tables designed to be used by Excel 2010 or newer. A slicer consists of a slicer cache and a slicer. To define a slicer cache, you need to specify a pivot table as the source and a field name as the SourceField
. The slicer cache is defined at the workbook level. The following code would set up a slicer:
Dim SCP as SlicerCache Dim SCR as SlicerCache Set SCP = ActiveWorkbook.SlicerCaches.Add(Source:=PT, SourceField:="Product") Set SCR = ActiveWorkbook.SlicerCaches.Add(Source:=PT, SourceField:="Region")
After you have defined the slicer cache, you can add the slicer. The slicer is defined as an object of the slicer cache. Specify a worksheet as the destination. The Name
argument controls the internal name for the slicer. The Caption
argument is the heading that is visible in the slicer. This might be useful if you would like to show the name Region, but the IT department defined the field as IDKRegn. Specify the size of the slicer by using height and width in points. Specify the location by using top and left in points.
In the following code, the values for top, left, height, and width are assigned to be equal to the location or size of certain cell ranges:
Dim SLP as Slicer Set SLP = SCP.Slicers.Add(SlicerDestination:=WSD, Name:="Product", _ Caption:="Product", _ Top:=WSD.Range("A12").Top, _ Left:=WSD.Range("A12").Left + 10, _ Width:=WSR.Range("A12:C12").Width, _ Height:=WSD.Range("A12:A16").Height)
Every slicer starts out as one column. You can change the style and number of columns code like this:
' Format the color and number of columns With SLP .Style = "SlicerStyleLight6" .NumberOfColumns = 5 End With
After the slicer is defined, you can use VBA to choose which items are activated in the slicer. It seems counterintuitive, but to choose items in the slicer, you have to change SlicerItem
, which is a member of the SlicerCache
, not a member of the Slicer
:
With SCP .SlicerItems("A292").Selected = True .SlicerItems("B722").Selected = True .SlicerItems("C409").Selected = False .SlicerItems("D625").Selected = False .SlicerItems("E438").Selected = False End With
Listing 12-4 shows how to build a pivot table with two slicers.
Sub PivotWithTwoSlicers() Dim SCP As SlicerCache ' For Product slicer Dim SCC As SlicerCache ' For Customer slicer Dim SLP As Slicer Dim SLC As Slicer Dim WSD As Worksheet Dim WSR As Worksheet Dim WBD As Workbook Dim PT As PivotTable Dim PTCache As PivotCache Dim Prange As Range Dim FinalRow As Long Set WBD = ActiveWorkbook Set WSD = Worksheets("Data") ' Delete any prior pivot tables For Each PT In WSD.PivotTables PT.TableRange2.Clear Next PT ' Delete any prior slicer cache For Each SC In ActiveWorkbook.SlicerCaches SC.Delete Next SC ' Define input area and set up a pivot cache WSD.Select FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row FinalCol = WSD.Cells(1, Columns.Count). _ End(xlToLeft).Column Set Prange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol) ' Define the pivot table cache Set PTCache = ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=Prange.Address, _ Version:=xlPivotTableVersion15) ' Create the pivot table from the pivot cache Set PT = PTCache.CreatePivotTable( _ TableDestination:=Cells(18, FinalCol + 2), _ TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion15) ' Set up the row and column fields PT.AddFields RowFields:=Array("Region") ' Set up the data fields With PT.PivotFields("Quantity") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0" .Name = "Quantity " End With With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "$#,##0" .Name = "Revenue " End With With PT.PivotFields("Profit") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "$#,##0" .Name = "Profit " End With ' Define the slicer caches Set SCC = WBD.SlicerCaches.Add(PT, "Customer") Set SCP = WBD.SlicerCaches.Add(PT, "Product") ' Define Product as a slicer Set SLP = SCP.Slicers.Add(WSD, , _ Name:="Product", _ Caption:="Product", _ Top:=WSD.Range("J14").Top + 5, _ Left:=WSD.Range("J14").Left + 5, _ Width:=343, Height:=54) SLP.Style = "SlicerStyleLight4" SLP.NumberOfColumns = 5 ' Define Customer as a slicer Set SLC = SCC.Slicers.Add(WSD, , _ Name:="Customer", _ Caption:="Customer", _ Top:=WSD.Range("J1").Top + 5, _ Left:=WSD.Range("J1").Left + 5, _ Width:=415, Height:=184) SLC.Style = "SlicerStyleLight2" SLC.NumberOfColumns = 3 ' Unselect some products With SCP .SlicerItems("C409").Selected = False .SlicerItems("D625").Selected = False .SlicerItems("E438").Selected = False End With ' Unselect one customer With SCC .SlicerItems("Guarded Kettle Corporation").Selected = False End With End Sub
The preceding code assigned the newly created slicer to an object variable so you could easily format the slicer. What if a slicer was created before your macro starts running? You can easily figure out the name of the slicer. If a slicer is created for the Product field, for example, the name of the SlicerCache
is "Slicer_Product"
. The following code formats existing slicers:
Sub MoveAndFormatSlicer() Dim SCP As SlicerCache Dim SLP as Slicer Dim WSD As Worksheet Set WSD = ActiveSheet Set SCP = ActiveWorkbook.SlicerCaches("Slicer_Product") Set SLP = SCS.Slicers("Product") With SLP .Style = "SlicerStyleLight6" .NumberOfColumns = 5 .Top = WSD.Range("A1").Top + 5 .Left = WSD.Range("A1").Left + 5 .Width = WSD.Range("A1:B14").Width – 60 .Height = WSD.Range("A1:B14").Height End With End Sub
Microsoft introduced the Timeline slicer in Excel 2013. This is a special type of slicer that is not compatible with Excel 2010 or earlier. The marketing name of Excel 2013 was Version 15, and VBA still uses that name, so if you plan on using a Timeline slicer, you have to specify xlPivotTableVersion15
(or higher) in two places in the code:
' Define the pivot table cache Set PTCache = ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=Prange.Address, _ Version:=xlPivotTableVersion15) ' Create the pivot table from the pivot cache Set PT = PTCache.CreatePivotTable( _ TableDestination:=Cells(10, FinalCol + 2), _ TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion15)
Later, after adding fields to your pivot table, you define a slicer cache and specify the type as xlTimeLine
:
' Define the slicer cache ' First two arguments are Source and SourceField ' Third argument, Name, should be skipped Set SC = WBD.SlicerCaches.Add2(PT, "ShipDate", , _ SlicerCacheType:=xlTimeline)
Then you add the slicer to the slicer cache:
' Define the timeline as a slicer Set SL = SC.Slicers.Add(WSD, , _ Name:="ShipDate", _ Caption:="Year", _ Top:=WSD.Range("J1").Top, _ Left:=WSD.Range("J1").Left, _ Width:=262.5, Height:=108)
Timelines can exist at the day, month, quarter, or year level. To change the level of a timeline, use the TimelineViewState.Level
property:
SL.TimelineViewState.Level = xlTimelineLevelYears
To filter a timeline to certain dates, you have to use the TimelineState.SetFilterDataRange
property, which applies to the slicer cache:
SC.TimelineState.SetFilterDateRange "1/1/2014", "12/31/2015"
Listing 12-5 shows the complete macro to build a version 15 pivot table and add a Timeline slicer.
Sub PivotWithYearSlicer() Dim SC As SlicerCache Dim SL As Slicer Dim WSD As Worksheet Dim WSR As Worksheet Dim WBD As Workbook Dim PT As PivotTable Dim PTCache As PivotCache Dim Prange As Range Dim FinalRow As Long Set WBD = ActiveWorkbook Set WSD = Worksheets("Data") ' Delete any prior pivot tables For Each PT In WSD.PivotTables PT.TableRange2.Clear Next PT ' Delete any prior slicer cache For Each SC In ActiveWorkbook.SlicerCaches SC.Delete Next SC ' Define input area and set up a pivot cache WSD.Select FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row FinalCol = WSD.Cells(1, Columns.Count). _ End(xlToLeft).Column Set Prange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol) ' Define the pivot table cache Set PTCache = ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=Prange.Address, _ Version:=xlPivotTableVersion15) ' Create the pivot table from the pivot cache Set PT = PTCache.CreatePivotTable( _ TableDestination:=Cells(10, FinalCol + 2), _ TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion15) ' Set up the row and column fields PT.AddFields RowFields:=Array("Customer") ' Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0" .Name = "Revenue " End With ' Define the slicer cache ' First two arguments are Source and SourceField ' Third argument, Name, should be skipped Set SC = WBD.SlicerCaches.Add2(PT, "ShipDate", , _ SlicerCacheType:=xlTimeline) ' Define the timeline as a slicer Set SL = SC.Slicers.Add(WSD, , _ Name:="ShipDate", _ Caption:="Year", _ Top:=WSD.Range("J1").Top, _ Left:=WSD.Range("J1").Left, _ Width:=262.5, Height:=108) ' Set the timeline to show years SL.TimelineViewState.Level = xlTimelineLevelYears ' Set the dates for the timeline SC.TimelineState.SetFilterDateRange "1/1/2018", "12/31/2018" End Sub
Figure 12-12 shows the Timeline slicer built by the code in Listing 12-5.
Microsoft 365 allows you to assign a formatting rule to the intersection of labels in a pivot table. Let’s say you have a pivot table with Sector in rows and Region in columns. If you add a fill color to the cell at the intersection of the Financial sector and the East region, the fill color will follow Financial and East as the pivot table is rearranged. If you add a new inner row field, such as Product, after Sector, then all of the product cells for the Financial sector will have the same fill color.
There is nothing particularly magical about the VBA to set this up. If the intersection of Financial and East is in B7, you would use the following:
With Range("B7").Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent4 .TintAndShade = -0.249946592608417 .PatternTintAndShade = 0 End With
Excel incorporates most parts of Power Pivot into the core Excel product. This means you can add two tables to the Data Model, create a relationship, build a measure, and then build a pivot table from the Data Model.
To follow along with this example, open the 12-BeforeDataModel.xlsm file from the sample download files. This workbook has two tables: Sales and Sector. Sector is a lookup table that is related to the Sales table via a customer field. To build the pivot table, follow these general steps:
Add the main table to the Data Model.
Add the lookup table to the Data Model.
Link the two tables with a relationship.
Create a pivot cache from ThisWorkbookDataModel
.
Create a pivot table from the cache.
Add row fields.
Define a measure. Add the measure to the pivot table.
You should already have a data set in the workbook that has been converted to a table using the Ctrl+T shortcut. On the Table Tools Design tab, change the table name to Sales. To link this table to the Data Model, use this code:
' Build Connection to the main Sales table Set WBT = ActiveWorkbook TableName = "Sales" WBT.Connections.Add Name:="LinkedTable_" & TableName, _ Description:="", _ ConnectionString:="WORKSHEET;" & WBT.FullName, _ CommandText:=WBT.Name & "!" & TableName, _ lCmdType:=7, _ CreateModelConnection:=True, _ ImportRelationships:=False
There are several variables in this code that use the table name, the workbook path, or the workbook name. By storing the table name in a variable at the top of the code, you can use the variables to build the connection name, connection string, and command text.
Adapting the preceding code to link to the lookup table then requires only changing the TableName
variable:
TableName = "Sector" WBT.Connections.Add Name:="LinkedTable_" & TableName, _ Description:="", _ ConnectionString:="WORKSHEET;" & WBT.FullName, _ CommandText:=WBT.Name & "!" & TableName, _ lCmdType:=7, _ CreateModelConnection:=True, _ ImportRelationships:=False
When you create a relationship in the Excel interface, you specify four items in the Create Relationship dialog box. The code to create the relationship is more streamlined. There can be only one Data Model per workbook. Set an object variable MO
to refer to the model in this workbook. Use the ModelRelationships.Add
method, specifying the two fields that are linked:
' Relate the two tables Dim MO As Model Set MO = ActiveWorkbook.Model MO.ModelRelationships.Add _ ForeignKeyColumn:= _ MO.ModelTables("Sales").ModelTableColumns("Customer"), _ PrimaryKeyColumn:= _ MO.ModelTables("Sector").ModelTableColumns("Customer")
The code to define the pivot cache specifies that the data is external. Even though the linked tables are in your workbook, and even though the Data Model is stored as a binary large object within the workbook, this is still considered an external data connection. The connection is always called ThisWorkbookDataModel
. To set up the pivot cache, use this code:
' Define the PivotCache Set PTCache = WBT.PivotCaches.Create(SourceType:=xlExternal, _ SourceData:=WBT.Connections("ThisWorkbookDataModel"), _ Version:=xlPivotTableVersion15) ' Create the pivot table from the pivot cache Set PT = PTCache.CreatePivotTable( _ TableDestination:=WSD.Cells(1, 1), TableName:="PivotTable1")
There are two types of fields you need to add to the pivot table. Text fields such as Customer, Sector, and Product are simply fields that can be added to the row or column area of the pivot table. No calculation has to happen to these fields. The code for adding text fields is shown in this section. When you add a numeric field to the Values area in the Excel interface, you are actually implicitly defining a new calculated field. To do this in VBA, you have to explicitly define the field and then add it.
First, let’s look at the simpler example of adding a text field to the row area. The VBA code generically looks like this:
With PT.CubeFields("[TableName].[FieldName]") .Orientation = xlRowField .Position = 1 End With
In the current example, add the Sector field from the Sector table by using this code:
With PT.CubeFields("[Sector].[Sector]") .Orientation = xlRowField .Position = 1 End With
If you have a Data Model pivot table and you check the Revenue field, you see the Revenue field move to the Values area. Behind the scenes, though, Excel is implicitly defining a new measure called Sum of Revenue. (You can see the implicit measures in the Power Pivot window.) In VBA, you need to define a new measure for Sum of Revenue. To make it easier to refer to this measure later, assign the new measure to an object variable:
' Before you can add Revenue to the pivot table, ' you have to define the measure. ' This happens using the GetMeasure method. ' Assign the cube field to the CFRevenue object Dim CFRevenue As CubeField Set CFRevenue = PT.CubeFields.GetMeasure( _ AttributeHierarchy:="[Sales].[Revenue]", _ Function:=xlSum, _ Caption:="Sum of Revenue") ' Add the newly created cube field to the pivot table PT.AddDataField Field:=CFRevenue, _ Caption:="Total Revenue" PT.PivotFields("Total Revenue").NumberFormat = "$#,##0,K"
You can use the preceding code to create a new measure. The following measure uses the new xlDistinctCount
function to count the number of unique customers in each sector:
' Add distinct count of customer as a cube field Dim CFCustCount As CubeField Set CFCustCount = PT.CubeFields.GetMeasure( _ AttributeHierarchy:="[Sales].[Customer]", _ Function:=xlDistinctCount, _ Caption:="Customer Count") ' Add the newly created cube field to the pivot table PT.AddDataField Field:=CFCustCount, _ Caption:="Customer Count"
Now that PowerPivot ships with every copy of Excel, you can use DAX formulas to create new measures. The following code adds a field for Median Sales:
' Add Median Sales using DAX ActiveWorkbook.Model.ModelMeasures.Add _ MeasureName:="Median Sales", _ AssociatedTable:=ActiveWorkbook.Model.ModelTables("Sales"), _ Formula:="Median([Revenue])", _ FormatInformation:=ActiveWorkbook.Model.ModelFormatCurrency("Default", 2) PT.AddDataField PT.CubeFields("[Measures].[Median Sales]")
Figure 12-13 shows the Data Model pivot table created using the code in Listing 12-6.
Sub BuildModelPivotTable() Dim WBT As Workbook Dim WC As WorkbookConnection Dim MO As Model Dim PTCache As PivotCache Dim PT As PivotTable Dim WSD As Worksheet Dim CFRevenue As CubeField Dim CFCustCount As CubeField Set WBT = ActiveWorkbook Set WSD = WBT.Worksheets("Report") ' Build connection to the main Sales table TableName = "Sales" WBT.Connections.Add2 Name:="LinkedTable_" & TableName, _ Description:="MainTable", _ ConnectionString:="WORKSHEET;" & WBT.FullName, _ CommandText:=WBT.Name & "!" & TableName, _ lCmdType:=7, _ CreateModelConnection:=True, _ ImportRelationships:=False ' Build connection to the Sector lookup table TableName = "Sector" WBT.Connections.Add2 Name:="LinkedTable_" & TableName, _ Description:="LookupTable", _ ConnectionString:="WORKSHEET;" & WBT.FullName, _ CommandText:=WBT.Name & "!" & TableName, _ lCmdType:=7, _ CreateModelConnection:=True, _ ImportRelationships:=False ' Relate the two tables Set MO = ActiveWorkbook.Model MO.ModelRelationships.Add _ ForeignKeyColumn:=MO.ModelTables("Sales") _ .ModelTableColumns("Customer"), _ PrimaryKeyColumn:=MO.ModelTables("Sector") _ .ModelTableColumns("Customer") ' Delete any prior pivot tables For Each PT In WSD.PivotTables PT.TableRange2.Clear Next PT ' Define the PivotCache Set PTCache = WBT.PivotCaches.Create(SourceType:=xlExternal, _ SourceData:=WBT.Connections("ThisWorkbookDataModel"), _ Version:=xlPivotTableVersion15) ' Create the pivot table from the pivot cache Set PT = PTCache.CreatePivotTable( _ TableDestination:=WSD.Cells(1, 1), TableName:="PivotTable1") ' Add the Sector field from the Sector table to the Row areas With PT.CubeFields("[Sector].[Sector]") .Orientation = xlRowField .Position = 1 End With ' Before you can add Revenue to the pivot table, ' you have to define the measure. ' This happens using the GetMeasure method ' Assign the cube field to the CFRevenue object Set CFRevenue = PT.CubeFields.GetMeasure( _ AttributeHierarchy:="[Sales].[Revenue]", _ Function:=xlSum, _ Caption:="Sum of Revenue") ' Add the newly created cube field to the pivot table PT.AddDataField Field:=CFRevenue, _ Caption:="Total Revenue" PT.PivotFields("[Measures].[Sum of Revenue]") _ .NumberFormat = "$#,##0,K" ' Add Distinct Count of Customer as a cube field Set CFCustCount = PT.CubeFields.GetMeasure( _ AttributeHierarchy:="[Sales].[Customer]", _ Function:=xlDistinctCount, _ Caption:="Customer Count") ' Add the newly created cube field to the pivot table PT.AddDataField Field:=CFCustCount, _ Caption:="Customer Count" ' Add Median Sales using DAX ActiveWorkbook.Model.ModelMeasures.Add _ MeasureName:="Median Sales", _ AssociatedTable:= _ ActiveWorkbook.Model.ModelTables("Sales"), _ Formula:="Median([Revenue])", _ FormatInformation:= _ ActiveWorkbook.Model.ModelFormatCurrency("Default", 2) PT.AddDataField PT.CubeFields("[Measures].[Median Sales]") End Sub
This section covers a few additional features in pivot tables that you might need to code with VBA.
Pivot tables offer two types of formulas. The more useful type creates a calculated field. This adds a new field to the pivot table. Calculations for calculated fields are always done at the summary level. If you define a calculated field for average price as revenue divided by units sold, Excel first adds the total revenue and total quantity, and then it does the division of these totals to get the result. In many cases, this is exactly what you need. If your calculation does not follow the associative law of mathematics, it might not work as you expect.
To set up a calculated field, use the Add
method with the CalculatedFields
object. You have to specify a field name and a formula, as shown here:
' Define calculated fields PT.CalculatedFields.Add Name:="ProfitPercent", _ Formula:="=Profit/Revenue" With PT.PivotFields("ProfitPercent") .Orientation = xlDataField .Function = xlSum .Position = 3 .NumberFormat = "#0.0%" .Name = "GP Pct" End With
Suppose you have a Measure field with two items: Budget and Actual. You would like to add a new position to calculate Variance as Actual minus Budget. You can do this with a calculated item by using this code:
' Define calculated item along the product dimension PT.PivotFields("Measure").CalculatedItems _ .Add "Variance", "='Actual'-'Budget'"
ShowDetail
to filter a record setWhen you double-click any number in any pivot table in the Excel user interface, Excel inserts a new sheet in the workbook and copies all the source records that represent that number. In the Excel user interface, this is a great way to perform a drill-down query into a data set.
The equivalent VBA property is ShowDetail
. By setting this property to True
for any cell in the pivot table, you generate a new worksheet with all the records that make up that cell:
PT.TableRange2.Offset(2, 1).Resize(1, 1).ShowDetail = True
The Layout group on the Design tab contains four drop-down menus that control the following:
Location of subtotals (top or bottom)
Presence of grand totals
Report layout, including whether outer row labels are repeated
Presence of blank rows
Subtotals can appear either at the top or at the bottom of a group of pivot items. The SubtotalLocation
property applies to the entire pivot table; valid values are xlAtBottom
and xlAtTop
:
PT.SubtotalLocation:=xlAtTop
Grand totals can be turned on or off for rows or columns. Because these two settings can be confusing, remember that at the bottom of a report, there is a total line that most people would call the grand total row. To turn off that row, you have to use the following:
PT.ColumnGrand = False
You need to turn off ColumnGrand
when you want to suppress the total row because Microsoft calls that row the “grand total for columns.” Get it? In other words, Microsoft is saying that the row at the bottom contains the total of the columns above it. It is one of the more awkward phrases in the Excel ribbon. It confuses me every time.
To suppress what you would call the grand total column along the right side of the report, you have to suppress what Microsoft calls the “total for rows” by using the following code:
PT.RowGrand = False
There are three settings for the report layout:
Tabular layout—Similar to the default layout in Excel 2003.
Outline layout—Optionally available in Excel 2003.
Compact layout—Introduced in Excel 2007.
When you create a pivot table in the Excel interface, you get the Compact layout. When you build a pivot table in VBA, you get the Tabular layout. You can change to one of the other layouts with one of these lines:
PT.RowAxisLayout xlTabularRow PT.RowAxisLayout xlOutlineRow PT.RowAxisLayout xlCompactRow
Starting in Excel 2007, you can add a blank line to the layout after each group of pivot items. Although the Design tab offers a single setting to affect the entire pivot table, the setting is actually applied individually to each pivot field. The macro recorder responds by recording a dozen lines of code for a pivot table with 12 fields. You can intelligently add a single line of code for the outer row fields:
PT.PivotFields("Region").LayoutBlankLine = True
As soon as you have more than one row field, Excel automatically adds subtotals for all but the innermost row field. That extra row field can get in the way if you plan to reuse the results of the pivot table as a new data set for some other purpose. Although accomplishing this task manually can be relatively simple, the VBA code to suppress subtotals is surprisingly complex.
Most people do not realize that it is possible to show multiple types of subtotals. For example, you can choose to show Total, Average, Min, and Max in the same pivot table.
To suppress subtotals for a field, you must set the Subtotals
property equal to an array of 12 False
values. The first False
turns off automatic subtotals, the second False
turns off the Sum
subtotal, the third False
turns off the Count
subtotal, and so on. This code suppresses the Region
subtotal:
PT.PivotFields("Region").Subtotals = Array(False, False, False, False, _ False, False, False, False, False, False, False, False)
A different technique is to turn on the first subtotal. This method automatically turns off the other 11 subtotals. You can then turn off the first subtotal to make sure that all subtotals are suppressed:
PT.PivotFields("Region").Subtotals(1) = True PT.PivotFields("Region").Subtotals(1) = False
There are billions of lines of VBA code running in Excel worksheets around the world. But Microsoft says that VBA will never be supported in Excel Online. They have created a new macro language to be used in Excel Online. Currently, the TypeScript language available for macros in Excel Online will not run in Desktop versions of Excel. It is much slower than VBA and not as complete as VBA.
However, it does exist, and you can create some interesting macros, including macros to create pivot tables in Excel Online.
Currently, TypeScript is limited to Microsoft 365 customers with a commercial or educational license. That excludes everyone with a Home license. In addition, you will need your Microsoft 365 administrator to enable them for the company. For the latest rules, open a browser and search for “Introduction to Office Scripts in Excel.”
At the time of writing, the TypeScript macros are still in beta. TypeScript and the macro recorder can successfully record these actions:
Add a new pivot table to the right of your data set.
Add Product to the Rows area.
Add Revenue to the Values area.
Add Region to the Columns area.
The recorded macro is short, relatively easy to understand, and runs successfully.
On the downside, the macro recorder does not (yet) support:
Changing the number format of a numeric field.
Replacing blanks in the Values area with zero.
Using the suggested pivot tables from the Ideas feature.
Figure 12-14 shows the recorded TypeScript code.
Here is a description of the recorded code with suggested changes:
The script is named Script 4. In the Script Details, click on the name and type a better name, such as CreatePTBySectorAndRegion.
Comment lines start with // instead of an apostophe in VBA.
It appears that selectSheet is an object variable. Where VBA would do:
Set selectedSheet = ActiveSheet Typescript does: let selectedSheet = workbook.getActiveWorksheet()
The code is case sensitive. You have to use getActiveWorksheet and not getactiveworksheet or GetActiveWorksheet. If you don’t use the case correctly, the editor will flag it as an error but then suggest that you could change the spelling to getActiveWorksheet. As a VBA fan, you will likely agree that it is a bit jerkish for the editor to know the spelling and to not automatically correct it.
When the macro recorder creates the pivot table using .addPivotTable, it specifies a name, source data, and destination data. Hard-coding A1:L564 is not a good practice. TypeScript’s equivalent of .CurrentRegion is .getSurroundingRegion() used as follows:
selectedSheet.getRange("A1").getSurroundingRegion()
The modified code to create a pivot table called PTOne, based on A:L and starting in cell N2, is shown here. Note that the results are stored in an object variable called newPivotTable for easy reference later in the script:
let newPivotTable = workbook.addPivotTable("PTOne", selectedSheet.getRange("A1").getSurroundingRegion(), selectedSheet.getRange("N2"));
Once the pivot table is created, you can add fields to the Rows area with the following:
newPivotTable.addRowHierarchy(newPivotTable.getHierarchy("Sector"));
For a column field, change .addRowHierarchy to .addColumnHierarchy. For a Values field, use .AddDataHierarchy.
Near the end of the script, the macro recorder added a stray line:
newPivotTable.getColumnHierarchy("Region").setPosition(0);
This would have been needed if you had multiple fields in the Columns area. But with only one field in the Columns area, that line can be deleted.
Documentation for everything shows up when you hover over any word in the script. This is better than VBA, where you have to press F1 and then wait for a web page to load.
After seeing the recorded code, it should be relatively easy to edit the recorded code. The following code in Listing 12-7 creates a pivot table with Sector in rows, Region in columns, and Revenue in values.
function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Add a new pivot table on selectedSheet let newPivotTable = workbook.addPivotTable("PTOne", selectedSheet.getRange("A1"). getSurroundingRegion(), selectedSheet.getRange("N2")); // Add Sector to Rows newPivotTable.addRowHierarchy(newPivotTable.getHierarchy("Sector")); // Add Revenue to Data newPivotTable.addDataHierarchy(newPivotTable.getHierarchy("Revenue")); // Add Region to Columns newPivotTable.addColumnHierarchy(newPivotTable.getHierarchy("Region")); }
The resulting pivot table is shown in Figure 12-15.
People who care a lot about speed note that the TypeScript macros are slower than VBA, but for this simple example, it does not matter.
There are still gaps. When you look at Figure 12-15, you might want to change “For Empty Cells, Show” to zero. There is a setting in Excel Online for this. But when you try to record a macro for how to make that change, the macro recorder tells you that this is not yet supported in TypeScript:
// Unsupported event received: PivotTable.nullStringChanged
I am writing this in 2021. Within a few years, these gaps will eventually be filled.
You may be able to tell that pivot tables are my favorite feature in Excel. They are incredibly powerful and flexible. Combined with VBA, they provide an excellent calculation engine and power many of the reports I build for clients. Chapter 13, “Excel power,” offers multiple techniques for handling various tasks in VBA.