Copy a Custom PivotTable Style

You can create custom PivotTable Styles in an Excel 2007 workbook, to fine tune the appearance of your pivot tables. If you’ve invested a lot of time in create a custom style, you might like to copy that style to a different workbook.

PivotCustomStyleMed

Unfortunately, there isn’t a built-in command that will let you copy PivotTable Styles from one workbook to another. However, you can follow a few simple steps to copy your styles to any workbook.

Copy a PivotTable Style

To copy a custom PivotTable style, you can temporarily copy a formatted pivot table to a different workbook. In this example, we’ll copy a custom PivotTable Style – MyMedium2 – from the MyOld.xlsx workbook to the MyNew.xlsx workbook.

  1. In the MyOld.xlsx workbook, select a cell in the pivot table that has the custom style applied.
  2. On the Ribbon’s Options tab, in the Actions group, click Select.PivotTableSelect
  3. Click the Entire Pivot Table command.
  4. On the Ribbon’s Home tab, click Copy.
  5. Switch to the MyNew.xlsx workbook.
  6. Select a blank worksheet, or insert a new blank worksheet.SheetInsert
  7. Select cell A1 on the blank worksheet.
  8. On the Ribbon’s Home tab, click Paste.
  9. Delete the sheet that contains the pasted copy of the pivot table.

Your custom PivotTable style now appears in the PivotTable Styles gallery.

PivotCustomStyle

Select any pivot table in the workbook, and apply your custom PivotTable Style.

Watch the Video

_____________________

For more information on Pivot Tables, please visit Pivot Table Topics index on the Contextures Website.

_____________________

4 thoughts on “Copy a Custom PivotTable Style”

  1. VBA version

    Sub copy_Custom_Pivottable_style()
    ' copies custom PivotTable sytle from active Pivottable to all other open workbooks
    Dim wbk As Workbook
    Dim pt As PivotTable
    With Application
    On Error Resume Next
    Set pt = ActiveCell.PivotTable
    On Error GoTo 0
    If Not pt Is Nothing Then
    .DisplayAlerts = False
    For Each wbk In .Workbooks
    If wbk.Name ActiveWorkbook.Name Then
    pt.TableRange2.Copy
    With wbk.Sheets.Add
    .Paste
    .Delete
    End With
    End If
    Next wbk
    .DisplayAlerts = True
    Else
    MsgBox "Select cell in pivot table and try again."
    End If
    End With
    End Sub

  2. Hi, In Office2019 / O365 copying pivot table will not work as it is pasted without format. Instead you can copy a sheet with pivot table to a new worksheet and then delete it and this works!
    Thanks for help with it!

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.