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.
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 797 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 798 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 799 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 800 Replace blanks with zero.
Result: The pivot table shows Yes or No values.
Figure 801 Instead of numbers, show Yes/No.
Additional Details: At this point, the heading of “Sum of Revenue" is not really appropriate. Select any Yes/No cell to make the active field box in the Options tab of the ribbon say Sum of Revenue. You can type a new name in that box.