Compare two excel files in windows 7

Since an Excel workbook only displays one sheet at a time, comparing two Excel files or two sheets in the same file can be difficult. Thankfully, Excel has some useful features that allow you to open and compare two Excel files quickly and easily. Usually, you will try comparing the excel files for dissimilarities before merging them into a single file.

However, when you have a large amount of data to evaluate, this becomes more complex and erroneous. Workbook comparison also assists you in identifying potential issues such as broken ties, duplicate documents, contradictory formulas, or incorrect formatting. The approach you use will be determined by the structure of your data and the type of comparison you want to make.

Check out these Data Entry Tips in Excel here. This fast and easy way of comparing Excel files could work well for you if you have relatively small workbooks and a keen eye for detail. This approach can be used to compare two workbooks or two sheets in the same workbook visually.

Step 1: Open the workbooks that you want to compare and contrast. That would be all that is required! Two separate Excel windows are shown horizontally by default. In order to compare two Excel files side- by- side, make sure the Synchronous Scrolling feature is switched. This is how you can compare data row by row, by scrolling through both worksheets at the same time.

This choice is found on the View tab, in the Window group, directly underneath the View Side by Side button, and it is typically switched on automatically as soon as View Side by Side mode is enabled.

Two sheets that you want to compare can be in the same workbook at times. Take the steps below to compare the Excel sheets side by side. Step 2: The same Excel file will be opened in a new browser.

Step 3: By clicking the corresponding button on the ribbon, you can activate View Side by Side mode. Step 4: In the first window, select sheet 1; in the second window, select sheet 2.

In addition to files saved on your computer or on a network, you can enter a web address to a site where your workbooks are saved. In the Compare Files dialog box, in the To row, browse to the version you want to compare that earlier version against. Note: You can compare two files with the same name if they're saved in different folders. Note: If you get an "Unable to open workbook" message, this might mean a workbook is password protected.

Click OK and then enter the password. Learn more about how passwords and Spreadsheet Compare work together. The results of the comparison appear in a two-pane grid.

The workbook on the left corresponds to the "Compare" file you chose and the workbook on the right corresponds to the "To" file. Details appear in a pane below the two grids. Changes are highlighted by color, depending on the kind of change. In the side-by-side grid, a worksheet for each file is compared to the worksheet in the other file, beginning with the leftmost worksheet in each.

If a worksheet is hidden in a workbook, it's still shown and compared in Spreadsheet Compare. If the cells are too narrow to show the cell contents, click Resize Cells to Fit.

Differences are highlighted with a cell fill color or text font color, depending on the type of difference. For example, cells with "entered values" non-formula cells are formatted with a green fill color in the side-by-side grid, and with a green font in the pane results list. The lower-left pane is a legend that shows what the colors mean.

If you want to save your results or analyze them elsewhere, you can export them to an Excel file or copy and paste them into in another program, such as Microsoft Word. It is especially useful when several users collaborate on the same Excel workbook because it lets you view the changes and comments of all users at a time. To leverage this feature, be sure to do the following preparations:.

Allow Excel to save the workbook if prompted. Turning on the Track Changes feature shares the workbook automatically. Each person who makes any edits to the shared workbook must save a copy. Now that all initial preparations are done properly, you are ready to combine the copies of a shared workbook. Although, the Compare and Merge Workbooks feature is available in all versions of Excel , Excel , Excel and lower, this command is not displayed anywhere in Excel by default.

To add it to the Quick Access toolbar, perform the following steps:. When all of the users have finished working with your shared Excel workbook, you can merge all the copies into one file. To point out the row and columns with differences, Excel highlights the column letters and row numbers in a dark red color.

At the cell level, edits from different users are marked with different colors. To see who made a specific change, just hover over the cell.

As you have just seen, Microsoft Excel provides a handful of features to compare data in two or more workbooks. But none of the built-in options is sufficient to comprehensively compare Excel sheets, let alone entire workbooks, spotting all the differences in values, formulas or formatting.

So, if you need advanced and really efficient means to compare two Excel files, then most likely you would have to use one of the third-party tools specially designed for comparing, updating and merging Excel sheets and workbooks. Below you will find a quick overview of a few tools that, in my opinion, are best performers in this area.

The Synkronizer Excel Compare add-in can quickly compare, merge and update two Excel files saving you the trouble of searching for differences manually. If you are looking for a quick and reliable method to compare two Excel sheets or workbooks, you will certainly find helpful the following features of Synkronizer Excel Compare:.

To get some basic idea about Synkronizer Excel Compare's capabilities and performance, let's carry out a couple of field tests. Supposing you are organizing some event and, in your Excel table, you gather information about the participants such as a participant name, arrival date, number of seats, etc. Also, you have a couple of managers in direct contact with participants and the database, and as a result, you have 2 versions of the same Excel file.

Okay, let's see how efficiently Synkronizer can compare our two sheets and identify differences between them. The Synkronizer pane will show up in the left part of your Excel window, where you do the following:. If the selected workbooks have any sheets with the same names, all those sheets will be matched and automatically selected for comparison like Participants sheets in the below screenshot.

Also, you can select worksheets manually or instruct the add-in to match sheets by other criteria, for example by worksheet type - all, protected, or hidden. Once you've selected the sheets, the Synkronizer add-in will open them side by side, arranged vertically or horizontally, like in Excel's View Side by Side mode. Select one of the following comparison options: Compare as normal worksheets - the default option that works in most cases.

Compare with link options - if the selected sheets do not contain any new or deleted rows and columns, you can compare them "1 on 1". Compare as database - recommended for comparing sheets that have a database structure. Compare selected ranges - if you do not want to compare the entire sheets, define the ranges to be compared. Choose the content types to be compared optional. On the Select tab, in the Compare group, you can choose the content type s relevant to your current task:.

Usually it takes Synkronizer only a few seconds to compare 2 sheets and present two summary reports on the Results tab:. The following screenshot shows the summary report in the upper part of the Results pane , and cell difference report in the lower part of the pane that were created for our sample sheets:. Clicking on a difference in the detailed report will select the corresponding cells on both sheets the below screenshot shows just one sheet because there's enough room to show both :.

In addition, you can create a difference repot in a separate workbook, either standard or hyperlinked, and jump to a specific difference with a mouse click:. If the two Excel files you are comparing contain multiple sheets, all matching worksheet pairs will be presented in the summary report for your perusal:. By default, the Synkronizer Excel Compare add-in highlights all found differences, like in the following screenshot:.

To highlight only the relevant differences , click the Outline button on the Results tab, and select the required options:. The merge function is definitely one of the most useful features of the Synkronizer Excel Compare add-in. To update one or more differences, select them on the Synkronizer's pane and click one of the 4 update buttons - the first and last buttons update all differences , while the 2 nd and 3 rd buttons update selected differences only the button arrows indicate the transfer direction :.

Well, these are the key features of the Synkronizer add-in, but there is certainly much more to it. Want to give it a try? An evaluation version is available for download here. Synkronizer is certainly worth your attention, but it's not the only way to compare files in Excel. A handful of other comparison tools exist, which basically provide the same set of features but in different implementations. The latest version of our Ultimate Suite includes over 40 new features and improvements, the most exciting of which is Compare Sheets - our own tool to compare worksheets in Excel.

Now, let's try the tool on our sample spreadsheets from the previous example and see if the results are any different. By default, the entire sheets are selected, but you can also select the current table or a specific range by clicking the corresponding button: On the next step, you select the comparison algorithm: No key columns default - works best for sheet-based documents like invoices or contracts.

By key columns - is appropriate for column-organized sheets that have one or more unique identifiers such as order numbers or product ID's.