Inside the two-tab Excel (.xlsx) workbook architecture
Most spreadsheet rent logs fail for structural reasons: inserting a mid-month partial payment shifts relative cell references out of alignment, or typing charges and credits into one mixed column makes annual tax totals tedious to extract. Every workbook exported from our rent ledger template for Excel uses a fixed ten-column accounting structure paired with a linked tax summary worksheet.
| Column | Header | Type / Formula logic |
|---|---|---|
A |
Period | Month and year text label (for example Nov 2026). |
B |
Status | IF(...) status badge formula resolving to Open, Paid, Partial, Unpaid, or Credit. |
C:E |
Rent Due, Other, Late Fee | Separate positive charge columns formatted as $#,##0.00. |
F |
Amount Paid | Positive gross payment column summed into Schedule E cash receipts. |
G:H |
Date Paid & Method | ISO or calendar date plus standardized payment channel (ACH, Zelle, Check, etc.). |
I |
Running Balance | Cumulative formula adding prior balance plus C:E charges minus F payments. |
J |
Memo / Ref | Wrapped multi-line note column for check numbers, utility invoice IDs, or adjustments. |
On the blank 12-month workbook downloaded from Download Blank Excel (.xlsx) above, each monthly row wraps the running-balance calculation inside =IF(COUNTA(A9,C9:F9)=0,"",SUM(E$5,C$9:E9)-SUM(F$9:F9)). Using SUM across range blocks rather than chaining + arithmetic prevents #VALUE! or cascading errors if you skip a row or leave optional columns blank. Sheet 2 (Summary & Tax Prep) links directly to Sheet 1 via cross-sheet references ('Rent Ledger'!C21) and computes collections by payment method with =SUMIF(...), giving bookkeepers an immediate breakdown across ACH, Zelle, Venmo, Check, Money Order, Cash, Card, and Portal deposits.
Importing into Google Sheets or extending CSV exports
Both the pre-filled Excel (.xlsx) export and the blank 12-month .xlsx file open cleanly in Microsoft Excel, LibreOffice Calc, Apple Numbers, and Google Sheets. To collaborate online with a co-owner, partner, or CPA in Google Sheets:
- Open Google Drive or a blank Google Sheet and select File > Import > Upload.
- Drop your downloaded
.xlsxfile into the uploader and choose Replace spreadsheet (or Insert new sheet(s) when adding a unit tab to an existing portfolio workbook). - Confirm Row 7 remains frozen under View > Freeze so column headers stay anchored while scrolling longer multi-year tenancies.
If you prefer working from the lightweight CSV download inside desktop Excel, select cell A3, press Ctrl+T (or Cmd+T on Mac) to convert the ten-column block into a named Excel Table, and enable Total Row under Table Design so new monthly rows inherit formatting and balance formulas automatically. If you need a browser-ready printable schedule without opening spreadsheet software, return to our free rent ledger template to print directly or customize layouts in PDF Studio.
Multi-unit tab structure and prorated rent formulas
Portfolio owners with two to twenty doors often try to save time by placing every tenant on one chronological cashflow sheet. That layout works as a building rent roll, not as a tenant-level ledger, because an overpayment or unpaid late fee in Unit 2B immediately corrupts the cumulative figure shown for Unit 2A.
Duplicate one worksheet tab per active lease and label each tab by unit (418-2B-Rivera, 712-3C-Patel). When a tenant moves in or vacates mid-month, avoid guessing rounded fractions in your head: check whether your lease requires actual calendar days (=BaseRent/DAY(EOMONTH(StartDate,0))*OccupiedDays) or a 30-day uniform month (=BaseRent/30*OccupiedDays), paste the resulting dollar figure into column C (Rent Due), and record the formula basis in Memo / Ref. For detailed guidance on separating prorated charges, utility rebills, and security deposits across a full tenancy, review our tenant ledger guide.
Reconciling Zelle, Venmo, and bank CSV exports
Digital payment apps make collecting rent fast, yet a filtered Zelle or bank statement CSV cannot replace a master Excel rent ledger. Bank downloads list cash inflows chronologically; they never record charges billed, lease grace windows, or unpaid balances when a resident skips a pass-through water bill.
At the end of each month, open your Summary & Tax Prep worksheet tab alongside your checking account CSV export and verify each channel total (ACH, Zelle, Venmo, Check) row by row. When a bank total matches a method row on Sheet 2, you have verified both sides of your books: every dollar deposited is credited to a specific tenancy, and every uncollected charge remains visible in column I.