Sowmik Chowdhury
Bank statements are one of the most necessary documents in our day-to-day life. Financial institutions need to prepare organized bank statements on a regular basis. When you have a tool like Microsoft Excel, you can easily organize a bank statement with a large set of data within a very short time. This article demonstrates how to organize bank statements in Excel with proper explanation.
Download Practice Workbook
You can download the practice workbook from the link below.
What Is a Bank Statement?
A bank statement is a record of all the transactions during a specific period of time and their summary is documented and provided by the financial institution. In other words, a bank statement contains information like account details, personnel details, statement period, account activity summary, transaction history, etc.
Making Bank Statements Outline Using Data Organization Guidelines in Excel
The very first step for preparing an organized Bank Statement is to make an organized outline for a Bank Statement using Data Organization Guidelines provided by Microsoft. Now, follow the statements below to create an outline for an organized Bank Statement.
- In the following screenshot, I have shown you an outline for a Bank Statement.
- Also, I have included information like account details, personnel details, statement period, account activity summary, and transaction history in the outline.
- Finally, follow the given example and make an outline of a Bank Statement on your own according to your preference.
2 Methods to Organize Bank Statements in Excel
Now, let’s assume you have a Bank Statement where you have all the required data inserted. However, in this case, you don’t have the transaction history data organized. So, I will show you two methods to organize a Bank Statement.
1. Organizing Bank Statements According to Date of Transaction in Excel
Organizing a Bank Statement according to the Date of Transaction in Excel is one of the most regular tasks. If you want to organize a Bank Statement according to the Date of Transaction, you can follow the steps below.
Steps:
- First, select the cell range you want to organize. In this case, I selected range B16:E21.
Here, cells B16 and E21 are the first and last cells of the columns Date and Withdrawal respectively.
- Second, go to the Data tab from the top of the page.
- Third, click on Sort as shown in the screenshot below.
- Fourth, click on the Down-Arrow beside the Sort by option.
- Fifth, select Date from the drop-down options.
- Lastly, click OK and you will get your organized Bank Statement.
- Finally, the Bank Statement will look like the screenshot below.
💡Notes:
- If you want to organize your Bank Statement according to the Deposit amount, you can select Deposit from the Sort by options and follow the other steps to do so.
- Also, if you want to organize your Bank Statement according to the Withdrawal amount, you can select Withdrawal from the Sort by options and follow the other steps to do so.
- Moreover, in the case of sorting, avoid selecting cells that include formulas.
Read More: How to Organize Data for Analysis in Excel (5 Useful Methods)
2. Showing Deposits First Organized According to Date of Transaction in Excel
Now, suppose you have to organize a Bank Statement in such a way that you get the Deposit amounts first and then you sort all of them by Date of Transaction. At this point, if you wish to do so, you may follow the steps below.
Steps:
- At the very beginning, select the cell range B16:E21.
- Next, go to the Data tab from the top of the page.
- After that, click on Sort as shown in the screenshot below.
- Consequently, click on the Down-arrow beside the Sort by.
- Now, select Withdrawal from the Sort by options.
- Then, make sure you have the Order from Smallest to Largest.
- Next, click on Add Level.
- After that, click on the Down-Arrow, beside Then by.
- Next, select Date from the Then by option.
- Finally, click on OK.
- As a result of all the above steps, you will have an output like the following screenshot.
💡Notes:
- Now, if you want to show the Withdrawals first organized according to their Date of Transaction in your Bank Statement, select Deposit instead of Withdrawal from the Sort by option.
- Also, make sure you have the Order in Smallest to Largest format.
- Moreover, in the case of sorting, avoid selecting cells that include formulas.
Read More: How to Organize Information in Excel (2 Circumstances)
Conclusion
In this article, I have shown you two methods to organize a bank statement, an outline for a bank statement, and also provided a template for the bank statement. So, you can download the Template and utilize it if you want. Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.
