How to Create a Bank Statement in Excel

Get FREE Advanced Excel Exercises with Solutions!

A bank statement is a list of records of transactions between a bank and its account holders. It is provided upon valid requests of any account holder of a bank. Microsoft Excel has some easy tools and features for creating a bank statement.

In this Excel guide, we will share 3 easy steps to create a bank statement in Excel. The following image presents an overview of how a bank statement can be created and edited in Excel.

Created Bank Statement in Excel


What is a Bank Statement?

A bank statement is a printed list of all transactions with a bank account over a specified period, usually, it is taken monthly, but also provided for users’ required period. Typically, a bank statement includes the details of deposits, withdrawals, and the beginning and ending balance of the account for the period. Also, a bank statement shows any charges deducted from and any interest earned on your deposit amount.

In these days, you must not go to the bank offices to get a bank statement. You may also receive a paper statement in the mail or can get access to it electronically via your bank’s website or app.


What Are the Key Types of Information in a Bank Statement?

A typical bank statement includes the following 5 types of information, but they may vary among banks and among the types of accounts for which the statement is generated. The elements of bank statements are bank information, account information, statement period, starting and ending balance, and finally account activity.

Firstly, a bank statement has details of the bank’s name, addresses, contact information, etc. It is generally printed at the top of the document. Next, the description of the account and account holder is presented in the bank statement. Also, the type of account (current, savings, or fixed) is printed on a bank statement.

The statement period is one of the most important parts of a bank statement. The main part of a bank statement is the list of account activities and transactions presented according to sequential dates. A beginning and ending balance is calculated and presented for the statement period in a bank statement.


Why Do We Need Bank Statements?

In the corporate world, a bank statement is valued as a core official document as it greatly helps account holders keep track of their finances. It is often used to identify errors and scope for reconciliation and recognize spending habits.

Not only businesses, you can also benefit personally by using a bank statement. A bank statement can also aid in setting saving goals and investment plans. To detect any fraud and errors, or to find any discrepancies, bank statements provide crucial support in that case.


How to Create A Bank Statement in Excel: 3 Easy Steps

To create a bank statement in Excel, you will need to follow 3 major steps. Such as: Preparing a proper outline, inputting and calculating necessary transaction information, and summarizing the account activity.

Step 1: Prepare a Proper Outline

  1. Prepare a proper outline for your bank statement containing all necessary information blocks such as Account Information, Personal Information, Statement Period, Account Activity Summary, etc.
  2. Along with these blocks, prepare a transaction table to insert dates, transaction descriptions, transaction reference no., transaction type, current balance, etc.
    Preparing Outline

Step 2: Insert and Calculate Necessary Transaction Information

  1. After creating the necessary blocks, insert the necessary information in the blue-shaded area.
    Inputting Necessary Information
  2. In the transaction table, insert the following formula in your Balance column to get your opening balance for the statement period.
    =$G$12
    Calculating First Balance for Transaction Table
  3. Select the following transaction’s Balance column and enter the following formula for the next transaction’s balance.
    =G18+E19-F19
    Calculating Second Balance for Transaction Table
  4. For all the other transactions’ balances to be calculated automatically, drag the fill handle downward along the Balance column.
    Calculating All Balances for Transaction Table

Step 3: Summarize Account Activity

  1. To calculate total deposits, select the output cell and insert the following formula: =SUM($E$19:$E$101)
    Summarizing Total Deposits
  2. To find total withdrawals, select your desired output cell and insert the following formula:
    =SUM($F$19:$F$101)
    Summarizing Total Withdrawals
  3. To extract the closing balance from the transaction table, insert the following formula:
    =LOOKUP(2,1/(G:G<>""),G:G)
    Calculating Closing Balance

How to Edit Bank Statement in Excel

Sometimes, we may need to collect bank statements from banks that may have complex formatting or structure. So, we may need to edit that statement to bring it into the structure that meets our requirements. For example, preview the following image. Here, using Microsoft Excel, we have edited the statement provided by the bank into our suitable format.

Edited Bank Statement in Excel

In the following article, you will find how to edit bank statements in Excel.

Read More: How to Edit Bank Statement in Excel


Download Practice Workbook

Download this practice workbook to better understand while reading or exercising this tutorial.


Conclusion

So, in this Excel guide, we introduced you to the bank statement which is a detailed record of all transactions between the account holder and the bank, within a specified period. Next, we explained three easy steps to create a bank statement in Excel. Further, we discussed how to edit bank statements in Excel. Feel free to use the steps to create or edit a bank statement and communicate any issue in the comment section.


Frequently Asked Questions

How do I get a bank statement in PDF format?

To get Excel bank statement in PDF version, open your file. Simply select the File menu and then click Save As. Then, change and save the format of the file to PDF.

How do I get a bank statement in CSV format?

To get a bank statement in CSV format, open your Excel workbook, switch to the File tab, and then click Save As. Alternatively, you can press F12 to open the same Save As dialog. In the Save as type box, choose to save your Excel file as CSV.


How to Create a Bank Statement in Excel: Knowledge Hub


<< Go Back To 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.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo