Excel compare two columns how to#

This formula returns 2, since there are two non-matching cells. The formula in G7 is: =SUMPRODUCT(-(B5:B15D5:D15)) To count non-matching values, you can reverse the logic and use the not equal to operator (). With no other arrays to multiply, SUMPRODUCT simply sums the values and returns 9. Working from the inside out, we compare the range B5:B15 to D5:D15 like this: B5:B15=D5:D15īecause there are 11 values in the first range, the result is an array with 11 TRUE and FALSE values like this: ) // returns 9 Its behavior is simple: it multiplies, then sums the product of arrays. The SUMPRODUCT function is a versatile function that handles array operations natively without any special array syntax.

A good way to solve this problem is to use the SUMPRODUCT function or the SUM function, as explained below. This means that the formatting rule will go row-by-row and compare cells in Columns B and C.Īs a result, cells with different values have a red fill color.In this example, the goal is to compare two columns and return the count of matches in corresponding rows. And in the result, you will get TRUE if both values are the same. The formula has a dollar sign to fix columns, only changing rows. To compare two cells in Excel, you can use the equal operator by referring to both cells. Then (3) click on the fill color icon, (4) choose red, and (5) click Done. In the window on the right side, (1) select Custom formula is under Format rules and (2) enter the formula:.

Select a data range you want to compare (B2:C9), and in the Menu, go to Format > Conditional formatting.You can also compare two columns using conditional formatting in Google Sheets. You could also have selected Duplicate in that case, the same values would be highlighted.Īs a result, cells containing different values in Columns B and C are highlighted in red. Excel - compare two columns, if match subtract values of other two columns from matching rows Ask Question Asked 7 years, 1 month ago Modified 5 years, 1 month ago Viewed 13k times 1 Im trying to compare two columns for identical values in cells. You can leave the default format (Light Red Fill with Dark Red Text). In the pop-up window, (1) select Unique and (2) click OK.Select data in the columns you want to compare and in the Ribbon, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.To highlight all differences (Rows 3, 6, 7, and 9) in red, follow these steps: Say you have the following data with two lists of names in Columns B and C. Select all the cells in the Name column (including the Name header). While in the Home tab, click on the Styles groups. Type MATCH( and link to the cell containing Kevin the name we want to look up.

Excel compare two columns how to#

In Excel, you can compare two columns and highlight their differences using conditional formatting. How to Compare Two Columns in Excel (6 Quick & Easy Ways) Select the cells in the dataset for comparison. In this tutorial, you will learn how to compare two columns and highlight differences in Excel and Google Sheets.Ĭompare Two Columns and Highlight the Differences