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
- Splunk Split Command: How to Use It
- Python split() and join() Explained with Examples
- Snowflake SPLIT_PART: Syntax, Examples, and Edge Cases
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Date Countdown Formula: Calculate Days Left in ExcelIf the target date is in cell B2, enter =B2-TODAY() in another cell to show the number of…
- Excel Date Validation Formula: How to Restrict EntriesThe quickest way to add date validation in Excel is to use Data Validation with a formula. For…
- How to Add Filter Options in Excel: A Step-by-Step GuideTo add filter options in Excel for Microsoft 365 on Windows, select a cell in your data, then…
- Excel COUNTIF Matches Reference Cell: Formula & TipsYes, you can use COUNTIF to count cells that match a reference cell by pointing the criteria argument…
- Excel Formula to Combine Text From Two Cells with a SpaceWith text in A1 and B1, join the contents directly with =A1&B1; to put a space between them,…
- Excel Formula for Calculating Overtime HoursTo calculate daily overtime in Excel, subtract unpaid break hours and the regular-hours threshold from the shift length,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.