How to use excel to compare two sets of data

1
Índice Show
Highlight the first cell of a blank column. When comparing two columns in a worksheet, you'll be outputting your results onto a blank column. Make sure you are starting on the same row as the two columns you're comparing.
- For example, if the two columns you want to compare start on A2 and B2, highlight C2.
2
Type the comparison formula for the first row. Type the following formula, which will compare A2 and B2. Change the cell values if your columns start on different cells:
- =IF(A2=B2,"Match","No match")
Advertisement
3
Double-click the Fill box in the bottom corner of the cell. This will apply the formula to the rest of the cells in the column, automatically adjusting the values to match.
4
Look for Match and No match. These will indicate whether the contents of the two cells had matching data. This will work for strings, dates, numbers, and times. Note that case is not taken into consideration ("RED" and "red" will match).[1]

1
Open the first workbook you want to compare. You can use the View Side by Side feature in Excel to view two different Excel files on the screen at the same time. This has the added benefit of scrolling both sheets at once.
2
Open the second workbook. You should now have two instances of Excel open on your computer.
3
Click the View tab on either window.
4
Click View Side by Side. You'll find this in the Window section of the ribbon. Both workbooks will appear in on the screen, oriented horizontally.
5

Click Arrange All to change the orientation.
6
Click Vertical and then OK. The workbooks will change so that one is on the left and the other is on the right.
7
Scroll in one window to scroll in both. When Side by Side is enabled, scrolling will be synchronized between both windows. This will allow you to easily look for differences as you scroll through the spreadsheets.
- You can disable this feature by clicking the Synchronous Scrolling button in the View tab.

