**Reconciliation **is a very common term in accounting, especially in the banking sector. There are many ways in excel to do **reconciliation**. In this article, we will discuss 15 effective techniques to do **reconciliation** in excel.

**Table of Contents**hide

## Download Workbook

Download the practice file to practice it.

## What Is Reconciliation?

In easy words, **reconciliation** means the process to find dissimilarities between two datasets. In the case of bank reconciliation, it confirms that accounts in the general ledger are consistent, fair and complete. It is also used for business purposes for keeping records of payments, deposits and transactions. **Reconciliation** represents output based on a certain period of time.

**Reconciliation** can be manual, automated and periodic. Henceforth, **periodic reconciliation **is the most useful one because we use different types of excel functions which makes the calculation easier than others.

## 12 Effective Techniques to Do Reconciliation in Excel

Let us take an example for demonstration. Here the two datasets represent sales information for **June **and **July **of 8 products.

Now, we will do the reconciliation of these two datasets to find mismatches.

### 1. Use VLOOKUP Function to Do Reconciliation in Excel

**The VLOOKUP function** can be used as a reconciliation tool apart from getting the required information from the data table. Letâ€™s see the process below:

- In the beginning, insert the
**VLOOKUP**formula in**cell H5**.

`=VLOOKUP(E5,$B$5:$C$12,2,FALSE)`

Here, as we are extracting data from the 1st dataset, therefore the column number is** 2**. **FALSE** is given to get the exact match.

- Then, press
**Enter**. - After that, insert this formula in c
**ell I5**for the deduction.

`=F5-H5`

- Next, hit
**Enter**. - Now, use the
**AutoFill**tool to apply the same formulas for other values. - Finally, you can see that the variation between the two datasets is visible.

Here, the sales value of **cell F7** is greater than **cell C7**. Whereas, the value of **cell F10** is less than **cell C10**. That is why the result is shown inside a bracket.

### 2. Reconciliation with INDEX-MATCH Functions

The combination of **the INDEX-MATCH functions **is an efficient alternative to the **VLOOKUP** function. See the process below:

- First, insert the
**INDEX-MATCH**formula in**cell H5**.

`=INDEX($C$5:$C$12,MATCH(E5,$B$5:$B$12))`

Here, the** INDEX **function is used to get the value from the selected column based on the row number provided. Besides, the** MATCH** function is based on the **LOOKUP** value to give the row number.

- Then, press
**Enter**. - After that, insert this formula to get the deducted value in
**cell I5**.

`=F5-H5`

- Again, press
**Enter**. - Now, apply the similar process in
**cell range H6:I12**. - Finally, we got the reconciled result for the datasets.

**Note**: Both for

**VLOOKUP**and

**INDEX-MATCH**functions, if there is repetitive information with different values in the two datasets then the results will show only the first ones.

### 3. Apply Pivot Table to Do Reconciliation

The **Pivot Table** tool is more suitable if your dataset has some repetition in information and values. Follow the steps below to do the reconciliation:

- First, combine two datasets into one.
- Along with that, as our datasets are for two different months, make a new column for it.

- Next, go to the
**Insert**tab and select**Pivot Table**. - Here, choose the option
**From Table/Range**to apply the select**cell range B4:D20**.

- Now, in the new dialogue box, you can see the cell range is showing to create a pivot table.
- Therefore, press
**OK**.

- Following, a new
**Pivot Table Fields**panel appears. - Here, place the columns named
**Product**,**Month**and**Sales**inside the**Rows**,**Columns**and**Values**category respectively.

- After that, besides the new pivot table, insert this formula in
**cell F5**.

`=B5-C5`

**Note**: You have to insert the cell number manually because if you click any cell for reference, it will show

**the GETPIVOTDATA function**which will not allow you to drag the formula all over the worksheet.

- Now, hit
**Enter**. - Finally, you can see the reconciliation product.
- Lastly, use the
**AutoFill**tool to apply the same formula for all the data series.

### 4. Apply Conditional Formatting to Do Reconciliation

In this section, we will apply **Conditional Formatting** to do reconciliation in excel. Letâ€™s see how it works for our dataset.

- In the beginning, select the
**cell range B5:F12**.

- After that, go to the
**Home**tab and click on**Conditional Formatting**.

- Then, select
**Highlight Cells Rules**and choose**Duplicate Value**from its drop-down options.

- Afterward, select the condition
**Unique**and press**OK**.

- Therefore, you can see the different values are marked.

### 5. Insert MATCH Function for Reconciliation

