When you create a pivot table, a default PivotTable Style is automatically applied. You can change to a different style, and you can even create custom pivot table styles. To help you keep track of the styles that you have, here’s a List All Pivot Table Styles macro.
Pivot Table Custom Styles
If you’re not sure how to create your own custom pivot table style, this short video shows the steps. Also, there are step-by-step written instructions on my Contextures site, on the Pivot Table Formatting page.
Default and Custom Pivot Table Styles
There are about 85 built-in pivot table styles in my version of Excel – there might be more than that in your version, or fewer styles.
If you create custom pivot table styles, they’ll be added to that list too. The custom styles also appear at the top of the style gallery.
List All the Pivot Table Styles
To get a list of all the pivot table styles in the active workbook, use the macro that’s shown below. The macro adds a sheet to the workbook, with a list of the pivot table style settings.
The list shows the style name and number, whether it’s built in or custom, the header colour, and inside border colour.
NOTE: Lots of black is used for the style formatting, so the macro shows a black dot, instead of filling the cell with black. I find that easier to read, and it also saves on printer toner, if you want to print the list!
Macro to List All Pivot Table Styles
This macro to list all Pivot Table styles is in the sample workbook that you can download. There are four other macros too, to list and set the styles.
Or, to use this macro in your own workbook, copy the code below to a regular code module. Then, add a worksheet button to run the macro, or run it from the Macros command on the Excel Ribbon’s View tab.
Sub StylesPTListALL()
Dim wb As Workbook
Dim lStyle As Long
Dim stl As TableStyle
Dim ws As Worksheet
Dim myRow As Long
Dim lClrH As Long
Dim lClrB As Long
Set wb = ActiveWorkbook
Set ws = Sheets.Add
On Error Resume Next
With ws
.Range(Cells(1, 1), Cells(1, 5)).Value _
= Array("Style", "Name", "BuiltIn", _
"Header", "Borders")
End With
myRow = 2
For lStyle = 1 To wb.TableStyles.Count
Set stl = wb.TableStyles(lStyle)
If stl.ShowAsAvailablePivotTableStyle = True Then
ws.Cells(myRow, 1).Value = lStyle
ws.Cells(myRow, 2).Value = stl.NameLocal
ws.Cells(myRow, 3).Value = stl.BuiltIn
lClrH = stl.TableStyleElements _
.Item(xlHeaderRow).Interior.Color
If lClrH = 0 Then
ws.Cells(myRow, 4).Value = "•"
Else
ws.Cells(myRow, 4).Interior.Color = lClrH
End If
lClrB = stl.TableStyleElements _
.Item(xlWholeTable) _
.Borders(xlInsideHorizontal).Color
If lClrB = 0 Then
ws.Cells(myRow, 5).Value = "•"
Else
ws.Cells(myRow, 5).Interior.Color = lClrB
End If
myRow = myRow + 1
End If
Next lStyle
With ws
.Range("A1:E1").Font.Bold = True
.Range("G1").Value = "• = Black"
.Columns("A:G").EntireColumn.AutoFit
.Columns("C:E").HorizontalAlignment = xlCenter
.Columns(6).ColumnWidth = 3.57
.Range("A1").Select
End With
End Sub
Get the Sample Workbook
To get the sample workbook with pivot tables, custom styles, and more macros, go to the Pivot Table Styles Macros page on my Contextures website.
The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the workbook, if you want to test the macros.
More Pivot Table Resources
__________________
