How to convert PivotTable to list in Excel
In some time, you may want to convert a designed pivot table to a list in Excel, now you can read this tutorial to finish the conversion.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
To convert pivot table to list, you must ensure that the pivot table has been set as your need. For example, I will convert the below pivot table to list.
1. Click at any cell in the pivot table, and go to Design tab, and click Subtotals > Do Not Show Subtotals.
2. Click Grand Totals > Off for Rows and Columns under the Design tab. See screenshot:
3. Click Report Layout > Repeat All Item Labels under the Design tab. See screen shot:
4. Click Report Layout again, and click Show in Tabular Form. See screenshot:
Now the pivot table show as below:
5. Click Options tab (or Analyze tab), and uncheck Buttons and Field Headers in the Show group.
Now the pivot table shown as below:
5. Now I will show the blank cells as zero. Click any cell of the pivot table, and right click to select PivotTable Options, then in the popped out dialog, under Layout & Format tab, uncheck For empty cells show, and click OK to close the PivotTable Options dialog. See screenshot:
If you want to know more about how to show empty cells as zero, please click here.
6. Select the pivot table and press Ctrl + C in a meanwhile to copy it, then place the cursor on the cell you want to paste the pivot table as list, and right click to select Paste Special > Value (V). See screenshot:
Note: In Excel 2007, you need to click the Home > Paste > Paste Values to paste the pivot table as list.
Now you can see the list showed as below: