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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Reconcile Data in 2 Excel Sheets: 4 Handy Ways

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.

Sample Data

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.

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 2:

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

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 3:

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

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 4:

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

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 5:

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

Handy Ways to Reconcile Data in 2 Excel Sheets

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.

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 7:

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

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 8:

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

Handy Ways to Reconcile Data in 2 Excel Sheets

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.

Handy Ways to Reconcile Data in 2 Excel Sheets

Read More: How to Reconcile Two Sets of Data 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.
=COUNTIF(COUNTIF1!$B$5:$B$10,COUNTIF!B5)
  • Consequently, we are selecting the data range from cell B5:B10 of second worksheet for comparison.

Handy Ways to Reconcile Data in 2 Excel Sheets

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.

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 3:

  • Thirdly, we will apply the following formula of the COUNTIF function in cell B5 of the second data set.
=COUNTIF(COUNTIF!$B$5:$B$10,COUNTIF1!B5)

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 4:

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

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 5:

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

Handy Ways to Reconcile Data in 2 Excel Sheets

Read More: How to Reconcile Vendor Statements in Excel


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.
=B5='Row Values 1'!B5
  • Consequently, we will match each row value of the first and second data sheets.

Handy Ways to Reconcile Data in 2 Excel 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.

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 3:

  • Thirdly, in cell B5 of the second data set write the following formula.
=B5='Row Values'!B5

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 4:

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

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 5:

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

Handy Ways to Reconcile Data in 2 Excel Sheets


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.

Handy Ways to Reconcile Data in 2 Excel Sheets

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.

Sample Data Set

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”.

Sample Data Set

Step 4:

  • Fourthly, type the following formula in the Edit the Rule Description box.
=$D5=0
  • Then, choose the Format command.

Sample Data Set

Step 5:

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

Sample Data Set

Step 6:

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

Sample Data Set

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.

Sample Data Set

Step 8:

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

Sample Data Set

Read More: How to Reconcile Data in Excel


Download Practice Workbook

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


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.


Related Articles


<< Go Back to Excel Reconciliation Formula | Excel for Accounting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo