How to Organize Bank Statements in Excel (2 Handy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.


What Is a Bank Statement?

A bank statement is a record of all the transactions during a specific period of time, and its 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.


How to Make 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.

making an outline for organized bank statement


How to Organize Bank Statements in Excel: 2 Handy Methods

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.

how to organize bank statements in excel dataset

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.

organizing bank statements according to date

  • Finally, the Bank Statement will look like the screenshot below.

according to date of transaction

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

2. Showing Deposits First and Organizing Bank Statements 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.

showing deposits first organized according to date of transaction in excel

  • Then, make sure you have the Order from Smallest to Largest.
  • Next, click on Add Level.

showing deposits first organized according to date of transaction

  • After that, click on the Down-Arrow, beside, Then by.
  • Next, select Date from the Then by option.
  • Finally, click on OK.

showing deposits first organized according to date of transaction

  • As a result of all the above steps, you will have an output like the following screenshot.

showing deposits first organized according to date of transaction

💡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 Convert Bank Statement Text to Excel


Download Practice Workbook

You can download the practice workbook from the link below.


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 least, I hope you found what you were looking for in this article. If you have any queries, please leave a comment below.


Related Article


<< Go Back To How to Create a Bank Statement in Excel |Excel For Finance | 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.
Sowmik Chowdhury
Sowmik Chowdhury

Sowmik Chowdhuri, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a crucial Excel & VBA Content Developer at ExcelDemy. His profound passion for research and innovation seamlessly aligns with his unwavering dedication to Excel. In this role, Sowmik not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, highlighting his steadfast commitment to consistently deliver content of exceptional quality and value. Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo