Sanjida Mehrun Guria
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.
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 cell 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.
Read More: How to Reconcile Two Sets of Data in Excel (9 Simple Ways)
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.
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
- 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.
Read More: How to Make a Vendor Ledger Reconciliation Format in Excel
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:
Read More: How to Reconcile Data in 2 Excel Sheets (4 Ways)
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.
