Compare two columns for matches and differences in Excel

This tutorial talks about how to compare two columns in Excel, which is a normal Excel job in our daily work. Comparing two columns can be done in different ways in Excel, such as comparing two columns row by row or cells by cells, comparing two columns for highlighting matches or differences, etc. Here this article covers most possible scenarios of the comparing two columns you might meet, and hope it can help you.

This Tutorial Covers

1. Compare two columns row by row

1.1 Compare cells in the same row for exactly match

1.2 Compare cells in the same row for exactly or case insensitive match (using IF formula)

1.3 Compare cells in the same row and highlight matching or mismatching data (using Conditional Formatting)

1.4 Compare two columns row by row and highlight mismatch values (using VBA)

2.Compare two columns in cells and select or highlight duplicate or unique data

2.1 Compare two columns cells by cells and display comparing result in another column (using formula)

2.2 Compare and highlight duplicate or unique data (using Conditional Formatting)

2.3 Compare and select or highlight duplicate or unique data (using a handy tool)

2.4 Compare two columns and list exact duplicates in another column (using VBA code)

2.5 Compare two columns and highlight duplicates (using VBA code)

3.Compare multiple columns in the same row

3.1 Find full matches in all cells in the same row (using IF formula)

3.2 Find matched in any two cells in the same row (using IF formula)

3.3 Find full matches in all cells in the same row and highlight (using Conditional Formatting)

3.4 Compare multiple columns and highlight row differences

4. Compare two columns and find missing data points

4.1 Compare and find the missing data points (using VLOOKUP or MATCH formula)

4.2 Compare two columns and list missing data in below (using INDEX formula)

4.3 Compare and find the missing data points and list them in another column (using a handy tool)

4.4 Compare two columns and list missing data in below (using VBA)

5. Compare two columns (dates) if greater than or less than

5.1 Compare two columns if greater than or less than (using formula)

5.2 Compare two columns if greater than or less than then format (using Conditional Formatting)

6. Compare two columns and return value in another column

6.1 Pull the exact matching data (using formula or a handy tool)

6.2 Pull the partial matching data (using formula)

7.  Compare two columns and count matches or difference

7.1 Compare two columns and count matches (using SUMPRODUCT formula)

7.2 Compare two columns and count matches or differences (using a handy tool)

8. Compare two columns with wildcard

9.   Delete differences or matches

10.  Compare two columns and calculate percentage change between

11.  Compare two ranges

11.1 Compare two ranges by cell

11.2 Compare two ranges if data in the same order

Note

In this tutorial, it provides some example data for better explaining the methods on comparing two columns in various of cases. Based on your dataset, you may need to change or adjust some contents (references), however, the basic principles would remain the same. Or directly download the samples of each cases if you only want to verify if the methods work or not.


1. Compare two columns row by row

Below is a data set where I need to check in the same row whether the names in column A are same with those in column B or not.
doc compare columns 1.1 1

doc sampleClick to download the sample file

1.1 Compare cells in the same row for exactly match


Generally, if you want to compare two columns row by row for exactly matching, you can use below formula:

=B2=C2

doc compare columns 1.1 1

Press Enter key and drag fill handle down to cell D8. If the formula returns TRUE, the values of two columns are totally same, if returns FALSE, they are different.
doc compare columns 1.1 1

1.2 Compare cells in the same row for exactly or case insensitive match (using IF formula)


If you want to compare two columns row by row for case insensitive, or get more description such as Match, Mismatch, you can use the IF function.

Comparing cells in same row for exactly match

If you want to use the texts “Match” and “Mismatch” to describe the comparing results, please use below formula:

=IF(EXACT(B2,C2),"Match","Mismatch")

doc compare columns 1.1 1

Press Enter key to get the first result, then drag auto fill handle to cell D8.
doc compare columns 1.1 1

Comparing cells in same row for case insensitive match

If you want to compare cells for case insensitive, you can use below formula:

=IF(B2=C2,"Match","Mismatch")

doc compare columns 1.1 1

Press Enter key to get the first result, then drag auto fill handle to cell E8.
doc compare columns 1.1 1

Remark

In above formulas, you can change the texts “Match” and “Mismatch” to your own description.

