How to set up a rent ledger in Google Sheets
Google Sheets runs in your browser, saves as you type and keeps a version history, which makes it a practical home for a rent ledger if you own 1 to 10 units. This page walks through building one step by step: the tabs, the columns, a running balance per tenant, and the Google Sheets settings that help keep it tidy. Near the end, this page describes Rentlark, the spreadsheets sold on this site, so you can judge whether it fits.
For why a ledger is laid out this way, see How to set up a rent ledger in a spreadsheet. For the wider routine (expenses, repairs, year end), see the guide: How to track rent and expenses in a spreadsheet.
Step 1: Create two tabs
Start a new spreadsheet and add two tabs at the bottom:
- Ledger: one row per charge or payment, oldest at the top.
- Lists: short lists you pick from: your unit IDs, your tenants' names and the row types (Rent charge, Payment, Late fee, Credit).
Keeping the lists on their own tab means a name is spelled the same way every time, and the balance formula below depends on that.
Step 2: Add the columns
On the Ledger tab, put these headers in row 1:
| Column | What goes in it |
|---|---|
| A Date | The day rent came due, or the day a payment arrived |
| B Unit | A short, fixed unit ID (for example OAK-1) |
| C Tenant | Picked from the Lists tab |
| D Type | Picked from the Lists tab |
| E Charge | Money owed. Blank on payment rows |
| F Payment | Money received. Blank on charge rows |
| G Balance | That tenant's running balance (formula) |
| H Method | Check number, transfer reference |
| I Note | For example "October rent" or "second half of October" |
Format column A as dates (Format > Number > Date) and columns E to G as currency (Format > Number > Currency). A date typed as text won't sort or filter correctly.
Step 3: Freeze the header and add dropdowns
- Freeze row 1 with View > Freeze > 1 row, so the headers stay visible as the ledger grows.
- Add dropdowns for Tenant and Type: select column C, open Data > Data validation, click Add rule, choose "Dropdown (from a range)" and point it at the tenant list on your Lists tab. Repeat for column D with the type list. Choosing from a list stops "J. Smith" and "John Smith" from becoming two tenants.
Step 4: Add a running balance per tenant
In G2, then copied down the column:
=SUMIFS($E$2:E2, $C$2:C2, C2) - SUMIFS($F$2:F2, $C$2:C2, C2)
It adds every charge and subtracts every payment for the tenant in that row, from the top of the ledger down to the current row. A positive number is owed; a negative number is a credit. SUMIFS is available in Google Sheets and in Microsoft Excel 2016 or later. If two tenants share a name, add the unit ID to the name in your list, or give each lease its own ID.
Step 5: Post charges and payments as they happen
- Post the rent charge on the due date, even before the tenant pays, so the balance always shows what is owed.
- Post each payment on the day it arrives, with the method. A tenant who pays in two parts gets two payment rows. The page on partial rent payments covers that case in more detail.
- Never type over an old row. To fix a mistake, reverse it with a negative row matching the wrong entry (same type and amount), with a note saying why, then enter the right amount: for a wrong payment, a negative Payment row.
Add a late fee as its own charge row, and only if your lease provides for it. Rules on late fees, and on accepting partial rent, vary by state and city, so check yours.
Step 6: Protect the formula column
Select column G, open Data > Protect sheets and ranges, and choose "Show a warning when editing this range". Anyone who starts typing over a balance then gets a prompt first. Google notes that protection isn't a security measure: it guards against accidental edits, not against someone with access to the file.
Step 7: Look at one tenant at a time with a filter view
A filter view lets you show only one tenant's rows (filter the Tenant column) without hiding rows for anyone else who has the file open. Create one from the Data menu and give it the tenant's name, so you can reopen it later. Filters only hide rows; the balance in column G still counts every row above it.
Step 8: Keep sharing restricted
Your ledger holds tenants' names and payment history. In the Share settings, keep general access Restricted and add only the people who need it, such as your bookkeeper. Keep security deposits on their own tab, so deposit money is never mistaken for rent; some states and cities have rules on how deposits are held, so check yours.
Step 9: Do a monthly check
Once a month, read each tenant's latest balance and match the payment rows to your bank statement. Anything still positive is open, and the rows above it show how it built up. If a cell looks wrong, File > Version history > See version history shows earlier versions of the sheet. The free one-page checklist covers the full monthly routine: Download the Small landlord monthly record-keeping checklist (PDF).
If you'd rather not build it yourself: Rentlark
Rentlark is a set of rent and expense tracking spreadsheets for owners of 1 to 10 units and house hackers, made by AI agents. It doesn't use the ledger layout above: rent is tracked in a Rent Status tab, a lease-by-month grid showing due, paid and balance. You log each payment by lease, and Rentlark applies each payment to the oldest unpaid month first; its "For month" column is for your reference only. What it includes:
- Tracks rent per unit and per lease. Designed for Google Sheets and Microsoft Excel 2016 or later.
- A Rent Status tab shows each lease month by month with statuses such as Paid, Paid late, Partial, Unpaid, Late, Credit and Vacant.
- Rent Status carries partial payments and credit forward to the next month for each lease.
- An Expenses tab where each cost is logged once.
- A Repairs tab with opened date, unit, issue, priority, vendor, status, closed date and cost.
- An Annual Summary that totals rent and expenses by property, a records summary for your tax preparer.
- Expense categories follow the IRS Schedule E expense lines, to help you organize records for your tax preparer. Not tax advice. Based on the IRS draft 2026 Schedule E; categories may be updated if the final form changes.
What it isn't: it tracks the rent you record; payments don't run through it, and it has no bank connections. It has no leases, notices or screening forms.
Price: the price on our Gumroad page. One-time purchase, no subscription. Free updates within version 1.x. 30-day refund through Gumroad. You must be 18 or older to buy. Gumroad may add sales tax or VAT at checkout.
On Google Sheets: Rentlark comes as .xlsx files. To use Google Sheets, upload the .xlsx file to Google Drive and open it with Google Sheets. In Google Sheets, an uploaded copy doesn't include the edit warnings that protect the formula cells, so type only in the input cells.
FAQ
Do I need any add-ons or scripts for this ledger? No. Everything on this page uses built-in Google Sheets features: SUMIFS, data validation, frozen rows, protected ranges and filter views.
Can tenants see my rent ledger in Google Sheets? Only if you share the file with them. Keep general access Restricted and share only with people who need it.
Why does a balance look wrong after I sort the ledger? The running balance adds the rows above it, so it only reads correctly with the oldest row at the top. Sort the whole ledger by Date, oldest first, never one column on its own.
Does Rentlark take rent payments from tenants? No. Rentlark tracks the rent you record. Payments don't run through it.
Which spreadsheet apps is Rentlark designed for? For Google Sheets and Microsoft Excel 2016 or later.
Who makes Rentlark? Rentlark is made by AI agents for seller Tomer Avni. Questions: rentlark@mail.grokbot.com.
Made by AI agents. Seller: Tomer Avni. Support: rentlark@mail.grokbot.com. Not tax or legal advice.
How this was made: this page was researched and written by AI agents for seller Tomer Avni.
Rentlark home · Rent and expense tracking guide · Monthly checklist (PDF) · Terms · Privacy · Refund policy