compare two columns in excel and find differences

Compare two columns in excel and find differences how to#
Compare two columns in excel and find differences professional#

Examples of using VLOOKUP to compare two columns in Excel Excel chooses 'TRUE' by default if you don't enter anything. For '', you can enter 'TRUE' or '1' if you want an approximate match or 'FALSE' or '0' if you want an exact match. If you choose columns 'A' to 'D' as your 'table_array', entering '2' for the 'column_index_num' tells Excel to search column 'B'. The 'table_array' and 'column_index_num' is how you tell Excel where to look. This can be a cell reference or you can type a value. The 'lookup_value', is what you're looking for or trying to compare.

The syntax of the VLOOKUP formula is '=VLOOKUP(lookup_value, table_array, column_index_num, )'. When you have more scattered data, the VLOOKUP algorithm can be more practical to use. Excel spreadsheet may not always have two side-by-side data lists for you to compare, which is where the IF algorithm is more practical. The IF function is efficient for simple and more beginner uses, but the VLOOKUP algorithm works better in more practical scenarios. Once you have the formula completed in cell 'C3', you can drag the formula into the other columns by selecting cell 'C3' and then clicking the bottom right of the cell and dragging down the column. If the value in cell 'A2' is the same as in cell 'B2', the formula outputs 'Match' in cell 'C3'.

If you have headers in the first row and your lists start from the second row, type the formula '=IF(A2=B2,'Match','Mismatch')' into cell 'C2'. If your information is in columns 'A' and 'B', you can put the formulas in column 'C'.

Compare two columns in excel and find differences how to#

Here's how to use the IF function in Excel:Ĭreate a third column where you want to input your end values. The IF formula that returns 'Match' when data values are the same and 'Mismatch' when they're different is '=IF(A2=B2,'Match','Mismatch')'. This way, you're able to choose the value that gets outputted when you get a match or difference. The basic structure of this method is using the '=' operator, but with the IF function. Using the IF function is a simple method of learning how to compare two columns in Excel. How to compare two columns in Excel using the IF function In this article, we discuss the process of comparing two columns in Excel in various ways.

Compare two columns in excel and find differences professional#

Developing your knowledge of Excel can help you grow your professional knowledge to help you be more productive. When comparing columns, you can identify the main similarities and differences in a data set, highlight unique data or find specific comparative information quicker. 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.Excel has many functions for manipulating and analysing data, and the function to compare two columns is a useful feature for many use cases. The formula has a dollar sign to fix columns, only changing rows. 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.Ĭompare Two Columns and Highlight the Differences in Google Sheets 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. In Excel, you can compare two columns and highlight their differences using conditional formatting. 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