1.3 Compare cells in the same row and highlight matching or mismatching data (using Conditional Formatting)


If you want to highlight the matching or different values, the Conditional Formatting feature can help you.

1. Select the two columns that are used to compared with (B2:C8, excluding column headers), then click Home > Conditional Formatting > New Rule.
doc compare columns 1.1 1

2. In the popping New Formatting Rule dialog, click to choose Use a formula to determine which cells to format in the Select a Rule Type section, then type =$B2=$C2 into the textbox of Format values where this formula is true.
doc compare columns 1.1 1

3. Now click Format to display the Format Cells dialog, then under Fill tab, choose one color you need to highlight the matches.

Or you can change the font size, font size, cell borders, or the number format to outstand the matches as you need in other tabs.
doc compare columns 1.1 1

4. Click OK > OK to close the dialogs, then the cells in the same row will be highlighted if they are same.
doc compare columns 1.1 1

If you want to highlight the mismatch values, you can use this in the =$B2<>$C2 into the Format values where this formula is true textbox in the Edit Formatting Rule dialog.
doc compare columns 1.1 1

Then the differences of two columns in the same row will be highlighted with a specified color.
doc compare columns 1.1 1

1.4 Compare two columns row by row and highlight mismatch values (using VBA)


If you want to compare two columns row by row with VBA code, this tutorial satisfies you.

1. Enable the worksheet that contains the two columns used to compare, press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window.

2. In the popping dialog, click Insert > Module.
doc compare columns 1.1 1

3. Then copy and paste below macro in the new module script.

VBA: Compare two columns row by row and highlight the differences

Sub ExtendOffice_HighlightColumnDifferences()
'UpdatebyKutools20201016
Dim xRg As Range

Dim xWs As Worksheet

Dim xFI As Integer

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

 

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns"

    GoTo SRg

End If

 

Set xWs = xRg.Worksheet

For xFI = 1 To xRg.Rows.Count

    If Not StrComp(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2), vbBinaryCompare) = 0 Then

        xWs.Range(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2)).Interior.ColorIndex = 7 'you can change the color index as you need.

End If

Next xFI

End Sub

doc compare columns 1.1 1

4. press F5 key to run the code, then a dialog pops out for selecting the two columns.
doc compare columns 1.1 1

5. Click OK. Then the differences of two columns have been highlighted with a background color.
doc compare columns 1.1 1

Remark

You can change the highlighting color based on your own need by changing the color index in the code, the Color index reference:
doc compare columns 1.1 1


2. Compare two columns in cells and select or highlight duplicate or unique data

In this part, the data set is shown as below, and you want to find all values which are in both column B and column C simultaneously, or, find the values only in column B.
doc compare columns 1.1 1

doc sampleClick to download the sample file

2.1 Compare two columns cells by cells and display comparing results in another column (using formula)


Here you can use the formula which is combined with IF and COUNTIF function to compare two columns and find the values that are in column B but not in column C.

=IF(COUNTIF($C$2:$C$8, $B2)=0, "No in C", "Yes in C")

 doc compare columns 1.1 1

Press Enter key and drag autofill handle over to cell D8.
doc compare columns 1.1 1

Remark

1. This formula compares two columns without case sensitive.

2. You can change the description “No in C” and “Yes in C” to others.

3. If you want to compare two entire columns, change the fixed range $C$2:$C$8 to $C:$C.

2.2 Compare and highlight duplicate or unique data (using Conditional Formatting)


The Conditional Formatting feature in Excel is powerful, here you can use it to compare two columns cells by cells and then highlight the differences or matches as you need.

Highlight all duplicates or unique values in two columns

1. Select two columns that you will compare with, then click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
doc compare columns 1.1 1

2. In the popping Duplicate Values dialog, choose a highlighting format you need from the drop-down list of values with.
doc compare columns 1.1 1

3. Click OK. Then the duplicates in two columns have been highlighted.
doc compare columns 1.1 1

Remark

If you want to highlight the unique values (the differences) in two columns, also click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to display the Duplicate Values dialog, change the Duplicate to Unique in the left drop-down list, then choose another format from the values with drop-down list, click OK.
doc compare columns 1.1 1

