Want to learn how to know if a check is cleared or outstanding?
Read on.
Written By Kevin Mil - Author/Founder
How do I know if a check has cleared?
To determine if a check has cleared or is still outstanding, you have three options.
The first is manual checking, which involves reviewing your records by hand.
Another method is using the Vlookup function in a spreadsheet to automatically match transactions.
However, I recommend using a PIVOT table for this task.
PIVOT tables provide a quick and flexible way to summarize your check data, making it easy to see the status of each check.
Whichever approach you choose, staying on top of your outstanding checks is important for effective financial management.
While we refer to the first method as manual checking, the other two methods, VLOOKUP and Pivot Tables, also require some manual effort.
To use these features effectively, you must first prepare your data in a specific format.
This allows the VLOOKUP formula to find matches between tables and Pivot Tables to summarize data meaningfully.
What makes a check outstanding?
A check remains outstanding if it’s not cashed or encashed in a bank.
This means the check won’t appear in the holder’s bank statement, which is typically debited when a check is encashed.
Not accounting for this can lead to overdraft issues, so it’s essential to consider this valid deduction from the bank balance.
Here are the ways to know if a check is cleared or outstaning:
1.Manual Checking
Manually checking your records involves comparing them to your bank statement, looking for matching check numbers.
Go through your cash book and highlight or mark each check that appears in both your cash in bank subsidiary ledger and on the bank statement.
This helps you reconcile any discrepancies between your internal records and the official bank documentation.
Carefully reviewing each item one by one, you can identify any missing or erroneous entries that need to be corrected in your own logs or brought to the bank’s attention.
2.Using VLOOKUP
You can use the VLOOKUP function to determine if a check has cleared or is still outstanding.
Start by gathering all the checks issued from your cash book, focusing on the check numbers and amounts.
Next, collect the checks and amounts recorded by the bank in the bank statement.
The cleared checks will be listed on the debit side of the statement.
Remember, this is the process I mentioned when I told you how to prepare your working paper.
Your working paper should include a minimum of three columns for both the depositor’s record and the cleared checks from the bank statement.
The depositor’s record columns need to show the date, check numbers, and amounts.
Similarly, the cleared checks columns should list the dates, check numbers, and amounts.
This format will make it easy to compare the two records and identify any discrepancies.
You can now use the VLOOKUP function since you have prepared the working paper.
The formula above uses the reference column from the book area to search for values in the bank statement area.
When it finds a match, the difference is calculated by adding the values from column C and column F.
A difference of zero indicates the check has cleared, while a result of #N/A means the check is still outstanding.
The VLOOKUP makes it easy to identify which checks have been processed by the bank and which ones are pending.
3.Pivot Method
The PIVOT method is the most effective approach I’ve encountered.
It uses a single working paper, with the bank area positioned below the book area.
Align the dates, check numbers, and amounts vertically.
The bank amounts should be entered as negative values.
This layout provides a clear and efficient reconciliation process.
How to interpret the results.
A PIVOT table with the reference as the row and amount as values will appear in column H of your Excel sheet.
To determine if a check has cleared or is still outstanding, examine the PIVOT table.
Check number 100007 has cleared because its amount equals zero.
However, check numbers 100008 and 100012 remain outstanding since their amounts are not zero.
Check number 100014 may be an unrecorded check.
The PIVOT table method, method no. 3, is the best approach for determining whether a check has cleared or is still outstanding.
To get the most accurate results, it’s important to set up your worksheet correctly.
This method is my personal favorite because it doesn’t require expensive software.
With a well-structured PIVOT table, you can easily track the status of your checks and keep your financial records up to date.