- Introduction
- How to Build a Daily Cash Journal in Excel (Step-by-Step)
- Step 1: Lay Out the Daily Journal Sheet
- Step 2: Number-Format and Freeze
- Step 3: Build the Account Drop-Down
- Step 4: Write the Running Balance Formula
- Step 5: Add the IF/AND Guard to Hide Empty Rows
- Step 6: Build the Account Summary With SUMIFS
- Step 7: Add Verification Totals
- Step 8: Log Real Transactions and Reconcile
- Template Features
- When to Upgrade From Excel
- Frequently Asked Questions
- Start Logging Today
Introduction
Most freelancers can tell you roughly what’s in their checking account. Very few can say what moved through it yesterday, or why. That gap is where cash flow surprises come from: a deposit you forgot to log, a subscription billed twice, a week where Money Out quietly beat Money In.
Your bank statement is not a cash journal. It records what the bank did with your money, not what your business did — so reconciling at month-end means reverse-engineering decisions from thirty days ago, with no context for who was paid.
The fix: log each transaction as it happens, carry a running balance on every row, and roll the totals up by account. The Daily Cash Journal is a three-sheet workbook — Instructions, Daily Journal, and Account Summary — with a balance column that recalculates on every entry, a drop-down covering six account types (Cash, Checking, Savings, Credit Card, PayPal, Business Checking), and a summary sheet totaling Money In, Money Out, and Net per account with SUMIFS.
Download it free above, or build it yourself with the tutorial below in 25 minutes.
📥 Download the Daily Cash Journal Template Stop building from scratch. Get the pre-formatted workbook with the running balance formula, the IF/AND blank-row guard, the account drop-down, and the SUMIFS summary sheet already wired up. Save 30+ minutes of setup.
Tested by our data analysts at Healora Studio — we use this workbook to reconcile client payments across six account types, including PayPal and business credit cards.
How to Build a Daily Cash Journal in Excel (Step-by-Step)
Build in this order — the drop-down has to exist before the formulas, or the SUMIFS rollups break later.
Step 1: Lay Out the Daily Journal Sheet
Rename Sheet1 to Daily Journal and build this structure:
| Cell / Range | Content |
|---|---|
A4 / B4 |
“Opening Balance” label and starting figure |
A6:G6 |
Headers: Date, Description, Account, Money In, Money Out, Balance, Notes |
A7 onward |
One row per transaction |
Starting data at row 7 leaves room for a title block and gives formulas a fixed anchor at $D$7 that doesn’t shift when you insert rows.
Pro Tip from Healora Studio Analysts: Keep the opening balance in B4 as a hard number, never as a formula chained to another sheet. A broken upstream reference produces a wrong balance on every row — and a wrong balance column is worse than none, because you’ll trust it.
Step 2: Number-Format and Freeze
Select A7:A1000, press Ctrl + 1, and apply a Date format like mm/dd/yyyy. Format D7:F1000 as Currency and bold F7:F1000 — the balance is the column you scan. Then click A7 and use View > Freeze Panes > Freeze Panes.
[Insert Real Screenshot Here: the Daily Journal sheet with headers in row 6, a formatted Opening Balance in B4, and the freeze line sitting directly below row 6]
Step 3: Build the Account Drop-Down
Checking, checking, and Chk are three different accounts to Excel, and each one breaks the summary.
- Select
C7:C1000, then openData > Data Validation - Set Allow to
List - In Source, paste:
Cash,Checking,Savings,Credit Card,PayPal,Business Checking - Click OK and confirm the arrow appears in
C7
If you add an account later, edit the validation source and reapply it to the whole column.
Step 4: Write the Running Balance Formula
In F7, enter:
=B4+SUM($D$7:D7)-SUM($E$7:E7)
Take the opening balance, add every Money In from row 7 down to the current row, subtract every Money Out. The mixed reference is the entire trick: $D$7 stays locked so the sum always starts at the first transaction, while D7 is relative and becomes D8 as you fill down. Drag F7 down the column and every row calculates its own live balance.
Step 5: Add the IF/AND Guard to Hide Empty Rows
Fill the formula down a thousand rows and $4,500.00 repeats below your last transaction. Replace it with this guarded version in F7:
=IF(AND(D7="",E7=""),"",$B$4+SUM($D$7:D7)-SUM($E$7:E7))
AND(D7="",E7="") asks whether both amount cells on this row are empty; if they are, the formula returns "" and the row displays blank. Note that $B$4 stays locked while the D7/E7 checks remain relative — that combination is what lets a single formula fill the whole column.
Step 6: Build the Account Summary With SUMIFS
Add a sheet with Shift + F11 and name it Account Summary. Put your account names in A2:A7, then enter these in row 2 and fill down:
=SUMIFS('Daily Journal'!$D$7:$D$1000,'Daily Journal'!$C$7:$C$1000,$A2)
=SUMIFS('Daily Journal'!$E$7:$E$1000,'Daily Journal'!$C$7:$C$1000,$A2)
=B2-C2
SUMIFS sums one range while testing another against a criterion: add up Money In, but only where the Account column equals A2. Because the criterion is a cell reference, one formula builds the whole table. Sheet names with spaces need single quotes — 'Daily Journal'! — or you get #NAME?.
Step 7: Add Verification Totals
Under the account table, add a totals row and a cross-check:
=SUM(B2:B7)
=SUM('Daily Journal'!D7:D1000)
They must match — as must SUM(B2:B7)-SUM(C2:C7) against your closing balance minus the opening balance. When they don’t, filter the Account column for blanks: a transaction with no account tag is invisible to every SUMIFS row.
[Insert Real Screenshot Here: the Account Summary sheet with the SUMIFS table filled in and the verification totals row matching the journal’s Money In total]
Step 8: Log Real Transactions and Reconcile
Enter five to ten actual transactions — not samples — and compare the final balance in column F against your real bank and PayPal balances. Logging then takes fifteen seconds per entry.
Pro Tip from Healora Studio Analysts: Log credit card spending as Money Out on the day it happens using the Credit Card tag, then log the card payment as a transfer when you pay the bill. Skipping the first entry overstates cash; skipping the second double-counts the expense.
Template Features
✅ Three pre-built sheets — Instructions, Daily Journal, and Account Summary ✅ Live running balance — every row recalculates as you type ✅ Blank-row guard — unused rows stay clean instead of repeating balances ✅ Account drop-down — six account types, preventing inconsistent entries ✅ SUMIFS account rollups — Money In, Money Out, and Net totaled per account ✅ Opening Balance anchor — one cell to update when you start a new period ✅ Compatible with: Excel 2016+, Microsoft 365, Google Sheets, Windows and macOS ✅ 100% unlocked — add accounts or columns freely; no macros, no protection
When to Upgrade From Excel
A spreadsheet journal is enough for one bank account, a couple of cards, and under roughly 100 transactions a month. Two things push people off it: volume, and accountant-ready reports.
QuickBooks Online — Best for Full Bookkeeping
Double-entry accounting, bank feed imports, and reports an accountant will accept without cleanup. Once you’re filing sales tax or issuing 1099s, Excel becomes a liability. – Pricing: Starts around $30/month for Simple Start, with frequent promotional discounts – [Affiliate Link: Start QuickBooks Online with a discount]
Xero — Best for Bank Reconciliation
Xero’s reconciliation interface is the most pleasant of the major platforms, and its unlimited-user pricing suits small teams where more than one person touches the books. – Pricing: Starts around $15–20/month for the entry tier – [Affiliate Link: Try Xero free for 30 days]
Expensify — Best for Receipt Capture
Expensify’s SmartScan reads a receipt photo and fills in merchant, date, and amount — the one data-entry step a journal can’t automate. – Pricing: Free for personal use with limited scans; paid plans from roughly $5–9 per user/month – [Affiliate Link: Sign up for Expensify free]
Our recommendation: Keep the spreadsheet until you’re logging more than 20 transactions a week, or until someone else needs to see your numbers.
Frequently Asked Questions
Is the Daily Cash Journal really free?
Yes — free for personal and business use, no email required. Affiliate links to software we’ve tested fund more free templates.
Can I use this template in Google Sheets?
Yes — upload the .xlsx via File > Import > Upload. The running balance, the IF/AND guard, and the SUMIFS rollups all work unchanged, and the validation converts into a working drop-down.
Why does my Balance column show a number on every empty row?
The plain formula can’t tell that a row is unused, so it repeats the last balance. Use the guarded version from Step 5 and empty rows display blank.
How do I track a transfer between two accounts?
Log it twice: Money Out on the source account, Money In on the destination, same date, described as “Transfer to Savings.” Net across accounts stays correct and each account shows true movement.
Start Logging Today
A cash journal only works if it’s current. Five transactions take two minutes; reconstructing a month takes an afternoon. Set your opening balance in B4 and log today before you close your laptop.
📥 Download the Daily Cash Journal Template Pre-formatted workbook with running balance, account drop-down, and SUMIFS summary — ready in seconds.
This tutorial and template are part of SheetMaster Hub by Healora Studio. For more data efficiency tools, visit healorastudio.com.