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.
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
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.
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.
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.
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:
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).
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:
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
Here is the full transcript for the video - Copy Pivot Table Format and Values, at the top of this page
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.
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.
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.
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.
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.
Last updated: May 23, 2022 3:43 PM