• Excel Book Excerpt

Excel Show Yes/No in a Pivot Table

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.

LE10000826.jpg

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.

LE10000827.jpg

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.

LE10000828.jpg

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

LE10000829.jpg

Figure 800 Replace blanks with zero.

Result: The pivot table shows Yes or No values.

LE10000830.jpg

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.

LE10000831.jpg

Figure 802 Change from “Sum of Revenue".

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: