How to Reconcile Vendor Statements in Excel (2 Easy Methods)

If you want to know how to reconcile vendor statements in Excel, this article is for you. Here, we will walk you through 2 easy and effective methods to do the task smoothly.

What Is Vendor Statement Reconciliation?

A report from a vendor’s accounting record consisting of all unpaid bills at a particular date is known as a vendor statement. And Vendor statement reconciliation is the procedure of resolving all payments and invoices with the vendors and distributors.

Vendor statement reconciliation allows a business to assure no obscurity or fault between what the vendor has demanded and the supplies or services gained by the organization.

How to Reconcile Vendor Statements in Excel: 2 Easy Methods

The following Vendor’s Details table has Invoice No., Date, and Amount columns. The Amount column shows the vendor’s demand for a particular invoice.

Next, the following Company’s Details table shows Invoice No., Date, and Amount columns. Here, the Amount column is showing what the vendor has gained from the company for a particular invoice.

You can easily notice that for a number of invoices, there is a difference between what the vendor has demanded and what the vendor has gained from the company. Therefore, the vendor statement needs reconciliation.

In the following article, we will demonstrate to you 2 easy methods to reconcile vendor statements in Excel. Here, we used Microsoft Office 365 to do the task. You can use any available Excel version.

1. Using Pivot Table to Reconcile Vendor Statement in Excel

In this method, we will use a Pivot Table to reconcile vendor statements in Excel.

Let’s go through the following steps to do the task.

Step-1: Making Dataset

In this step, we will make a dataset with data of both the Vendor’s Details and Company’s Details.

First of all, we will select the entire dataset of the Vendor’s Details table.

Here, you can select the entire dataset by clicking on cell B4 and pressing CTRL+SHIFT+END.

After that, we will right-click on it and select Copy from the Context Menu.

Here, you can copy the dataset by pressing CTRL+C.

Afterward, we will paste the copied cell into another worksheet.

Here, to paste the copied cell we click on cell B4 and press CTRL+V.

Therefore, you can see the Vendor’s Details in the following picture.

At this point, we add a Type column in the dataset.

Along with that, in the Type column, we typed Vendor’s Details in cell E5.
In addition, we will drag down the cell content of cell E5 with the Fill Handle tool.

As a result, you can see Vendor’s Details in the following dataset.

Next, we will add Company’s Details to the same dataset.

To do so, we will copy the data from the Company’s Details table.

Here, we must exclude the heading from copying.

Hence, we selected cells B5:D11 >> right-click on it.
Moreover, we will select Copy from the Context Menu.

Here, you can copy the dataset by pressing CTRL+C.

Next, we will go back to the dataset that we are making to insert Pivot Table.

After that, click on cell B12 and press CTRL+V.

As a result, you can see the Company’s Details data in the dataset.

Afterward, we will add Company’s Details in the Type column.

To do so, we will type Company’s Details in cell E12.
In addition, we will drag down the cell content of cell E12 with the Fill Handle tool.

As a result, you can see a complete dataset. The dataset contains data on both the Vendor’s Details and Company’s Details.

Step-2: Inserting Pivot Table

In this step, using the following dataset, we will insert a Pivot Table.

In the beginning, we will select the entire dataset >> go to the Insert tab.
After that, from Pivot Table group >> select From Table/Range.

Next, a PivotTable from table or range dialog box will appear.

After that, make sure New Worksheet is marked.

Then, click OK.

Afterward, PivotTable Fields will appear in a new worksheet.

At this point, we will mark Invoice No., and drag it down to the Rows group.
Along with that, we will mark Amount, and drag it down to the Values group.
In addition, we will mark Type, and drag it down to the Columns group.

Therefore, you can see the created Pivot Table.

Next, we want to remove the Grand Total column from the Pivot Table.

To do so, we will select the Grand Total column >> go to the Design tab.
Afterward, from Grand Totals >> select Off Rows and Columns.

Therefore, you can see no Grand Total column in the Pivot Table.

Next, we will find the difference between Company’s Details and the Vendor’s Details.

Therefore, we add a heading named Difference in the Pivot Table.

Afterward, we will type the following formula in cell D5.

=D5-C5

This will simply find out the difference between vendor demand and vendor gain.

Afterward, press ENTER.

Then, you can see the result in cell D5.

Next, we will drag down the formula with the Fill Handle tool.

As a result, you can see the complete Difference column.

Therefore, you can easily find out the difference between the vendor’s demand and what the vendor has gained.

Afterward, to make the Pivot Table more presentable, we remove the gridlines from the worksheet, and we inserted a column at A.

As a result, you can see the reconciliation vendor statement.

2. Applying VLOOKUP Function to Reconcile Vendor Statement in Excel

The following dataset has Invoice No., Date, and Vendor’s Demand columns. Along with that, it has an incomplete Vendor’s Gain and Difference column. Here, we will use the VLOOKUP function to complete the Vendor’s Gain column. After that, we will find out the difference between Vendor’s Demand and the Vendor’s Gain. as a result, we will be able to reconcile vendor statements in Excel.

Here, the VLOOKUP function needs a table_array to look up a value. We will use our Company’s Detail table as the table_array.

Let’s go through the following steps to do the task.

First of all, we will type the following formula in cell E5.

=VLOOKUP(B5,'Company''s Detail'!$B$4:$D$11,3,FALSE)

Formula Breakdown

VLOOKUP(B5,’Company”s Detail’!$B$4:$D$11,3,FALSE) → the VLOOKUP function looks for values in a fixed column index of a table array.
B5 → is the lookup_value.
‘Company”s Detail’!$B$4:$D$11 → is the table_array.
3 → is the column_index_number.
FALSE → indicates the Exact match.
- Output: $18200
- Explanation: $18200 is the Vendor’s Gain for Invoice No. INV/2022/101.

Afterward, press ENTER.

As a result, you can see the result in cell E5.

Furthermore, we will drag down the formula with the Fill Handle tool.

Therefore, you can see the complete Vendor’s Gain column.

After that, we will type the following formula in cell F5.

=D5-E5

This will simply subtract Vendor’s Gain from Vendor’s Demand.

At this point, press ENTER.

As a result, you can see the result in cell F5.

Moreover, we will drag down the formula with the Fill Handle tool.

Hence, you can see the complete Difference column.

Therefore, we reconcile the vendor statements.

Practice Section

You can download the above Excel file to practice the explained methods.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.

Reconcile Vendor Statements.xlsx

Conclusion

Here, we tried to show you 2 easy methods to reconcile vendor statements in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.

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

Get FREE Advanced Excel Exercises with Solutions!