Spreadsheets: Problems with Pivot Table Labels

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.

Drive Business Strategy and Growth

Learn how NetSuite Financial Management allows you to quickly and easily model what-if scenarios and generate reports.

Learn more About Oracle NetSuite

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:

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

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:

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

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.