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.
- 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.
- 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.
- 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."
- Replace blanks with zero.
Result: The pivot table shows Yes or No values.
- 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.
- Change from "Sum of Revenue".