# How to Reconcile Data in Excel – 4 Methods

### Method 1. Finding Duplicate Values to Reconcile Data

To find the duplicate values and 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:

• 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

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
• VLOOKUP(B5,’Bank Statement’!B5:D13,3,FALSE)-D5 â†’ it becomes
• Â \$2,500 – D5
• Output â†’ \$0
• Press ENTER.

This is the output.

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

There are values greater than 0 in the Discrepancy column. Data in the Bank Statement and the Personal Record doesnâ€™t match.

Step 2 – Highlighting the Discrepancy

• Select the dataset and go to the Home tab.
• Select Conditional Formatting.
• Choose Highlight Cells Rules.
• Click Greater Than.

The Greater Than dialog box is displayed.

• Enter \$0 in Format cells that are GREATER THAN:.
• Click OK.

Values that are greater than 0 in the Discrepancy column are highlighted.

Practice here.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF