Home > Pivot > Layout > Copy Formatting Copy Pivot Table Formatting Copy and paste a pivot table, to keep its values and formatting, without the source data. Do the steps manually, or with a macro, to create an unlinked pivot table, with its style formatting.

Copy PivotTable Style Format and Values

When you try to copy the pivot table formatting and values to another location, the PivotTable Style formatting isn't copied. This video shows how to manually copy the formatting from a pivot table. Written instructions are below the video.

There is also a macro to copy pivot table values and formatting, which makes the task easier.

The full Video Transcript is at the bottom of this page

Problems Copying Pivot Table Formatting

When you try to copy a pivot table's formatting and values to another location, without the underlying source 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 PivotTable 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.

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

   

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



4. Right-click the cell again, and under Paste Options, click Formatting



5. 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. Select the cell where you want to paste the values and formatting
3. Press Ctrl + V to paste the Report Filters



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



5. Select the cell where you want to paste the pivot table body values and formatting
6. Press Ctrl + V to paste the pivot table body



7. 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).

Manual - With or Without Report Filters

Instead of using Excel's paste special commands, you can use the Office Clipboard to paste the copy of a 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 in their layout. 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. In the Clipboard, click on the pivot table copy, in the list of copied items..



5. 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. Click OK to close the message.

Copy the following VBA 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

Video Transcript: Copy Pivot Table Format & Values

Here is the full transcript for the video - Copy Pivot Table Format and Values, at the top of this page

'-----------------------------

Introduction

When you create a pivot table in Excel, you can change its appearance by using a pivot table style.

So right now, this pivot table has the default style, which is quite plain.

And I'm going to the pivot table tools tab and I'll click design tab, and there's a menu of pivot table styles.

So I'll select something, maybe something from the dark category.

So this one, the preview shows me has a dark heading and a row at the bottom with some blue in the centre.

So I'll select that.

Copy and Paste Pivot Table

Now, if I try and copy this pivot table, so I'm just getting the values, but not the underlying data.

So I want to change this just into values instead of a real pivot table.

When I highlight columns A and B, and then copy them,

I'll click at the top of column B and I don't want to paste. I want to just paste the values.

So from this drop down, I'll paste the values.

The other option is this Values and Source Formatting.

When I click that, we can see that I get the values from the pivot table, but not the pivot table style that I applied.

Different Way to Copy and Paste

So to delete this, I'll just delete those rows and to try a different way of copying and pasting a pivot table so we can get the formatting.

I'll again, select those columns and copy

Click, where I'd like to paste it. But instead of using the paste button here, I'm going to open the clipboard.

So I'll click this dialogue launcher, and here's the clipboard.

And you can see the item that I just copied.

And when I click that, it will paste the pivot table values and the formatting, not the column widths, however, I'd have to adjust those manually.

Pivot Formatting & Values Pasted

But when I click here, you can see the pivot table field list appears. So is a pivot table.

When I click here, there's no pivot table field list.

We have all the values and the formatting, but it's not a pivot table anymore.

So none of my underlying source data would be disclosed, if I copied this now and sent it to someone.

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