Show Yes/No As the Values in a Pivot Table

by Best Excel Tutorial Data Analysis

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.

See also  Newton Raphson Method in Excel

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

An alternative method using Max

There is an alternative method to display Yes/No as the values in a Pivot Table. Here’s how you can do it:

  1. Create a Pivot Table by selecting the data range and clicking on the “PivotTable” button in the “Insert” tab.
  2. Drag the field that contains Yes/No values to the “Values” area in the “PivotTable Fields” pane.
  3. By default, Excel will count the occurrences of Yes and No values. Right-click on any cell in the “Values” area and select “Value Field Settings.”
  4. In the “Value Field Settings” dialog box, select “Max” instead of “Count” under the “Summarize value field by” section.
  5. Click on “OK” to close the dialog box and update the Pivot Table.

Now, you should see the Yes/No values displayed in the Pivot Table. The column headers will show “Max of” followed by the field name, and the cells will display “Yes” or “No” depending on the maximum value in each grouping. If there is at least one “Yes” value in a group, the cell will display “Yes.” If there are no “Yes” values, the cell will display “No.”

See also  How to Do an Exponential Regression in Excel to Predict Future Values

Related posts:

Compare Columns TableHow to Find Duplicates in Excel Scientific NotationHow to stop Excel from doing numbers E+? KPI dashboard Raw DataHow to Create Kpi Dashboard in Excel