The unique values will be highlighted.
doc compare columns 1.1 1

Find and highlight the values in column B if it is also in column C

If you want to highlight the values in column B that are also in column C, the Conditional Formatting feature also can do you a favor.

1. Select the column B2:B8, click Home > Conditional Formatting > New Rule.
doc compare columns 1.1 1

2. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format from the Select a Rule Type section, then type =COUNTIF($C$2:$C$8, $B2)>0 into the textbox of Format values where this formula is true.
doc compare columns 1.1 1

3. click Format to go to the Format Cells dialog, under Fill tab, choose one color to highlight the matches.

You can use other formats to highlight the values in Font, Number, Border tabs.
doc compare columns 1.1 1

4. Click OK > OK. Then the values in column B that also exist in column C have been highlighted with specified color.
doc compare columns 1.1 1

If you want to highlight the values that only in column B but not in column C, repeat above steps, but change the formula in step 2 to =COUNTIF($C$2:$C$8, $B2)=0 in the New Formatting Rule dialog.
doc compare columns 1.1 1

Then choose another color to format the values.
doc compare columns 1.1 1

Remark

Here the Conditional Formatting compares two columns without case insensitive.

2.3 Compare and select or highlight duplicate or unique data (using a handy tool)


Sometimes, after comparing two columns, you may take other actions on the matches or difference, such as selection, deletion, copy and so on. In this case, a handy tool – Select Same & Different Cells of Kutools for Excel can directly select the matches or difference for better doing next operation, also can directly highlight the values.
doc compare columns 1.1 1

After free installing Kutools for Excel, please do as below:

1. Click Kutools > Select > Select Same & Different Cells.
doc compare columns 1.1 1

2. Then in the Select Same & Different Cells dialog, please do as below:

1) In the Find values in and According to sections, choose two columns that you want to compare with;

2) Choose Each row option;

3) Choose Same values or Different Values as you need;

4) If you want to highlight the values, choose the color you need.
doc compare columns 1.1 1

3. Click Ok, a dialog pops out to remind you the number of values that have been found, click OK to close the dialog. And at the same time, the values have been selected, now you can delete or copy or do other operations.
doc compare columns 1.1 1

If you check the Fill backcolor and Fill font color checkboxes, the result is shown as this:
doc compare columns 1.1 1

Remark

If you want to compare with case sensitive, check the Case sensitive option.

This tool supports to compare two columns in different worksheets.

2.4 Compare two columns and list exact duplicates in another column (using VBA code)


If you want to list the matching values in another column after comparing two columns cell by cell, here the below macro code can help you.

1. Enable the sheet that you want to compare two columns, then press Alt + F11 keys to display the Microsoft Visual Basic for Applications window.

2. Click Insert > Module in the Microsoft Visual Basic for Applications window.
doc compare columns 1.1 1

3. Then copy and paste below code to the new blank module script.

VBA: List duplicates in beside column after comparing two columns

Sub ExtendOffice_FindMatches()
'UpdatebyKutools20201019
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range

Dim xIntSR, xIntER, xIntSC, xIntEC As Integer

On Error Resume Next

SRg:

Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)

If xRgC1 Is Nothing Then Exit Sub

If xRgC1.Columns.Count <> 1 Then

    MsgBox "Please select single column"

    GoTo SRg

End If

SsRg:

Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)

If xRgC2 Is Nothing Then Exit Sub

If xRgC2.Columns.Count <> 1 Then

    MsgBox "Please select single column"

    GoTo SsRg

End If

Set xWs = xRg.Worksheet

 

For Each xRgF1 In xRgC1

    For Each xRgF2 In xRgC2

        If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value

    Next xRgF2

Next xRgF1

End Sub

doc compare columns 1.1 1

4. Press F5 key to run the code, there are two dialogs popping out one by one for you to select two columns separately.

Note: Please select the left column firstly, then select the right column in the second dialog, or the duplicates will replace original data in the second column.
doc compare columns 1.1 1doc compare columns 1.1 1

5. Click OK > OK, then the matches have been automatically listed in the right column of the two columns.
doc compare columns 1.1 1

Remark

The VBA code compares two columns with case sensitive.

2.5 Compare two columns and highlight duplicates (using VBA code)


