Show Yes No in a PivotTable

Sometimes we may want to show Yes or No in a PivotTable instead of other values. This tutorial shows you how to show Yes and No in a PivotTable.

How to show Yes and No in a PivotTable

We use the following PivotTable in our illustration. We will show Yes in the cells that contain sale amounts and No in the cells that have no values. This is done in two steps.

Step 1: Display zeroes in the empty cells

We first need to change the settings of the PivotTable such that zeroes are displayed in the empty cells.

We use the steps below:

1. Click PivotTable Analyze >> PivotTable >> Options Arrow >> Options. This opens the PivotTable Options dialog box.

2. In the PivotTable Options dialog box that appears, ensure that the For empty cells show checkbox is checked. Then type 0 (zero) in the box next to it and click OK.

Zeros are displayed in the once empty cells:

Step 2: Create a custom number format that displays Yes or No in the data cells

We create a custom number format that displays Yes for positive sale amounts and No for 0 (zero) values.

We use the following steps:

1. Right-click any data cell in the PivotTable and choose Value Field Settings from the shortcut menu.

2. Click the Number Format button in the Value Field Settings dialog box.

3. In the Format Cells dialog box that appears, select Custom in the Category list box.

4. Select and delete the number format displayed in the Type box on the right. Then type in the following custom number format and click OK:

“Yes”;;”No”

Note: What this number format is telling Excel is, “Show Yes for positive values, show nothing for negative values, and show No for zero values.”

5. Click the OK button in the Value Field Settings dialog box.

Yes and No values are now displayed in the PivotTable.

Conclusion

We can show Yes and No in a PivotTable by first displaying 0s (zeros) in empty data cells. We then create a custom number format that displays Yes for positive values and No for 0 (zero) values.