How to Reconcile Data in 2 Excel Sheets: 4 Ways

Method 1 – Applying Sort Command to Reconcile Data in 2 Excel Sheets

Step 1:

Select the data range of cell B4:C10 from the first worksheet.

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 2:

Right-click on the data range and select the Sort command.
From the sort options, choose the Custom Sort… command.

Handy Ways to Reconcile Data in 2 Excel Sheets

Step 3:

See the Sort dialogue box.
In the Sort by dialogue box, select Order ID.
Press OK.

Step 4:

See the first data set will be sorted in terms of Order ID.

Step 5:

Apply the Sort command to sort the second data set as well.

Step 6:

Create a side-by-side view for both worksheets.
Go to the View tab of the ribbon.
In the Window group, select the New Window command.

Step 7:

From the View tab, choose the Arrange All command.

Step 8:

See the Arrange Windows window.
Under the Arrange heading, select Vertical.
Press OK.

Step 9:

See the two worksheets side-by-side after this action.
Identify any similarities or dissimilarities between them after carefully going through the data sets.

Method 2 – Using COUNTIF Function to Reconcile Data in 2 Excel Sheets

Step 1:

Select the side-by-side view for the worksheets as shown in the previous method.
In cell B5 of our first worksheet, type the following formula for the COUNTIF function.

=COUNTIF(COUNTIF1!$B$5:$B$10,COUNTIF!B5)

We are selecting the data range from cell B5:B10 of the second worksheet for comparison.

Step 2:

Press Enter to get the result.
Use the AutoFill feature to drag the formula to the lower cells.
You will get 1 or 0 as a result.
1 indicates if the first data set has any matching value with the second one.
0 indicates no match of data.

Step 3:

Apply the following formula of the COUNTIF function in cell B5 of the second data set.

=COUNTIF(COUNTIF!$B$5:$B$10,COUNTIF1!B5)

Step 4:

Press Enter to see the result.
Use the AutoFill feature to get the results of the lower cells.

Step 5:

See the similarities or dissimilarities between the data sets automatically in the form of 1 or 0.

Method 3 – Matching Row Values to Reconcile Data in 2 Excel Sheets

Step 1:

To compare row values type the following formula in cell B5 of the first data set.

=B5='Row Values 1'!B5

Match each row value of the first and second data sheets.

Step 2:

Press Enter and drag the formula to the lower cells using AutoFill.
If any of the row values match with the same row values of the second data set it will show TRUE as a result.
It will show False.

Step 3:

In cell B5 of the second data set, write the following formula.

=B5='Row Values'!B5

Step 4:

Press Enter and drag the formula to the lower cells using AutoFill.

Step 5:

Compare or match the row values of the two data sets by following the above steps.

Method 4 – Utilizing Conditional Formatting to Reconcile Data

Step 1:

Set the side-by-side view for both worksheets.

Step 2:

In the first worksheet, select the data range B5:C10.
From the Home tab, select Conditional Formatting.
From the drop-down, select the New Rule option.

Step 3:

See the New Formatting Rule dialogue box.
Go to the Select a Rule Type heading.
Select the option named “Use a formula to determine which cells to format”.

Step 4:

Type the following formula in the Edit the Rule Description box.

=$D5=0

Choose the Format command.

Step 5:

From the Fill tab of the Format Cells dialogue box, choose any color of your preference.
Press OK.

Step 6:

The dialogue box from Step 4 will be back with all the editing.
Press OK.

Step 7:

Your first data set will look like the following picture after completing all the steps.
After formatting, the cells with 0 in them will be highlighted, meaning they do not match.