Chapter 17: Working with Pivot Tables

IN THIS CHAPTER

• Creating pivot tables with VBA

• Looking at examples of VBA procedures that create pivot tables

• Using VBA to create a worksheet table from a summary table

An Introductory Pivot Table Example

Excel's pivot table feature is, arguably, its most innovative and powerful feature. Pivot tables first appeared in Excel 5, and the feature has been improved in every subsequent version. This chapter is not an introduction to pivot tables. I assume that you're familiar with this feature and its terminology and that you know how to create and modify pivot tables manually.

As you probably know, creating a pivot table from a database or list enables you to summarize data in ways that otherwise would not be possible — and it's amazingly fast and requires no formulas. You also can write VBA code to generate and modify pivot tables.

This section gets the ball rolling with a simple example of using VBA to create a pivot table.

Figure 17-1 shows a very simple worksheet range. It contains four fields: SalesRep, Region, Month, and Sales. Each record describes the sales for a particular sales representative in a particular month.

This workbook, named simple pivot table.xlsm, is available on the companion CD-ROM.

FIGURE 17-1: This table is a good candidate for a pivot table.

Creating a pivot table

Figure 17-2 shows a pivot table created from the data, along with the PivotTable Field List task bar. This pivot table summarizes the sales performance by sales representative and month. This pivot table is set up with the following fields:

FIGURE 17-2: A pivot table created from the data in Figure 17-1.

• Region: A report filter field in the pivot table.

• SalesRep: A row field in the pivot table.

• Month: A column field in the pivot table.

• Sales: A values field in the pivot table that uses the Sum function.

I turned on the macro recorder before I created this pivot table and specified a new worksheet for the pivot table location. The code that was generated follows:

Sub RecordedMacro()

Range(“A1”).Select

Sheets.Add

ActiveWorkbook.PivotCaches.Create _

(SourceType:=xlDatabase, _

SourceData:=”Sheet1!R1C1:R13C4”, _

Version:=xlPivotTableVersion14).CreatePivotTable _

TableDestination:=”Sheet2!R3C1”, _

TableName:=”PivotTable1”, _

DefaultVersion:=xlPivotTableVersion14)

Sheets(“Sheet2”).Select

Cells(3, 1).Select

With ActiveSheet.PivotTables(“PivotTable1”) _

.PivotFields(“SalesRep”)

.Orientation = xlRowField

.Position = 1

End With

With ActiveSheet.PivotTables(“PivotTable1”) _

.PivotFields(“Month”)

.Orientation = xlColumnField

.Position = 1

End With

ActiveSheet.PivotTables(“PivotTable1”) _

.AddDataField ActiveSheet.PivotTables(“PivotTable1”) _

.PivotFields(“Sales”), “Sum of Sales”, xlSum

With ActiveSheet.PivotTables(“PivotTable1”). _

PivotFields(“Region”)

.Orientation = xlPageField

.Position = 1

End With

End Sub

If you execute this macro, it will almost certainly produce an error. Examine the code, and you'll see that the macro recorder hard-coded the worksheet name (Sheet2) for the pivot table. If that sheet already exists (or if the new sheet that's added has a different name), the macro ends with an error. It also hard-coded the pivot table name. The name won't be PivotTable1 if the workbook has other pivot tables.

But even though the recorded macro doesn't work, it's not completely useless. The code provides lots of insight for writing code to generate pivot tables.

Examining the recorded code for the pivot table

VBA code that works with pivot tables can be confusing. to make any sense of the recorded macro, you need to know about a few relevant objects, all of which are explained in the Help system.

• PivotCaches: A collection of PivotCache objects in a Workbook object (the data used by a pivot table is stored in a pivot cache).

• PivotTables: A collection of PivotTable objects in a Worksheet object.

• PivotFields: A collection of fields in a PivotTable object.

• PivotItems: A collection of individual data items within a field category.

• CreatePivotTable: A method that creates a pivot table by using the data in a pivot cache.

Cleaning up the recorded pivot table code

As with most recorded macros, the preceding example isn't as efficient as it could be. And, as I noted, it's very likely to generate an error. You can simplify the code to make it more understandable and also to prevent the error. The hand-crafted code that follows generates the same pivot table as the procedure previously listed:

Sub CreatePivotTable()

Dim PTCache As PivotCache

Dim PT As PivotTable

‘ Create the cache