1
Open the workbook containing the two sheets you want to compare. To use this comparison formula, both sheets must be in the same workbook file.
2
Click the + button to create a new blank sheet. You'll see this at the bottom of the screen to the right of your open sheets.
3
Place your cursor in cell A1 on the new sheet.
4
Enter the comparison formula. Type or copy the following formula into A1 on your new sheet:
- =IF(Sheet1!A1<> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")
5
Click and drag the Fill box in the corner of the cell.
6
Drag the Fill box down. Drag it down as far down as the first two sheets go. For example, if your spreadsheets go down to Row 27, drag the Fill box down to that row.
7

Drag the Fill box right. After dragging it down, drag it to the right to cover the original sheets. For example, if your spreadsheets go to Column Q, drag the Fill box to that column.
8
Look for differences in the cells that don't match. After dragging the Fill box across the new sheet, you'll see cells fill wherever differences between the sheets were found. The cell will display the value of the cell in the first sheet and the value of the same cell in the second sheet.
- For example, A1 in Sheet1 is "Apples," and A1 in Sheet2 is "Oranges." A1 in Sheet3 will display "Sheet1:Apples vs Sheet2:Oranges" when using this comparison formula.[2]

Add New Question

Question

How do I find duplicate values in a row?

To find duplicate values simply use the Data --> Remove Duplicates feature and follow the prompts on the screen.
Question

How do I subtract two rows?

You have to select the 2 rows {shift} and then right click on the now highlighted rows. A box will pop up with a list of actions. You should click on 'delete rows'.

Ask a Question

This article was co-authored by wikiHow Staff. Our trained team of editors and researchers validate articles for accuracy and comprehensiveness. wikiHow's Content Management Team carefully monitors the work from our editorial staff to ensure that each article is backed by trusted research and meets our high quality standards. This article has been viewed 1,804,520 times.

Co-authors: 32

Updated: March 8, 2022

Categories: Microsoft Excel

Print
Send fan mail to authors

Thanks to all authors for creating a page that has been read 1,804,520 times.

Below are the six different methods used to compare two lists of a column in Excel for matches and differences.

Method 1: Compare Two Lists Using Equal Sign Operator
Method 2: Match Data by Using Row Difference Technique
Method 3: Match Row Difference by Using IF Condition
Method 4: Match Data Even If There is a Row Difference
Method 5: Highlight All the Matching Data using Conditional Formatting
Method 6: Partial Matching Technique

Now, let us discuss each of the methods in detail with an example: –

#1 Compare Two Lists Using Equal Sign Operator

We must follow the below steps to compare the two lists.

Immediately after the two columns, we must insert a new column called “Status” in the next column.
Now, we must put the formula in cell C2 as =A2=B2.
This formula tests whether cell A2 value is equal to cell B2. If both the cell values are matched, we will get the result as “TRUE” or “FALSE.”
We will drag the formula to cell C9 to determine the other values.

Wherever we have the same values in common rows, we get the result as “TRUE” or “FALSE.”

#2 Match Data by Using Row Difference Technique

You might not have used the “Row Difference” technique at your workplace. But today, we will show you how to use this technique to match data row by row.

Step 1: To highlight non-matching cells row by row, we must select the entire data first.

Step 3: Press the “F5” key to open this window. Now in the “Go To” window, press the “Special” tab.

Step 4: In the next window, we must go to the “Go To Special” and choose the “Row differences” option. Then, click on “OK.”

We will get the following result.

As we can see in the above window, it has selected the cells wherever there is a row difference. Therefore, we must fill in some colors to highlight the row difference values.

#3 Match Row Difference by Using IF Condition

How can we leave out the IF condition when we want to match data row by row. In the first example, we have got either “TRUE” or “FALSE.” But what if we need a different result instead of the default results of either “TRUE or FALSE.” Assume we need a result as “Matching” if there is no row difference and the result should be “Not Matching” if there is a row difference.

Step 1: First, we must open the IF condition in cell C2.

Step 3: We must enter the result criteria if the logical test is “TRUE.” In this scenario, the result criteria are “Matching,” If the row does not match, we need the result as “Not Matching.”

Step 4: Next, we need to apply the formula to get the result.

Step 5: We must drag the formula to cell C9 to determine the other values.

#4 Match Data Even If There is a Row Difference

The matching data on the row differences method may not work all the time; the value may be in other cells too. So we need to use different technologies in these scenarios.

Now, look at the below data.

In the above image, we have two lists of numbers. We need to compare list 2 with list 1. So let us use our favorite function VLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more.

So, if the data matches, we get the number; otherwise, we get the error value as #N/A.

Showing error values does not look good. So instead of showing the error, let us replace them with the word “Not Available.” For this, use the IFERROR function in excelThe IFERROR function in Excel checks a formula (or a cell) for errors and returns a specified value in place of the error.read more.

#5 Highlight All the Matching Data

If you are not a fan of excel formulasThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions.read more, do not worry. We can still match data without the formula. For example, using simple conditional formatting in excelConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more, we can highlight all the matching data of two lists.

Step 1: We must first select the data.

Step 2: Now, we must go to “Conditional Formatting” and choose “Highlight Cell Rules” >> “Duplicate Values.”

Step 3: As a result, we can see the “Duplicate Cell Values” formatting window.

Step 5: This will highlight all the matching data from the two lists.

Step 6: Just in the case, instead of highlighting all the matching data, if we want to highlight not matching data, then we can go to the “Duplicate Values” window and choose the option “Unique.”

As a result, it will highlight all the non-matching values, as shown below.

#6 Partial Matching Technique

We have seen the issue of not having full or the same data in two lists. For example, if the List 1 data has “ABC Pvt Ltd.“ In List 2, we have “ABC” only. In these cases, all our default formulas and tools are not recognized. Therefore, we need to employ the special character asterisk (*) to match partial values in these cases.

In List 1, we have the company name and revenue details. In List 2, we have company names but not exact values as we have in List 1. It is a tricky situation that we all have faced at our workplace.

In such cases, still, we can match data by using a special character asterisk (*).

We get the following result.

We will drag the formula to cell E9 to determine the other values.

The wildcard character asterisk (*) was used to represent any number of characters so that it will match the full character for the word “ABC” as “ABC Pvt Ltd.”

Things to Remember

The use of the above techniques for comparing two lists in Excel upon the data structure.
If the data ends not organized, row by row matching is not the best suited.
VLOOKUP is the often-used formula to match values.