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.
Pivot Table Tools
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:
- copy the formatting from one pivot table, and apply it to another pivot table.
- change all the values from Count to Sum
- remove the “Sum of” from all the headings
and much more!
More Pivot Table Resources