Set PTCache = ActiveWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=Range(“A1”).CurrentRegion)

‘ Add a new sheet for the pivot table

Worksheets.Add

‘ Create the pivot table

Set PT = ActiveSheet.PivotTables.Add( _

PivotCache:=PTCache, _

TableDestination:=Range(“A3”))

‘ Specify the fields

With PT

.PivotFields(“Region”).Orientation = xlPageField

.PivotFields(“Month”).Orientation = xlColumnField

.PivotFields(“SalesRep”).Orientation = xlRowField

.PivotFields(“Sales”).Orientation = xlDataField

‘no field captions

.DisplayFieldCaptions = False

End With

End Sub

The CreatePivotTable procedure is simplified (and might be easier to understand) because it declares two object variables: PTCache and PT. A new PivotCache object is created by using the Create method. A worksheet is added, and it becomes the active sheet (the destination for the pivot table). Then a new PivotTable object is created by using the Add method of the PivotTables collection. The last section of the code adds the four fields to the pivot table and specifies their location within it by assigning a value to the Orientation property.

The original macro hard-coded both the data range used to create the PivotCache object (‘Sheet1!R1C1:R13C4') and the pivot table location (Sheet2). In the CreatePivotTable procedure, the pivot table is based on the current region surrounding cell A1. This ensures that the macro will continue to work properly if more data is added.

Adding the worksheet before the pivot table is created eliminates the need to hard-code the sheet reference. Yet another difference is that the hand-written macro doesn't specify a pivot table name. Because the PT object variable is created, your code doesn't ever have to refer to the pivot table by name.

The code also could be more general through the use of indices rather than literal strings for the PivotFields collections. This way, if the user changes the column headings, the code will still work. For example, more general code would use PivotFields(1) rather than PivotFields(‘Region').

As always, the best way to master this topic is to record your actions within a macro to find out its relevant objects, methods, and properties. Then study the Help topics to understand how everything fits together. In almost every case, you'll need to modify the recorded macros. Or, after you understand how to work with pivot tables, you can write code from scratch and avoid the macro recorder.

Creating a More Complex Pivot Table

In this section, I present VBA code to create a relatively complex pivot table.

Figure 17-3 shows part of a large worksheet table. This table has 15,840 rows and consists of hierarchical budget data for a corporation. The corporation has five divisions, and each division contains 11 departments. Each department has four budget categories, and each budget category contains several budget items. Budgeted and actual amounts are included for each of the 12 months. The goal is to summarize this information with a pivot table.

FIGURE 17-3: The data in this workbook will be summarized in a pivot table.

This workbook is available on the companion CD-ROM. The file is named budget pivot table.xlsm.

Figure 17-4 shows a pivot table created from the data. Notice that the pivot table contains a calculated field named Variance. This field is the difference between the Budget amount and the Actual amount.

FIGURE 17-4: A pivot table created from the budget data.

Another option is to insert a new column in the table and create a formula to calculate the difference between the budget and actual amounts. If the data is from an external source (rather than in a worksheet), that option may not be possible.

The code that created the pivot table

Here's the VBA code that created the pivot table:

Sub CreatePivotTable()

Dim PTcache As PivotCache

Dim PT As PivotTable

Application.ScreenUpdating = False

‘ Delete PivotSheet if it exists

On Error Resume Next

Application.DisplayAlerts = False

Sheets(“PivotSheet”).Delete

On Error GoTo 0

‘ Create a Pivot Cache

Set PTcache = ActiveWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=Range(“A1”).CurrentRegion.Address)

‘ Add new worksheet

Worksheets.Add

ActiveSheet.Name = “PivotSheet”

ActiveWindow.DisplayGridlines = False

‘ Create the Pivot Table from the Cache

Set PT = ActiveSheet.PivotTables.Add( _

PivotCache:=PTcache, _

TableDestination:=Range(“A1”), _

TableName:=”BudgetPivot”)

With PT

‘ Add fields

.PivotFields(“Category”).Orientation = xlPageField

.PivotFields(“Division”).Orientation = xlPageField

.PivotFields(“Department”).Orientation = xlRowField

.PivotFields(“Month”).Orientation = xlColumnField

.PivotFields(“Budget”).Orientation = xlDataField

.PivotFields(“Actual”).Orientation = xlDataField

.DataPivotField.Orientation = xlRowField

‘ Add a calculated field to compute variance

.CalculatedFields.Add “Variance”, “=Budget-Actual”

.PivotFields(“Variance”).Orientation = xlDataField

‘ Specify a number format

.DataBodyRange.NumberFormat = “0,000”

‘ Apply a style

.TableStyle2 = “PivotStyleMedium2”

‘ Hide Field Headers

.DisplayFieldCaptions = False

‘ Change the captions

.PivotFields(“Sum of Budget”).Caption = “ Budget”

.PivotFields(“Sum of Actual”).Caption = “ Actual”

.PivotFields(“Sum of Variance”).Caption = “ Variance”

End With

End Sub

How the more complex pivot table works

The CreatePivotTable procedure starts by deleting the PivotSheet worksheet if it already exists. It then creates a PivotCache object, inserts a new worksheet named PivotSheet, and creates the pivot table from the PivotCache. The code then adds the following fields to the pivot table:

• Category: A report filter (page) field

• Division: A report filter (page) field

• Department: A row field

• Month: A column field

• Budget: A data field

• Actual: A data field

Notice that the Orientation property of the DataPivotField is set to xlRowField in the following statement:

.DataPivotField.Orientation = xlRowField

This statement determines the overall orientation of the pivot table, and it represents the Sum Value field in the Pivot Table Field list (see Figure 17-5). Try moving that field to the Column Labels section to see how it affects the pivot table layout.

Next, the procedure uses the Add method of the CalculatedFields collection to create the calculated field Variance, which subtracts the Actual amount from the Budget amount. This calculated field is assigned as a data field.

To add a calculated field to a pivot table manually, use the PivotTable⇒Options ⇒Calculations⇒Fields, Items, & Sets ⇒Calculated Field command, which displays the Insert Calculated Field dialog box.

Finally, the code makes a few cosmetic adjustments:

• Applies a number format to the DataBodyRange (which represents the entire pivot table data).

• Applies a style.

• Hides the captions (equivalent to the PivotTable Tools⇒Options⇒Show ⇒Field Headers control).

FIGURE 17-5: The Pivot Table Field List.

• Changes the captions displayed in the pivot table. For example, Sum of Budget is replaced by Budget. Note that the string Budget is preceded by a space. Excel doesn't allow you to change a caption that corresponds to a field name, so adding a space gets around this restriction.

While creating this procedure, I used the macro recorder extensively to learn about the various properties. That, combined with the information in the Help system (and a fair amount of trial and error), provided all the information I needed.

Creating Multiple Pivot Tables

The final example creates a series of pivot tables that summarize data collected in a customer survey. That data is stored in a worksheet database (see Figure 17-6) and consists of 150 rows. Each row contains the respondent's sex plus a numerical rating using a 1–5 scale for each of the 14 survey items.

This workbook, named survey data pivot tables.xlsm, is available on the companion CD-ROM.

FIGURE 17-6: Creating a series of pivot tables will summarize this survey data.

Figure 17-7 shows a few of the 28 pivot tables produced by the macro. Each survey item is summarized in two pivot tables (one showing percentages, and one showing the actual frequencies).

The VBA code that created the pivot tables follows:

Sub MakePivotTables()

‘ This procedure creates 28 pivot tables

Dim PTCache As PivotCache

Dim PT As PivotTable

Dim SummarySheet As Worksheet

Dim ItemName As String

Dim Row As Long, Col As Long, i As Long

Application.ScreenUpdating = False

‘ Delete Summary sheet if it exists

On Error Resume Next

Application.DisplayAlerts = False

Sheets(“Summary”).Delete

On Error GoTo 0

‘ Add Summary sheet

Set SummarySheet = Worksheets.Add

ActiveSheet.Name = “Summary”

‘ Create Pivot Cache

Set PTCache = ActiveWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=Sheets(“SurveyData”).Range(“A1”). _

FIGURE 17-7: Six of the 28 pivot tables created by a VBA procedure.

CurrentRegion)

