15 Essential Excel Formulas for Bank Statement Analysis

1. SUMIF - Sum Transactions by Merchant

Use case: "How much did I spend at Amazon this month?"

2. SUMIFS - Sum with Multiple Criteria

Use case: "How much did I spend at restaurants in January?"

3. SUMPRODUCT - Complex Conditional Sums

Use case: "Sum only negative amounts (expenses) from a specific merchant"

4. AVERAGEIF - Average Transaction Amount

Use case: "What's my average gas station purchase?"

5. COUNTIF - Count Transactions by Type

Use case: "How many times did I use my credit card this month?"

6. VLOOKUP - Match Transactions Between Sheets

Use case: "Does this transaction appear in my accounting records?"

7. INDEX MATCH - Flexible Transaction Lookup

Use case: "Find accounting entry for this bank transaction (more flexible than VLOOKUP)"

8. Running Balance Validation

Use case: "Verify statement balance at each transaction"

9. IFERROR - Clean Up Lookup Errors

Use case: "Show friendly message instead of #N/A when transaction not found"

10. ISERROR - Flag Unmatched Transactions

Use case: "Mark all transactions that don't appear in accounting system"

11. COUNTIFS - Detect Duplicate Transactions

Use case: "Find transactions with identical date, description, and amount (potential duplicates)"

12. TEXT - Analyze Spending by Month/Quarter

Use case: "Sum spending for each month"

13. ABS - Find Near-Duplicate Amounts

Use case: "Find transactions on same day with similar amounts (within $1)"

14. Conditional Formatting - Highlight Unusual Transactions

Use case: "Automatically highlight expenses over $500 or large credits"

15. SUBTOTAL - Sum Only Visible (Filtered) Rows

Use case: "Total visible transactions after applying filters"

What Excel formula should I use to sum transactions by category?

Use SUMIF for single criteria or SUMIFS for multiple criteria:

• =SUMIF(C:C,"*Amazon*",D:D) - Sums all Amazon transactions
• =SUMIFS(D:D,E:E,"Dining",A:A,">="&DATE(2024,1,1)) - Sums dining expenses from Jan 1, 2024 onward

For complex logic (OR conditions, calculated criteria), use SUMPRODUCT instead.

How do I find duplicate transactions in Excel?

Use COUNTIFS to detect exact duplicates (same date, description, amount):

This returns TRUE if the same transaction appears multiple times. Add to helper column, filter for TRUE, review duplicates.

For near-duplicates (same day, similar amount within $1), use ABS: =ABS(D2-D3)<1 to compare adjacent rows.

What's the best way to reconcile bank statements in Excel?

Use VLOOKUP or INDEX MATCH to compare two statements:

1. Create helper column concatenating date+description+amount: =A2&C2&D2
2. Use VLOOKUP to find match in second sheet: =VLOOKUP(E2,Sheet2!E:E,1,FALSE)
3. Wrap in IFERROR: =IFERROR(VLOOKUP(...),"Missing")
4. Filter for "Missing" to find unmatched transactions

Also validate running balance: =IF(ROW()=2,StartBalance+D2,E1+D2) - final balance should match statement ending balance.

How do I create a monthly spending summary in Excel?

Method 1: Pivot Table (easiest)

• Insert → PivotTable
• Rows: Month (from Date field)
• Values: Sum of Amount
• Automatically groups by month

Method 2: SUMIFS by month

Copy formula for each month, adjusting dates. Pivot tables are faster for multi-month analysis.

What's the difference between VLOOKUP and INDEX MATCH?

VLOOKUP limitations:

• Can only look right (return value must be right of lookup column)
• Column number breaks if you insert/delete columns
• Slower with large datasets

INDEX MATCH advantages:

• Can look in any direction (return column can be anywhere)
• References column by range, not number (more stable)
• Faster performance (MATCH runs once, INDEX retrieves)

Recommendation: Use VLOOKUP for simple lookups. Use INDEX MATCH for complex reconciliation or large statements (1000+ rows).

How do I calculate running balance in Excel?

Use this formula in balance column (assuming E is balance, D is amount, F1 has starting balance):

How it works:

• ROW()=2: First data row (row 2) uses starting balance from F1
• Other rows: Take previous balance (E1) + current transaction (D2)
• Copy formula down entire column

Validation: Final balance in last row should match statement ending balance. If not, you have missing transactions or data entry errors.

Can Excel formulas categorize transactions automatically?

Yes, use nested IF or VLOOKUP with category table:

Method 1: IF with wildcards

Method 2: VLOOKUP with merchant table

• Create table: Merchant | Category (Amazon | Shopping, Shell | Gas, etc.)
• Use: =VLOOKUP(C2,MerchantTable,2,FALSE)
• More maintainable than nested IF (add merchants to table, not formula)

What's the best Excel date format for bank statements?

For importing: Use YYYY-MM-DD (ISO 8601 format) - universally recognized, sorts correctly, no ambiguity.

For analysis: Store as Excel date (number), display as preferred format:

• Right-click → Format Cells → Date → choose display format
• US: m/d/yyyy (1/15/2024)
• International: dd/mm/yyyy (15/01/2024)
• Sortable: yyyy-mm-dd (2024-01-15)

Critical: Ensure dates are stored as numbers (date type), not text. Test: =ISNUMBER(A2) should return TRUE. If FALSE, use =DATEVALUE(A2) to convert.