If you copy a pivot table and then paste it, you will get the pivot table. But, if you want to copy the values of a pivot table with the formatting, it can be a little tricky. The objective of this article is to explain how to copy and paste pivot table values with formatting in Excel.
How to Copy and Paste Pivot Table Values with Formatting in Excel: 3 Simple Ways
To explain this article, I have taken the following dataset. This dataset contains sales information. It has 3 columns, State, Product, and Sales. Here, I will create a pivot table with this dataset and then explain how to copy and paste pivot table values with formatting in Excel. I will explain 3 different methods of doing it.
1. Use Clipboard to Copy and Paste Pivot Table Values with Formatting
In this first method, I will insert the pivot table first. Then, I will format the pivot table. And, I will use the Clipboard to copy and paste pivot table values with formatting in Excel. Let’s see the steps.
Step-01: Insert Pivot Table
In this step, I will insert the pivot table.
- Firstly, select the data with which you want to make the pivot table.
- Secondly, go to the Insert tab from the Ribbon.
- Thirdly, select PivotTable.
- Next, the PivotTable from table or range will appear.
- Here, the Table/Range will be selected automatically.
- Afterward, select New Worksheet.
- Then, select OK.
- After that, a PivotTable will be inserted into a new Excel sheet.
- Now, I will add fields to this PivotTable.
- Click any cell of the table.
- After that, the PivotTable Fields Task Pane will appear on the Right side of the screen.
- Then, select and drag the fields you want in the Rows.
- Next, select and drag the field you want as Values.
- Finally, you can see that I have added the fields in the PivotTable.
Step-02: Format Pivot Table in Excel
Now, I will format the PivotTable I inserted in the step before.
- To begin with, I formatted the sheet to give it a better look.
- Now, I will change the format of the cells.
- To do so, select the cells.
- Then, right-click on the selected cells.
- Afterward, select Format Cells.
- Consequently, the Format Cells dialog box will appear.
- Next, select the Category you want. Here, I selected Currency.
- Then, select OK.
- Now, you can see that the format is changed to Currency.
- Next, I will change the Style of the PivotTable.
- Select the PivotTable.
- After that, go to the Design tab.
- Then, click on the marked button in the following image.
- Here, a drop-down menu will appear.
- Select the Style you want. Here, I selected the marked Style for the PivotTable.
- In the end, you can see that I have formatted my PivotTable. And, this is how it looks.
Step-03: Copy Pivot Table with Formatting
In this step, I will show you how you can copy and paste pivot table values with formatting in Excel.
- First, select the PivotTable.
- Next, press Ctrl + C on your keyboard to copy the table.
- After that, go to the Home tab.
- Then, click on the marked portion to open the Clipboard.
- After that, the Clipboard will appear on the left side of the screen.
- Select the cell where you want to paste the pivot table values with formatting.
- Then, select the PivotTable from the Clipboard.
- Finally, you will see that you have copied the PivotTable values with formatting.
2. Employ Paste Option to Copy and Paste Pivot Table Values with Formatting in Excel
Here, I will use the Paste option to copy and paste pivot table values in Excel. Let’s see the steps.
Steps:
- Firstly, select the PivotTable.
- Secondly, press Ctrl + C on your keyboard to copy the PivotTable.
- Thirdly, select the cell where you want to paste the pivot table values. Here, I selected Cell E4.
- Then, go to the Home tab.
- Afterward, click on the drop-down option for Paste.
- Next, select Values & Source Formatting.
- Now, you will see that the values in the pivot table are pasted as it is.
- Then, click on the drop-down menu to paste again.
- After that, select Formatting.
- Finally, you will see that you have copied the values in the Pivot Table and got your desired results.
3. Apply Excel VBA to Copy and Paste Pivot Table Values with Formatting
In this method, I will use VBA to copy and paste pivot table values with formatting. Here, I will paste the values into a new worksheet. Let’s see how it is done.
Steps:
- In the beginning, go to the Developer tab.
- Then, select Visual Basic.
- Consequently, the Visual Basic Editor window will appear.
- Select the Insert tab.
- After that, select Module.
- Here, a module will appear.
- Write the following code in that module.
Sub Paste_PT_with_Format()
Dim my_worksheet As Worksheet
Dim pivot_table As PivotTable
Dim pvt_tbl_rng As Range
Dim pvt_tbl_rngA As Range
Dim copy_rng As Range
Dim copy_rng2 As Range
Dim pt_top_row As Long
Dim pt_rows As Long
Dim pt_rows_page As Long
Dim message_spaces As String
On Error Resume Next
Set pivot_table = ActiveCell.PivotTable
Set pvt_tbl_rngA = pivot_table.PageRange
On Error GoTo errHandler
If pivot_table Is Nothing Then
   MsgBox "Excel Can't Copy PitotTable"
   GoTo exitHandler
End If
If pivot_table.PageFieldOrder = xlOverThenDown Then
 If pivot_table.PageFields.Count > 1 Then
   message_spaces = "Spaces with filters." _
     & vbCrLf _
     & "Can't copy filters."
 End If
End If
Set pvt_tbl_rng = pivot_table.TableRange1
pt_top_row = pvt_tbl_rng.Rows(1).Row
pt_rows = pvt_tbl_rng.Rows.Count
Set my_worksheet = Worksheets.Add
Set copy_rng = pvt_tbl_rng.Resize(pt_rows - 1)
Set copy_rng2 = pvt_tbl_rng.Rows(pt_rows)
copy_rng.Copy Destination:=my_worksheet.Cells(pt_top_row, 1)
copy_rng2.Copy _
 Destination:=my_worksheet.Cells(pt_top_row + pt_rows - 1, 1)
If Not pvt_tbl_rngA Is Nothing Then
   pt_rows_page = pvt_tbl_rngA.Rows(1).Row
   pvt_tbl_rngA.Copy Destination:=my_worksheet.Cells(pt_rows_page, 1)
End If
my_worksheet.Columns.AutoFit
If message_spaces <> "" Then
 MsgBox message_spaces
End If
exitHandler:
   Exit Sub
errHandler:
   MsgBox "Excel Can't Copy PitotTable"
   Resume exitHandler
End Sub
🔎 How Does the Code Work?
- Here, I created a Sub Procedure named Paste_PT_with_Format.
- Then, I declared the variables needed for this Sub Procedure.
- After that, I used the If Statement to check different logical situations for the PivotTable.
- Next, I used the Copy method to copy the PivotTable range.
- Finally, I ended the Sub Procedure.
- After that, Save the code and go back to your worksheet.
- Select the PivotTable.
- Then, go to the Developer tab.
- Next, select Macros.
- Consequently, the Macro dialog box will appear.
- Select Paste_PT_with_Format as the Macro name.
- Then, select Run.
- Finally, you will see that I have copied and pasted the values of the pivot table with formatting in a new worksheet.
- In the end, I formatted the sheet to give it a better look.
Things to Remember
- If you are working with VBA, you must save the Excel file as Excel Macro-Enabled Workbook. Otherwise, the VBA code will not work properly.
Practice Section
Here, I have provided a practice sheet for you to practice how to copy and paste pivot table values with formatting in Excel.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
To conclude, I tried to explain how to copy and paste pivot table values with formatting in Excel. Here, I explained 3 different methods. I hope this article was clear to you. If you have any questions, feel free to let me know in the comment section below.
Related Articles
<<Go Back to How to Copy Pivot Table| Pivot Table in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hello, How to copy selected pivot and paste it to new book?
thanks
Hey There, Mr. Mark.
Thanks for dropping your query here. You can follow the steps to copy the pivot table to the new book.
Regards
ExcelDemy Team