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

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Reconcile Vendor Statements in Excel

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.

How to Reconcile Vendor Statements in Excel

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.

How to Reconcile Vendor Statements in Excel

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.

How to Reconcile Vendor Statements in Excel

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.

How to Reconcile Vendor Statements in Excel

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.

How to Reconcile Vendor Statements in Excel

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.

How to Reconcile Vendor Statements in Excel

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.

How to Reconcile Vendor Statements in Excel

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.

How to Reconcile Vendor Statements in Excel

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

Read More: How to Do Intercompany Reconciliation in Excel


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.

How 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)

How to Reconcile Vendor Statements in Excel

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.

How to Reconcile Vendor Statements in Excel

Hence, you can see the complete Difference column.

Therefore, we reconcile the vendor statements.

Read More: How to Perform Bank Reconciliation Using VLOOKUP in Excel


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.


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo