Get FREE Advanced Excel Exercises with Solutions!

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**.

**Table of Contents**hide

## Download Practice Workbook

## 4 Ways to Reconcile Data in Excel

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**.

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

Here, cell **B5 **represents the cell of the **Order ID**, the range **$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 theargument, and*range***B5**is the*criteria***Output → 1**

**IF(COUNTIF($B$5:$B$14,B5)>1,”Duplicate”,”Unique”)**→ therefore, when the output of the**COUNTIF**function is**larger than 1**, it means it is a**Duplicate**value. Else, it is a**Unique**value.

- 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.

`=$F5="Duplicate"`

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 (12 Effective Techniques)**

### 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 (9 Simple Ways)**

### 3. Employing Excel Functions to Reconcile Data

To **reconcile data in Exce**l, 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**.

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

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

*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 theargument,*lookup_value***C6**is theargument, and*lookup_array***0**indicates theargument.*match_type***Output → 1**

**=ISNUMBER(MATCH(F6,C6,0))**→ it becomes**=ISNUMBER(1)**→ the**ISNUMBER function**will check whether a cell value is a number or not and returns**TRUE**or**FALSE**as output.**Output → TRUE**.

- 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.

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

- 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**.

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

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**.

**Formula Breakdown**

**VLOOKUP(B5,’Bank Statement’!B5:D13,3,FALSE)**→ it checks for a value in the table’s left-most column and then returns a value from a column you specify in the same row. Here,**B5**is theargument,*lookup_value***‘Bank Statement’!B5:D13**is theargument,*table_array***3**is theargument, and*column_index_number***FALSE**means that we are looking for an**exact match**.**Output → $2,500**

**VLOOKUP(B5,’Bank Statement’!B5:D13,3,FALSE)-D5**→ it becomes**$2,500 – D5****Output → $0**

- 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 Rule**s 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.

## 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. To learn more about Excel, you can visit our website, **ExcelDemy**. Happy learning!