How to compare 2 columns in pivot table and show results in the ROW where both columns have data?

Question

Hello,

I have a pivot table and I need to only show the values in the ROW, where both columns have data.  I can add an IF statement outside of the pivot, but then lose the data behind the clicking of value and ability to sort.

Any ideas?

Thanks in advance!

Answer 1

Hi Christie,

Thank you for contacting Microsoft, I am happy to help!

You can Compare Two Columns in the Pivot Table in Excel Using IF Condition

In Excel, you can compare two columns using the IF condition. The formula to compare two columns is =IF(A2=B2,”Match”,” ”). It returns the result as Match against the rows that contain matching values, and the remaining rows are left empty.

I hope this is helpful!

Best regards,

Shannah

Answer 2

Hello,

Thank you for the quick response.

I only want to show the rows that have values for both columns.

For example, I would NOT show these rows in Red.

Is this possible?

I've tried where value count > 0 but I think it's taking the grand total.

Answer 3

Hello Christie, Thank you for reaching out and I am sorry to hear you are experiencing such difficulty.

If your pivot table has two or more columns of data and you want to ensure only rows with non-empty values in both columns are displayed, you can use Value Filters.

Right-click on a cell in the column you want to apply the filter to within the pivot table. Hover over Filter, then select Value Filters. Choose Does Not Equal and then enter or select the value that represents an empty cell (e.g., 0 or an empty string, depending on your data). Repeat the process for the other column.

Another solution is to prepare your data to ensure it meets your criteria before it even gets into the pivot table.

Add a new column in your source data. Use an IF or similar formula to identify rows where both of the relevant columns have data (e.g., =IF(AND([@Column1] <> "", [@Column2] <> ""), "Include", "Exclude")). Refresh or create your pivot table based on this adjusted data set. Use the new column as a report filter or row filter and set it to only show rows where the value is "Include". This method keeps all data manipulation within the pivot table's capabilities, maintaining your ability to sort.

I hope I have been able to resolve this for you today and I hope you have a good day.

Best Regards, Ozi N