List All Pivot Table Styles Macro

List Your Pivot Table Styles

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 _
    If lClrH = 0 Then
      ws.Cells(myRow, 4).Value = "•"
      ws.Cells(myRow, 4).Interior.Color = lClrH
    End If
    lClrB = stl.TableStyleElements _
        .Item(xlWholeTable) _
    If lClrB = 0 Then
      ws.Cells(myRow, 5).Value = "•"
      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("C:E").HorizontalAlignment = xlCenter
  .Columns(6).ColumnWidth = 3.57
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

Pivot Table Formatting

Classic Pivot Table Format

Copy Pivot Table Formatting


List Your Pivot Table Styles

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.