In this video, I demonstrate how to compare two Excel worksheets in order to find the differences between them. I look at two scenarios: one where the worksheets are in the same workbook and then where the worksheets are in separate workbooks. Download the featured files here:
//www.bluepecantraining.com/wp-content/uploads/2021/01/Compare-Two-Sheets.xlsx //www.bluepecantraining.com/wp-content/uploads/2021/01/Ver1.xlsx //www.bluepecantraining.com/wp-content/uploads/2021/01/Ver2.xlsx
Compare Data in the Same Workbook
If the worksheets are in the same workbook, the first thing to do is to display both worksheets on the screen at the same time. To achieve this:
- Click on the Ribbon’s View tab and then click on the New Window button in the Window group.
- Still on the Ribbon’s View tab, click on the Arrange All button.
- In the Arrange Windows dialog box, select Vertical and then click OK.
- Select the worksheets you want to appear in each window.
Method 1: Conditional Formatting
To use conditional formatting to compare the two worksheets, do the following:
- Select all the data in the first worksheet.
- On the Ribbon’s Home tab click on Conditional Formatting in the Styles group.
- Select New Rule… in the menu.
- Under Select a Rule Type:, select Use a formula to determine which cells to format.
- In the Format values where this formula is true: box, write the following formula: =A1<>’Ver2′!A1, where Ver2 is the name of worksheet you are comparing with. The formula returns TRUE if the cells contain values that are different.
- Click on the Format button to specify a format to apply if the rule is met – for example a red fill and white font. Click on OK to confirm the format.
- Click on OK to confirm the rule.
Method 2: A Formula
The second method for comparing worksheets and showing differences uses a formula. To use this method:
- Create a new worksheet within the same workbook that contains the worksheets you want to compare.
- The formula to use is =IF(‘Ver1′!A1=’Ver2′!A1,’Ver2′!A1,”Ver1:”&’Ver1′!A1&” | Ver2:”&’Ver2’!A1)
- Copy the formula across and down the worksheet.
Compare Data in Different Workbooks
If the worksheets are in two different workbooks, follow these steps:
- Arrange the workbooks on your screen so they can both be seen. On the Ribbon’s View tab, click the Arrange All button in the Windows group.
- In the Arrange Windows dialog box, select Vertical and then click OK.
- Conditional formatting can’t be used across workbooks so use the formula method described above. In one of the workbooks, create a new worksheet and use the same formula to compare the first cell in workbook 1 with the first cell in workbook 2. The only thing to watch out for is that when you refer to a cell in another workbook (by clicking on it), the cell address is by default locked with dollars. Delete the dollars to make the cell reference relative. If you are not sure about how to create this formula, please watch the video.
- RESOURCES
- EXCEL
- GOOGLE SHEETS
- FINANCE
- SUCCESS STORIES
- PRODUCT
Don’t forget to share this post
One of the most common scenarios when working with Excel spreadsheets is having files with similar or duplicate data. The reasons for this could be many, but it usually involves spending a considerable amount of time checking complete files or separate worksheets manually.
This article will explain how to compare two or multiple Excel files, as well as two Excel sheets, for differences. To achieve this, we will describe how to use three useful methods to spot differences in a quick and easy way; these include side-by-side viewing, conditional formatting rules, and the =IF formula.
We will start by illustrating how to compare two Excel workbooks using the side-by-side view. However, we recommend using this method in case your dataset is not too large; if not, we recommend using one of the two methods outlined further on in this article. This is how you can compare two Excel files using the side-by-side viewing feature.
- 1. Open the two Excel workbooks you would like to compare and go to View > View Side by Side on any of the opened files.
How to compare two Excel files - View side by side
- 2. By default, Excel will place both files horizontally, as shown below.
How to compare two Excel files - Horizontal view
- 3. To arrange them vertically, click “Arrange All”, and then select “Vertical”.
How to compare two Excel files - Arrange vertically
- 4. The two Excel files will now be arranged vertically, as shown below.
How to compare two Excel files - Vertical view
- 5. Make sure that the “Synchronous Scrolling” option is activated since this will allow you to scroll through the data on both files simultaneously and allow you to compare more easily. Although it activates automatically as soon as you enable the side-by-side view, you can also check that it’s activated in your toolbar within the “Window” group.
How to compare two Excel files - Synchronous Scrolling
Now that we know how easy it is to compare two Excel files, let’s see how we can apply this viewing method to Excel sheets.
Discover the most popular methods used to manually or automatically combine multiple Excel spreadsheets and data inputs into one master file
READ MORE
Sometimes, similar or duplicate data may appear within the same spreadsheet. IF you want to avoid having to switch from one sheet to another to compare the data, this is how you can quickly compare two Excel sheets side by side.
- 1. Open the Excel file where you would like to compare sheets. Then, go to View > New Window.
How to compare two Excel files - View New Window
- 2. You will now have the same Excel file open up in a different window, as shown below.
How to compare two Excel files - Same file in New Window
- 3. Select “View Side by Side” and make sure to select a different sheet on each file. As before, you can select vertical or horizontal viewing according to your preference.
How to compare two Excel files - Sheet 1 and 2
So far, you have seen how easy it is to compare two files and sheets on Excel. However, what if you would like to compare more than two files at the same time? This is how you can compare multiple Excel files using the side-by-side view.
Comparing multiple files for differences follows a similar process and will only take you a few simple steps.
- 1. Open all the Excel workbooks you would like to compare and go to View > View Side by Side. Select the file you want to start comparing with in the “Compare Side by Side” dialog box.
How to compare two Excel files - Compare side by side
- 2. Click “Arrange All” to view all the opened files at the same time.
How to compare two Excel files - Arrange All
- 3. Then, select the type of arrangement according to your preference. Here, I have chosen the “Tiled” arrangement.
How to compare two Excel files - Tiled arrangement
So far, these methods are useful in case your datasets are not too large and easily manageable. If you want to compare larger datasets for differences in values, the best way is to use the =IF formula or a conditional formatting rule. Let’s explore the =IF formula first.
How to compare two Excel sheets using a formula?
This is the most straightforward way to compare data between two Excel sheets. This formula will allow you to identify cells containing different values, and a comparative report will be generated in a new worksheet.
- 1. Open a new empty sheet in your Excel workbook and enter the following formula in cell A1: =IF(Sheet1!A1 <> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")
How to compare two Excel files - Enter IF formula
- 2. Grab the bottom-right corner of the formula cell, and drag down.
How to compare two Excel files - A1 cell comparison in Sheets
The formula will adapt to the column and row position it fills. This way, the formula in cell A1 compares to cell A1 in “Sheet1” and “Sheet2”; the formula in cell B1 will compare cell B1 in both sheets as well.
We will now turn to how to compare two Excel sheets by highlighting the differences. The best way to do so is using Excel conditional formatting feature.
When comparing two very similar and large datasets, the best and quickest way to spot differences in values is to highlight them using the conditional formatting feature.
- 1. Open the Excel sheets and select the range of data you would like to compare for differences. A quick way to do this is to click the upper-left cell and then Ctrl + Shift + End to extend the selection to the last cell containing values.
How to compare two Excel files - Select cell range
- 2. Go to Home > Conditional Formatting > New rule.
How to compare two Excel files - Create New Rule
- 3. Select the rule type “Use a formula to determine which cells to format” and enter the following, =A1<>Sheet1!A1. The Sheet name included in the formula corresponds to the sheet you are comparing with and not the one you are creating the rule in.
How to compare two Excel files - Use a formula
- 4. Once you’ve entered the formula, click “Format”, next to the “Preview” pane.
How to compare two Excel files - Format cells
- 5. Select how you would like to format the cells, i.e. according to “Number”, “Font”, “Border”, or “Fill”. We recommend highlighting with color fill, so we have chosen a color that will clearly stand out.
How to compare two Excel files - Format Fill
- 6. As you can see, Excel has highlighted the different cells in “Sheet 2” compared to “Sheet 1”.
How to compare two Excel files - Different cells highlighted
Now you know how to compare two or multiple Excel files and two sheets on your desktop. What if you want to compare and highlight differences in your Excel sheets online?
How to compare two Excel sheets and highlight differences online?
In case you don’t have Excel installed on your desktop or simply prefer to work online altogether, there are online tools that allow you to compare Excel files and sheets for differences.
Below, we provide a list of third-party tools that will allow you to compare Excel files and sheets online:
- Synkronizer Excel Compare: In addition to the features outlined in this article, it allows you to combine multiple Excel files into one, while maintaining unique values and avoiding duplicates.
- Ablebits Compare Sheets for Excel: This tool provides step-by-step guidance for efficient comparison and displays the differences found between sheets in the “Review Differences” mode for better management.
- Florencesoft DiffEngineX: Another excellent alternative that allows you to compare Excel files directly from Microsoft Outlook.
- Layer: Apart from allowing you to review spreadsheet changes and combine multiple spreadsheets into one, Layer offers additional features for file storage and management at a business level.
How to track spreadsheet changes with Layer?
Layer adds productivity features to your Google Sheets. Share parts of your spreadsheet, request input, and accept or reject changes to make collaboration seamless and more efficient while keeping full control over your data.
Using Layer, you can:
- Manage Access: Give spreadsheet access to relevant stakeholders on a tab or cell level.
- Review & Track: Consolidate input, and easily track changes.
- Collaborate: Define, assign, and automate tasks and set deadlines.
Sign up for early access and start automating your Google Sheets workflows with Layer!
This article has shown you how to compare the data in two Excel files for differences. You can compare data between two files, two sheets, or multiple files using the side-by-side view for a quick and easy comparison. If your dataset is larger, you can apply the IF formula to compare two Excel sheets or use conditional formatting rules to highlight the differences.
Alternatively, for users that prefer to work online, there are platforms that can help you achieve this level of comparison in an online setting, for example, Layer. We also recommend reading our blog article on How To Combine Multiple Files into One as a great way to complete this data comparison process.
Sign up for the Layer Google Sheets Add-on before Sept 14th and receive a free 6-month license with access to all paid features!