How to Compare Two Excel Sheets and Extract Common Data

Comparing two Excel spreadsheets manually can be a tedious and error-prone process. Fortunately, Excel offers powerful features to automate this task and extract common data efficiently. This guide provides a step-by-step approach to comparing two Excel sheets and identifying matching information using formulas and conditional formatting.

Comparing Data with the MATCH Function

The MATCH function is a powerful tool for locating the position of a specific value within a range. When comparing two sheets, it can identify if a value in one sheet exists in another.

1. Prepare your worksheets: Ensure both sheets are within the same Excel workbook.

2. Create a helper column: In the sheet where you want to display the results, insert a new column.

3. Apply the MATCH formula: In the first cell of the helper column, enter the following formula: =MATCH(B2,Sheet1!B2:B6,0). Replace B2 with the cell containing the value you want to search for, Sheet1!B2:B6 with the range in the other sheet where you want to search, and 0 to specify an exact match.

4. Interpret the results: If a match is found, the formula returns the position of the match within the specified range. If no match is found, the formula returns #N/A.

Highlighting Matching Data with Conditional Formatting

Conditional formatting allows you to visually highlight cells that meet specific criteria, making it easy to identify matching data between sheets.

1. Select a cell: Choose a cell in the sheet where you want to apply conditional formatting.

2. Enter the formula: Use the following formula in conditional formatting: =IF(ISNUMBER(MATCH(B2,Sheet1!$B$2:$B$6,0))=TRUE,"IS ON THE LIST","NOT MATCH"). This formula checks if the MATCH function returns a number (indicating a match) and displays “IS ON THE LIST” if true, otherwise “NOT MATCH”.

3. Apply conditional formatting: Go to Home > Conditional Formatting > New Rule… > Use a formula to determine which cells to format. Paste the formula into the formula input box.

4. Format the cells: Click on the Format… button and choose a formatting style to highlight the matching cells (e.g., change fill color). Click OK twice to apply the formatting.

5. Review the results: The cells containing matching data will be highlighted according to the chosen formatting.

Conclusion

By leveraging the MATCH function and conditional formatting, you can efficiently compare two Excel sheets, extract common data, and highlight matching information for easier analysis. These techniques can significantly improve your productivity when working with large datasets and streamline your workflow.