Row = 1

For i = 1 To 14

For Col = 1 To 6 Step 5 ‘2 columns

ItemName = Sheets(“SurveyData”).Cells(1, i + 2)

With Cells(Row, Col)

.Value = ItemName

.Font.Size = 16

End With

‘ Create pivot table

Set PT = ActiveSheet.PivotTables.Add( _

PivotCache:=PTCache, _

TableDestination:=SummarySheet.Cells(Row + 1, Col))

‘ Add the fields

If Col = 1 Then ‘Frequency tables

With PT.PivotFields(ItemName)

.Orientation = xlDataField

.Name = “Frequency”

.Function = xlCount

End With

Else ‘ Percent tables

With PT.PivotFields(ItemName)

.Orientation = xlDataField

.Name = “Percent”

.Function = xlCount

.Calculation = xlPercentOfColumn

.NumberFormat = “0.0%”

End With

End If

PT.PivotFields(ItemName).Orientation = xlRowField

PT.PivotFields(“Sex”).Orientation = xlColumnField

PT.TableStyle2 = “PivotStyleMedium2”

PT.DisplayFieldCaptions = False

If Col = 6 Then

‘ add data bars to the last column

PT.ColumnGrand = False

PT.DataBodyRange.Columns(3).FormatConditions. _

AddDatabar