If you want to compare two columns cell by cell and then highlight the matches, you can try below code.

1. Enable the sheet that you want to compare two columns, then press Alt + F11 keys to display the Microsoft Visual Basic for Applications window.

2. Click Insert > Module in the Microsoft Visual Basic for Applications window.
doc compare columns 1.1 1

3. Copy below macro code and paste them in the new blank Module script.

VBA: Compare two columns and highlight duplicates

Sub ExtendOffice_CompareTwoRanges()
'UpdatebyKutools20201019

Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range

SRg:

Set xRgC1 = Application.InputBox("Select the column you want compare according to", "Kutools for Excel", , , , , , 8)

If xRgC1 Is Nothing Then Exit Sub

If xRgC1.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SRg

End If

SsRg:

Set xRgC2 = Application.InputBox("Select the column you want to highlight duplicates in:", "Kutools for Excel", , , , , , 8)

If xRgC2 Is Nothing Then Exit Sub

If xRgC2.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SsRg

End If

 

    For Each xRgF1 In xRgC1

        For Each xRgF2 In xRgC2

            If xRgF1.Value = xRgF2.Value Then

               xRgF2.Interior.ColorIndex = 38 '(you can change the color index as you need)

            End If

        Next

    Next

End Sub

doc compare columns 1.1 1

4. Press F5 key to run the code. In the first popping out dialog, select the column that you want to compare duplicate values according to.
doc compare columns 1.1 1

5. Click OK. In the second dialog, select the column you want to highlight duplicate values in.
doc compare columns 1.1 1

6. Click OK. Now the duplicates in the second column have been highlighted with a background color after comparing with first column.
doc compare columns 1.1 1

Remark

1. The code compares columns with case sensitive.

2. You can change the highlighting color based on your own need by changing the color index in the code, the Color index reference:
doc compare columns 1.1 1


3. Compare multiple columns in the same row

Sometimes, you may want to compare more than two columns in the same row, such as the data set as below screenshot shown. Here in this section, it lists different methods on comparing multiple columns.
doc compare columns 1.1 1

doc sampleClick to download the sample file

3.1 Find full matches in all cells in the same row (using IF formula)


To find full matches across columns in the same row, the below IF formula can help you.

=IF(AND(B2=C2, B2=D2), "Full match", "Not")

 doc compare columns 1.1 1

If the cells in the same row match each other, “Full match” is displayed, or “Not” is displayed.

Press Enter key to get the first comparing result, then drag auto fill handle over to cell E7.
doc compare columns 1.1 1

Remark

1. The formula compares columns without case sensitive.

2. If you need to compare more or equal to three columns, you can use below formula:

=IF(COUNTIF($B2:$D2, $B2)=3, "Full match", "Not")

 doc compare columns 1.1 1

In the formula, 3 is the number of columns, you can change it to meet your need.

3.2 Find matched in any two cells in the same row (using IF formula)


Sometimes, you want to find out if any two columns in the same row are matched, you can use below IF formula.

=IF(OR(B2=C2, C2=D2, B2=D2), "Match", "Not")

 doc compare columns 1.1 1

In this formula, you need to compare any one pair of cells in the same row. “Match” indicates that there are any two cells matched, if there are no cells matching, the formula display “Not”, you can change the texts as you need.

Press Enter key, and drag fill handle over to cell E7.
doc compare columns 1.1 1

Remark

1. This formula does not support case insensitive.

2. If there are many columns needed to compare, to compare any one pair of cells in the formula may be too long. In this case, you can consider to use below formula which combines the IF and COUNTIF functions.

=IF(COUNTIF(C11:E11,B11)+COUNTIF(D11:E11,C11)+(D11=E11)=0,"Not","Match")

 doc compare columns 1.1 1

3.3 Find full matches in all cells in the same row and highlight (using Conditional Formatting)


If you want to highlight the rows that all cells are matching with each other, you can use the Conditional Formatting feature in Excel.

1. Select the range that you use, then click Home > Conditional Formatting > New Rule.
doc compare columns 1.1 1

2. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format from the Select a Rule Type section, then you can use one of below formulas in the Format values where this formula is true textbox.

