How To Compare Large Data Sets In Excel

One of the most mutual scenarios when working with Excel spreadsheets is having files with similar or duplicate data. The reasons for this could be many, but it commonly involves spending a considerable amount of fourth dimension checking complete files or separate worksheets manually.

This article will explain how to compare ii or multiple Excel files, every bit well as two Excel sheets, for differences. To accomplish this, we will depict how to use iii useful methods to spot differences in a quick and easy way; these include side-by-side viewing, conditional formatting rules, and the =IF formula.

How to compare two Excel files using side-by-side view?

We will outset by illustrating how to compare two Excel workbooks using the side-by-side view. Even so, we recommend using this method in example your dataset is non likewise large; if non, we recommend using one of the two methods outlined farther on in this commodity. This is how y'all tin compare two Excel files using the side-by-side viewing feature.

1. 1. Open the ii Excel workbooks yous would similar to compare and get to View > View Next on any of the opened files.

How to compare 2 Excel files - View next

1. two. By default, Excel volition place both files horizontally, as shown below.

How to compare ii Excel files - Horizontal view

1. three. To arrange them vertically, click "Suit All", so select "Vertical".

How to compare 2 Excel files - Arrange vertically

1. 4. The two Excel files will now be bundled vertically, as shown beneath.

How to compare two Excel files - Vertical view

1. 5. Make certain that the "Synchronous Scrolling" choice is activated since this will allow you to coil through the data on both files simultaneously and allow you to compare more than easily. Although it activates automatically equally soon equally you enable the side-past-side view, you lot can also check that it's activated in your toolbar within the "Window" group.

How to compare two Excel files - Synchronous Scrolling

Now that nosotros know how easy it is to compare two Excel files, let's run across how we tin apply this viewing method to Excel sheets.

How to Combine Multiple Excel Files Into Ane

Notice the most popular methods used to manually or automatically combine multiple Excel spreadsheets and data inputs into ane master file

READ MORE

How to compare two Excel sheets using side-by-side view?

Sometimes, similar or duplicate data may appear inside the aforementioned spreadsheet. IF you want to avoid having to switch from one sail to another to compare the data, this is how you tin quickly compare two Excel sheets side by side.

1. 1. Open the Excel file where you would similar to compare sheets. Then, go to View > New Window.

How to compare two Excel files - View New Window

1. ii. You will now have the same Excel file open up in a different window, as shown below.

How to compare two Excel files - Aforementioned file in New Window

1. 3. Select "View Side past Side" and make sure to select a different canvass on each file. As before, you tin select vertical or horizontal viewing co-ordinate to your preference.

How to compare two Excel files - Canvas 1 and 2

And so far, yous accept seen how easy it is to compare 2 files and sheets on Excel. However, what if y'all would similar 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.

How to compare multiple Excel files using side-by-side view?

Comparing multiple files for differences follows a similar process and will only accept yous a few simple steps.

1. 1. Open all the Excel workbooks yous would like to compare and go to View > View Adjacent. Select the file you want to start comparison with in the "Compare Next" dialog box.

How to compare ii Excel files - Compare adjacent

1. 2. Click "Arrange All" to view all the opened files at the same time.

How to compare two Excel files - Arrange All

1. 3. Then, select the type of arrangement according to your preference. Here, I accept chosen the "Tiled" arrangement.

How to compare two Excel files - Tiled arrangement

So far, these methods are useful in instance your datasets are not besides big and easily manageable. If you desire to compare larger datasets for differences in values, the best way is to employ the =IF formula or a conditional formatting rule. Let's explore the =IF formula starting time.

How to compare two Excel sheets using a formula?

This is the most straightforward fashion to compare data between 2 Excel sheets. This formula volition allow you to identify cells containing dissimilar values, and a comparative written report will be generated in a new worksheet.

1. 1. Open up a new empty sheet in your Excel workbook and enter the post-obit formula in prison cell A1: =IF(Sheet1!A1 <> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")

How to compare two Excel files - Enter IF formula

1. 2. Grab the bottom-correct corner of the formula cell, and drag down.

How to compare 2 Excel files - A1 jail cell comparison in Sheets

The formula volition adapt to the column and row position it fills. This way, the formula in prison cell A1 compares to jail cell A1 in "Sheet1" and "Sheet2"; the formula in cell B1 will compare jail cell B1 in both sheets equally 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.

How to compare two Excel sheets using conditional formatting?

When comparison 2 very like and big datasets, the best and quickest fashion to spot differences in values is to highlight them using the conditional formatting feature.

1. ane. Open the Excel sheets and select the range of data you lot would like to compare for differences. A quick way to do this is to click the upper-left jail cell and then Ctrl + Shift + Cease to extend the option to the final jail cell containing values.

How to compare two Excel files - Select cell range

1. 2. Go to Dwelling > Provisional Formatting > New dominion.

How to compare two Excel files - Create New Rule

1. 3. Select the rule type "Employ 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 yous are comparing with and not the ane you are creating the rule in.

How to compare two Excel files - Use a formula

1. 4. Once you lot've entered the formula, click "Format", next to the "Preview" pane.

How to compare two Excel files - Format cells

1. v. Select how you would like to format the cells, i.due east. according to "Number", "Font", "Edge", or "Fill". Nosotros recommend highlighting with color fill, and so we have called a colour that will clearly stand out.

How to compare ii Excel files - Format Fill

1. 6. Equally you lot can see, Excel has highlighted the dissimilar cells in "Sheet two" compared to "Sheet one".

How to compare two Excel files - Unlike cells highlighted

Now yous know how to compare two or multiple Excel files and two sheets on your desktop. What if you desire 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 only adopt 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 3rd-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 y'all to combine multiple Excel files into one, while maintaining unique values and avoiding duplicates.
• Ablebits Compare Sheets for Excel: This tool provides step-past-step guidance for efficient comparison and displays the differences constitute between sheets in the "Review Differences" mode for amend management.
• Florencesoft DiffEngineX: Another excellent culling that allows y'all to compare Excel files directly from Microsoft Outlook.
• Layer: Apart from allowing yous to review Excel changes and combine multiple Excel files into one, Layer offers boosted features for file storage and management at a business level.

How to track Excel changes with Layer?

Layer is a collaboration platform for spreadsheets that works on top of your existing Excel files and Google Sheets. Share parts of your spreadsheet, collect and consolidate data, and review changes to make collaboration seamless and more efficient while keeping full control over your data. Using Layer, you tin:

• Manage Access: Requite spreadsheet admission on a tab or cell level to relevant stakeholders.
• Collaborate: Automate tasks, gear up deadlines, and communicate on top of your files.
• Review & Track: Consolidate input, runway changes, and restore previous versions.

Sign up to become started with Layer correct now or schedule an onboarding call so we can help you lot set and share your spreadsheet flows.

Conclusion

This article has shown you how to compare the data in two Excel files for differences. You tin compare data betwixt 2 files, 2 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 ii Excel sheets or use conditional formatting rules to highlight the differences.

Alternatively, for users that prefer to piece of work online, there are platforms that can help you lot 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 I as a great way to complete this data comparison process.

Source: https://blog.golayer.io/excel/compare-two-excel-files

Share this post