How to Reconcile Data in Excel (4 Easy Ways)

We often need to reconcile data while working in Microsoft Excel. Doing it manually can be a long tiresome and time-consuming task. But here comes Excel with some delicate ways to reconcile data. In this article, we will demonstrate 4 simple methods to reconcile data in Excel.

How to Reconcile Data in Excel: 4 Easy Ways

In this section of the article, we are going to learn the detailed steps of the 4 methods, to reconcile data in Excel.

Not to mention that we have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.

1. Finding Duplicate Values to Reconcile Data

Finding duplicate values is an efficient way to reconcile data in Excel. In the following dataset, we have some order information of a tech store. But there are some duplicate entries. Here, we will use the IF function and the COUNTIF function. So, let’s find these duplicate values and reconcile our data by following the steps mentioned below.

Step 01: Finding the Duplicates

• Firstly, create an additional column named Duplicate Check as shown in the following picture.

• Following that, enter the following formula in cell F5.

Here, cell B5 represents the cell of the Order ID, the range $B$5:$B$14 indicates the array of the Order ID.

• After that, press ENTER.

Note: Here, we have used absolute cell reference for selecting the range in the COUNTIF function ($B$5:$B$14). Because we need to search in a fixed range for each criteria cell.

Subsequently, you will see the following output on your worksheet as marked in the image given below.

• Now, by using the AutoFill feature of Excel, we can get the rest of the outputs.

Step 02: Using Conditional Formatting to Highlight the Duplicates

• Firstly, select the dataset and then go to the Home tab from the ribbon.
• After that, click on the Conditional Formatting option.
• Next, choose New Rule from the drop-down.

Afterward, the New Formatting Rule dialogue box will be open as shown in the following picture.

• Now, from the New Formatting Rule dialogue box, click on Use a formula to determine which cells to format.
• Following that, enter the following formula in the Format values where this formula is true: box.

Here, cell F5 refers to the cell of the Duplicate Check column.

• Then, click on the Format option and choose your preferred formatting.

• When you are happy with your formatting select OK.

Consequently, you will see that the Duplicate values are highlighted according to your formatting like in the following image.

Read More: How to Do Reconciliation in Excel

2. Using Table Format to Reconcile Data in Excel

Using Table Format is another effective method to reconcile data in Excel. While dealing with large datasets, we can drastically reduce the size of the table by choosing only the duplicate values. To do this let’s follow the steps discussed below.

Steps:

• Firstly, follow the steps mentioned in the previous method to obtain the following output.

• After that, select the dataset and press CTRL + T.

Consequently, Create Table dialogue box will open on your worksheet.

• Make sure to check the box of My table has headers.
• Then, click on OK.

As a result, your data will be converted into a table like in the image given below.

• Now, click on the drop-down icon beside the Duplicate Check column.
• Subsequently, uncheck the box of Unique from the drop-down.
• Following that, click on OK.

Consequently, only the duplicate values will be shown in your table.

Read More: How to Reconcile Two Sets of Data in Excel

3. Employing Excel Functions to Reconcile Data

To reconcile data in Excel, we can also use various Excel Functions. Such as the MATCH function, the IF function, the XMATCH function, the VLOOKUP function, the XLOOKUP function, etc.

In the following dataset, we have System Data and Store Data of prices of a restaurant. We will check if there is any mismatch in the prices between the System Data and the Store Data.

3.1 Utilizing MATCH Function

Firstly, we will use the MATCH function to reconcile data in Excel. Let’s follow the steps given below.

Steps:

• Firstly, create an extra column named Check as shown in the image given below.

• Following that, insert the formula given below in cell H6.

Here, F6 is the Price from Store Data and C6 indicates the Price from System Data.

• Now, hit ENTER.

Consequently, you will see the following output on your screen.

• Now, by dragging the Fill Handle, you can get the remaining outputs as shown in the image given below.

Here, you can see that the Price of Nachos doesn’t match the System Data and the Store Data. That is why the Check column returns FALSE.

3.2 Using IF Function

Using the IF function is one of the easiest ways to reconcile data in Excel. Let’s discuss the steps to do it.

Steps:

• Firstly, navigate to cell H5 and enter the following formula.

• After that, press ENTER.

As a result, you will be able to see the following output.

• Then, drag the Fill Handle to get the rest of the outputs as marked in the following picture.

Here, you can see that the Price of Nachos doesn’t match the System Data and the Store Data. That is why the Check column returns Incorrect.

Follow these steps to reconcile data for the other functions

4. Reconciling Data for Bank Statements

In this portion of the article, we will learn to reconcile data in Excel for bank statements.

Here, in the following dataset, we have the Bank Statement of a company with various amounts of money paid to different Clients. The company also keeps a Personal Record. Our aim is to find if there is any Discrepancy between these two sets of data.

Step 01: Finding the Discrepancy

• Firstly, in the Personal Record worksheet, create an additional column named Discrepancy.

• Following that, enter the formula given below in cell E5.

Here cell B5 refers to the cell of Transaction ID, cell D5 represents the cell of the Amount Paid column, and range B5:D13 denotes the array of Bank Statement.

• Now, press ENTER.

After that, you will see the following output as marked in the image given below.

• Then, you can obtain the rest of the data by using the AutoFill option of Excel.

Here, the values that are larger than 0 in the Discrepancy column are creating concerns. These data don’t match the Bank Statement and the Personal Record.

Step 02: Highlighting the Discrepancy

• Firstly, select the dataset and go to the Home tab.
• Afterward, select the Conditional Formatting option.
• Next, choose Highlight Cells Rules from the drop-down.
• Following that, click on the Greater Than option.

As a result, Greater Than dialogue box will open like the following picture.

• Subsequently, enter $0 in the marked box of the below-given image.
• Finally, click OK.

Consequently, the values that are greater than 0 in the Discrepancy column will be highlighted.

Read More: Automation of Bank Reconciliation with Excel Macros

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of each worksheet. Please practice these by yourself.

Download Practice Workbook

Conclusion

Finally, we have to the end of the article. I sincerely hope that this article was able to guide you to reconcile data in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. Happy learning!

Related Articles

• How to Do Intercompany Reconciliation in Excel
• How to Reconcile Vendor Statements in Excel
• How to Reconcile Data in 2 Excel Sheets
• How to Perform Bank Reconciliation Using VLOOKUP in Excel
• How to Reconcile Credit Card Statements in Excel

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