=AND($B2=$C2, $B2=$D2)

Or

=COUNTIF($B2:$D2, $B2)=3

 doc compare columns 1.1 1

Note: If the number of columns is more than three, supposing is 5, the formulas should be changed to:

=AND($B2=$C2, $B2=$D2, $B2=$E2, $B2=$F2)

Or

=COUNTIF($B2:$F2, $B2)=5

3. Click Format button to go to the Format Cells dialog, then choose one fill color or other cell formatting to outstand the rows.
doc compare columns 1.1 1

4. Click OK > OK, now only rows within which all cells are matching will be highlighted.
doc compare columns 1.1 1

Remark

Above formulas do not support case sensitive.

3.4 Compare multiple columns and highlight row differences


If you want to highlight the row differences, which means it compares column cells one by one, and find the different cells according to the first column, you can use Excel built-in feature-Go To Special.

1. Select the range that you want to highlight row differences, and click Home > Find & Select > Go To Special.
doc compare columns 1.1 1

2. In the popping Go To Special dialog, check Row differences option.
doc compare columns 1.1 1

3. Click OK. Now the row differences have been selected.
doc compare columns 1.1 1

4. Now keep the cells selected, click Home > Fill Color to select one color from the drop-down menu.
doc compare columns 1.1 1

Remark

This method compares cells without case sensitive.


4. Compare two columns and find missing data points

Supposing there are two columns, column B is longer, and column C is shorter as below screenshot shown. Compared with column B, how to find out the missing data in column C?
doc compare columns 1.1 1

doc sampleClick to download the sample file

4.1 Compare and find the missing data points (using VLOOKUP or MATCH formula)


If you only want to identify which data is missing after comparing two columns, you can use one of below formulas:

=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))

Or

=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))

 doc compare columns 1.1 1

Press Enter key, then drag auto fill handle over cell D10. Now if the data is in both column B and column C, the formula returns FALSE, if the data is only in column B but misses in column C, the formula returns TRUE.
doc compare columns 1.1 1

Remark

Above two formulas compare data without case sensitive.

4.2 Compare two columns and list missing data in below (using INDEX formula)


If you want to list the missing data below the shorter column after comparing two columns, the INDEX array formula can help you.

In the below cell of the shorter column, supposing cell C7, type below formula:

=INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0))

 doc compare columns 1.1 1

Press Shift + Ctrl + Enter key to get the first missing data, then drag auto fill handle down until it returns the error value #N/A.
doc compare columns 1.1 1

Then you can remove the error value, and all missing data has been listed below the shorter column.

Remark

This formula compares cells without case sensitive.

4.3 Compare and find the missing data points and list them in another column (using a handy tool)


If you want to do some follow-up operation on the missing data after comparing two columns, such as listing the missing data in another column or supplementing the missing data below the shorter column, you can try a handy tool-Select Same & Different Cells of Kutools for Excel.

1. Click Kutools > Select > Select Same & Different Cells.
doc compare columns 1.1 1

2. In the Select Same & Different Cells dialog, do as below:

1) In Find values in section, choose the longer column that contains the complete list.
In According to section, choose the shorter column which misses some data.

2) Choose Each row option.

3) Choose Different Values option.
doc compare columns 1.1 1

3. Click Ok, a dialog pops out to remind you the number of missing data, click OK to close it. Then the missing data has been selected.
doc compare columns 1.1 1

Now you can press Ctrl + C keys to copy the selected missing data, and paste them by pressing Ctrl + V keys below the shorter column or another new column as you need.
doc compare columns 1.1 1doc compare columns 1.1 1

Remark

Ticking the Case insensitive option in the Select Same & Different Cells dialog will compare two columns with case sensitive.

4.4 Compare two columns and list missing data in below (using VBA)


Here is a macro code that can fill the missing data below the two columns.

1. Open the sheet that you use, press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module to create a new module to place below VBA code into.
doc compare columns 1.1 1

VBA: Compare two columns and fill missing data

Sub ExtendOffice_PullUniques()
'UpdatebyKutools20201021

Dim xRg, xRgC1, xRgC2, xFRg1, xFRg2 As Range

Dim xIntR, xIntSR, xIntER, xIntSC, xIntEC As Integer

