VLOOKUP As a CONTROL
When closing for the month, many use an Excel workbook to reconcile balances to the general ledger. As the general ledger changes, do you have a process in place to easily determine that balances previously reconciled still tie to the general ledger? If not, using Vlookup on each reconciliation worksheet provides a quick and easy way to accomplish this control.
Usually, the month-end balance from a reconciliation worksheet is at the bottom of the worksheet. Below this balance, you can enter the associated general ledger account number to be used in a Vlookup formula. After the account number and Vlookup formula have been entered in each workbook, all you need to do is cut and paste the most recent general ledger balances into a separate worksheet. The Vlookup will display the most recent balance and you can use conditional formatting to highlight whether the two balances are within a tolerable error limit.
The example below assumes that the AR Trial Balance has been created and the total needs to be compared to the general ledger. There is a worksheet labeled “Accounts Receivable” and a separate worksheet labeled “General ledger”.
The general ledger account for accounts receivable has been entered at the bottom of the Accounts Receivable worksheet and a Vlookup formula is used to compare this total to the amount in the General Ledger worksheet.
The Vlookup function is searching for a worksheet titled “General ledger” in the same workbook. The key to making this work is that you never delete this worksheet. When the general ledger changes, simply paste the new general ledger data over the old data in the same worksheet.
For Vlookup to work, the lookup value, in this case the general ledger account number, must be in the leftmost column of the General Ledger worksheet (In this example, column A). The general ledger data is in columns A and B and the account balance is in column 2 (which is the B column). The FALSE range lookup value is used to indicate an exact match. The example general ledger is shown below.
Why use the entire columns of A and B? That way, if general ledger accounts are added, we’re not constrained by a limited range. This is important as we want to paste over this data again and again without using new Vlookup formulas each time.
To calculate any differences, subtract the general ledger balance from the reconciliation balance. Use the ABS function in the formula since we just want to know the absolute amount of a difference and do not need to know whether it is positive or negative (ABS provides the absolute value of a number).
In this case, the difference is 0. However, we want a way to highlight a difference if it exceeds a tolerable error limit. Assume that we want to highlight any change greater than $100 and, if the difference is greater than $100, highlight the cell in yellow.
Select cell B13 and then select the Conditional Formatting button on the Excel Home ribbon. From the Conditional Formatting options, select “Highlight Cell Rules”, then “Greater Than”.
Once “Greater Than” is selected, you have the opportunity to input both an amount and highlight colors. In this example, our amount is greater than 100 and we’ll use yellow fill with a yellow text to highlight the difference. Note that we used an absolute value since the difference could be either greater than 100 or less than 100, depending on whether the general ledger amount was more or less than then the reconciliation amount. Having only one value makes the Conditional Formatting comparison much easier.
Press OK.
Since the difference is not currently greater than 100, there should be no yellow highlight. If the general ledger balance is changed to 31,500 (a difference that is greater than 100), the difference is now highlighted.
With this Vlookup control in place for each month-end reconciliation worksheet, you can copy and paste the new general ledger data into the General Ledger worksheet and the new balances will update. Any differences that exceed a tolerable error amount will be highlighted and you can simply scan each worksheet to determine if a difference exists.
This process is much easier to implement if all month-end account reconciliations are in one workbook. That way, the general ledger data only needs to be pasted once in the General Ledger worksheet each time the general ledger is updated.
CPASelfStudy has several in-depth courses on Vlookup and Conditional Formatting. If you want more information or other productivity ideas, please check out our Excel course offerings.