How to Copy and Paste Pivot Table Values with Formatting in Excel

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.


Download Practice Workbook

You can download the practice workbook from here.


3 Simple Ways to Copy and Paste Pivot Table Values with Formatting in Excel

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.

How to Copy and Paste Pivot Table Values with Formatting


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.

Use Clipboard to Copy and Paste Pivot Table Values with Formatting

  • Next, the PivotTable from table or range will appear.
  • Here, the Table/Range will be selected automatically.
  • Afterward, select New Worksheet.
  • Then, select OK.

Inserting Pivot Table to Copy and Paste the Values with Formatting

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

Format Pivot Table in Excel

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

Format Pivot Table in Excel to Copy and Paste Values with Formatting

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

Changing Pivot Table Styles to Copy and Paste Values with Formatting

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

Copy Pivot Table with Formatting

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

Employ Paste Option to Copy and Paste Pivot Table Values with Formatting in Excel

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

Apply Excel VBA to Copy and Paste Pivot Table Values with Formatting

  • 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

Writing VBA Code to Copy and Paste Pivot Table values with Formatting

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

Running Macros to Copy and Paste Pivot Table Values with Formatting

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

Practice Sheet for How to Copy and Paste Pivot Table Values with Formatting


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.

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo