Select your invoice rows, click Format > Conditional formatting, set Format cells if to Custom formula is, and enter a rule such as =AND($D2<>"",$D2<TODAY(),$F2<>"Yes"), where column D is the due date and column F says whether it is paid. Pick a fill color and click Done. ProfitDrawer's bookkeeping spreadsheets have an Unpaid Invoices tab that lists up to 40 unpaid invoices and flags them at 30 and 60 days.
Steps in Google Sheets (computer):
- Select the invoice rows, for example A2:F500, with headers in row 1.
- Click Format > Conditional formatting. A panel opens on the right.
- Under Format cells if, choose Custom formula is.
- Enter the formula, choose a formatting style (for example a red fill) and click Done.
Example layout: A = invoice number, B = client, C = invoice date, D = due date, E = amount, F = paid (Yes/No).
| Goal | Custom formula |
|---|---|
| Past due and unpaid | =AND($D2<>"",$D2<TODAY(),$F2<>"Yes") |
| More than 30 days since invoice date, unpaid | =AND($C2<>"",TODAY()-$C2>30,$F2<>"Yes") |
Why it works: the dollar sign before the column letter keeps every cell in a row looking at the same columns, so the whole row lights up. TODAY() returns the current date each time the sheet recalculates, so rows turn red on their own as due dates pass. The <>"" part skips blank rows.
Custom formulas can only refer to the same sheet; to use another sheet, Google points to the INDIRECT function.
Source: Google Docs Editors Help: Use conditional formatting rules in Google Sheets · Updated
Tools and spreadsheets for this
Related questions
All questions on ProfitDrawer Answers →
General information, not tax, legal or financial advice. Check the rules for your state and your situation.