How to Reconcile Data in 2 Excel Sheets (4 Ways)

While working in Excel, users have to reconcile two data sets for various working purposes. If the two data sets are in the same worksheet, then it is easy to reconcile them. But if there are two Excel sheets, then the method would be slightly different. In this article, we will show you how to reconcile data in 2 Excel sheets.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

4 Handy Ways to Reconcile Data in 2 Excel Sheets

By reconciling data, it means comparing or finding similarities or dissimilarities between two different data sets on the same sheet or different sheets. In this article, you will see four different ways to reconcile data in 2 Excel sheets. In our first method, we will apply the Sort command; in the second one, we will use the COUNTIF function; in the third method, we will match the row values of our data sets; and lastly, we will utilize conditional formatting. For our working purposes, we will use the following data set. Here are two different data sets in two different sheets.

1. Applying Sort Command to Reconcile Data in 2 Excel Sheets

In our first attempt, we will apply the Sort command to both the worksheets to check for any similarities or dissimilarities between them. See the below-given steps for this procedure.

Step 1:

• Firstly, select the data range of cell B4:C10 from the first worksheet.

Step 2:

• Secondly, right-click on the data range and select the Sort command.
• Then, from the sort options, choose the Custom Sort… command.

Step 3:

• Thirdly, you will see the Sort dialogue box.
• Then, in the Sort by dialogue box select Order ID.
• Lastly, press OK.

Step 4:

• Fourthly, you will see the first data set will be sorted in terms of Order ID.

Step 5:

• Fifthly, apply the Sort command for sorting the second data set as well.

Step 6:

• In the sixth step, we will create a side-by-side view for both worksheets.
• For that, go to the View tab of the ribbon.
• Then, in the Window group select the New Window command.

Step 7:

• After that, from the View tab, choose the Arrange All command.

Step 8:

• In the eighth step, you will see the Arrange Windows window.
• Then, under the Arrange heading select Vertical.
• Lastly, press OK.

Step 9:

• Consequently, you will see the two worksheets side-by-side after this action.
• Then, you can identify any similarities or dissimilarities between them after carefully going through the data sets.

Read More: How to Make a Vendor Ledger Reconciliation Format in Excel

2. Using COUNTIF Function to Reconcile Data in 2 Excel Sheets

The first method of our article will be time-consuming if the data sets are too large. So, we will use the COUNTIF function for reconciliation in the second procedure.

Step 1:

• First of all, select the side-by-side view for the worksheets as shown in the previous method.
• Then, in cell B5 of our first worksheet, type the following formula for the COUNTIF function.

• Consequently, we are selecting the data range from cell B5:B10 of second worksheet for comparison.

Step 2:

• Secondly, press Enter to get the result.
• Then use the AutoFill feature to drag the formula to the lower cells.
• Here, you will get 1 or 0 as a result.
• Consequently, 1 indicates if the first data set has any matching value with the second one.
• And, 0 indicates no match of data.

Step 3:

• Thirdly, we will apply the following formula of the COUNTIF function in cell B5 of the second data set.

Step 4:

• Fourthly, press Enter to see the result.
• Then, use the AutoFill feature to get the results of the lower cells.

Step 5:

• Finally, you will see the similarities or dissimilarities between the data sets automatically in the form of 1 or 0.

Read More: How to Reconcile Vendor Statements in Excel (2 Easy Methods)

3. Matching Row Values to Reconcile Data in 2 Excel Sheets

You can also compare two data sets by matching their row values. In the following steps, we have given the detailed steps.

Step 1:

• Firstly, to compare row values type the following formula in cell B5 of the first data set.

• Consequently, we will match each row value of the first and second data sheets.

Step 2:

• Secondly, press Enter and drag the formula to the lower cells using AutoFill.
• Consequently, if any of the row values match with the same row values of the second data set it will show TRUE as a result.
• Otherwise, it will show False.

Step 3:

• Thirdly, in cell B5 of the second data set write the following formula.

Step 4:

• Fourthly, press Enter and drag the formula to the lower cells using AutoFill.

Step 5:

• Finally, you can compare or match the row values of the two data sets by following the above steps.

4. Utilizing Conditional Formatting to Reconcile Data

Finally, we will utilize the Conditional Formatting feature of Excel as our last method. Go through the following steps for a better understanding.

Step 1:

• First of all, set the side-by-side view for both the worksheets.

Step 2:

• Secondly, in the first worksheet select the data range B5:C10.
• Then, from the Home tab select Conditional Formatting.
• After that, from the drop down, select the New Rule… option.

Step 3:

• Thirdly, you will see the New Formatting Rule dialogue box.
• Then, go to the Select a Rule Type heading.
• From there, select the option named “Use a formula to determine which cells to format”.

Step 4:

• Fourthly, type the following formula in the Edit the Rule Description box.

• Then, choose the Format command.

Step 5:

• Fifthly, from the Fill tab of the Format Cells dialogue box, choose any color of your preference.
• Then, press OK.

Step 6:

• In the next step, the dialogue box from Step 4 will be back with all the editing.
• Lastly, press OK.

Step 7:

• Finally, your first data set will look like the following picture after completing all the steps.
• Here, after formatting, the cells with 0 in them will be highlighted meaning no match.

Step 8:

• Consequently, repeat Step 1 to Step 6 to get the result for the second data set as well.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to reconcile data in 2 Excel sheets by using any of the above-described methods. Please share any further queries or recommendations with us in the comments section below. The Exceldemy team is always concerned about your preferences.

Related Articles

• How to Create a Party Ledger Reconciliation Format in Excel
• How to Do Bank Reconciliation in Excel (with Easy Steps)
• How to Do Intercompany Reconciliation in Excel (2 Easy Methods)
• Automation of Bank Reconciliation with Excel Macros