3 Simple Ways to Convert Pivot Tables to Excel Sheets

In this guide, we will explore three straightforward methods to convert your pivot tables into regular Excel sheets. Whether you're looking to manipulate data further or share your findings, these techniques will help you achieve that efficiently. Let's dive into the steps to make your pivot table data more accessible and manageable.

Method 1: Copy and Paste

The first method involves a simple copy and paste, which is quick but doesn’t maintain the connections with the source data.

• Select the entire pivot table.
• Right-click and choose “Copy” or press Ctrl + C.
• Go to a new Excel sheet.
• Right-click and choose “Paste Values” or press Alt + E, S, V, Enter.

🔗 Note: This method will remove the pivot functionality, making the data static. If you need to update the data later, you'll have to manually redo this process.

Method 2: Exporting Pivot Table Data

Exporting pivot table data to a new Excel workbook preserves your work and allows for further manipulation without affecting the original source.

• Right-click on the pivot table.
• Select “PivotTable Options”.
• Go to the “Data” tab and check “Save source data with file”.
• Click “OK”.
• Choose “Save As” and select “Excel Workbook” or “Excel Binary Workbook” for faster performance.

📁 Note: The "Excel Binary Workbook" format saves space and can be beneficial for larger datasets.

Method 3: Using VBA Macro

For more control over how your pivot table data is exported, you can use VBA to automate the process. Here’s how you can set it up:

• Press Alt + F11 to open the VBA Editor.
• Go to “Insert” > “Module”.
• Paste the following code into the module:

  
  Sub ExportPivotToNewSheet()
      Dim ws As Worksheet
      Dim pt As PivotTable
      Dim NewSheet As Worksheet
      Set ws = ActiveSheet
      Set pt = ws.PivotTables(1)
      pt.TableRange2.Copy
      Set NewSheet = ThisWorkbook.Worksheets.Add
      With NewSheet
          .Name = “Pivot Export”
          .Range(“A1”).PasteSpecial xlPasteValuesAndNumberFormats
          .Columns.AutoFit
      End With
  End Sub
  
  

• Close the VBA Editor.
• Go back to Excel, press Alt + F8, select “ExportPivotToNewSheet”, and run the macro.

📝 Note: To use VBA, you'll need to enable macros in your Excel settings.

Each method provides a different level of flexibility and control over your pivot table data. Whether you need a quick and easy solution, or a customizable and repeatable process, there's a method for every user's needs. While converting a pivot table to a regular sheet makes the data static, it also opens up new possibilities for further analysis and sharing. Remember that if you ever need to update the data or reflect changes in the original dataset, you'll have to manually recreate or refresh your exported sheet unless you've saved the pivot cache in a way that allows updates.

What happens if I update the source data after converting the pivot table?

+

Once you convert the pivot table into a regular sheet, the data becomes static. Any changes to the source data won’t reflect in the exported sheet unless you have saved the pivot cache or you manually update the exported sheet.

Can I keep the pivot table’s format when I export it?

+

Yes, if you use the “Copy and Paste Values” method, the formatting will stay intact. However, when using the VBA method, you might need to set the formatting within the VBA code or manually apply it after the data is exported.

What’s the benefit of using an Excel Binary Workbook?

+

An Excel Binary Workbook (.xlsb) provides faster performance with large datasets since it uses less memory to open and save. This can make handling large pivot tables or sheets more efficient.