Why Use Excel Macros for Bank Statement Data?
Bank statements often arrive in inconsistent formats, packed with extra columns, merged data, or unwanted rows. Manually cleaning and organizing these statements is tedious, error-prone, and time-consuming. By leveraging Excel macros, you can automate repetitive tasks, minimize mistakes, and drastically cut processing time.
Below you'll find a curated selection of essential macros, each addressing a common pain point in bank statement processing. All macros are written in VBA (Visual Basic for Applications) and can be added to your workbook through the VBA editor (ALT + F11
in Excel).
1. Remove Blank Rows
Blank rows can interfere with sorting, filtering, and analysis. This macro searches for entirely blank rows and deletes them in one sweep.
Sub RemoveBlankRows() Dim ws As Worksheet Dim LastRow As Long, i As Long Set ws = ActiveSheet LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = LastRow To 1 Step -1 If Application.WorksheetFunction.CountA(ws.Rows(i)) = 0 Then ws.Rows(i).Delete End If Next i End Sub
2. Split Date and Description Columns
Sometimes banks export date and description into a single column. This macro separates them, assuming they're divided by a space or a delimiter, and places them into adjacent columns.
Sub SplitDateDescription() Dim ws As Worksheet Dim LastRow As Long Set ws = ActiveSheet LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ws.Columns("A:A").TextToColumns Destination:=ws.Range("A1"), DataType:=xlDelimited, Space:=True End Sub
Tip: Adjust the DataType
and delimiter according to your statement's actual format.
3. Standardize Date Formats
Dates can come in various regional formats, causing issues in sorting or analysis. Use this macro to convert all dates in Column A to a standard format.
Sub StandardizeDates() Dim ws As Worksheet Dim LastRow As Long, i As Long Set ws = ActiveSheet LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 2 To LastRow 'Assuming header in row 1 If IsDate(ws.Cells(i, 1)) Then ws.Cells(i, 1).Value = Format(ws.Cells(i, 1).Value, "yyyy-mm-dd") End If Next i End Sub
4. Highlight Duplicate Transactions
Duplicate entries can skew financial analysis. This macro highlights duplicate rows based on transaction amount and date.
Sub HighlightDuplicates() Dim ws As Worksheet Dim LastRow As Long Set ws = ActiveSheet LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ws.Range("A1:C" & LastRow).FormatConditions.AddUniqueValues ws.Range("A1:C" & LastRow).FormatConditions(ws.Range("A1:C" & LastRow).FormatConditions.Count).SetFirstPriority ws.Range("A1:C" & LastRow).FormatConditions(1).DupeUnique = xlDuplicate ws.Range("A1:C" & LastRow).FormatConditions(1).Interior.Color = vbYellow End Sub
Modify the range to match the columns containing your transaction date and amount.
5. Categorize Transactions Automatically
Assigning categories (e.g., “Groceries”, “Utilities”) to transactions can be automated based on keywords. This macro checks transaction descriptions and assigns categories in a new column.
Sub AutoCategorize() Dim ws As Worksheet Dim LastRow As Long, i As Long Set ws = ActiveSheet LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row 'Assume descriptions are in column B For i = 2 To LastRow Select Case True Case InStr(1, ws.Cells(i, 2).Value, "Walmart", vbTextCompare) > 0 ws.Cells(i, 4).Value = "Groceries" Case InStr(1, ws.Cells(i, 2).Value, "Shell", vbTextCompare) > 0 ws.Cells(i, 4).Value = "Fuel" Case InStr(1, ws.Cells(i, 2).Value, "Netflix", vbTextCompare) > 0 ws.Cells(i, 4).Value = "Entertainment" Case Else ws.Cells(i, 4).Value = "Other" End Select Next i End Sub
Expand the Select Case
block with additional keywords and categories to suit your needs.
6. Summarize Monthly Totals
Quickly calculate total inflows and outflows by month. This macro assumes your dates are in column A and amounts in column C.
Sub MonthlySummary() Dim ws As Worksheet Dim dict As Object Dim LastRow As Long, i As Long Dim dt As String, amt As Double Set ws = ActiveSheet Set dict = CreateObject("Scripting.Dictionary") LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 2 To LastRow If IsDate(ws.Cells(i, 1).Value) Then dt = Format(ws.Cells(i, 1).Value, "yyyy-mm") amt = ws.Cells(i, 3).Value If dict.Exists(dt) Then dict(dt) = dict(dt) + amt Else dict.Add dt, amt End If End If Next i ' Output summary ws.Range("E1").Value = "Month" ws.Range("F1").Value = "Total" i = 2 For Each dt In dict.Keys ws.Cells(i, 5).Value = dt ws.Cells(i, 6).Value = dict(dt) i = i + 1 Next dt End Sub
How to Add and Use These Macros
- Open your workbook and press
ALT + F11
to launch the VBA editor. - Insert a new module with
Insert > Module
. - Copy and paste the desired macro(s) into the module window.
- Close the editor and return to Excel.
- Run the macro via
ALT + F8
, then select the macro by name.
Tips for Safe Macro Use
- Work on a copy of your statement data to prevent accidental loss.
- Test macros on a small dataset before applying them broadly.
- Customize column references and keyword lists for your specific bank statement layout.
Empowering your bank statement workflows with macros not only saves time but also ensures consistent, accurate results. With a bit of customization, these tools can transform how you process financial data in Excel.