reconciliation formula excel

Mastering Reconciliation: Your Ultimate Excel Formula Guide

By Robert Garcia
AD

Are you struggling to reconcile differences between two sets of data in Excel? You're not alone. Excel provides a built-in tool called the reconciliation formula to help you efficiently identify and resolve discrepancies between two data ranges. Let's delve into the world of Excel reconciliation formulas and learn how to make the most of this powerful feature.

Before we dive into the specifics, let's clarify what we mean by 'reconciliation.' In the context of Excel, reconciliation refers to the process of comparing two sets of data and highlighting the differences between them. This could involve identifying differences in values, dates, or even the order of data. Once the differences are identified, you can reconcile them by updating one set of data to match the other, or vise versa.

The POINTSTO and POINTSTOERROR Functions

The heart of Excel's reconciliation formula lies in two functions: POINTSTO and POINTSTOERROR. These functions help you pinpoint the differences between two ranges of data. Let's explore each function in detail, starting with the more basic of the two, POINTSTO.

AD
a table with the bank recondition statement for march, 2013 and month ends
a table with the bank recondition statement for march, 2013 and month ends
Excel Reconciliation Formula (for Data, Vendor, Bank) - ExcelDemy
Excel Reconciliation Formula (for Data, Vendor, Bank) - ExcelDemy
10+ Accounts Reconciliation Template | Template Business PSD, Excel, Word, PDF
10+ Accounts Reconciliation Template | Template Business PSD, Excel, Word, PDF

POINTSTO compares two ranges of data and returns the points at which the data differs. It's a useful function for identifying the discrepancies between two sets of data. So, if you're looking to find out where your sales figures don't match up, POINTSTO can help you identify those problematic cells.

Syntax and Arguments

The syntax for the POINTSTO function is straightforward. It takes two arguments: the first range ( Arrays1 ), and the second range ( Arrays2 ). Here's the general syntax:

How to Make a Vendor Ledger Reconciliation Format in Excel - ExcelDemy
How to Make a Vendor Ledger Reconciliation Format in Excel - ExcelDemy
What formulas can be used in Excel for an automated bank reconciliation template?
What formulas can be used in Excel for an automated bank reconciliation template?
How to Do a GST Reconciliation in Excel (4 Methods) - ExcelDemy
How to Do a GST Reconciliation in Excel (4 Methods) - ExcelDemy

POINTSTO(Arrays1, Arrays2)

Example Use

Let's say you have two ranges of data, A1:A10 and B1:B10. You want to find out where they don't match. You can use the POINTSTO function like this:

AD

POINTSTO(A1:A10, B1:B10)

a printable balance sheet for cash and other items to be used in the bank account
a printable balance sheet for cash and other items to be used in the bank account
Sales Reconciliation Template
Sales Reconciliation Template
Excel Reconciliation Process using vlookup
Excel Reconciliation Process using vlookup
General Ledger (Gl) Reconciliation Template in Excel, Google Sheets - Download | Template.net
General Ledger (Gl) Reconciliation Template in Excel, Google Sheets - Download | Template.net
Ledger Reconciliation Template, Excel Google Sheets, Account Reconciliation Spreadsheet, General Ledger Reconciliation, Bookkeeping Sheet
Ledger Reconciliation Template, Excel Google Sheets, Account Reconciliation Spreadsheet, General Ledger Reconciliation, Bookkeeping Sheet

This will return an array of points where the data in A1:A10 doesn't match the data in B1:B10. For each non-matching point, it will return both the cell reference and the mismatch value.

The POINTSTOERROR Function

POINTSTOERROR is a more advanced function that not only identifies the mismatches but also calculates the error value. It's particularly useful when you want to reconcile data that should be identical (e.g., two sets of data from the same source but at different times).

AD

POINTSTOERROR works similarly to POINTSTO, but it has an additional argument for the expected values. It compares Arrays1 and Arrays2 and returns the points at which they differ, along with the difference (error) between the expected and actual values.

Syntax and Arguments

The syntax for the POINTSTOERROR function is as follows:

POINTSTOERROR(Arrays1, Arrays2, Arraysexpected)

AD

Example Use

Let's say you have two ranges of data, C1:C10 (current sales) and D1:D10 (expected sales). You want to find where the current sales don't meet your expectations. You can use the POINTSTOERROR function like this:

POINTSTOERROR(C1:C10, D1:D10, D1:D10)

This will return an array of points where the current sales differ from the expected sales, along with the difference in sales. For each non-matching point, it will return both the cell reference and the sales difference.

Now that you know how to use Excel's reconciliation formulas, you're well on your way to streamlining your data reconciliation tasks. Whether you're a seasoned Excel user or just starting out, these functions can save you time and help you maintain accurate data. So, the next time you're faced with a reconciliation challenge, don't despair – reach for Excel's reconciliation formulas, and watch as your data comes into line. Happy reconciling!

AD