Google Sheets Rent Roll: Complete Setup Guide with Formulas

If you manage rental properties, you need a rent roll. It's just a list of every unit, who lives there, what they owe, and whether they've paid. Nothing fancy.

I ran mine in a notebook for the first year. Then I lost the notebook. So I moved to Google Sheets and never looked back.

Here's exactly how to build a rent roll in Google Sheets that actually works. I'll give you the column headers, the formulas, and the conditional formatting. By the end, you'll have a working rent roll you can use today.

Step 1: Create Your Sheet

Open Google Sheets. Create a new blank spreadsheet. Name it something like "Rent Roll 2025" or whatever year you're in.

Rename the first tab to the current month. January, February, whatever. You'll duplicate this tab each month.

Step 2: Set Up Your Column Headers

Row 1 is your header row. Here are the 8 columns you need:

A: Unit The unit number or address. "4B" or "123 Main St #2." Whatever you use to identify each door.

B: Tenant Tenant's full name. If it's vacant, type "VACANT."

C: Rent Amount Monthly rent for that unit. Format this column as currency.

D: Due Date When rent is due. For most landlords, this is the 1st of the month. Format as a date.

E: Date Paid When you actually received payment. Leave blank until they pay.

F: Days Late Calculated automatically. Don't type anything here.

G: Status Also calculated automatically.

H: Notes Partial payments, bounced checks, whatever you need to remember.

That's it. 8 columns. Everything else is extra.

Step 3: Enter Your Data

Starting in row 2, fill in each unit. One row per unit. If you have 8 units, you'll have rows 2 through 9.

For the Due Date column, enter the actual date. Like 01/01/2025. Not "the 1st." Google Sheets needs a real date to do math with it.

Leave the Date Paid column blank for now. You'll fill it in as payments come in.

Step 4: Add the Days Late Formula

Click on cell F2. This is where it gets good. Paste this formula:

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

Here's what it does in plain English: If there's no payment date AND today is past the due date, calculate how many days late. Otherwise, show 0.

So if rent was due January 1st, nobody's paid, and today is January 8th, this cell shows 7. If they've paid, it shows 0.

Copy this formula down for every row. Select F2, grab the little blue square in the bottom-right corner, and drag it down to your last row.

Step 5: Add the Status Formula

Click on cell G2. Paste this:

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

This one's even simpler. If there's a payment date, it says "Paid." If today is past the due date and there's no payment, it says "OVERDUE." Otherwise, "Current."

Three possible values. Paid, OVERDUE, or Current. That's all you need to know at a glance.

Copy this formula down to match your other rows.

Step 6: Add a Summary Section

Skip a row below your last unit. Let's say your last unit is row 13. Go to row 15.

In A15, type "Total Expected." In C15, put: =SUM(C2:C13)

In A16, type "Total Collected." In C16, put: =SUMIF(E2:E13,"<>",C2:C13)

This adds up rent only for units where the Date Paid column isn't blank. So it's your actual collected rent.

In A17, type "Total Outstanding." In C17, put: =C15-C16

In A18, type "Overdue Count." In C18, put: =COUNTIF(G2:G13,"OVERDUE")

These 4 numbers tell you everything. Total expected, total collected, what's still out there, and how many units are late.

Step 7: Conditional Formatting

This is the part that makes your sheet actually useful at a glance. Select your entire data range, from A2 to H13 (or however many rows you have).

Go to Format > Conditional formatting.

Rule 1: Overdue rows turn red. Set "Custom formula is" to =$G2="OVERDUE". Pick a red background with white text. This is your "something's wrong" signal.

Rule 2: Paid rows turn green. Set "Custom formula is" to =$G2="Paid". Pick a light green background. This is your "all good" signal.

Rule 3: Current rows stay neutral. You don't need a rule for this. The default white background works fine.

Now your sheet lights up like a traffic light. Open it, scan for red, make your calls. Green means you're good. Takes 5 seconds.

Step 8: Freeze the Header Row

Go to View > Freeze > 1 row. Now when you scroll down, your column headers stay visible. Small thing, but it matters when you have 20+ units.

Step 9: Duplicate for Next Month

At the end of the month, right-click the tab and select "Duplicate." Rename the new tab to the next month. Clear the Date Paid and Notes columns. Update the Due Dates.

Your formulas carry over. Your formatting carries over. Takes 2 minutes.

Optional: Add a Grace Period Column

If you give tenants a grace period (most landlords give 3-5 days), you can add a Grace Period End column between Due Date and Date Paid.

Then change your Days Late formula to reference the grace period end date instead of the due date. So rent is "due" on the 1st but not "late" until the 6th.

Your call on whether you want this. I use it. It keeps me from seeing red on the 2nd when my lease actually gives tenants until the 5th.

What You Should Have Now

A clean, working rent roll with automatic status tracking. Open the sheet, see what's overdue, take action. No scrolling through texts. No checking your bank app. Everything's in one place.

If you want a head start, grab our free rent payment tracker template. It's pre-built with all of these formulas and formatting.

The biggest risk with a rent roll isn't building it. It's forgetting to check it. Build it today, get in the habit of opening it every morning, and you'll catch late payments on day 1 instead of day 11.