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

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.

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.
2. Choose “EXACT” from the drop-down menu. The “Exact” formula works on numbers as well.
3. A window called “Function Arguments” appears. You need to specify which cells you want to compare.
   
4. To compare cells “A1” and “B1,” type “A1” in the “Text1” box and then “B1” in the “Text2” box, then click “OK.”
   
5. Since the numbers from cells “A1” and “B1” don’t match, Excel returns a “FALSE” value and stores the result in cell “C1.”
   
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.
   
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.
3. Excel also allows you to check for duplicates and differences simultaneously by typing the following formula: =IF (A1=B1, “No Match”, “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.