How to Tell if Two Cells in Excel Contain the Same Value

YouTube video

Many companies still use Excel as it allows them to store different types of data, such as tax records and business contacts. Since many things are often done manually in Excel, there is a potential risk of storing duplicate information. It’s usually not an intentional act; it just happens when entering data with a typo, such as a name, account number, amount, or even an address.

Typos or misspellings often lead to new entries when existing data already exists. For instance, your records may have data for John Doe, Jon Dow, or Jon Dow, even though they are the same person.

Making these kinds of mistakes can sometimes lead to grave consequences. That’s precisely why accuracy is so important when working in spreadsheets. Thankfully, Excel includes features and tools that help everyday users check their data and correct errors.

This article shows you how to check whether two or more Excel cells have the same value.

How to Check for Duplicate Cells using the Exact Function

If you want to check whether or not two or more cells have the same value but don’t want to go through the whole table manually, you can make Excel do the work for you. Excel has a built-in function called “Exact.” This function works for both numbers and text.

How to Use the Exact Excel Function to Check for Duplicates

Let’s say you are working with the worksheet shown in the picture below. As you can see, it isn’t easy to determine whether the numbers from column A are the same as any numbers found in column B. Of course, it’s easier than comparing different cells from each column, but you get the idea.

Two Cells in Excel Contain the Same Value

To ensure that cells from column “A” don’t have a duplicate entry in the corresponding column “B” cells, use the “Exact” function, such as checking cells “A1” and “B1” by adding the formula to cell “C1.”

  1. Click on the “Formulas” tab, then select the “Text” button. Excel Formulas Tab
  2. Choose “EXACT” from the drop-down menu. The “Exact” formula works on numbers as well. Excel Formulas Tab - Text Dropdown Menu
  3. A window called “Function Arguments” appears. You need to specify which cells you want to compare.
    Excel Text Function Arguments Window
  4. To compare cells “A1” and “B1,” type “A1” in the “Text1” box and then “B1” in the “Text2” box, then click “OK.”
    Excel Text Function Arguments Window 2
  5. Since the numbers from cells “A1” and “B1” don’t match, Excel returns a “FALSE” value and stores the result in cell “C1.”
    Excel Text Function Results
  6. To check all cells, drag the “fill handle” (small square in the bottom-right corner of the cell) down the column as far as needed. This action copies and applies an adjusted formula to all other rows.
  7. After copying the formula down the column, you should notice that the “FALSE” value appears for non-duplicates in each row, and “TRUE” appears for identical ones.

How to Check Excel for Duplicate Cells using the IF Function

Another function that allows you to check two or more cells for duplicates is the “IF” function. It compares cells from one column, row by row. Let’s use the same two columns (A1 and B1) as in the previous example.

To use the “IF” function correctly, remember its syntax.

  1. In cell “C1,” type the following formula: =IF(A1=B1, “Match”, “”), and you’ll see “Match” next to the cells that have duplicate entries.
    Microsoft Excel If Function
  2. To check for differences, you should type the following formula: =IF(A1<>B1, “No match”,” “). Again, use the fill handle by dragging it down to apply the function to all cells.Excel If Function Results - No match
  3. Excel also allows you to check for duplicates and differences simultaneously by typing the following formula: =IF (A1=B1, “No Match”, “Match“).Excel If Function Results - No match and Match

In closing, checking for duplicates in Excel is relatively easy when you implement formulas. The human eyes sometimes overlook identical cells, especially when there are hundreds of them to compare. Also, using formulas builds on efficiency and reduces fatigue, not to mention eye strain. These are the easiest methods to find out whether two cells have the same value in Excel.

Of course, there are times when duplicate cells are valid entries, such as dollar amounts for more than one account, two different family members with the same name, or even repeat transactions. Therefore, check the entries before taking action on them, and make a copy first to prevent data loss if something goes wrong.

Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.

Todays Highlights
How to See Google Search History
how to download photos from google photos