How can compare two excel spreadsheets

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. Cell-by-cell - best to be used to compare spreadsheets with the same layout and size, like balance sheets or year-to-year reports.

If you are unsure which option is right for you, go with the default one No key columns. Whichever algorithm you select, the add-in will find all the differences, it will only highlight them differently entire rows or individual cells.

On the same step, you can choose the preferred match type :. In this example, we will look for Best match by using the default No key columns comparing mode: Finally, specify which differences to highlight and which to ignore, and how to mark the differences.

This will take you to the download page for your result document. Download your comparison document. Once you've uploaded your workbooks and set your parameters, you'll have a document showing comparisons between data in the two files available for download.

Click the underlined Click here text in the Download the comparison file box. If you want to run any other comparisons, click New comparison in the bottom-right corner of the page to restart the file upload process. Method 4. Locate your workbook and sheet names. The first row is the name of the column. Create a comparison workbook. We will work on Book3.

Place the title of the column. Grab it from the bottom right square and drag it, copying all names. Name the second column. For example Estimate the difference of each product.

B2 You can do any normal Excel operation with the referred cell from the referred file. Drag down the lower corner square and get all differences, as before. Include your email address to get a message when this question is answered.

Remember it is important to have the referred files closed. If you have them opened, Excel may override what you type in the cell, making it impossible to access the file afterward unless again you have it open.

Helpful 0 Not Helpful 0. Submit a Tip All tip submissions are carefully reviewed before being published. Related wikiHows How to. How to. About This Article. Co-authored by:. Co-authors: 5. Updated: February 24, Categories: Software. Article Summary X 1.

Nederlands: Twee Excel bestanden vergelijken. Deutsch: Zwei Excel Dateien vergleichen. Italiano: Confrontare Due File Excel. Thanks to all authors for creating a page that has been read , times. Is this article up to date? Cookies make wikiHow better. By continuing to use our site, you agree to our cookie policy. Categories Computers and Electronics Software. Label it Results to make the results sheet easier to identify later.

In Cell A2 of the results sheet, paste the following formula and press Enter :. You can modify this formula to start at any cell you like. If the data in your sheet starts at row B and column 3, you change the formula to use B3 instead of A2. This formula compares the cell from Sheet1 to the same cell in Sheet2. If the cells are the same, the results sheet displays No Difference.

If they're different, the cell provides the different values from each sheet. Click the corner of the cell and drag the mouse across the sheet to the last column that has data from the other sheets you're comparing.

This fills the comparison formula to the last column and automatically adjusts the cell references. With the same row highlighted, click the corner of the last cell and drag the mouse down the sheet to the last row that has data from the other sheets you're comparing. This fills the comparison formula to the last row and automatically adjusts the cell references.

Scrolling through the sheet, you'll see all the cells that are different between Sheet1 and Sheet2 are displayed with the values from each sheet. Just scroll through to identify all of the differences.

Using formulas is one of the easiest ways to compare individual sheets, because you don't have to alter the original sheets in any way. Another approach to comparing two sheets is using conditional formatting. As you can see from the results sheet above, with all cells using the same formatting, it can be difficult to spot the differences between sheets.

Using conditional formatting is especially useful for large sheets with a lot of data. Changing the color or formatting of cells that have differences makes it much easier to identify those differences even in sheets with many rows and columns of data.

You can use conditional formatting to identify the differences. Highlight all cells in the results sheet. Select the Home menu. In the New Formatting Rule window that openss, select Use a formula to determine which cells to format. In the Format values where this formula is true field, paste the following formula and then select Format.

Just as with the formula approach, you can start the formatting feature at any cell. If your data starts at B3 instead of A2, edit this formula to use B3 instead. The formatting will start at B3 and fill all rows and columns below and to the right of it. Configure the formatting you want the cells to have when there are differences between the two sheets. You can select font style, underline, color, and strikethrough.

Select OK. The formatting makes it easy to see where the cells on the two sheets have differences. One simple and quick way to compare two worksheets is by doing so visually. Excel provides an easy way to visually compare two sheets side-by-side.

In the workbook where you have two sheets you want to compare, select the View menu. Select New Window to open the same workbook in a new Excel window in the background. Select the View menu again, and click View Side by Side. This places the two workbook windows side by side, each filling half the screen. In one window, select the sheet you want to compare. As you scroll the sheet in the window on one side, you'll see the sheet in the other window scroll at the same time.

Side-by-side viewing is especially useful in those situations where two worksheets are mostly the same, but you know there are a few differences. Synchronized scrolling lets you scan down the spreadsheet visually to find those differences.

If you are using an earlier version of Excel, the individual worksheet files open in the same Window. You can still use the View Side-by-Side feature, but finding it is a little different. Open both files in the same Excel window.