Who wants to dig through forty rows of payment notes just to see if everyone paid their share of the rental deposit? Putting a dedicated dashboard tab on top of your ledger solves that immediately. It gives the house a single view of the total money collected, what remains in escrow, and who still owes cash. You keep your raw log untouched while everyone sees the numbers that actually matter.
Nobody accidentally overwrites a formula. It keeps move-in week calm.
To make this work cleanly, you need two tabs. Call the first one Ledger and the second one Dashboard. It sounds simple, but people often try cramming summary boxes into the top rows of their transaction sheet, which gets messy fast when rows get added or sorted. Keep them apart.
Set Up the Ledger as a Named Table
A dashboard only works if the source data stays consistent. Organizing your raw payments into a structured table keeps new entries from falling outside your formula ranges.
| Column | Data Type | Example Entry | Purpose |
|---|---|---|---|
| Date | Date | 2026-08-01 | Tracks when cash moved |
| Roommate | Text | Jordan | Identifies payer or payee |
| Category | Dropdown | Initial Deposit | Splits deposits, refunds, and interest |
| Amount | Currency | 1200.00 | Exact dollar figure |
| Status | Dropdown | Paid | Marks Pending, Paid, or Returned |
| Notes | Text | Sent via Zelle | Stores confirmation IDs or bank details |
Highlight those rows and use the Google Sheets Tables feature by selecting Format and then Convert to table. Once converted, name the table DepositLedger in the table menu. This lets you write clean formulas like DepositLedger[Amount] instead of tracking messy cell coordinates across sheets. Clean names prevent broken references.
Build the Core Metric Cards
Switch to your Dashboard tab and reserve the top two rows for high-level cards. Most shared apartments only need three core totals: total deposits collected, net cash currently held, and any deductions logged against the house. Turns out, structured references make these formulas easy to read.
Total Collected:
=SUMIFS(DepositLedger[Amount], DepositLedger[Category], "Initial Deposit", DepositLedger[Status], "Paid")
Total Held in Account:
=SUMIFS(DepositLedger[Amount], DepositLedger[Status], "Paid") - SUMIFS(DepositLedger[Amount], DepositLedger[Category], "Refund")
Total Deductions:
=SUMIFS(DepositLedger[Amount], DepositLedger[Category], "Deduction")These three cards update the second you log a new line on the ledger. They update instantly.
Track Individual Roommate Stakes
A roommate paying the full $3,000 security deposit upfront does not necessarily own a 100 percent stake in the eventual refund. If three roommates agreed to an equal split, each person holds a $1,000 baseline share. Problems happen when move-out arrives and someone claims they should get more back because their personal check went to the landlord on day one. Your summary should separate initial out-of-pocket payments from agreed ownership stakes. Set up a side table for individual balances.
- Agreed share: List each person's target deposit amount based on rent ratio or equal division.
- Paid so far: Calculate actual contributions with
=SUMIFS(DepositLedger[Amount], DepositLedger[Roommate], "Sam", DepositLedger[Status], "Paid"). - Remaining owed: Subtract paid amounts from the agreed share to flag who still needs to reimburse the house.
- Estimated refund: Multiply the net funds held by each person's percentage stake once the lease wraps up.
Format share figures as percentages so typing 50 registers as 50% rather than 5,000%. That small formatting oversight is notorious for turning a routine $2,000 deposit into a phantom six-figure balance. Always double-check cell formats.
Pull Pending Payments Automatically
Instead of scanning rows for unpaid contributions, let a filter formula do the work. The QUERY function reads your table and extracts rows that need attention right onto the dashboard.
=QUERY(DepositLedger, "SELECT Roommate, Category, Amount WHERE Status = 'Pending'", 1)
When an unpaid pet deposit or a partial move-in transfer is marked Pending, it stays visible on the summary screen. Change the dropdown to Paid on the ledger tab, and the line disappears immediately. You never have to delete or retype anything on the dashboard. It runs on its own.
Settle Interest and Move-Out Deductions
Security deposit rules differ by state. Some jurisdictions require landlords to hold deposits in dedicated, interest-bearing escrow accounts and return accrued interest annually or at lease end. Other states impose no interest rules at all. If your group holds funds in a high-yield account before handing them over, or if your landlord sends back interest alongside itemized repair deductions, handle the settlement through a clear four-step process:
- Log interest as income: Enter any bank interest received under the Category dropdown with a positive amount.
- Log deductions as expenses: Enter landlord repair or cleaning deductions as negative values or tag them under a Deduction category.
- Audit the final balance: Verify that original deposits plus interest minus deductions match the bank payout check down to the cent.
- Allocate individual deductions: If damage occurred in a private bedroom, assign that deduction row directly to the responsible person rather than splitting it across the group.
Shared common-area deductions get split according to each roommate's original percentage stake. That prevents bitter arguments about who chipped the baseboard or scratched the kitchen counter during moving day. Keep written receipts on file. Save every single invoice.
Protect Sheets and Share Access
Giving everyone full editing rights on a shared spreadsheet is an open invitation for broken formulas. Someone accidentally types a number into a cell containing a SUMIFS formula, and suddenly your whole dashboard goes blank. Thing is, hiding the ledger tab does not actually stop anyone with edit permissions from unhiding it and altering rows. You need explicit permission rules. It takes two minutes.
- Give roommates View-Only access: Share the main Google Sheet with View-Only permissions for everyone except the primary manager, which lets roommates check their balances without modifying cells.
- Protect the Dashboard tab: Go to Data, select Protect sheets and ranges, choose the Dashboard sheet, and set permissions so only you can make edits to the layout.
- Unlock designated input cells: If roommates must log their own payments, protect the Ledger tab but use the "Except certain cells" option to keep the Date, Amount, and Notes columns editable.
Before inviting your housemates, test the sheet by entering a sample payment and verifying that the dashboard metric cards and QUERY table update as expected. Once the math checks out, drop the link into your household group chat with a quick note explaining where each person can verify their balance. Clear numbers keep housemates friendly. That prevents messy disputes.