Once a Bank of Baroda statement is in Excel as rows and columns, the next job is to turn separate debit and credit columns into one signed Amount column that you can add up. Use a simple IF formula for that, convert any text-looking numbers first, and check the sum against the closing balance. We have not assumed the bank's column order, so swap in the right column letters for your file.
Why one signed amount column?
Two money columns, one blank and one filled, are hard to sort, pivot or filter. A single Amount column where money out is negative and money in is positive lets you use plain SUM, SUMIFS and a pivot table without extra thought.
What is the formula when debit and credit are separate?
Say your layout is: A Date, B Narration, C Debit, D Credit, E Balance. In F2 write one of these:
- =IF(D2<>"",D2,-C2)
- =N(D2)-N(C2)
The first says: if there is a credit use it, else use the debit with a minus sign. The second treats blank cells as zero. Both only work when the cells contain real numbers. If they are left-aligned and a green triangle appears, they are text and need converting first.
How do I fix numbers stored as text?
Commas in the Indian style, such as 1,23,456.50, can make a pasted number stay as text. In a spare column use:
=VALUE(SUBSTITUTE(C2,",",""))
Copy down, paste as values over the originals, and delete the helper. Blank cells give an error here, so wrap it as =IF(C2="","",VALUE(SUBSTITUTE(C2,",",""))).
What if one column shows amounts with Dr or Cr?
Some layouts put the amount and a Dr or Cr suffix in one cell, such as 1,200.00 Dr. If the amount is in C2, this formula gives credits positive and debits negative:
=IF(RIGHT(TRIM(C2),2)="Cr",1,-1)*VALUE(SUBSTITUTE(TRIM(LEFT(TRIM(C2),LEN(TRIM(C2))-2)),",",""))
Check it against three rows you can read by eye before filling the whole column.
Does it add up? A worked check
| Date | Narration (illustrative) | Debit | Credit | Signed amount |
|---|---|---|---|---|
| 02/06/2026 | Rent transfer | 18,000 | – | −18,000 |
| 05/06/2026 | Salary credit | – | 62,000 | 62,000 |
| 09/06/2026 | Grocery payment | 3,450 | – | −3,450 |
| 14/06/2026 | Refund | – | 899 | 899 |
The signed amounts add to −18,000 + 62,000 − 3,450 + 899 = ₹41,449. If the opening balance was ₹10,000, the closing balance should be 10,000 + 41,449 = ₹51,449. In Excel: =10000+SUM(F2:F5). The same total should appear on the statement.
How do I sort and filter without breaking the rows?
Once the signed amount works, you will want to sort by date or filter by narration. A few habits keep the rows intact:
- Select the whole block of data, or click inside it and use the Sort command, so every column moves together. Sorting one column alone scrambles the sheet.
- Turn the range into a table with Ctrl+T so that new rows pick up the formula automatically.
- Freeze the heading row so you can still see the column names after scrolling.
- Paste formulas as values before sorting if any of them refer to the row above, since a sorted row has a different neighbour.
- Keep an untouched copy of the original sheet on another tab.
After any sort, rerun the closing-balance check. It takes seconds and proves that nothing fell out of the range.
What should I do before I trust it?
- Make sure no row has both a debit and a credit.
- Compare the count of rows with the transaction count the statement shows, if it gives one.
- Compare the closing balance formula with the printed balance.
- Keep the original PDF next to the sheet.
If you do this monthly, a tool that reads the statement itself saves the repeat work; SpendixAI accepts PDF, CSV or Excel and shows each row for review before saving.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