Dim xWs As Worksheet

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns as a range"

    GoTo SRg

End If

Set xWs = xRg.Worksheet

 

xIntSC = xRg.Column

xIntEC = xRg.Columns.Count + xIntSC - 1

xIntSR = xRg.Row

xIntER = xRg.Rows.Count + xIntSR - 1

 

Set xRg = xRg.Columns

Set xRgC1 = xWs.Range(xWs.Cells(xIntSR, xIntSC), xWs.Cells(xIntER, xIntSC))

Set xRgC2 = xWs.Range(xWs.Cells(xIntSR, xIntEC), xWs.Cells(xIntER, xIntEC))

xIntR = 1

For Each xFRg In xRgC1

    If WorksheetFunction.CountIf(xRgC2, xFRg.Value) = 0 Then

        xWs.Cells(xIntER, xIntEC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

xIntR = 1

For Each xFRg In xRgC2

    If WorksheetFunction.CountIf(xRgC1, xFRg) = 0 Then

        xWs.Cells(xIntER, xIntSC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

End Sub

doc compare columns 1.1 1

3. Then press F5 key to run the code, a dialog pops out for selecting the two comparing columns.
doc compare columns 1.1 1

4. Click OK. Now the missing data has been listed below the two columns.
doc compare columns 1.1 1

Remark

The code compares cells without case sensitive.


5. Compare two columns (dates) if greater than or less than

If there are two columns of dates as below screenshot shown, you may want to compare which date is later in the same row.
doc compare columns 1.1 1

doc sampleClick to download the sample file

5.1 Compare two columns if greater than or less than (using formula)


You can use the simple formula to quickly find whether the date 1 is later than date 2 in each row.

=IF(B2>C2,"Yes","No")

 doc compare columns 1.1 1

Press Enter key to get the first compared result, then drag auto fill handle over to cell C6 to get all results.
doc compare columns 1.1 1

Remark

1. In Excel, dates are stored as number series, they are numbers in fact. Therefore, you apply the formula to compare dates directly.

2. If you want to compare if date 1 is earlier than date 2 in each row, change the symbol > to < in the formula.

5.2 Compare two columns if greater than or less than then format (using Conditional Formatting)


If you want to highlight the cells in column Date 1 if are greater than Date 2, you can use the Conditional Formatting feature in Excel.

1. Select the dates in column B (Date1), then click Home > Conditional Formatting > New Rule.
doc compare columns 1.1 1

2. In the New Formatting Rule dialog, select Use a formula to determine which cells to format in the Select a Rule Type section, then type formula =$B2>$C2 into the textbox of Format values where this formula is true.
doc compare columns 1.1 1

If you want to highlight the cells in column B that are smaller than those in column C, use the formula =$B2<$C2.

3. Click Format button to open the Format Cells dialog, then choose the format type as you need.
doc compare columns 1.1 1

4. Click OK > OK. Then the cells in column Date1 which are greater than those in column Date2 have been highlighted.
doc compare columns 1.1 1


6. Compare two columns and return value in another column

For example, there are two tables, now you want to compare column B and column E, then find the relative prices from the column C and return them in column F.
doc compare columns 1.1 1

doc sampleClick to download the sample file

6.1 Pull the exact matching data (using formula or a handy tool)


Here it introduces some helpful formulas and a tool to solve this job.

Formula method

In the cell F2 (the cell you want to place the returned value in), use one of below formulas:

=VLOOKUP(E2,$B$2:$C$8,2,0)

Or

=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)

 doc compare columns 1.1 1

Press Enter key, and the first value has been found. Then drag auto fill handle down to cell F6, all values have been extracted.
doc compare columns 1.1 1

Remark

1. The formulas do not support case sensitive.

2. The number 2 in the formula indicates that you find the matching values in the second column of the table array.

3. If the formulas cannot find the relative value, it returns error value #N/A.
doc compare columns 1.1 1

A handy tool method

If you are confused with formulas, you can try the handy tool – Formula Helper of Kutools for Excel, which contains multiple formulas for solving most of problems in Excel. With it, you only need to select the range but not need to remember how the formulas use.

After free installing Kutools for Excel, please do as below:

1. Select the cell F2 (the cell you want to place the return value), and click Kutools > Formula Helper > Lookup & Reference > Look for a value in list.
doc compare columns 1.1 1

2. In the Formulas Helper dialog, in right Arguments input section, select the table array range, lookup value and specify which column you want to look for the return value.

Tip: Remember to change the absolute reference in the Lookup_value box to relative, or you only can find the first one value.
doc compare columns 1.1 1

3. Click Ok, the first value has been found, then drag auto fill handle down to cell F6.
doc compare columns 1.1 1

6.2 Pull the partial matching data (using formula)


If there are some minor difference between the two compared columns as below screenshot shown, the above methods cannot work.
doc compare columns 1.1 1

Please choose one of below formulas to solve this job:

=VLOOKUP("*"&E2&"*",$B$2:$C$8,2,0)

Or

=INDEX($B$2:$C$8,MATCH("*"&E2&"*",$B$2:$B$8,0),2)

 doc compare columns 1.1 1

Press Enter key, then drag auto fill handle down to the cell F5, all values have been found.
doc compare columns 1.1 1

Remark

1. The formulas do not support case sensitive.

2. The number 2 in the formula indicates that you find the return values in the second column of the table array.

3. If the formulas cannot find the relative value, it returns error value #N/A.

4. * in the formula is a wildcard which is used to indicates any character or strings.


7. Compare two columns and count matches or difference

Below data set is an example for comparing and counting matches or difference.
doc compare columns 1.1 1

doc sampleClick to download the sample file

7.1 Compare two columns and count matches (using SUMPRODUCT formula)


The SUMPRODUCT formula can quickly count the matches in two columns.

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B8,C2:C8,0))))

 doc compare columns 1.1 1

