How to Convert Pivot Table to a Table in Excel (2 Quick Methods)

While pivot tables are extremely helpful for data analysis, sometimes you just want to use a regular table. Maybe you just needed the formatting provided by the pivot table, and now you want to transfer the data to a standard table so that you can work with it normally. In this article, we will learn how to convert pivot table to table in excel.

Key Takeaways

➤ Select the whole table and copy it.
➤ Go to your destination cell, right-click on it, and select Paste Values.
➤ Press Ctrl + T while you have the new cells selected to convert the range to a table.

That was one way of doing that. However, in this article, we will explain the process with images for each step, so that you won't miss anything. We will also cover an advanced method of converting a pivot table to a table, so that whether you are a beginner or an advanced user, you will learn something from the tutorial.

Here, we have some product inventory data on the pivot table. The pivot table made it easier to format the data and perform calculations such as the sum of stock and reorder level.

Now, we need to convert this pivot table to a regular table to process the information further. Here is how to do it:

➤ Select a cell from the pivot table first.
➤ Head to the PivotTable Analyze From the Actions section, go to Select > Entire PivotTable.

➤ Press Ctrl + C to copy the pivot table.
➤ Go to the new cell where you want to paste the data. For this example, we will be working on an entirely new sheet.
➤ From the Clipboard section of the Home tab, select Paste > Paste Values.

➤ The pasted range should be automatically selected. If it isn’t, select the new range using your mouse. Then press Ctrl + T .

➤ Press OK to create a new table. You have successfully converted your pivot table to a regular table.

Manually converting a pivot table to a table requires a lot of steps. Using VBA, we can automate all of those steps and just use some code to convert a pivot table to a table. In this method, we will learn how to convert pivot table to table vba. Follow the steps below to do so:

➤ While you are in the sheet where the pivot table exists, press Alt + F11 to open up the Microsoft Visual Basic for Applications Window.
➤ From the menu at the top bar, go to Insert > Module to open the code editor.
➤ Write this code:

Sub ConvertPivotTableToTable()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim rng As Range
    Dim destCell As Range
    Dim destinationSheet As Worksheet
    Dim tbl As ListObject
    Set ws = ThisWorkbook.Sheets("PivotSheet")
    Set destinationSheet = ThisWorkbook.Sheets("PivotVBATable")
    Set pt = ws.PivotTables(1)
    Set destCell = destinationSheet.Range("A1")
    pt.TableRange2.Copy
    destCell.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    lastRow = destinationSheet.Cells(destinationSheet.Rows.Count, "A").End(xlUp).Row
    lastCol = destinationSheet.Cells(1, destinationSheet.Columns.Count).End(xlToLeft).Column
    Set tbl = destinationSheet.ListObjects.Add(xlSrcRange, destCell.Resize(lastRow, lastCol), , xlYes)
    tbl.TableStyle = "TableStyleMedium2"
End Sub

➤ Replace PivotSheet with the sheet where your source pivot table belongs, PivotVBATable with the new sheet where the regular table will be placed, A1 with the cell where the new table will start, and A with the column of the new table.
➤ Press F5 to run the code.
➤ Go to the spreadsheet where you want the new table to appear and view the result.

Downloadable Resources

Get our practice workbook or necessary files that we have worked with to prepare this article.

Wrapping Up

In this article, you learned how to convert a pivot table to a table in Excel. Remember to download the workbook to explore the spreadsheet used in this tutorial. Leave your thoughts below, and check out new tutorials regularly.