Bill Payment Reminder in Excel: Highlight Due and Overdue Bills Automatically with TODAY

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: bill-payment-reminder-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

Zoya's bills (made-up), checked on 17 October 2026:

Mobile and society maintenance are already Paid. Summary: 2 overdue bills, ₹2,430 (1,150 + 1,280). 3 due within 7 days, ₹10,179 (8,640 + 740 + 799). Unpaid this month: ₹18,809.

The template uses =TODAY(), so when you open it on another day the days and colours change on their own.

2. Build it step by step

Step 1. Today's date

1. In A3 type Today and in B3 type =TODAY() (TODAY).

Step 2. The bill list

1. In A5:G5 type Bill, Amount (₹), Due date, Status, Days left, Alert, Next due date.
2. Type bills, amounts and due dates from row 6 (yellow cells).
3. Select D6:D200 > Data > Data Validation > List: Paid,Unpaid.

Step 3. Days left

1. In E6 type =IF(D6="Paid","",C6-$B$3) and copy down.
2. A minus number means the due date has passed. A paid bill shows nothing.

Step 4. Alert word

1. In F6 type =IF(D6="Paid","Paid",IF(C6<$B$3,"Overdue",IF(C6-$B$3<=7,"Due soon","Later"))) (nested IF).
2. The order matters: check Paid first, then Overdue, then Due soon.

Step 5. Next due date

1. In G6 type =EDATE(C6,1): the same day next month. After paying, copy it into the Due date.

Step 6. Colour whole rows

1. Select A6:G200 > Home > Conditional Formatting > New Rule > Use a formula (formula rules).
2. Rule 1: =$D6="Paid", grey font and fill.
3. Rule 2: =AND($C6<>"",$C6<$B$3), red fill.
4. Rule 3: =AND($C6<>"",$C6-$B$3<=7), amber fill.
5. Open Manage Rules and keep the order Paid, Red, Amber with Stop If True ticked, so a paid overdue bill stays grey.

Step 7. Summary

1. In J6 type =COUNTIF(F6:F13,"Overdue"): 2.
2. In J7 type =SUMIF(F6:F13,"Overdue",B6:B13): ₹2,430.
3. In J8 and J9 use "Due soon": 3 bills, ₹10,179.
4. In J10 type =SUMIF(D6:D13,"Unpaid",B6:B13): ₹18,809.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. $C6 vs $C$6 in the rule. The rule is written for row 6 but used on every row. Lock only the column ($C6), not the row, or every row copies row 6's colour.
2. Wrong rule order. If the red rule is above the Paid rule, a paid bill that was overdue stays red. Paid must be first with Stop If True.
3. Due dates stored as text. C6-$B$3 then gives #VALUE!. Type real dates.
4. Typing today's date by hand. A typed date never moves. Use =TODAY() so the sheet is right every morning.
5. Blank rows turning red. An empty due date counts as 0, which is before today. $C6<>"" inside AND stops that.

5. More real examples

Example 1: Credit card statement. Add a Minimum due column and alert on the full amount; the late fee hits if even the minimum is missed.

Example 2: Yearly bills. For insurance or property tax use =EDATE(C6,12) for the next due date.

Example 3: Rent collection for a landlord. Same sheet with tenant names instead of bills; Overdue means the rent has not come yet.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I highlight overdue dates in Excel?

Conditional formatting rule with a formula: =AND($C6<>"",$C6<TODAY()) and a red fill. Using a cell with TODAY() (B3) keeps every rule pointing to one date.

How do I show days remaining until a due date?

=C6-TODAY(). A negative number means it is overdue.

Will the sheet update tomorrow by itself?

Yes. TODAY() recalculates whenever the file opens or changes.

Can Excel send me a reminder?

A normal sheet cannot send messages. Open it once a week, or keep it on your phone in the Excel app.

How do I get next month's due date?

=EDATE(C6,1) adds one month and keeps the day, for example 14-10-2026 to 14-11-2026.

7. Quick quiz

Ten questions on this page. Sign in to save your score.