A rent tracker in Google Sheets needs only one tab and six columns: month, due date, paid date, amount, UPI reference and a status formula. When the status column is driven by the dates, conditional formatting can colour each row, so a tenant sees which months are settled without reading a single cell.
What columns does the sheet need?
Keep one row per month of the tenancy, twelve rows for a year, and type the headings in row 1. Nothing else is needed on the tab.
| Column | Heading | What goes in it |
|---|---|---|
| A | Month | A real date such as 01-04-2026, formatted to show only month and year |
| B | Due date | The date rent is due under your agreement, for example the 5th |
| C | Paid date | Left empty until you pay; then the date on the UPI confirmation |
| D | Amount | What actually left your account that month |
| E | UPI reference | The transaction ID from the payment confirmation, copied as text |
| F | Status | A formula, never typed by hand |
Type dates so Google Sheets recognises them. If a date sits on the left edge of its cell, it was read as text and the formulas below will misbehave. Use Format, then Number, then Date to fix a column.
How do I make the status column work by itself?
Put this formula in F2 and drag it down to F13. It reads the paid date first, then compares today with the due date.
- =IF(C2<>"", IF(C2<=B2, "Paid on time", "Paid late"), IF(TODAY()>B2, "Overdue", "Upcoming"))
- If C2 holds a date on or before the due date, the row says Paid on time.
- If C2 holds a later date, it says Paid late, which is useful if a landlord ever disputes a month.
- If C2 is empty and today is past the due date, it says Overdue; otherwise Upcoming.
TODAY() recalculates whenever the sheet opens, so an Upcoming row turns Overdue on its own the day after the due date.
How do I colour the rows by status?
- Select A2:F13.
- Open Format, then Conditional formatting. Under the Format cells if dropdown, choose Custom formula is.
- Enter =$F2="Overdue" and pick a light red fill. The dollar sign before F keeps the test on column F for the whole row.
- Click Add another rule and use =LEFT($F2,4)="Paid" with a light green fill.
- Add a third rule, =$F2="Upcoming", with no fill or a pale grey.
Now a glance down the sheet is enough: green rows are done, red rows need a payment today.
What should I record in the UPI reference column?
Copy the transaction ID shown on the payment success screen or in your bank's debit alert. Format column E as plain text so a long number is not turned into scientific notation. It is also worth typing the remark you put on the payment, such as Rent Apr 2026, because that is what you will search for on your bank statement later. Never paste anything secret into the sheet: a reference number is enough, and nothing that lets someone move money.
Can the sheet total things for me?
Yes, three small formulas under the table are enough. They assume your data runs from row 2 to row 13.
- Months paid so far: =COUNTIF($F$2:$F$13, "Paid*")
- Total rent paid: =SUMIF($F$2:$F$13, "Paid*", $D$2:$D$13)
- Months overdue right now: =COUNTIF($F$2:$F$13, "Overdue")
The star in "Paid*" matches both Paid on time and Paid late. If the agreement says rent for the year is, say, ₹2,16,000 and the sheet shows ₹1,08,000 paid, you are halfway through the tenancy, and the arithmetic takes a second.
When does a single sheet stop being enough?
A rent sheet is excellent for one cost, but rent is only one line of a household. When you want rent, bills and everything else in the same picture, SpendixAI lets you upload bank or card statements as PDF, CSV or Excel, review the rows before saving, and see rent as one category among the rest. Until then, the sheet above does its job.
A short setup checklist
- Twelve rows, six columns, real dates in A, B and C.
- Status formula in F2 copied to F13.
- Three conditional formatting rules for overdue, paid and upcoming.
- The UPI reference copied from each confirmation on the day you pay.
- A screenshot of each confirmation saved in a folder named for the tenancy year.
Useful ideas start with a clearer picture of your own spending.
Explore your reports
