For a cash or bank account, enter positive amounts in separate Debit and Credit columns, then calculate the running balance with =E5+C6-D6. This adds receipts and subtracts payments from the previous balance. That calculation creates a ledger or cashbook balance—not a complete balance sheet. A true balance-sheet summary also classifies account balances as assets, liabilities, and equity, then checks whether Assets = Liabilities + Equity.
1. Set up the worksheet
Use a layout like this for a single cash or bank account:
As an Amazon Associate I earn from qualifying purchases.
| Date | Description | Debit | Credit | Balance |
|---|---|---|---|---|
| 2026-01-01 | Opening balance | 1,000 | ||
| 2026-01-02 | Customer payment | 500 | 1,500 | |
| 2026-01-03 | Office supplies | 125 | 1,375 |
In the examples below:
E5contains the opening balance.C6:C1000contains debit amounts.D6:D1000contains credit amounts.E6:E1000contains calculated balances.
Enter positive values in both Debit and Credit. Let the formula apply the sign. If you enter a credit as -125 and also subtract the Credit column, Excel will add it back instead of reducing the balance.
2. Debit and credit depend on the account
For a cash or bank asset under this worksheet convention, a debit commonly represents money received or an increase in cash, while a credit represents money paid out or a decrease in cash. Therefore:
#1 Best Overall
- Classic Office Apps | Includes classic desktop versions of Word, Excel, PowerPoint, and OneNote for creating documents, spreadsheets, and presentations with ease.
- Install on a Single Device | Install classic desktop Office Apps for use on a single Windows laptop, Windows desktop, MacBook, or iMac.
- Ideal for One Person | With a one-time purchase of Microsoft Office 2024, you can create, organize, and get things done.
- Consider Upgrading to Microsoft 365 | Get premium benefits with a Microsoft 365 subscription, including ongoing updates, advanced security, and access to premium versions of Word, Excel, PowerPoint, Outlook, and more, plus 1TB cloud storage per person and multi-device support for Windows, Mac, iPhone, iPad, and Android.
Previous balance + Debit - CreditThis is not a universal “debit minus credit” rule. Normal directions differ by account:
| Account type | Typical normal direction |
|---|---|
| Asset | Debit |
| Liability | Credit |
| Equity | Credit |
| Revenue | Credit |
| Expense | Debit |
For a liability or equity account, a natural balance calculation may instead be:
Previous balance + Credit - DebitDo not apply the cash-account formula blindly to accounts payable, loans, capital, revenue, or expenses. The source examples focus primarily on a bank-account running balance.
3. Example 1: the simple running-balance formula
Click cell E6 and enter:
=E5+C6-D6Press Enter, then fill or copy the formula down the Balance column. In row 6, an opening balance of $1,000, a $500 debit, and no credit produce:
1,000 + 500 - 0 = 1,500When copied to row 7, Excel changes the references to:
=E6+C7-D7If row 7 contains a $125 credit, the result is $1,375.
The equivalent formula shown in the original ExcelDemy example is:
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Rank #2
- Designed for Your Windows and Apple Devices | Install premium Office apps on your Windows laptop, desktop, MacBook or iMac. Works seamlessly across your devices for home, school, or personal productivity.
- Includes Word, Excel, PowerPoint & Outlook | Get premium versions of the essential Office apps that help you work, study, create, and stay organized.
- 1 TB Secure Cloud Storage | Store and access your documents, photos, and files from your Windows, Mac or mobile devices.
- Premium Tools Across Your Devices | Your subscription lets you work across all of your Windows, Mac, iPhone, iPad, and Android devices with apps that sync instantly through the cloud.
- Easy Digital Download with Microsoft Account | Product delivered electronically for quick setup. Sign in with your Microsoft account, redeem your code, and download your apps instantly to your Windows, Mac, iPhone, iPad, and Android devices.
=SUM(E5+C6-D6)It returns the same result, but SUM adds no capability when it wraps one arithmetic expression. The direct formula is easier to audit.
Advantages and limitation
- Advantages: clear, compatible with old and current Excel versions, and easy to check row by row.
- Limitation: each row depends on the previous balance, so chronological order and a complete transaction sequence matter.
4. Example 2: use SUM with OFFSET
In E6, you can use:
=SUM(C6,-D6,OFFSET(E6,-1,0))This formula adds the current debit, subtracts the current credit, and retrieves the cell one row above as the previous balance:
C6is the current debit.-D6turns the current credit into a subtraction.OFFSET(E6,-1,0)moves one row up and zero columns across.
Fill the formula down the column. This is useful for demonstrating a dynamic previous-row reference, but OFFSET is volatile. It can recalculate more often than necessary in a large workbook. For a small cashbook this is normally immaterial; for a large ledger, prefer the simple formula or a Table-based approach.
5. Example 3: use an Excel Table
Tables are usually the most practical option for a transaction list that will grow.
- Select the transaction range, including its headers.
- Press Ctrl+T.
- Check My table has headers.
- Open the Table Design tab and name the table
Transactions. - Use headers such as
Date,Description,Debit,Credit, andBalance.
Inside the Balance calculated column, the cumulative formula from the source example is:
=SUM(INDEX([Credit],1):[@Credit]-INDEX([Debit],1):[@Debit])Its intent is to create a range from the first Table row through the current row, then calculate cumulative credits minus debits. For a cash or bank account with an opening balance, this clearer version adds the opening balance and uses the cash-account direction:
=$E$5+SUM(INDEX(Transactions[Debit],1):[@Debit])-SUM(INDEX(Transactions[Credit],1):[@Credit])Alternatively, name the opening-balance cell OpeningBalance and use:
Rank #3
![Microsoft Office Home & Business 2024 | Classic Desktop Apps: Word, Excel, PowerPoint, Outlook and OneNote | One-Time Purchase for 1 PC/MAC | Instant Download [PC/Mac Online Code]](https://m.media-amazon.com/images/I/41H8B5iqO4L._SL160_.jpg)
- [Ideal for One Person] — With a one-time purchase of Microsoft Office Home & Business 2024, you can create, organize, and get things done.
- [Classic Office Apps] — Includes Word, Excel, PowerPoint, Outlook and OneNote.
- [Desktop Only & Customer Support] — To install and use on one PC or Mac, on desktop only. Microsoft 365 has your back with readily available technical support through chat or phone.
=OpeningBalance+SUM(INDEX(Transactions[Debit],1):[@Debit])-SUM(INDEX(Transactions[Credit],1):[@Credit])When you add a new row directly below the Table, Excel normally extends the Table and inherits the calculated-column formula.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Table trade-offs
- Benefits: new rows inherit formulas, structured references are readable, and sorting and filtering are more reliable than with an ordinary range.
- Drawbacks: structured references are unfamiliar at first, formulas copied outside the Table may not work as expected, and header names must match the formula.
Which method should you choose?
| Method | Best for | Main trade-off |
|---|---|---|
=PreviousBalance+Debit-Credit |
Beginners and small cashbooks | Depends on the previous row |
SUM and OFFSET |
Learning dynamic row references | OFFSET is volatile |
Excel Table plus INDEX |
Growing transaction lists | More complex syntax |
SUMIFS |
Balance-sheet reporting by account type | Requires clean classifications |
| PivotTable | Larger multi-account datasets | Requires refresh and PivotTable knowledge |
6. Build an actual balance-sheet summary
A running bank balance tells you the balance of one account. A balance sheet summarizes all relevant account balances at a specific date. It normally presents assets separately from liabilities and equity and must satisfy:
Total Assets = Total Liabilities + Total EquityStart with a categorized ledger or trial-balance table named Ledger:
| Account | Account Type | Debit | Credit |
|---|---|---|---|
| Cash | Asset | 10,000 | |
| Equipment | Asset | 5,000 | |
| Accounts payable | Liability | 3,000 | |
| Owner capital | Equity | 12,000 |
These formulas assume positive numbers in separate Debit and Credit columns:
Total assets
=SUMIFS(Ledger[Debit],Ledger[Account Type],"Asset")-SUMIFS(Ledger[Credit],Ledger[Account Type],"Asset")Total liabilities
=SUMIFS(Ledger[Credit],Ledger[Account Type],"Liability")-SUMIFS(Ledger[Debit],Ledger[Account Type],"Liability")Total equity
=SUMIFS(Ledger[Credit],Ledger[Account Type],"Equity")-SUMIFS(Ledger[Debit],Ledger[Account Type],"Equity")Then calculate total liabilities and equity by adding the two totals. The balance-sheet layout examples use the same basic idea: subtotal asset sections, subtotal liability sections, calculate equity, and compare the totals.
Free tools Windows power users keep installed
One-click scans. No signup required.
Revenue and expenses generally flow through profit or loss and ultimately affect equity. Do not simply place them on the balance sheet without explaining how the period’s profit or loss is transferred to equity.
7. Add reconciliation checks
Balance-sheet check
If B10 is total assets, B20 is total liabilities, and B25 is total equity, use:
=B10-(B20+B25)The expected result is 0. A logical alternative is:
=B10=B20+B25A zero result confirms mathematical agreement among the displayed figures. It does not prove that every transaction was recorded, classified correctly, reconciled to a bank statement, or adjusted for accruals, depreciation, taxes, or other accounting requirements.
Recommended Free Tools
Trial-balance check
For a balanced journal, use:
=SUM(Ledger[Debit])-SUM(Ledger[Credit])The expected result is also 0. A nonzero value may indicate an incomplete journal or an unbalanced entry.
8. Add an account-classification lookup
A separate chart-of-accounts table helps prevent inconsistent classifications. For example, it can contain account names and their types. With modern Excel, retrieve the type with:
=XLOOKUP([@Account],Accounts[Account],Accounts[Type],"Unmapped")XLOOKUP availability depends on the Excel edition and platform. For broader legacy compatibility, use:
=VLOOKUP(A2,Accounts!$A$2:$B$100,2,FALSE)The related profit-and-loss and balance-sheet example demonstrates lookup-based debit/credit classification and conditional aggregation.
9. Prevent common errors
Blank transaction rows
To keep unused rows blank, use:
=IF(COUNTA(C6:D6)=0,"",E5+C6-D6)If you also want to suppress a result when the previous balance is blank:
Best Value
- Designed for Your Windows and Apple Devices | Install premium Office apps on your Windows laptop, desktop, MacBook or iMac. Works seamlessly across your devices for home, school, or personal productivity.
- Includes Word, Excel, PowerPoint & Outlook | Get premium versions of the essential Office apps that help you work, study, create, and stay organized.
- Up to 2 TB Shared Cloud Storage | Store and access your documents, photos, and files from your Windows, Mac or mobile devices.
- Premium Tools Across Your Devices | Your subscription lets you work across all of your Windows, Mac, iPhone, iPad, and Android devices with apps that sync instantly through the cloud.
- Share Your Family Subscription | You can share all of your subscription benefits with up to 6 people for use across all their devices.
=IF(COUNTA(C6:D6)=0,"",N(E5)+N(C6)-N(D6))Be careful with N(): it converts nonnumeric text to zero and can hide data-entry errors. For accounting work, visible errors are often safer.
Both debit and credit entered
For a simple one-account cashbook, flag rows containing both:
=IF(AND(C6<>"",D6<>""),"ERROR: enter debit or credit, not both","")This check is not appropriate for every journal. A true double-entry journal may use separate lines for both sides of a transaction.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Negative amounts
If your convention requires positive amounts in both columns, use:
=IF(OR(C6<0,D6<0),"ERROR: use positive amounts","")Order, duplicates, and missing opening balance
- Keep dates and a transaction ID, and sort chronologically. Sorting a running-balance ledger by description or amount breaks the sequence.
- Use a unique transaction ID and check duplicates with
=COUNTIF($A:$A,A6)>1. - Enter the opening balance in a clearly labeled cell; otherwise every later balance can be wrong.
- Protect formula cells and keep versioned backups before importing or rearranging transactions.
Locale and Table issues
Some Excel installations use semicolons instead of commas between function arguments. If Excel rejects a formula, replace argument commas with semicolons according to your regional settings. Structured references such as [@Debit] belong inside an Excel Table and may require adaptation in Google Sheets, LibreOffice Calc, or an ordinary range.
When Excel is—and is not—the right tool
Excel is suitable for a small, simple cashbook, a learning exercise, or a manually maintained worksheet with few users and straightforward transactions. A collaborative browser worksheet such as Google Sheets may suit a basic shared cashbook, while LibreOffice Calc is a desktop alternative. Exact Excel Table compatibility should not be assumed in either application.
Consider structured accounting software when you need bank feeds, reconciliation workflows, audit trails, multiple users, recurring transactions, inventory, payroll, tax reporting, or accountant collaboration. Products such as QuickBooks and Xero are designed for those broader workflows, but current plans and suitability depend on your country and requirements. A spreadsheet that balances is not automatically a complete or compliant set of books.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteConclusion
For one cash or bank account, start with =E5+C6-D6 and fill it down. Use the OFFSET version only when its dynamic reference is useful, and use an Excel Table when new transactions will be added regularly. For a real balance-sheet report, classify accounts and aggregate them with SUMIFS or a PivotTable, then verify Assets - Liabilities - Equity = 0. Correct classification, chronological records, and reconciliation matter as much as the formula.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
