cfo.com

Print this article | Return to Article | Return to CFO.com

Spreadsheets: Problems with Pivot Table Labels

How do you get pivot table row labels to appear in multiple columns instead of one column?
Bill Jelen, CFO.com | US
October 6, 2010

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

MrExcel 6-Fig1B

 

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:

  1. 1. Select any cell inside the pivot table. The PivotTable Tools tabs appear in the Ribbon.
  2. 2. Go to Design tab of the ribbon.
  3. 3. From the Design tab, open the Report Layout dropdown.
  4. 4. Choose Show in Tabular Form as shown in Figure 2.

Fig. 2

MrExcel 6-Fig2

 

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

MrExcel 6-Fig3

 

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

MrExcel 6-Fig4

 

You may not want total rows for the outer row fields. To remove the totals, follow these steps:

  1. Select one cell in the Product column.
  2. From the PivotTable Tools Options ribbon tab, choose the Field Settings icon.
  3. In the Field Settings dialog, choose None instead of Automatic as shown in Figure 5.
  4. Repeat steps 1-3 for the Region column.
  5. Unselect the +/- Buttons icon from the right side of the Options tab.
  6. To remove the Grand Total row, go to the Design tab of the ribbon, open the Grand Totals dropdown, and choose Off For Rows and Columns.

Fig. 5

MrExcel 6-Fig5

 

The result will be a solid block of data as shown in Figure 6. You can easily sort or filter this data.

Fig. 6

MrExcel 6-Fig6

 

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.





CFO Publishing Corporation 2009. All rights reserved.