With pt.DataBodyRange.Columns(3).FormatConditions(1)

.BarFillType = xlDataBarFillSolid

.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0

.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=1

End With

End If

Next Col

Row = Row + 10

Next i

‘ Replace numbers with descriptive text

With Range(“A:A,F:F”)

.Replace “1”, “Strongly Disagree”

.Replace “2”, “Disagree”

.Replace “3”, “Undecided”

.Replace “4”, “Agree”

.Replace “5”, “Strongly Agree”

End With

End Sub

Notice that all these pivot tables were created from a single PivotCache object.

The pivot tables are created within a nested loop. The Col loop counter goes from 1 to 6 by using the Step parameter. The instructions vary a bit for the second column of pivot tables. Specifically, the pivot tables in the second column do the following:

• Display the count as a percent of the column.

• Do not show grand totals for the rows.

• Are assigned a number format.

• Display format conditioning data bars.

The Row variable keeps track of the starting row of each pivot table. The final step is to replace the numeric categories in columns A and F with text. For example, 1 is replaced with Strongly Agree.

Creating a Reverse Pivot Table

A pivot table is a summary of data in a table. But what if you have a summary table, and you'd like to create a table from it? Figure 17-8 shows an example. Range B2:F14 contains a summary table — similar to a very simple pivot table. Columns I:K contain a 48-row table created from the summary table. In the table, each row contains one data point, and the first two columns describe that data point. In other words, the transformed data is normalized. (See the sidebar, “Data appropriate for a pivot table,” earlier in this chapter.)

Excel doesn't provide a way to transform a summary table into a normalized table, so it's a good job for a VBA macro. After I created this macro, I spent a bit more time and added a UserForm, shown in Figure 17-9. The UserForm gets the input and output ranges and also has an option to convert the output range to a table.

This workbook, named reverse pivot table.xlsm, is available on the companion CD-ROM.

FIGURE 17-8: The summary table on the left will be converted to the table on the right.

FIGURE 17-9: This dialog box asks the user for the ranges.

When the user clicks the OK button in the UserForm, VBA code validates the ranges and then calls the ReversePivot procedure with this statement:

Call ReversePivot(SummaryTable, OutputRange, cbCreateTable)

It passes three arguments:

• SummaryTable: A Range object that represents the summary table.

• OutputRange: A Range object that represents the upper-left cell of the output range.

• cbCreateTable: The Checkbox object on the UserForm.

This procedure will work for any size summary table. The number of data rows in the output table will be equal to (r-1) * (c-1), where r and c represent the number of rows and columns in the SummaryTable.

The code for the ReversePivot procedure follows:

Sub ReversePivot(SummaryTable As Range, _

OutputRange As Range, CreateTable As Boolean)

Dim r As Long, c As Long

Dim OutRow As Long, OutCol As Long

‘ Convert the range

OutRow = 2

Application.ScreenUpdating = False

OutputRange.Range(“A1:C3”) = Array(“Column1”, “Column2”, “Column3”)

For r = 2 To SummaryTable.Rows.Count

For c = 2 To SummaryTable.Columns.Count

OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)

OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)

OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)

OutRow = OutRow + 1

Next c

Next r

‘ Make it a table?

If CreateTable Then _

ActiveSheet.ListObjects.Add xlSrcRange, _

OutputRange.CurrentRegion, , xlYes

End Sub

The procedure is fairly simple. The code loops through the rows and columns in the input range and then writes the data to the output range. The output range will always have three columns. The OutRow variable keeps track of the current row in the output range. Finally, if the user checked the check box, the output range is converted to a table by using the Add method of the ListObjects collection.