The **MATCH **function will show the results whether it is **True **or **False**, not in values like before. Here are the quick steps to perform this:

- First, insert this formula in
**cell H5**.

`=ISNUMBER(MATCH(F5:F12,$C$5:$C$12,0))`

Here, **the ISNUMBER function **denotes the value, while the **MATCH** function compares dynamic arrays between two datasets. Lastly, insert **0** for the exact match.

- Then, press
**Enter**.

- Thatâ€™s it, you will get all the results automatically at once for the datasets.

### 6. Reconcile with XMATCH Function in Excel

**The XMATCH function** is similar to the** MATCH **function as it returns a relative position in a list. Letâ€™s see the process below:

- First, insert this formula in
**cell H5**.

`=NOT(ISNUMBER(XMATCH(F5:F12,C5:C12)))`

Here, The **ISNUMBER** function returns **TRUE** when a cell contains a number, and **FALSE** if not. This is why** the NOT function** is used at the beginning to get an exact match. The **XMATCH** function searches for the specific item among the selected cells.

- Then, press
**Enter**. - Finally, you will get the reconciliation output for all the values at once.

### 7. Use IF Function for Reconciliation

In this section, we will use** the IF function **for reconciliation in excel.

- First, insert this formula in
**cell****H5**.

`=IF(C5=F5,"TRUE","FALSE")`

Here, the **IF** function compares the selected cells on the condition of true or false.

- Then, hit
**Enter**. - Lastly, use
**AutoFill**to drag the formula in**cell range H6:H12**.

### 8. Apply COUNTIF Function to Do Reconciliation in Excel

You can also use **the COUNTIF function** to do reconciliation in excel. Just follow these simple steps:

- First, insert this formula in
**cell H5**.

`=IF(COUNTIF(C5:C12,F5:F12)<>0,"True","False")`

Here, the **COUNTIF **function counts the number of times a value, or text is contained within a range.

- Then, hit
**Enter**. - Finally, it will show up all the reconciliation output at once.

### 9. Reconcile Datasets with XLOOKUP Function

The reconciliation process with **the XLOOKUP function **is quite similar to the **VLOOKUP** with the only difference in the output illustration. Letâ€™s see the short steps below:

- First, insert this formula in
**cell H5**.

`=ISTEXT(XLOOKUP(E5,$B$5:$B$12,$B$5:$B$12))`

Here, the **XLOOKUP** will return a corresponding value from a cell, and you can define a result if the value is not found. **The ISTEXT function** will fetch the texts from the dataset.

- Next, press
**Enter**. - Afterward, use the
**AutoFill**tool to drag the formula in**cell range H6:H12**. - Thatâ€™s it, we have got our required output.

### 10. Comparing Rows for Reconciliation of Datasets in Excel

This is one of the simplest methods for reconciliation. Here, we will compare two datasets by rows. Check the steps below:

- In the beginning, put this formula in
**cell H5**.

`=C5=F5`

- Afterward, hit
**Enter**. - Then, drag the bottom corner of
**cell H5**up to**cell H12**. - Finally, you can see the compared results.

### 11. Apply New Window Option for Reconciliation in Excel

In this last method, we will discuss applying the **New Window** option in the same workbook in excel for reconciliation. Here is the process to perform:

- In the beginning, go to the
**View**tab in worksheet**Dataset 1**. - Here, choose
**New Window**.

- After that, you will get a copy of the similar workbook.
- Here, go to the
**Dataset 2**worksheet.

- Now, on
**Dataset 1**worksheet, go to the**Home**tab again and select**Arrange All**.

- Then, on the new
**Arrange Windows**dialogue box, select**Vertical**. - Afterward, press
**OK**.

- Finally, you can view the worksheets simultaneously and highlight differences among them.

### 12. Use View Side By Side Tool to Reconcile Two Workbooks

So far, we have discussed the techniques to do reconciliation in excel in a single worksheet. Now, we will illustrate the process to match data between two workbooks. Follow the steps below:

- In the beginning, go to the
**View**tab. - Then, select
**View Side by Side**under the**Window**group.

- Right after that, you will see the other workbook in parallel.

- Now, you can compare the datasets and highlight the differences like this:

**Note**: You must keep both workbooks open for this process. Otherwise, the

**View Side by Side**will be

**grayed**and inaccessible.

## Conclusion

I hope it was a useful article for you on how to do reconciliation in excel with 12 effective techniques. Let us know if you have any more methods for this. Follow ExcelDemy for more excel blogs.