How to Convert a Pivot Table to a Standard List

Watch this video tutorial in High Definition on YouTube

A viewer aksed for my help. She has a boss and a few customers who:

Do not know how to create or use Pivot Tables
Have no desire to learn how to create and use Pivot Tables

Convert Pivot Table to List

Nevertheless, in this example, she is being asked to create a summary report – by Year and by Quarter – from @ 5,000 daily transaction records. The fastest way to create this report is with a Pivot Table. Now, to meet the demands of her boss and clients, she needs to convert this Pivot Table into a standard list that can be Filtered and Subtotaled.

Summarize by Year and Quarter

To create this Pivot Table report:

Add the “Date” field to the grid and then select a single date.
Group the Date field by Month, Quarter and Year.
Add and arrange the Customer and Invoice fields to the Pivot Table Grid.

Convert Pivot Table to Standard List

Remove both the Subtotals and Grand Totals from the Pivot Table Report.
In Excel 2007 & 2010, ensure that you are using either the Outline or Tabular report layouts – NOT the new “default” Compact layout.
Also in Excel 2007 & 2010, remove the Field Headers and the + / – Symbols.
Now, select the entire Pivot talbe (use Ctrl + A) and copy it to the clipboard.
Use Paste Special Values to paste the Pivot Table Results.

Filling the Blank Cells in the List

As you can see, whenever you have “nested rows” in a Pivot Table you get a nice, clean report. However, in order to Filter and Subtotal this new list, you will need to “fill in the blank cells.”

Select all of the values and all of the blanks in Column A.
Use Ctrl + G (or F5) to open the Go To Dialog Box and choose Special.
From the Go To Special choices choose “Blanks.”
With all of the Blank Cells selected use this formula: = ↑
Do NOT press Enter. Rather, press Ctrl + Enter and all of the Blank Cells are now filled in with the Customer Names!
Finally use Copy, Paste Special, Values to complete this process.

Watch This Video on My YouTube Channel – DannyRocksExcels

Since this video tutorial is a little longer than usual, I have decided to post it as a video – in High Definition – on my YouTube Channel. Click here to view it or copy and paste this link into your browser:

Link to YouTube Video: http://youtu.be/IwReVDJ7BAI?hd=1