ExcelInsider Team
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.
➤ 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.
Converting Pivot Table to Table Manually
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.
Using VBA to Convert Pivot Table to 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.
Frequently Asked Questions
How to make a PivotTable like a table?
Go to the Design tab. From the Layout section, select Report Layout > Show in Tabular Form. Go to Report Layout again, and select Repeat All Item Labels. Now your pivot table looks like a normal table.
Can you convert PivotTable to data?
The GETPIVOTDATA function helps extract data from a pivot table. The formula is structured like the following:
=GETPIVOTDATA(“Data Field”, $A$3, B1, C1)
Here, “Data Field” is the name of the data field, $A$3 is the pivot table, and B1, C1 represent the field and item cells.
How do I remove a pivot table in Excel?
After transferring a pivot table to a regular table, or before making another pivot table, you might want to remove the pivot table. To do so, select a cell of the pivot table and right-click on it. From the context menu, choose Delete PivotTable to remove the pivot table.
How to make a PivotTable look normal?
Normal can mean anything; however, Microsoft Excel offers numerous styles to customize your pivot table. From the Design tab, you can select any style you like or even create your own style from the PivotTable Styles section. To make the table look more like a regular table, change the layout of the table.
Can I transpose a PivotTable?
Transposing a pivot table is easier than transposing a regular table. You can move the rows and columns freely from the PivotTable Fields panel, which will do the transposition for you.
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.