Press Enter key to get the result.
doc compare columns 1.1 1

Remark

The formula counts cells without case sensitive.

7.2 Compare two columns and count matches or differences (using a handy tool)


If you want to count matches or differences between two columns, the handy tool – Select Same & Different Cells.

After free installing Kutools for Excel, please do as below:

1. Click Kutools > Select > Select Same & Different Cells.
doc compare columns 1.1 1

2. In the Select Same & Difference Cells dialog, choose the two columns ranges in Find values in and According to sections separately, then choose Each row, and choose Same values or Different Values option as you need.
doc compare columns 1.1 1

3. Click Ok. A dialog pops out to tell you how many matching or different cells are selected.

Matching cells
doc compare columns 1.1 1

Different cells
doc compare columns 1.1 1


8. Compare two columns with wildcard

Supposing here is a list of data in column B, and you want to count the cells which contain ”Apple” or “Candy” in column D as below screenshot shown:
doc compare columns 1.1 1

doc sampleClick to download the sample file

To count if a cell contains one or more values, you can use a formula with wildcards to solve this problem.

=SUM(COUNTIF(B2,"*" & $D$2:$D$3 & "*"))

 doc compare columns 1.1 1

Press Shift + Ctrl + Enter key to get the first checking, then drag autofill handle down to cell F8.
doc compare columns 1.1 1

Now if the related cell contains one or more values in column D, the result displays the number greater than 0, if it does not contain any value in the column D, it returns 0.

If you want to count the total number of cells that contain the values in column D, use the formula in the below of cell F8:
doc compare columns 1.1 1

Remark

1. Also you can use the formula to count if the cell contains values in another column

=SUMPRODUCT(COUNTIF(B2,"*" &$D$2:$D$3& "*"))

This formula only needs to press Enter key and then drag auto fill handle.

2. In the formulas, * is the wildcard which indicates any character or string.


9. Delete differences or matches

If you want to delete the differences or matches after comparing two columns as below screenshot shown:
doc compare columns 1.1 1

doc sampleClick to download the sample file

You can use the formula to find the difference or matches firstly:

=IF(ISERROR(MATCH(B2,$D$2:$D$8,0)),"Difference","Match")

doc compare columns 1.1 1

Press Enter key, and drag auto fill handle down to the cell D8.
doc compare columns 1.1 1

Then apply the Filter feature in Excel to filter out the differences or matches value.

Select the formula column, then click Data > Filter.
doc compare columns 1.1 1

