Show Yes/No in a Pivot Table
December 12, 2022 - by Bill Jelen

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.

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.

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

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

Result: The pivot table shows Yes or No values.

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 Analyze tab of the ribbon say Sum of Revenue. You can type a new name in that box.

This article is an excerpt from Power Excel With MrExcel
Title photo by Ana Municio on Unsplash