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
