Show Yes/No As the Values in a Pivot Table

Showing the yes/no value in the Pivot Table has multiple steps to it. See how to do that in Excel. 

Yes no data preparation

To count the values of "yes no" in a PivotTable, you need the following data:

Pivot Table data table yes no

1. Click on an empty cell beside the value (1), and type =IF(B4>4500,"Yes","No") (2). Finally, double click on the small square in the lower right corner of the result (3).

Pivot Table empty cell create formula

Inserting a pivot table

Unique yes no

2. Mark the data (1), click insert (2), and then click Pivot Table (3).

insert pivot table yes no

3. Click ok

4. Check a label, which in this case is the (name).

pivot table fields name

Note: Check the unique too.

5. Drag a name to the row, and then do the same with unique.

Pivot Table click drag name

Note: The name and unique were dragged here.

The Pivot would look something like this:

ready yes no pivot table

Amount yes no

Examine the next case. You have received the sales report. This is a total sale, but you are only interested in trades over $ 10,000.

Add a column where you enter YES if sales are greater than $ 1000 and NO for the lower amount.

Use the if formula: =IF(C4>10000,"YES","NO")

Pivot Table yes no sales report

Create a pivot table. By adding an additional column, you now have the option to create a yes no sales report.

The pivot table below shows a report of the number of transactions over $ 10,000 per day.

Pivot Table number of transactions a day

Aggregate report yes no

Yes no data also allows us to aggregate.

In this case, we have a sales report with 3 customer groups:

  • private
  • commercial
  • vip

Private clients are not preferential for you.

You want to create a pivot table with sales values. You are only interested in preferential customers.

Create an additional column and, thanks to the if function, aggregate commercial and vip clients: =IF(D4="Private","NO","YES")

 Pivot Table preferential clients

Thanks to the preferential column, you are able to create a pivot table showing how often your key customers make transactions.

Pivot Table preferential customers report