Formatting Issues

How to Use Conditional Formatting for Overdue and Upcoming Invoices in Excel

Steven Parker Oct 20, 2026 868 views

Question details

The user needs to set up conditional formatting rules to highlight overdue unpaid invoices in red and upcoming unpaid invoices in green, based on outstanding amount and due date columns, while ignoring blank date cells.

How to Use Conditional Formatting for Overdue and Upcoming Invoices in Excel

Product: Spreadsheet
Device & OS: not provided
Scenario: Tracking invoice payments to easily visually identify which accounts are overdue and which are approaching their due dates.
Observed behavior: Requires a custom formula-based rule to evaluate multiple conditions (amount greater than 0, date compared to today, and non-blank cells) simultaneously.

Before you start

Ensure your invoice data is organized into clear columns, and identify the exact cell references for your Outstanding Amount (e.g., column CQ) and Due Date (e.g., column CS) starting from the first row of your data.

Solution 1Recommended

Use Custom Formulas for Conditional Formatting

Apply the AND function within the conditional formatting tool to test multiple criteria simultaneously, ensuring only the correct rows are highlighted.

To properly format the invoices, we need two separate rules: one for overdue invoices (past due dates with an outstanding balance) and one for upcoming invoices (due today or later with an outstanding balance).

Select the target range

Highlight the entire range of cells or rows you want the conditional formatting to apply to. Ensure your active cell is the first cell of the data range (e.g., row 2).

Create the overdue rule

Navigate to the Home tab, click on Conditional Formatting, and select New Rule. Choose 'Use a formula to determine which cells to format'.

Enter the overdue formula

In the formula bar, type: =AND($CQ2>0,$CS2<TODAY(),$CS2<>0). Click Format, select a red fill color under the Fill tab, and click OK.

Create the upcoming invoice rule

Go back to Conditional Formatting > New Rule > 'Use a formula to determine which cells to format'. Enter the formula: =AND($CQ2>0,$CS2>=TODAY(),$CS2>0). Click Format, choose a green fill color, and click OK.

Apply and verify

Click Apply in the Rules Manager. Your spreadsheet will now dynamically highlight overdue invoices in red and upcoming ones in green, leaving paid or blank rows unformatted.

Use Custom Formulas for Conditional Formatting

Handling Blank Cells: The condition $CS2<>0 (or $CS2>0) is crucial. Without it, Excel evaluates blank date cells as zero, which is mathematically less than TODAY(), causing empty rows to falsely turn red.

Manage Invoice Tracking Effortlessly with WPS Spreadsheet

WPS Spreadsheet offers an intuitive and powerful conditional formatting rule manager that is fully compatible with Excel formulas, making invoice tracking highly efficient.

1. Open your invoice tracker: Launch WPS Spreadsheet and open your invoice document.
2. Highlight your data: Select the rows or columns where you want the color coding to appear.
3. Access conditional formatting: Go to the Home tab on the top ribbon, click on Conditional Formatting, and select New Rule.
4. Input your formulas: Choose the option to use a formula. Enter your custom AND formulas for overdue and upcoming conditions.
5. Set colors and apply: Format the cells with red and green fills accordingly, click OK, and your invoice tracker will update automatically.

Fully compatible with Microsoft Excel (.xlsx) formats and formulasBuilt-in robust Conditional Formatting rule managerFree and lightweight office suite for seamless data managementEasy-to-use interface matching industry standards

microsoft office alternative - wps office

Frequently Asked Questions

Why are my blank due date cells turning red?

Spreadsheet programs often interpret blank date cells as a zero value. Because zero represents a date far in the past, it evaluates as less than TODAY(). Adding $CS2<>0 to your AND formula tells the program to ignore blank cells.

How do I make the conditional formatting apply to the entire row instead of just one column?

To highlight the entire row, you must lock the column reference in your formula by placing a dollar sign ($) before the column letter (e.g., $CQ2 and $CS2). Then, make sure your initial selection range encompasses all the columns in your table before applying the rule.

How do I edit a conditional formatting rule I previously created?

Go to the Home tab, click Conditional Formatting, and choose Manage Rules. In the dropdown at the top of the manager window, select 'This Worksheet' to see all rules. Select the rule you want to change and click 'Edit Rule'.

Excel & Data Problems