Excel Formula for Checking Account Register

Formulas & functions · Updated November 22, 2026

The core formula for a checking account register in Excel is a running balance: =SUM($C$2:C2) (or =C2 for the first row) placed in the balance column, where column C holds deposit and withdrawal amounts (withdrawals entered as negative numbers). This gives you a real-time balance that updates as you add rows, and you can extend it with =IF() to handle cleared vs. pending transactions.

Why a Running Balance Beats a Static Total

Most people first try to calculate a balance with =SUM(C2:C100) at the bottom of the register. That works for a single total, but it doesn’t show you what your balance was after each transaction—something you need to reconcile against your bank statement or avoid overdrafts. A running balance formula recalculates automatically every time you add or edit a row, and it’s the same logic used by professional bookkeepers in Excel and Google Sheets.

For a simple register with headers in row 1, put your starting balance in cell C2 (as a positive number if it’s money you have). Then in D2, enter =C2. In D3, enter =D2+C3 and copy that down. If you prefer to keep deposits and withdrawals in separate columns (say B for deposits, C for withdrawals), the formula becomes =D2+B3-C3.

How to Set Up the Register Layout

Before writing any formula, structure your sheet so Excel can read it cleanly. A standard layout uses columns for:

  • Date (column A)
  • Description (column B)
  • Amount (column C) – deposits as positive, withdrawals as negative
  • Balance (column D)

Enter your opening balance in C2 (e.g., 500.00). Then in D2, type =C2. In D3, type =D2+C3 and press Enter. Select D3, hover over the bottom-right corner until you see a small square (the fill handle), and double-click or drag down to copy the formula to the rows you need. Excel will adjust the row numbers automatically.

If you use separate deposit and withdrawal columns, the balance formula in D3 becomes =D2+B3-C3. The key is that every row’s balance equals the previous balance plus the net of that row’s activity.

Handling Cleared vs. Pending Transactions

A common real-world need is to see your “available” balance versus your “ledger” balance. You can do this with an IF function that checks a “Cleared” column (say column E, with “Y” or “N”).

Assume columns: A=Date, B=Description, C=Amount, D=Cleared (Y/N), E=Balance. In E2, enter =C2. In E3, enter =IF(D3="Y", E2+C3, E2). This only adds the transaction to the running balance if it’s marked cleared. For the available balance, use a separate column F with =SUMIF(D:D,"Y",C:C) to total only cleared amounts, then add your starting balance.

For a more advanced approach, use the SUMIFS function to sum amounts up to a specific date: =SUMIFS(C:C,A:A,"<="&A3). This gives a balance as of a certain date, which is useful for monthly reconciliations.

Common Errors and How to Fix Them

Even a simple formula can break. Here are the most frequent mistakes and their solutions:

  • #VALUE! error – Usually means a cell in the amount column contains text, like a space or a dash. Check for non-numeric entries.
  • Blank rows – If you copy the formula down past your data, it will show the last balance repeated. To avoid this, wrap the formula in an IF check: =IF(C3="","",D2+C3).
  • Wrong sign for withdrawals – If you enter withdrawals as positive numbers, your balance will grow. Always use negative for money going out, or subtract explicitly.
  • Formula not updating – Excel recalculates automatically, but if you’ve set calculation to manual (Formulas > Calculation Options), press F9 to refresh.

Another common misconception is that you need a complex array formula. You don’t—the simple running balance is sufficient for 99% of personal registers.

Comparing Excel to Google Sheets and Other Tools

Excel and Google Sheets share the same core formulas, so =D2+C3 works identically in both. The main difference is that Google Sheets uses =ARRAYFORMULA if you want to apply a formula to an entire column without dragging, e.g., =ARRAYFORMULA(IF(C3:C="","",D2:D+C3:C))—but that’s optional. For Mac users, Excel’s keyboard shortcuts differ: use Cmd instead of Ctrl for copy/paste, but the formulas are the same.

If you’re using a dedicated register template from Microsoft, it often includes a “Cleared” column and a conditional formatting rule that highlights cleared transactions. You can replicate that with Home > Conditional Formatting > Highlight Cells Rules > Equal To, setting the rule to “Y”.

Advanced Features: Conditional Formatting and Data Validation

To make your register more robust, add data validation to the “Cleared” column: select the column, go to Data > Data Validation, choose “List” and enter Y,N. This prevents typos. For the amount column, use Data Validation to allow only numbers (allow “Decimal” between a minimum and maximum).

Conditional formatting can alert you to a low balance. Select the balance column (D2:D100), go to Home > Conditional Formatting > New Rule > Use a formula. Enter =D2<100 and set a red fill. This highlights any balance below $100. You can also highlight negative balances with =D2<0.

Frequently Asked Questions

How do I add a starting balance to my register?

Put your starting balance in the first row’s amount column (as a positive number), then in the balance column for that row, enter =C2. The next row’s formula becomes =D2+C3, and the starting balance is automatically included.

Can I use a formula to automatically calculate the balance for thousands of rows?

Yes, but dragging the fill handle down thousands of rows can be tedious. Instead, select the first balance cell, press Ctrl+Shift+Down to select the entire column range, then press Ctrl+D to fill down. On Mac, use Cmd+Shift+Down and Cmd+D.

What if I want to separate cleared and pending transactions visually?

Use conditional formatting. Select the transaction rows, go to Home > Conditional Formatting > New Rule > Use a formula, and enter =$D2="N" (assuming D is the cleared column). Set a gray font or fill. This will gray out pending transactions.

Is there a way to see the balance as of a specific date without filtering?

Yes, use =SUMIFS(C:C,A:A,"<="&DATE(2025,3,31)) plus your starting balance. For example, if your starting balance is in cell C2, the formula is =C2+SUMIFS(C3:C100,A3:A100,"<="&DATE(2025,3,31)). This sums all amounts up to that date.

Does this formula work in Excel for the web?

Yes, the basic running balance formula works identically in Excel for the web. The only difference is that some keyboard shortcuts differ (e.g., Ctrl+D may not work; you can drag the fill handle instead).

Can I use a pivot table to summarize my register?

Yes, but a pivot table is better for monthly summaries (e.g., total deposits and withdrawals by month) than for a running balance. To create one, select your data range, go to Insert > PivotTable, then drag Date to Rows and Amount to Values, grouping by month.

Final Tips for Accuracy

Always double-check your sign conventions: deposits positive, withdrawals negative. Test your formula with a known example—if you start with $100, add $50, and subtract $20, your balance should be $130. If you’re reconciling with a bank statement, use the cleared balance formula to match the bank’s number. And remember to save a backup copy of your workbook before experimenting with macros or advanced features, as those cannot be undone with Ctrl+Z.

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.