How to clean a tenant ledger spreadsheet before moving it into software
A clean tenant ledger has one row per charge or payment, one date format, one tenant name spelling and a running balance that matches what the tenant owes. Fix those four things in the spreadsheet first, then carry over only the balance as of your cutover date. Years of history rarely need to be imported; they need to be saved.
What should one ledger row look like?
Whatever the software, a ledger boils down to the same columns. Put each tenant's history in one table so every row says what happened and when.
| Column | Example value | Check |
|---|---|---|
| Date | 2026-09-01 | One format, a real date |
| Property and unit | 14 Elm St, 2B | Same spelling every time |
| Tenant | Dana Rivera | One spelling per person |
| Type | Rent charge, Payment, Late fee, Deposit | A short list, used consistently |
| Amount | 1250.00 | A plain number, charges positive and payments negative |
| Note | Check 2041 | Optional, for your own reference |
What are the common problems?
- Merged cells and blank rows between months.
- Dates typed as text, such as "9/1" with no year.
- A tenant named three ways: "D Rivera", "Dana Rivera" and "Rivera, D".
- Rent and deposit mixed in one column, so the balance looks wrong.
- Partial payments recorded as a note instead of a row.
- Totals typed over formulas, so the sheet shows a balance the rows do not support.
Fix them one column at a time. Sort by tenant, then by date, and look for gaps: a missing month is usually a missing row.
How do you check the balance?
Add up every charge and every payment for a tenant. The difference is the balance. Then compare it with what the tenant says and with the bank deposits.
Example a made-up tenant has charges of $1,250 a month for 6 months, so $7,500, and a $50 late fee, so $7,550. Payments received add up to $7,300. The balance owed is $7,550 - $7,300 = $250. If your sheet shows $200, one row is wrong; find it before you carry the number over.
Do this for every tenant, then add the balances. That total should equal what your rent reports say is outstanding across the portfolio.
How much history should you bring?
Bring the balance as of the cutover date, the current lease terms and the deposit held. Keep the full ledger as a file. The IRS page on how long to keep records explains that records supporting your tax return should be kept for as long as they may be needed, so save the originals with your bank statements.
- Import: current tenants, rent, lease dates, deposits, balance on cutover date.
- Save as files: every ledger, by year, as a spreadsheet and a PDF.
- Do not retype old payments to make the new system look complete.
What mistakes do people make with the ledger?
The most common is importing too much. Owners retype years of payments to make the new system look complete, then spend weeks fixing rows that no one will read again. The second is changing the old ledger after the export, so the two disagree. Freeze the old sheet the day you export it.
The third is skipping the tie-out. A balance that is off by a small amount, such as $50, usually hides one wrong row, and a small error carried into a new system is harder to find there than in the sheet you know.
How DoorKeeping handles it
Once your rent roll is clean you can import it from a CSV or XLSX file and confirm the units, tenants and leases it reads. The same check, balance by balance, tells you the import is right.
DoorKeeping costs $2 per unit per month with every feature included, no setup cost and no minimum. You import a rent roll from a CSV or XLSX file, and each property gets its own books. Tenants pay your own bank directly, and anything involving money waits for your one-tap approval.
Sources
- IRS, How long should I keep records?, read Oct 30, 2026
Questions
- Do I need to import every old payment?
- No. Import current leases, deposits and the balance on the cutover date, and keep the old ledgers as files.
- What date format should I use?
- Use one format throughout, such as 2026-09-01, so no software has to guess the month and day.
- How do I record a partial payment?
- As its own payment row for the amount received, so the running balance stays correct.