COLLAPSE AND EXPAND PIVOT FIELDS

Additional Details: If you select the innermost row field (in this case, Customer) and select Expand

Entire Field, Excel assumes that you must need more detail for Customer. Because there is no additional detail in the pivot table, Excel will display the Show Detail dialog, allowing you to add a new field as the innermost row field.

Figure 787 Try to expand the innermost row field, and Excel will offer to add a new field.

SPECIFY A NUMBER FORMAT FOR A PIVOT TABLE FIELD

Problem: In a pivot table, a Values field tends to appear in a General format. This doesn’t always work for me. I might want thousands separators or even to show numbers in thousands. If I change the number format using the settings in the Home tab of the ribbon, the number format is lost after the next pivot table refresh.

Strategy: Microsoft has fixed this long-standing problem, but in a very subtle way.

In the figure below, you must select all of the Revenue cells, including the Grand Total Row and the Grand Total column. If you select the entire range, you can apply any number formatting from the Home tab and that formatting will stick.

Figure 788 Select all revenue cells before changing the format.

This never used to work! It was fixed in Excel 2010 but the fix was not documented. I accidentally discov- ered the fix in 2015 and even after I discovered the fix, I did not believe it would work. One of the program managers on the Excel team confirmed that the behavior changed recently.

Gotcha: One of the conventions in formatting tables says that you should include a currency symbol on only the first and total rows of a data set. There is no inherent way to do this with a pivot table. However, you can use the numeric formatting attached to the Sum of Revenue field to assign a non-currency format. Then you select the first row of cells and assign a currency format by pressing Ctrl+1 to display the Format

Cells dialog. This will work initially, but will be lost when you change the table.

PRESERVE COLUMN WIDTHS

Problem: I’ve nicely formatted my pivot table, including using narrow column widths.

Figure 792 Column widths stay as you set them.

SHOW YES/NO IN A PIVOT TABLE

Problem: I want to show Yes/No values in a pivot table. If the customer bought from us in a period, show Yes. If there were no sales, show No.

Figure 793 Instead of numbers, show Yes/No.

Strategy: Use a custom number format of “Yes”;”Credit”;”No”. Follow the steps in “Specifying a Number Format In a Pivot Table”. Once you are in the Format Cells dialog for the Sum of Revenue field, choose

Custom and type the code, including the quotes.

Figure 794 Show Yes for any positive value, No for zero.

Gotcha: This trick initially shows Yes for periods where there is a purchase, but leaves the other periods blank.

Figure 795 The Yes values appear.

To display the No values, you have to replace the blanks in the pivot table with a zero. See “Replace Blanks in a Pivot Table with Zeroes.”

Figure 796 Replace blanks with zero.