How to convert PivotTable to list in Excel?
In daily Excel work, you might build a comprehensive PivotTable in order to analyze, summarize, or visualize large amounts of data. However, there are scenarios where you need to "flatten" or convert the structured PivotTable back into a regular list or standard table format—perhaps to further process the data, share it with others, or import it into another application. This tutorial explores several practical methods to convert a PivotTable back to a list in Excel, suitable for both small and large datasets.
VBA Code: Convert PivotTable to List Automatically
Convert pivot table to list
Before converting your PivotTable into a standard list, make sure the PivotTable layout is finalized as needed. For example, suppose you have designed the following PivotTable that you want to revert to a flat list structure:
1. Select any cell within your PivotTable. Go to the Design tab on the ribbon, choose Subtotals, and then select Do Not Show Subtotals. This step removes any subgrouping or subtotal rows that could interfere with the flat list format you want.
2. Still under the Design tab, click Grand Totals and choose Off for Rows and Columns. Turning off grand totals ensures that only your actual data remains, preventing summary rows or columns from appearing in your output.
3. Within the Design tab, click Report Layout and select Repeat All Item Labels. This step is important for flattening multi-level row labels, so every row in the final list will show the correct context regardless of its group or category.
4. Click Report Layout again, and then choose Show in Tabular Form. Tabular form presents all data in a true columnar layout, which is closer to a standard list or table.
At this point, your PivotTable will resemble a traditional table, making it much simpler to export as a list:
5. To further streamline the output, go to the Options (or Analyze, depending on your Excel version) tab. In the Show group, uncheck both Buttons and Field Headers. Removing these elements helps clean up your data for list conversion and makes your output tidier.
Your PivotTable will now appear clearer, with only your relevant data fields:
6. If there are empty cells you prefer to display as zeros, right-click any cell in the PivotTable and choose PivotTable Options. In the pop-up dialog, under the Layout & Format tab, uncheck For empty cells show. Then click OK. This step ensures your exported list will not have blanks where you expected values, which can be crucial when further processing or importing to other systems.
7. Select the entire PivotTable range you wish to convert. Press Ctrl + C to copy, then move your cursor to the desired cell location in your worksheet where you want to display the list. Right-click, choose Paste Special, and select Values (V). This process pastes only the results as static values, without any PivotTable structure or interactivity.
Note: In Excel 2007 or earlier, use Home > Paste > Paste Values for this step.
After pasting, your data is now displayed in standard list format, ready for further analysis, sharing, or export:
This method is straightforward and works well for small to moderately sized PivotTables that do not require frequent updates. However, if you have a dynamic or frequently changing PivotTable, consider automating this conversion process or using a built-in data transformation tool for efficiency and accuracy. Also, note that complex PivotTables with calculated fields or grouped items might require extra adjustments after conversion.
VBA Code: Convert PivotTable to List Automatically
If you need to frequently convert PivotTables to a standard list or your PivotTables are particularly large, automating the process can save significant time and effort. Using VBA (Visual Basic for Applications), you can flatten a PivotTable into a list with minimal manual steps. This solution is suitable for advanced users, batch processing, or when dealing with dozens or hundreds of PivotTables.
Advantages: Fast for repetitive tasks, handles large datasets, reduces the chance of manual errors.
Disadvantages: Requires enabling macros and some understanding of VBA. A backup is recommended before running code for the first time.
1. Click Developer Tools > Visual Basic to open the VBA editor. In the editor, click Insert > Module, and paste the following code into the new module window:
Sub ConvertPivotTableToList()
Dim pt As PivotTable
Dim wsPivot As Worksheet
Dim rngTable As Range
Dim wsNew As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set wsPivot = Application.ActiveSheet
Set pt = wsPivot.PivotTables(1)
If pt Is Nothing Then
MsgBox "No PivotTable found on the current sheet.", vbExclamation, xTitleId
Exit Sub
End If
Set rngTable = pt.TableRange2
Set wsNew = Worksheets.Add
rngTable.Copy
wsNew.Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
MsgBox "Converted PivotTable to static list on new sheet: " & wsNew.Name, vbInformation, xTitleId
End Sub2. Return to the worksheet and select the PivotTable you want to convert to a list. Press Alt + F8, select ConvertPivotTableToList from the macro list, and click Run. This creates a new worksheet with the PivotTable copied as a static list.
If you encounter an error message, check that you have selected the correct sheet containing the PivotTable before running the macro. Also, ensure macros are enabled in Excel. If your workbook contains multiple PivotTables, this code will process the first one found on the active worksheet.
Tip: You can further modify the code to process all PivotTables in a workbook or to specify which PivotTable to convert.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in