Excel: Show Yes/No in a Pivot Table

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


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.

  1. 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.

  2. 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.

  3. 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."

  4. Replace blanks with zero.

    Result: The pivot table shows Yes or No values.

  5. 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.

  6. Change from "œSum of Revenue".