How Do I Compare Two Excel Sheets to Highlight Duplicates?
If you’re working with multiple Excel files, it can be helpful to compare one worksheet against the other. This can help you spot inconsistencies, outdated values, broken formulas, and other issues with your data. You can use the built-in tools in Excel to perform a side-by-side comparison, or you can use third-party apps and add-ins.
Using Conditional Formatting
If your spreadsheets are within the same workbook, you can easily compare sheets by selecting the data group you want to examine and then applying a conditional formatting rule that highlights duplicate values. To do this, select the data group and then go to the Home tab, in the Styles group, click Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the Create New Rule window, select the “Use a formula to determine which cells to format” option and enter the following formula: =COUNTIFS(Animals,$A1:$A10,Continents,$B1:$C10,Countries)
When you click Create Rule, the duplicate values will be highlighted in red. You can also select the Format cells window to change the color or other settings for the highlighted duplicates. You can use the same method to find duplicates in different worksheets if they are in separate workbooks.
Using the VLOOKUP Function
If the spreadsheets are in different workbooks, you’ll need to use a slightly more complex formula to find duplicates. You’ll need to include the worksheet name and sheet index in quotation marks (e.g., =VLOOKUP(Sheet2,$A2:$A5+1,FALSE)). This will search for an exact match to the data in your first sheet and return the results of that search. Then, it will look for an approximate match to the data in your second sheet and return those results.
You can then arrange the two worksheets to display them side by side by going to the View tab and selecting Arrange All. Choose either Vertical or Horizontal in the resulting dialog box to view the worksheets in that arrangement. Alternatively, you can use the Freeze Panes tool to freeze the top row of each worksheet so they don’t move when you scroll through them.
This method works well for comparing data in the same sheet, but it’s not as effective when you have to manually scroll through both sheets to identify duplicates. For this reason, it’s best to stick with the methods described in this article if you’re comparing large datasets or datasets in different workbooks. If you want to do even more advanced comparisons, you can try Power Query, which is an Excel add-in that lets you perform a variety of data transformation and preparation tasks, including finding duplicate values. It’s available for both Office 365 and Microsoft Azure. You can learn more about the tool in this article: How to Compare Worksheets in Excel with Power Query. This tool can help you improve your workflows and make your Excel spreadsheets more usable, efficient, and professional. You can even use it to find and delete duplicate rows or columns in a single worksheet!