Then the filter button appears in the column C, click at the filter button to expand the drop-down menu, choose the Difference or Match as you need. Then click OK to finish.
doc compare columns 1.1 1

Now only the differences or matches have been filtered out. You can select them and press Delete key to remove them.
doc compare columns 1.1 1doc compare columns 1.1 1

Now click Data > Filter again to clear the filter.
doc compare columns 1.1 1

Remove the formula column if you do not need it any more.
doc compare columns 1.1 1


10. Compare two columns and calculate percentage change between

There are two columns, one contains the original prices, and the other contains the sale prices. Now this part introduces a formula to compare these two columns and then calculate the percentage change between the two columns.
doc compare columns 1.1 1

doc sampleClick to download the sample file

You can use below formula to figure out the percentage change between the two prices in the same row.

=(C2-B2)/B2

 doc compare columns 1.1 1

Press Enter key to get a number, then drag auto fill handle down to cell D7.
doc compare columns 1.1 1

Then format the formula result as percentage. Select the results, click Home tab and go to Number group to click Percent Style.
doc compare columns 1.1 1

The formula results have been formatted as percentages.
doc compare columns 1.1 1


11. Compare two ranges

Now you know how to compare two columns after reading above methods. However, in some cases, you may want to compare two ranges (two series with multiple columns) You can use above methods (the formulas or conditional formatting) to compare them column by column, but here introduces a handy tool – Kutools for Excel can solve this job in different cases quickly with formula free.

doc sampleClick to download the sample file

11.1 Compare two ranges by cell


Here are two ranges needed to be compared by cells, you can use the Select Same & Different Cells utility of Kutools for Excel to handle it.
doc compare columns 1.1 1

After free installing Kutools for Excel, please do as below:

1. Click Kutools > Select > Select Same & Different Cells.
doc compare columns 1.1 1

2. In the popping Select Same & Different Cells dialog, do as below:

1) In the Find values in section, choose the range that you want to find out the matches or differences after comparing two ranges.

2) In the According to section, choose the other range used to compared range.

3) In Based on section, choose Single cell.

4) Then in the Find section, choose the type of cells that you want to select or highlight.

5) In the Processing of results section, you can highlight the cells by fill background color or the font color, if you do not need to highlight, do not tick the checkboxes.
doc compare columns 1.1 1

3. Click Ok. A dialog pops out and reminds how many cells/rows have been selected, click OK to close it. Now the cells that are different with those in the other range have been selected and highlighted.
doc compare columns 1.1 1

Highlighting the same values
doc compare columns 1.1 1

Remark

If you want to compare two ranges by row, you also can apply the Select Same & Different Cells feature, but in this case, choose the Each row option.
doc compare columns 1.1 1doc compare columns 1.1 1

11.2 Compare two ranges if data in the same order


If you want to compare two ranges by row, the Compare Cells feature of Kutools for Excel can help you.

After free installing Kutools for Excel, please do as below:

Supposing, the range F2:H7 is a model, now you want to find if the data in range B2:D7 is in the right order according to the range F2:H7.
doc compare columns 1.1 1

1. Click Kutools > Compare Cells.
doc compare columns 1.1 1

2. In the Compare Cells dialog, set as below:

1) Choose the two ranges into the Find values in and According to boxes separately.

2) Choose the cell type you want to highlight in the Find section.

3) Choose the highlighting type in the Processing of results section.
doc compare columns 1.1 1

3. Click Ok. A dialog pops out and reminds how many cells have been selected, click OK to close it. Now the cells that are different with those in the other range have been selected and highlighted.
doc compare columns 1.1 1


You may also be interested in

Compare Dates If Greater Than Another Date In Excel
Provides formulas and a handy tool for comparing two dates and find if the dates are greater than another.

Compare Two Sheets Side by Side
In this article, it can help you quickly compare two sheets to find the matches and differences.

Vlookup To Compare Two Lists In Separated Worksheets
Here will introduce the VLOOKUP function to compare two lists in different two sheets.

Find, Highlight, Filter, Count, Delete Duplicates In Excel
It is a long and detailed tutorial that provides many different methods on finding, highlighting, filtering, counting and deleting duplicates.



  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.