How to Convert a PivotTable to a List in Excel?

A pivot table is a table of grouped values that aggregates the individual items of a more extensive table with multiple distinct categories. The operations that we can perform include sum, max, min, average, and many other mathematical operations. Have you ever tried to convert a pivot table to a list in Excel? If you try to solve this problem manually, it can be a time-consuming and complex process. Read this tutorial to learn a simple trick that you can use to convert a Pivot table to a list in a faster and more accurate way.

Converting a PivotTable to a List in Excel

Here we will first create the pivot table for the given data and then make changes to the table to complete the task. Let's take a look at a simple procedure for converting a pivot table to a list in Excel.

Step 1

Let us consider a excel sheet where the data in the sheet is similar to below image.

To create the pivot table, select the table, then click on "insert" and select "pivot table" and select all the values, and our pivot table will be similar to the below image.

We need to convert the above pivot table into a list.

Step 2

Click on any element of the pivot table, then click on design, click on subtotal, and then select do not show subtotals.

Step 3

Select Off for rows and columns after clicking on Grand totals.

Step 4

Click on "Report layout" and select "Repeat all item labels."

Step 5

Click on pivot table analyse, then on shown, and finally, deselect both buttons and field heads.

Step 6

Now, right-click on the pivot table, select pivot table options, deselect the check box named "For empty cells show," and click OK.

Step 7

Finally, select the table and use the command CTRL + C to copy them, then click on an empty cell where you want your list, then right-click and select paste special.

Next, select Values in the pop-up window and press OK.

Conclusion

In this tutorial, we used a simple example to demonstrate how you can convert a pivot table to a list in Excel.