How to Reconcile Data in Excel - 4 Methods

Method 1. Finding Duplicate Values to Reconcile Data

Use the IF function and the COUNTIF function.

To find the duplicate values and reconcile data:

how to reconcile data in excel Finding Duplicate Values to Reconcile Data

Step 1 – Finding the Duplicates

Create an additional column: Duplicate Check.

Enter the following formula in F5.

=IF(COUNTIF($B$5:$B$14,B5)>1,"Duplicate","Unique")

B5 represents the Order ID and $B$5:$B$14 indicates the array of the Order ID.

Formula Breakdown

COUNTIF($B$5:$B$14,B5) → returns the number of occurrences of an item in a range. Here, $B$5:$B$14 is the range argument, and B5 is the criteria argument.
- Output → 1
IF(COUNTIF($B$5:$B$14,B5)>1,”Duplicate”,”Unique”) → if the output of the COUNTIF function is larger than 1, it is a Duplicate value. Otherwise, it is a Unique value.

Press ENTER.

Note: An absolute cell reference is used.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

Step 2 – Using Conditional Formatting to Highlight the Duplicates

Select the dataset and go to the Home tab.
Click Conditional Formatting.
Choose New Rule.

The New Formatting Rule dialog box will be displayed:

Click Use a formula to determine which cells to format.
Enter the following formula in Format values where this formula is true:.

=$F5="Duplicate"

F5 refers to the Duplicate Check column.

Click Format and choose a formatting.

Click OK.

The Duplicate values are highlighted:

Read More: How to Do Reconciliation in Excel

Method 2 – Using the Table Format to Reconcile Data in Excel

Steps:

Follow the steps described in the previous method to see the following output.

Select the dataset and press CTRL + T.

The Create Table dialog box will be displayed.

Check My table has headers.
Click OK.

Your data will be converted into a table:

Click the drop-down icon beside the Duplicate Check column.
Uncheck Unique.
Click OK.

Only the duplicate values will be displayed.

Method 3 – Using Excel Functions to Reconcile Data

Use the MATCH function, the IF function, the XMATCH function, the VLOOKUP function, the XLOOKUP function, etc.

The following dataset showcases System Data and Store Data of prices in a restaurant. To check if there is any mismatch in the prices between System Data and Store Data:

3.1 Utilizing the MATCH Function

Steps:

Create an extra column (Check).

Enter the formula below in H6.

=ISNUMBER(MATCH(F6,C6,0))

F6 is the Price in Store Data and C6 is the Price in System Data.

Formula Breakdown

MATCH(F6,C6,0) → returns the relative position of an item in an array that matches a supplied value in a defined order. Here, F6 is the lookup_value argument, C6 is the lookup_array argument, and 0 indicates the match_type argument.
- Output → 1
=ISNUMBER(MATCH(F6,C6,0)) → it becomes
- =ISNUMBER(1) → The ISNUMBER function checks whether a cell value is a number or not and returns TRUE or FALSE as output.
- Output → TRUE.

Press ENTER.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

The Price of Nachos doesn’t match in System Data and in Store Data. The Check column returns FALSE.

3.2 Using the IF Function

Steps:

Enter the formula below in H5.

=IF(F6=C6,"Correct","Incorrect")

Press ENTER.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

The Price of Nachos doesn’t match in System Data and in Store Data. The Check column returns Incorrect.

Method 4 – Reconciling Data for Bank Statements

To reconcile data in Excel for bank statements:

The following dataset showcases a Bank Statement of a company with amounts paid to different Clients. The company also keeps a Personal Record. To find if there is any Discrepancy between these two sets of data.

Step 1 – Finding the Discrepancy

In the Personal Record worksheet, create an additional column named Discrepancy.

Enter the formula below in E5.

=VLOOKUP(B5,'Bank Statement'!B5:D13,3,FALSE)-D5

B5 refers to the Transaction ID, D5 represents the Amount Paid, and B5:D13 denotes the array in Bank Statement.

Formula Breakdown

VLOOKUP(B5,’Bank Statement’!B5:D13,3,FALSE) → checks for a value in the left-most column and returns a value from a specified column in the same row. B5 is the lookup_value argument, ‘Bank Statement’!B5:D13 is the table_array argument, 3 is the column_index_number argument, and FALSE indicates an exact match.
- Output → $2,500