How to Compare Two Columns in Excel to Find Differences

This is an interesting situation that often comes up. Namely, sometimes one needs to match data in two different columns or different sheets.

So, let’s get started with a simple example, to illustrate how to accomplish this.

Introduction

Biochemical researchers working in chemical or pharmaceutical laboratories, developing new drugs, have to order strains of disease causing bacteria, in order to test the effectiveness of the drugs they are developing. One can order strains from microbiological laboratories at some universities or from biotechnological companies. Antibiotic resistance due to bacteria mutating in ways that decrease the effectiveness of drugs is a serious global concern.

Read More: Data clean-up techniques in Excel: Matching text in a list

Disease-causing bacteria such as Streptococcus, Shigella, Campylobacter, Mycobacterium tuberculosis, Clostridium tetani and Chlamydia particularly are of great interest to the medicinal chemistry and scientific community.

Streptococcus infections can result in relatively mild throat infections but can also cause life-threatening infections of the blood. Shigella infections cause diarrhoea since the intestine is targeted. Campylobacter infections occur as a result of food poisoning and cause diarrhea. Campylobacter is one of the major causes of diarrhoeal diseases. Mycobacterium tuberculosis is responsible for TB infections, one of the oldest known diseases and still causes many deaths globally. Tetanus is caused by Clostridium tetani. Tetanus results in severe stiffening of the jaw muscles. Chlamydia trachomatis is a sexually transmitted infection (STI) caused by bacteria.

In our example, a hypothetical biochemist has contacted two different microbiology labs and noted in two different columns, the names of the disease-causing strains they sell. He intends to, where possible, buy the same strain from the two labs, in order to add extra validity to his experimental design.

The source data is shown below.

How to Compare Two Columns in Excel to Find Differences - Image 1

Using simple Conditional Formatting to find Duplicate or Matching Data

1) We will first use simple conditional formatting, in order to see the values that are the same (matching) in the two columns.

2) So, first things first, highlight the range A6: B9 as shown below.

How to Compare Two Columns in Excel to Find Differences - Image 2

3) Go to Home>Styles>Conditional Formatting as shown below.

How to Compare Two Columns in Excel to Find Differences - Image 3

4) Select Highlight Cells Rules, Duplicate Values.

How to Compare Two Columns in Excel to Find Differences - Image 4

5) Choose a Format in the Duplicate Values dialog box as shown below.

How to Compare Two Columns in Excel to Find Differences - Image 5

6) Click Ok, and the duplicate values i.e matching values are highlighted.

How to Compare Two Columns in Excel to Find Differences - Image 6

Using a Formula with the MATCH Function and Conditional Formatting to compare data from two columns

1) We now are going to use the MATCH Function with conditional formatting, in order to compare the data in column A, with the data in column B, our source data is the same as for the simple conditioning example.

Read More: How to Use Wildcards in Excel?

2) First things first, let’s clear the simple conditional formatting from the cells, by highlighting the range A6: B9 as shown below.

How to Compare Two Columns in Excel to Find Differences with formulas - Image 1

3) Go to Home>Styles>Conditional Formatting>Clear Rules>Clear Rules from Selected Cells.

How to Compare Two Columns in Excel to Find Differences with formulas - Image 2

How to Compare Two Columns in Excel to Find Differences with formulas - Image 3

4) Now we are going to compare the values in Column A with Column B and use the MATCH Function to see where there is a matching value using conditional formatting.

5) This time we highlight only A6: A9 as shown below.

How to Compare Two Columns in Excel to Find Differences with formulas - Image 4

6) Go to Home>Styles>Conditional Formatting>New Rule…. as shown below.

How to Compare Two Columns in Excel to Find Differences with formulas - Image 5

7) In the New Formatting Rule Dialog Box, select the Use a formula to determine which cells to format, and enter the following formula:

=MATCH(A6, $B$6: $B$9, 0)

How to Compare Two Columns in Excel to Find Differences with formulas - Image 6

8) Select the Format……. Button.

9) In the Format Cells dialog box, using the Fill Tab, select a Green Fill as shown below.

10) Select Ok and then OK again and in this case, the values in column A that have matching values in column B, are highlighted only, as shown below.

How to compare two columns in different excel sheets using conditional formatting

In this case, we have the data from the first lab stored on the first sheet called LabOne, and the data from the second lab on the sheet called LabTwo. The source data is shown below.

how to compare two columns in different excel sheets - Image 1

how to compare two columns in different excel sheets - Image 2

1) On the sheet called LabOne, highlight the range A6: A9 as shown below.

how to compare two columns in different excel sheets - Image 3

2) Go to Home>Styles>Conditional Formatting>New Rule and Use a formula to determine which cells to format and enter the following formula as shown below.

=COUNTIF(LabTwo!$A$6:$A$9,A6)=1

Read More: The Main Differences between a Bar Graph and a Histogram

Choose a Blue Fill in the Format option.

how to compare two columns in different excel sheets - Image 4

3) Click Ok.

how to compare two columns in different excel sheets - Image 5

In this case, he COUNTIF Formula is evaluating the range on the second sheet, and if there is a single match as designated by the =1 in the formula, applying the formatting to the match in the first sheet.

And there you have it.

Download Working Files

MatchingDatainTwoColumns

MatchingDatainTwoSheets

Conclusion

Simple Conditional formatting and using conditional formatting in conjunction with a formula provides an effective way to locate matching data either in columns on the same sheet or on different sheets. Since once is using conditional formatting, the visual emphasis is placed on matching data.

Please feel free to tell comment and tell us if you use conditional formatting to locate matching data.

Useful Links

Read more about Antibiotic resistance

Streptococcal infections

Shigella infections

Campylobacter infections

Mycobacterium tuberculosis

Clostridium tetani

Chlamydia trachomatis

Find Duplicates in Excel sheets

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

You may also like...

3 Responses

  1. Guillermo.J.Wainselboim@IRS.Gov' Guillermo J Wainselboim says:

    Good day

    My apologies for the inconvenience

    The files are currently not available for review/download

    Thank you and have a nice day

  2. haivh.neu@gmail.com' Hai says:

    Working Files are not found.
    Could you resend alive links?
    Tanks

  3. Kawser says:

    Thanks for notifying me. It was a technical mistake. I’ve uploaded them and you can download now. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.