This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Create a Flattened Pivot Table for Reuse
Problem: Why would they put three different kinds of information in column A? Doesn’t this make pivot tables as silly as the person who created the bad data set back in “Add a Customer Number to Each Detail Record"?
Figure 779 Microsoft is mixing 3 fields in one column.
My goal is to use the pivot table to make a summary, then convert to values for use as a new data set. Having three different fields in column A is really bad form.
Strategy: It is very annoying that Microsoft made this new view be the default. Luckily, it is only a few clicks to go back to the proper view.
1. Select one cell in the pivot table.
2. Choose the Design tab of the ribbon.
3. Open the Report Layout dropdown.
4. Change from Compact Form to Tabular Form.
5. If you have Excel 2010, open the Report Layout dropdown again and choose Repeat All Item Labels.
Figure 780 New in Excel 2010, eliminate blanks in the row area.
6. For each field in the Row Area except the last field, open the dropdown in the Row Area dropdown and choose Field Settings.