Google Sheets Formulas Every Landlord Needs (Copy-Paste Ready)

I've been managing 12 units in Google Sheets for 3 years. Along the way I've built up a collection of formulas that I actually use every month.

Not theoretical stuff. Formulas that solve real problems. Copy them, paste them, change the cell references to match your sheet, and you're good.

1. Days Overdue

What it does: Shows how many days a payment is past due. Returns 0 if the tenant has paid or rent isn't due yet.

The formula:

=IF(AND(E2="",TODAY()>D2),TODAY()-D2,0)

Where to put it: In your "Days Late" column. D2 is the due date, E2 is the date paid.

Example output: If rent was due Jan 1 and today is Jan 8 with no payment, it shows 7.

2. Late Fee Calculation

What it does: Calculates the late fee based on your policy. This example charges $50 flat plus $5/day after day 5.

The formula:

=IF(F2>5, 50+(F2-5)*5, IF(F2>0, 50, 0))

Where to put it: In a "Late Fee" column. F2 is your Days Overdue column.

Example output: 3 days late = $50. 10 days late = $75 ($50 + 5 days x $5). On time = $0.

3. Payment Status

What it does: Shows Paid, OVERDUE, or Current based on payment data.

The formula:

=IF(E2<>"","Paid",IF(TODAY()>D2,"OVERDUE","Current"))

Where to put it: In your "Status" column. E2 is date paid, D2 is due date.

Example output: "Paid" if there's a payment date. "OVERDUE" if past due with no payment. "Current" otherwise.

4. Vacancy Rate

What it does: Calculates what percentage of your units are vacant right now.

The formula:

=COUNTIF(B2:B20,"VACANT")/COUNTA(B2:B20)

Where to put it: In a summary section or dashboard. B2:B20 is your Tenant column. Mark vacant units with "VACANT."

Example output: If 2 of 12 units are vacant, it shows 16.7%. Format the cell as a percentage.

5. Total Outstanding Rent

What it does: Adds up rent for all units that haven't paid yet.

The formula:

=SUMIF(E2:E20,"",C2:C20)-SUMIF(G2:G20,"Current",C2:C20)

Where to put it: Summary section. E2:E20 is date paid, C2:C20 is rent amount, G2:G20 is status.

Simpler version:

=SUMIF(G2:G20,"OVERDUE",C2:C20)

Example output: If 3 units at $1,200 each are overdue, it shows $3,600.

6. Rent Collected by Property

What it does: Totals rent collected for a specific property across all entries.

The formula:

=SUMIF(B:B,"123 Main St",E:E)

Where to put it: Summary or dashboard tab. B is your Property column, E is your Amount column.

Example output: All payments received for 123 Main St. Change the property name for each row.

7. Count of Overdue Units

What it does: Counts how many units are currently overdue.

The formula:

=COUNTIF(G2:G20,"OVERDUE")

Where to put it: Top of your sheet or dashboard. G2:G20 is your Status column.

Example output: If 3 units show OVERDUE, it returns 3. When this number is 0, you're having a good month.

8. Lease Duration Remaining

What it does: Shows how many months are left on a tenant's lease.

The formula:

=DATEDIF(TODAY(),H2,"M")

Where to put it: In a "Months Left" column. H2 is the lease end date.

Example output: If the lease ends June 30 and today is February 7, it shows 4. When it hits 2, start thinking about renewal.

9. Auto-Fill Status with ARRAYFORMULA

What it does: Fills in the Status column automatically for every row. No dragging formulas down.

The formula:

=ARRAYFORMULA(IF(A2:A="",,IF(E2:E<>"","Paid",IF(TODAY()>D2:D,"OVERDUE","Current"))))

Where to put it: In G2 only. Delete any other formulas in the G column. This one formula handles every row.

Example output: Same as the regular Status formula, but it auto-extends. Add a new unit in row 25 and it's already covered.

10. Pull Specific Tenant Data with QUERY

What it does: Pulls a filtered view of your data. This example shows only overdue tenants.

The formula:

=QUERY(A1:H20,"SELECT A,B,C,F WHERE G='OVERDUE' ORDER BY F DESC")

Where to put it: A separate "Overdue" tab or dashboard section. Adjust the range and columns to match your sheet.

Example output: A clean table showing only Unit, Tenant, Rent Amount, and Days Late for overdue units. Sorted by most days late first.

11. Maintenance Request Age

What it does: Shows how many days a maintenance request has been open.

The formula:

=IF(I2="",TODAY()-A2,I2-A2)

Where to put it: In your maintenance tracker. A2 is request date, I2 is completion date.

Example output: If a request came in 6 days ago and it's still open, it shows 6. If it was completed in 3 days, it shows 3. Great for tracking response times.

12. Monthly Expense Total

What it does: Adds up expenses for a specific month and year.

The formula:

=SUMPRODUCT((MONTH(Expenses!A:A)=1)*(YEAR(Expenses!A:A)=2025)*(Expenses!E:E))

Where to put it: Summary or dashboard tab. Change the month number (1=Jan, 2=Feb) for each row.

Example output: Total expenses for January 2025. Line these up in a column and you can see spending trends month by month.

How to Use These

Copy the formula. Click the cell where you want it. Paste. Then update the cell references to match your specific sheet layout.

The column letters in my examples might not match yours. If your rent amount is in column D instead of column C, change every C reference to D. That's the only adjustment you need to make.

For more on tracking overdue rent specifically, check out our guide on tracking overdue rent in Google Sheets. And for the full spreadsheet management approach, read managing rental properties with spreadsheets.

Formulas are powerful. But they only work if you open the sheet. The landlords who actually catch late payments on day 1 are the ones who check every single morning. Or the ones who set up something to check for them.