Tenant and Lease Tracking Spreadsheet: What to Track, and How to Catch a Lease Expiration Early

A rental property doesn't usually get into trouble because a landlord forgot the mortgage was due. It gets into trouble in smaller ways: a lease that quietly rolled into month-to-month because nobody flagged the renewal date, a security deposit amount nobody wrote down anywhere, a rent roll that's really just a guess because half the "active" tenants on the list gave notice weeks ago. A tenant and lease tracking spreadsheet fixes all three, and it doesn't take much — one sheet, nine columns, and one formula worth actually building properly.

Why a notes app or a folder of PDFs isn't enough

Most landlords start with something informal — a note on their phone, a folder of signed leases, a spreadsheet with just names and rent amounts. That works fine for holding information. It fails at the one thing that actually matters day to day: telling you something before it becomes a problem. A folder of lease PDFs can't flag that a lease ends in six weeks. A note on your phone can't total up what you're collecting in rent right now versus what you were collecting last quarter. The information exists, but it's passive — it only helps if you remember to go looking for it, and lease-end dates are exactly the kind of thing that's easy to forget to check.

A spreadsheet with the right structure flips that. Instead of information you have to remember to review, you get a sheet that tells you what needs attention the moment you open it.

The columns that actually matter

Keep the tenant log to one row per tenant, with these columns:

Status is worth doing properly as a dropdown rather than free text. "Active", "Notice Given" and "Vacant" typed consistently is what lets every other formula on the sheet — the rent roll, the active tenant count — actually work. Typed inconsistently ("active", "Active ", "occupied") it breaks quietly, and you won't notice until the totals look wrong.

The one formula worth building properly

Next to Lease End, add a formula that subtracts today's date from it: lease end date minus today. That single subtraction turns a static date into a live "days remaining" count that recalculates every time you open the file — no manual checking, no mental arithmetic every time you glance at the sheet.

On its own, a column of numbers still needs reading line by line. Add conditional formatting on top and it stops needing that: anything from 0 to 90 days out turns amber, and anything already negative — a lease that's past its end date — turns red. A sheet of fifty tenants becomes something you can scan in five seconds: no color, nothing due soon; amber, start the renewal conversation; red, that lease has already lapsed and the tenant is on borrowed time or month-to-month with no agreement in writing. That's the entire early-warning system — one subtraction and two formatting rules, and it never depends on you remembering to check a calendar.

Building a rent roll you can actually trust

"Rent roll" sounds like it should be complicated. It's one number: total expected monthly rent, right now, across every occupied unit. The trap is building it as a sum of every row in your tenant log — that counts rent from units where the tenant already gave notice and is moving out, which overstates what you're actually going to collect next month.

Build it as a conditional sum instead: add up Monthly Rent only where Status equals Active. Pair that with a count of active tenants and a count of leases falling inside your 90-day warning window, and three numbers answer the questions you actually ask day to day — how many tenants do I have, what am I actually collecting, and what needs my attention soon — without opening the full log at all.

Handling more than one property

The Property column is what lets one tenant log cover several addresses instead of one workbook per property. Keep property names identical, character for character, across every row — a dropdown sourced from a settings list rather than free-typed text is what keeps "123 Main St" and "123 Main Street" from splitting one property's numbers into two. With that in place, a property-level summary can look up each property's tenant status and rent with a simple lookup formula and show it on a dashboard — occupied or vacant, at a glance, for every address you own.

There's one real limit worth knowing before you rely on that summary: a lookup formula built to return one tenant per property name will only ever return the first match it finds. If you have a multi-unit building and you log every unit under the same property name, the summary will show you one of those tenants and silently ignore the rest — it won't show a combined status or a wrong number, it'll just show you the first row and nothing tells you it stopped there. The fix is straightforward: give each unit its own distinct property name — "456 Oak Ave Unit A", "456 Oak Ave Unit B" — and the lookup finds each one correctly, because each name only has one active tenant behind it. Don't treat a single property-name lookup as a way to track several tenants under one roof; it isn't built to summarize that, even though the underlying log can still hold every row you need.

What the dashboard should actually show you

Once the tenant log is set up, the numbers worth pulling onto a dashboard are the ones that answer questions you'd otherwise have to scroll for: how many tenants are currently active, what your total rent roll actually is right now, and how many leases fall inside your 90-day warning window at this exact moment. A per-property summary on top of that — one line per address, showing whether it's occupied and what it's bringing in — turns fifty rows of raw tenant data into something you can check in the time it takes to open the file, rather than something you have to read end to end every time you want an answer.

The point of building it this way isn't to make the sheet look impressive. It's that a landlord checking in for two minutes between other things should be able to see everything that needs attention without reading a single row of raw data — the dashboard does the reading for you, and the tenant log stays there as the detail underneath it when you need to dig in.

What this kind of spreadsheet doesn't do

Worth being direct about the edges. A tenant and lease tracking spreadsheet is a record — it doesn't screen prospective tenants, collect rent online, e-sign a lease, or send anyone an automated reminder without you opening the file. It's a place to keep the facts straight and get an early flag on dates that matter, built out of formulas and formatting, not a connected system that acts on your behalf. For a handful of properties, that's usually enough. Once you're coordinating showings and running screening across enough units that checking a spreadsheet becomes the bottleneck, that's the point where dedicated software starts earning its cost.

None of this is legal advice — what belongs in a lease, notice periods, and what you're required to disclose about a security deposit all vary by location. Use the spreadsheet to keep records complete and organized, and check anything lease-specific with a qualified professional. If you want the property's underlying economics checked too — whether it's actually worth what you paid — the rental property ROI calculator runs cap rate and cash-on-cash return from your numbers, and if you're weighing long-term leasing against furnishing a unit for short-term rental instead, the Airbnb vs long-term rental calculator compares both on the same property side by side.