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.


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.

making an outline for organized bank statement


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.

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.

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.

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



Related Articles

Sowmik Chowdhury

Sowmik Chowdhury

Hello! Welcome to my Profile. I , Sowmik Chowdhury, graduated from the Bangladesh University of Engineering and Technology from the Department of Naval Architecture and Marine Engineering. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this..

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo