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.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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: