Editor’s Note:
To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page.
Reader Tom O. wins a copy of the book Excel Gurus Gone Wild for his question: “In a pivot table, how can you get the row labels to appear in multiple columns instead of one column?”
I can tell that Tom has upgraded to either Excel 2007 or Excel 2010 because this is when Microsoft started squeezing all of the row fields into the left-most column of a pivot table. In Figure 1, you will see that Product, Region, and Customer are all in column A of the pivot table.
This is frustrating for anyone who plans to reuse the results of the pivot table in further analysis. Let’s face it: many people use pivot tables to create a summary report, only to immediately copy and paste as values to reuse that summary report elsewhere. When Microsoft puts three different fields in the same column, it is tough to do meaningful lookups or sorting or anything with this view of the data.
This new format is called the Compact Layout. The Excel team is so enamored with the Compact Layout that it made it the default layout for all new pivot tables created with Excel 2007 or newer.
Fig. 1
The good news: there is a way to go back to the original view. The bad news: you have to repeat this step for every pivot table you ever create.
To return to a normal layout of the pivot table, follow these steps:
Fig. 2
Excel will revert to the traditional layout. The Product, Region, and Customer fields will each get their own column. The awkward heading of “Row Labels” will be replaced with meaningful headings such as “Product,” “Region,” and “Customer” as shown in Figure 3.
Fig. 3
If you are planning to reuse the data, there are still some problems. There are many blank cells in columns A and B that need to be filled in with the value of the cell above. Starting in Excel 2010, you can solve this by choosing the Repeat All Item Labels menu option from the Report Layout dropdown (see Figure 4).
Fig. 4
You may not want total rows for the outer row fields. To remove the totals, follow these steps:
Fig. 5
The result will be a solid block of data as shown in Figure 6. You can easily sort or filter this data.
Fig. 6
Bill Jelen is the author of 32 books, including Power Pivot for the Excel Data Analyst, and is the host of MrExcel.com. Suggest a topic for his next column at CFO.com’s Spreadsheet Community Center (right) and if your suggestion is chosen, you’ll receive a copy of one of Jelen’s new books.