Contextures

Copy Pivot Table Formatting

Copy and paste a pivot table, to keep its values and formatting. Do the steps manually, or with a macro.

Problems Copying Pivot Table Formatting

When you try to copy a pivot table's formatting and values to another location, without the underlying data, you might run into problems.

In Excel 2007 and newer versions, if you paste the copy with the Values and Source Formatting command, the pivot table's Style formatting is not pasted -- just the values, and any normal formatting that you had applied, such as Fill colour from the Home tab.

The instructions below show how to successfully copy a pivot table's formatting and values to another location, either manually or with a macro.

pivot copy body

Copy Pivot Table Format - Manual

To manually copy pivot table values and formatting, use one of the techniques shown below. If your pivot table does NOT have any fields in the Filters area, use method 1 or 3. For pivot tables with report filters, use method 2 or 3.

  1. Manual steps - no report filters
  2. Manual steps - with report filters
  3. Manual steps - with or without report filters

Manual - No Report Filters

If the pivot table does NOT have any report filters, follow these steps to manually copy and paste the pivot table formatting and values. Thanks to Jeffrey Browne for this tip.

  1. In the original pivot table, copy the entire pivot table body
    NOTE: Do not include cells that are outside of the pivot table

    pivot copy body

  2. Right-click the cell where you want to paste the values and formatting
  3. Under Paste Options, click Values
  4. paste values

  5. Right-click the cell again, and under Paste Options, click Formatting
  6. paste formatting

  7. The values and formatting appear in the new location.

Manual - With Report Filters

If the pivot table has one or more report filters, the technique shown above does not work. Use this two-step technique instead. Thanks to Loh HW for suggesting this technique.

To manually copy and paste the pivot table formatting and values, follow these steps:

  1. In the original pivot table, copy the Report Filter labels and fields only
    NOTE: Do NOT include any blank cells
  2. copy Report Filters

  3. Select the cell where you want to paste the values and formatting
  4. Press Ctrl + V to paste the Report Filters
  5. paste Report Filters

  6. Go back to the original pivot table, and copy the entire body - do not copy any cells outside of the pivot table.
  7. copy pivot table body

  8. Select the cell where you want to paste the pivot table body values and formatting
  9. Press Ctrl + V to paste the pivot table body
  10. copy pivot table body

  11. The values and formatting appear in the new location.

NOTE: This technique will not work if you select any cells outside of the pivot table, or blank cells between Report filter fields, horizontally arranged (as shown below).

blank cell

Manual - With or Without Report Filters

Instead of using Excel's paste commands, you can use the Office Clipboard to paste the pivot table values and style formatting. Thanks to John Walkenbach for sharing this tip.

NOTE: This technique works for pivot tables with or without report filters. It also works if there are spaces between the report filters, or if you copy blank cells outside of the pivot table.

Follow these steps to copy a pivot table's values and formatting:

  1. Select the original pivot table, and copy it.
  2. Click the cell where you want to paste the copy.
  3. On the Excel Ribbon's Home tab, click the Dialog Launcher button in the Clipboard group .
  4. clipboard launcher

  5. In the Clipboard, click on the pivot table copy, in the list of copied items..
  6. paste from clipboard

  7. The pasted copy has the values and style formatting from the original pivot table.   ▲TOP

Copy Pivot Table Format - Macro

The following macro will copy pivot table values and formatting, and works for pivot table with or without report filters.

The macro adds a new sheet, then it copies the selected pivot table's values and formatting to the new worksheet. The pivot table is copied to column A, in the same row as the existing pivot table.

NOTE: Report filter formatting will not be copied if there are multiple Report filter fields, horizontally arranged. In that case, you'll see a message at the end of the macro, to let you know about the problem.go to top

Copy the following code to a regular code module in Excel. Then, select a pivot table cell, and run the macro.

Sub PivotCopyFormatValues()
'select pivot table cell first
Dim ws As Worksheet
Dim pt As PivotTable
Dim rngPT As Range
Dim rngPTa As Range
Dim rngCopy As Range
Dim rngCopy2 As Range
Dim lRowTop As Long
Dim lRowsPT As Long
Dim lRowPage As Long
Dim msgSpace As String

On Error Resume Next
Set pt = ActiveCell.PivotTable
Set rngPTa = pt.PageRange
On Error GoTo errHandler

If pt Is Nothing Then
    MsgBox "Could not copy pivot table for active cell"
    GoTo exitHandler
End If

If pt.PageFieldOrder = xlOverThenDown Then
  If pt.PageFields.Count > 1 Then
    msgSpace = "Horizontal filters with spaces." _
      & vbCrLf _
      & "Could not copy Filters formatting."
  End If
End If

Set rngPT = pt.TableRange1
lRowTop = rngPT.Rows(1).Row
lRowsPT = rngPT.Rows.Count
Set ws = Worksheets.Add
Set rngCopy = rngPT.Resize(lRowsPT - 1)
Set rngCopy2 = rngPT.Rows(lRowsPT)

rngCopy.Copy Destination:=ws.Cells(lRowTop, 1)
rngCopy2.Copy _
  Destination:=ws.Cells(lRowTop + lRowsPT - 1, 1)

If Not rngPTa Is Nothing Then
    lRowPage = rngPTa.Rows(1).Row
    rngPTa.Copy Destination:=ws.Cells(lRowPage, 1)
End If
    
ws.Columns.AutoFit
If msgSpace <> "" Then
  MsgBox msgSpace
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not copy pivot table for active cell"
    Resume exitHandler
End Sub

go to top

Download the Sample File

Get the Copy Pivot Table Values and Formatting sample file, with a pivot table for testing, and the macro from this page. The zipped file is in xlsb format (Binary), and be sure to enable macros when you open the workbook, if you want to try the macro.

More Tutorials

FAQs - Pivot Tables

Excel Slicer Macros

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table

Search Contextures Sites

 

Get weekly Excel tips from Debra

 

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: April 17, 2019 4:31 PM
Contextures RSS Feed