Cards and UPI

Merging statements from different banks into one sheet

Merge bank statements from different banks into one Excel sheet by normalising columns, adding a source column and removing transfers between accounts.

Editorial illustration for Merging statements from different banks into one sheet

To merge statements from several banks into one sheet, give every statement the same five columns, date, narration, debit, credit and source, paste them one below another, and then remove transfers between your own accounts so money is not counted twice. The work is mostly cleaning: banks name columns differently, write dates in different formats and sometimes show amounts in a single column.

What columns should the combined sheet have?

ColumnWhat goes in itNotes
DateTransaction date as a real dateSame format throughout
NarrationThe bank's description textKeep the original
DebitMoney out, as a positive numberBlank when it is a credit
CreditMoney in, as a positive numberBlank when it is a debit
SourceWhich account or card it came fromTyped once per pasted block

Look at your own statements first. Some banks give separate debit and credit columns, others give one amount column with a sign or a Dr and Cr marker. Convert them so every row follows the layout above.

How do I normalise dates and amounts?

  1. Check whether dates are real dates or text. If left-aligned in Excel, they are probably text.
  2. Convert text dates with the Data tab's Text to Columns tool and choose the correct day-month-year order, or rebuild them with =DATE(year,month,day) from separate parts.
  3. Remove commas and any Dr or Cr suffix from amounts, then convert them to numbers.
  4. If a bank uses one amount column with a marker, create the Debit and Credit columns with formulas such as =IF(D2="Dr",C2,"") and =IF(D2="Cr",C2,"").
  5. Sort by date once all blocks are pasted in.

The formula examples assume the amount is in C2 and the marker in D2. Adjust the cell references to match your sheet.

How do I add a source column?

Before pasting each statement, add a column to its block and fill it down with a name, such as "Bank A savings" or "Card B". Without this, you cannot tell afterwards where a row came from, which makes every later check harder. Use the same names each time you add a month.

How do I handle transfers between my own accounts?

Moving ₹20,000 from savings to a card appears as a debit in one statement and a credit in another. It is not income or spending. Left in, it inflates both. To find them, sort by amount and date and look for equal amounts a day or two apart, one out and one in. Mark them in a Type column as Transfer and exclude them from spending totals.

A helper formula can flag candidates. With debits in D and credits in E, and the sheet sorted by date, a row is suspicious when there is another row with the same amount on the other side. Use =COUNTIF($E$2:$E$1000,D2)>0 in a helper column to test debits against credits, then confirm each one by eye, because two unrelated payments can match.

How do I check the merged sheet is right?

  • For each source, the sum of debits and credits should equal the statement's totals for the period.
  • The row count per source should match the count on the statement.
  • Look for duplicate rows at the join between two months.

When does a sheet stop being enough?

Doing this each month for three or four accounts is a lot of repeated cleaning. SpendixAI takes statement uploads in PDF, CSV or Excel for each account, lets you review rows before they are saved, flags possible duplicate imports and keeps every transaction with its account, so the source column exists without the pasting. Layouts differ, so check the rows after each import. Until then, the sheet method above works fine.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports