This makes it easier to do a line by line comparison and spot any differences.īut to do this, you need to enable Synchronous Scrolling. You can change this so that when you scroll in one sheet, the other also scrolls at the same time. This will open the ‘Arrange Windows’ dialog box where you can select ‘Vertical’.Īt this point, if you scroll down in one of the worksheets, the other one would remain as is. In case you want to arrange the files vertically, click on the Arrange All option (in the View tab).
Both of the files will be visible, and you’re free to edit/compare these files while they are arranged side by side. This becomes available only when you have two or more Excel files open.Īs soon as you click on the View side by side option, Excel will arrange the workbook horizontally.
Let’s see how to use this when you have to compare two separate files or two sheets in the same file. If you have a large dataset, I recommend using the conditional method or the formula method covered later in this tutorial. This is recommended only when you have a small dataset and manually comparing these files is likely to be less time-consuming and error-prone. If you want to compare two separate Excel files side by side (or two sheets in the same workbook), there is an in-built feature in Excel to do this. Using a Third-Party Tool – XL ComparatorĬompare Two Excel Sheets in Separate Excel Files (Side-by-Side).Compare Two Excel Files/Sheets And Get The Differences Using VBA.Compare Two Excel Files/Sheets And Get The Differences Using Formula.Compare Two Sheets and Highlight Differences (Using Conditional Formatting).Compare Two Sheets (Side-by-Side) in the Same Excel Workbook.Compare Two Excel Sheets in Separate Excel Files (Side-by-Side).We are going to find the exact matches in the second sheet and pull out the required information to show it in the first one. We can use the VLOOKUP formulato find the matches in different worksheets. Using Excel VLOOKUP In Multiple Worksheets to Compare Duplicates The more duplicate values on the other sheet are highlighted.Ĥ. From the Format, choose the Fill color and click OK.Select the Rule bar and click Duplicate Rule.Click on the Conditional Formatting and select Manage Rule.At first, select a cell and go to the Home tab.If there are more than two duplicates on the other sheet, we can highlight them. Search for More Duplicate Values on The Other Sheet and Highlight in Excel Now the final result is here and we can see the duplicated values are highlighted.ģ.In the Format section, choose the Fill color and click OK.For that, we will go to the first sheet and select the cell.
Now put a comma and specify the criteria. Here select all the data from where we are looking at and press F4 to make it absolute. Now in the rule description box use COUNTIF Function,.In the New Formatting Rule select the pointed rule type.
Click Arrange All and select Vertical and click Ok. The same workbook will open in two windows. Here we are going to compare them viewing side by side.
Let’s consider we have an Excel workbook with two sheets. Compare from One Workbook with Two Different Sheets by Viewing Them Side by Side 4 Quick Ways to Compare Two Excel Sheets for Duplicates 1.