Compare two excel files for duplicates

To enable the toolbar, just select any cell in the corresponding sheet. By using this toolbar, you go through the found differences one-by-one and decide whether to merge or ignore them:. As soon as the last difference is dealt with, you will be prompted to save the workbooks and exit the Review differences mode. If you have not finished processing the differences yet but would like to take a break for now, click the Exit Review Differences button at the bottom of the toolbar and choose either to:.

That's how you compare two sheets in Excel with our tool hope you liked it : If you are curious to give it a shot, an evaluation version is available for download here. It identifies added, deleted and changed data and allows you to quickly merge differences.

In addition, it provides the following options:. DiffEngineX is another utility that can help you identify differences between cell values, formulas, comments, Excel names, and VBA code. It can compare two Excel workbooks or selected sheets.

DiffEngineX works with xls, xlsx, xlsm and xlsb files, and supports Excel , Excel , Excel and Excel With Change pro for Excel , you can compare two sheets in desktop Excel as well as on mobile devices with optional server-based comparison. The key features of this tool are:. Apart from desktop tools and utilities, there exist a number of online services that let you quickly compare two Excel sheets for differences without installing any software on your computer.

Probably it's not the best solution in terms of security, but if your Excel files do not contain any sensitive information why not use some free online service for immediate results? For example, you can try XLComparator or CloudyExcel , or find some other similar services on the net. This is how the CloudyExcel service looks like:. You just upload the two Excel workbooks you want to compare, and click the Find Difference button at the bottom of the screen.

In a moment, the differences in two active sheets will get highlighted with different colors:. Well, this is how you can compare Excel files for differences. If none of the solutions described in this tutorial is suitable for your task, check out the following resources that cover other aspects of Excel file comparison.

And if you know any other ways to compare two Excel files, your comments will be greatly appreciated. I thank you for reading and hope to see you on our blog next week! How to compare two Excel files for differences by Svetlana Cheusheva updated on December 16, Compare two Excel files by viewing them side-by-side Compare two Excel sheets for differences using formulas Highlight differences between 2 sheets with conditional formatting Compare and merge copies of a shared workbook Specialized tools to compare files in Excel Online services to compare two Excel sheets How to compare two Excel files by viewing them side by side If you have relatively small workbooks and a sharp eye for detail, this quick and easy way to compare Excel files might work well for you.

Compare 2 Excel workbooks Let's say you have sales reports for two months and you want to view both of them simultaneously to understand which products performed better this month and which did better last month.

To open two Excel files side by side, do the following: Open the workbooks you want to compare. That's it! By default, two separate Excel windows are displayed horizontally.

To split Excel windows vertically , click Arrange All button and select Vertical : As the result, two separate Excel windows will be arranged side by side, like in the below screenshot. Arrange multiple Excel windows side by side To view more than 2 Excel files at a time, open all the workbooks you want to compare, and click the View Side by Side button. Compare two sheets in same workbook Sometimes, 2 sheets that you want to compare reside in the same workbook.

This will open the same Excel file in a different window. Enable View Side by Side mode by clicking the corresponding button on the ribbon. Select sheet 1 in the first window and sheet 2 in the second window. How to compare two Excel sheets for differences in values It is the simplest way to compare data in Excel that lets you identify cells with different values.

A1, "" Due to the we use of relative cell references , the formula will change based on a relative position of the column and row. The result will look similar to this: As you can see in the above screenshot, the formula compares 2 sheets, identifies cells with deferent values and displays the differences in corresponding cells. Highlight differences between 2 sheets with conditional formatting To highlight cells that have different values in two sheets with the color of your choosing, use the Excel conditional formatting feature: In the worksheet where you want to highlight differences, select all used cells.

With the Conditional Formatting utility, you can highlight the differences between two sheets in the same workbook. A1 in the Format values where this formula is true box, then click Format to specify formatting style to highlight the cells. Tip: in the formula, A1 is the first cell in your selection, Sheet7 is the sheet you want to compare with.

After free installing Kutools for Excel, please do as below:. In the Compare Worksheets dialog, the current sheet is under Main worksheet section, go to Lookup section, click the browse button to choose the second sheet that you want to use compare with the current sheet. Click Next , in this step, specify the settings for highlighting differences. Click Ok to start comparing sheets. Then after you reediting the data to the same with the same cell in another sheet, the background color marker will disappear.

Attention : this feature compare two worksheets in different workbooks. In the popping dialog, click Browse button in the Find values in and According to box to select the two ranges you want to compare with, and check Single cell and Different values options, and specify the background color and font color to highlight the differences.

Click Ok , a dialog pops out to remind you the different cells have been found, and click OK to close it, the differences have been highlighted and selected as well. Note: The other languages of the website are Google-translated. Back to English. Log in. Remember Me Forgot your password? Password Reset. Please enter the email address for your account. Cookie Settings. Learn why people trust wikiHow. Download Article Explore this Article methods.

Tips and Warnings. Related Articles. Article Summary. Method 1. Open the workbooks you need to compare. You can find these by opening Excel, clicking File then Open , and selecting two workbooks to compare from the menu that appears.

Navigate to the folder where you have the Excel workbooks saved, select each workbook separately, and keep both workbooks open. Once you've opened one of the workbooks, you can click on the View tab in the top-center of the window. This is located in the Window group of the ribbon for the View menu and has two sheets as its icon.

This will pull up both worksheets into smaller windows stacked vertically. This option may not be readily visible under the View tab if you only have one workbook open in Excel.

If there are two workbooks open, then Excel will automatically choose these as the documents to view side by side. Click Arrange All.

This setting lets you change the orientation of the workbooks when they're displayed side-by-side. In the menu that pops up, you can select to have the workbooks Horizontal , Vertical , Cascade , or Tiled.

Once you have both worksheets open, click on Synchronous Scrolling located under the View Side by Side option to make it easier to scroll through both Excel files line-by-line to check for any differences in data manually.

Scroll through one workbook to scroll through both. Once Synchronous Scrolling is enabled, you'll be able to easily scroll through both workbooks at the same time and compare their data more easily. Method 2. Navigate to the folder where you have the Excel workbooks saved, select each workbook separately and keep both workbooks open.

Decide on which cell you would like the user to select from. This is where a drop-down list will appear later. Click the DATA tab on the toolbar. A pop up should appear. Click the button with the red arrow. This will let you pick your source in other words, your first column , which will then be processed into data in the drop-down menu.

The add-in searches for duplicates and uniques by the key columns you select. The dupes and uniques are identified only in table 1. Before you start Excel instances Please make sure both tables are opened in Excel. They can be in different files but the add-in will compare them as long as they are open in the same instance of Excel.

We strongly recommend keeping the checkbox Create a backup copy of the worksheet selected, as Excel won't let you undo the changes made by the add-in.

First, select the range with your table 1: all changes will be applied to the table you select on this step. If you select just one cell in the table, the add-in will automatically highlight the used range at the start.