Excel Formula for Checking Account? Track Balances

Formulas & functions · Updated November 22, 2026

The quickest formula to track a checking account in Excel is a running balance: with your starting balance in C2, deposits in D3, and withdrawals in E3, enter =C2+D3-E3 in C3 and drag it down. This gives you a live balance for every transaction, and you can expand it with SUMIF for automatic categorization.

Why Use Excel for a Checking Account

Most people use a bank’s app or website to check their balance, but those tools only show what the bank knows. Excel lets you track pending transactions, plan for upcoming bills, and see your true available balance before the bank clears everything. It also gives you a permanent record you can sort, filter, and analyze—something most banking apps make difficult. You can build a simple check register in minutes, and because it’s a spreadsheet, you can add formulas that calculate totals, flag overdrafts, or forecast next month’s spending.

Setting Up a Basic Check Register

Start with a clean sheet and these column headers in row 1: Date (A), Description (B), Deposit (C), Withdrawal (D), Balance (E). In E2, enter your starting balance (for example, 1500). In row 3, enter your first transaction: put the date in A3, a description in B3, the deposit amount in C3 (if any), the withdrawal amount in D3 (if any), and in E3 enter =E2+C3-D3. Press Enter, then select E3 and double-click the fill handle (the small square at the bottom-right of the cell) to copy the formula down the column. Excel will automatically adjust the row references for each new row. If you add transactions later, just type in the next row and copy the formula down again.

Using SUMIF to Categorize Transactions

A running balance is useful, but you often want to know how much you spent on groceries or rent. That’s where SUMIF comes in. Assume your transactions are in rows 2 to 100, with descriptions in B2:B100 and amounts in C2:D100 (deposits positive, withdrawals negative). To total all withdrawals for a category like “Groceries,” use =SUMIF(B2:B100,"Groceries",D2:D100). This sums only the withdrawal amounts where the description matches exactly. For a partial match, use wildcards: =SUMIF(B2:B100,"*Grocery*",D2:D100) will catch “Grocery Store” and “Grocery” alike. To get net cash flow (deposits minus withdrawals) for the whole period, use =SUM(C2:C100)-SUM(D2:D100).

Adding Conditional Formatting for Overdrafts

To make low balances stand out, use conditional formatting. Select the balance range (E2:E100), then go to Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter 0 and choose a red fill. This will highlight any negative balance in red, so you can spot an overdraft at a glance. For a more advanced rule, use a formula to highlight balances below a certain threshold, like $100: select the range, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =E2<100 (adjust the first cell reference to match your selection), and set a format. On a Mac, the menu path is the same, but the dialog looks slightly different.

Common Problems and How to Fix Them

  • Formula not copying: If you drag the fill handle and the balance doesn't update, check that the formula references the cell above it correctly. If you inserted a row, the formula may skip a row—just re-enter it.
  • Negative numbers showing as parentheses: To display negative amounts in red or with parentheses, select the cells, press Ctrl+1 (or Cmd+1 on Mac), go to the Number tab, choose Number or Currency, and set the negative format.
  • Dates not sorting: If your dates are entered as text, Excel won't sort them properly. Enter dates as actual dates (e.g., 1/15/2025) and format the column as Date.
  • SUMIF returning zero: Check for extra spaces in descriptions. Use =TRIM(B2) to clean them, or use wildcards as shown above.

Comparing Excel to Budgeting Apps

Budgeting apps like Mint or YNAB automate transaction imports and categorization, but they require linking your bank account and often have subscription fees. Excel gives you full control, no monthly cost, and no data sharing with third parties. The trade-off is manual entry—you must type in every transaction or import a CSV from your bank. Many banks let you export transactions as a CSV file, which you can open directly in Excel and then format as a register. Excel also lets you build custom reports, like a monthly spending breakdown, that apps may not offer. However, if you want automatic syncing and don't mind paying, an app might be more convenient.

Safety and Accuracy Tips

When using Excel for financial tracking, accuracy is critical. Always double-check that deposits are entered as positive numbers and withdrawals as negative numbers, or use separate columns as shown. Keep a backup of your workbook—save a copy to OneDrive or another cloud service. Never share your spreadsheet with sensitive account numbers; use only descriptions and amounts. If you use formulas that reference other sheets, make sure the references are correct. For budgeting or forecasting, remember that Excel is a tool, not a financial advisor—verify any loan or interest calculations with your lender or a qualified professional.

Advanced: Using PivotTables for Monthly Summaries

If you want a monthly summary of income and expenses, a PivotTable is powerful. Select your transaction data (including headers), go to Insert > PivotTable, and choose where to place it. Drag the Date field to the Rows area, the Deposit and Withdrawal fields to the Values area. Then right-click any date in the PivotTable, choose Group, and select Months. This will show totals per month. On a Mac, the steps are the same. This gives you a quick view of your cash flow without writing complex formulas.

Frequently Asked Questions

How do I calculate my current balance in Excel?

Use a running balance formula: in the first balance cell, enter your starting balance, then in the next cell, enter =previous_balance + deposit - withdrawal. Copy it down for all transactions. This gives your current balance after each transaction.

Can I link Excel to my bank account automatically?

No, Excel does not have a built-in bank feed. You can manually export a CSV from your bank and import it, or use third-party add-ins, but they may require credentials and are not officially supported by Excel. Always be cautious with third-party tools.

What if I have multiple accounts?

Create a separate sheet for each account, or use one sheet with a column for the account name. Then use SUMIFS to total deposits or withdrawals for a specific account, like =SUMIFS(C:C,A:A,"Checking").

How do I handle pending transactions?

Add a column labeled "Status" and mark each transaction as "Cleared" or "Pending." Then use SUMIF to calculate your available balance by summing only cleared transactions, or use a formula like =SUMIF(F:F,"Cleared",C:C)-SUMIF(F:F,"Cleared",D:D).

Is Excel safe for tracking my finances?

Yes, if you protect your file with a password (File > Info > Protect Workbook > Encrypt with Password) and avoid storing full account numbers. Use only transaction descriptions and amounts. Keep backups in a secure location.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.