Skip to content
GridBelt
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign in
Start the free test
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign inCreate account
Start the free test
GridBelt

Practice Excel in your browser.

Excel Test

  • Level 1: Beginner
  • Level 2: Intermediate
  • Level 3: Expert
  • Excel test in 7 days
  • Leaderboard
  • Excel test for interview
  • Indeed Excel assessment

Practice

  • All exercises
  • Programs
  • For beginners

Guides

  • Excel formulas
  • Excel shortcuts

Company

  • About
  • Pricing
  • How we grade

© GridBelt. All rights reserved.

  • Privacy Policy
  • Terms of Use
  • Refund Policy

Excel is a trademark of Microsoft Corporation. Indeed, TestGorilla, SHL and other marks are the property of their owners. GridBelt is an independent practice site and is not affiliated with, endorsed by or sponsored by Microsoft, TestGorilla, SHL or Indeed.

  1. Practice
  2. /
  3. Conditional formatting

Conditional formatting practice: highlight overdue invoices with a formula rule

  • Intermediate
  • ~8 min
  • Conditional formatting

The receivables list holds 80 invoices issued between July and October 2026, sorted by customer, with the due date in column D and the payment date in column F — blank while the invoice is unpaid. On the report date in J2 (2026-10-31), 46 invoices are still open: some are past due, others are not due yet (one falls due on the report date itself) and 3 have no due date because the terms are still being agreed; 16 other invoices were paid late but are paid. The controller wants the overdue rows highlighted by a conditional-formatting rule. The rule is a TRUE/FALSE formula, so you write it in column G first and watch it work row by row.

What you need to do

  1. 1Click G2 and type =AND(D2<>"",D2<$J$2,F2=""). An invoice is overdue when it has a due date, the due date is before the report date and nothing has been paid. Select J2 in the formula and press F4 (Windows) or ⌘T (Mac) to lock it as $J$2.
  2. 2D2<>"" matters: an empty due date counts as 0, the earliest date there is, so without it the 3 invoices with no terms (rows 21, 45, 64) would show TRUE.
  3. 3Press Enter, then fill G2 down to G81: double-click the fill handle, or select G2:G81 and press Ctrl+D (Windows) / ⌘D (Mac). Each row shows TRUE or FALSE — exactly what a formatting rule evaluates.
  4. 4Click J3 and count the TRUE values: =COUNTIF(G2:G81,TRUE). Click J4 and total their amounts: =SUMIF(G2:G81,TRUE,E2:E81).
  5. 5Now apply the rule itself: select A2:F81, then Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format" (Windows keyboard: Alt, H, L, N; on Mac the same menu path). Enter =AND($D2<>"",$D2<$J$2,$F2=""), pick a light red fill and click OK. The checker grades your column G and the totals; the coloured rows are the result you would hand to the controller.
  6. 6Change J2 to 2026-10-15: J3 drops to 19. Put 2026-10-31 back before you check.

Answer cells

Feedback

Type your formula in the grid, press Enter, then Check. We grade the value in each answer cell and the formula behind it.

Tasks

  1. Not checkedIn G2, write a formula that returns TRUE when the invoice is overdue — it has a due date (D2), that date is before the report date in J2, and Paid on (F2) is empty — and FALSE otherwise; fill it down to G81.
  2. Not checkedIn J3, count the overdue invoices (for example COUNTIF over your TRUE/FALSE column, or COUNTIFS on the dates and the empty payment cells).
  3. Not checkedIn J4, total the amounts (E2:E81) of the overdue invoices.

Desktop recommended for spreadsheet tasks. The sheet scrolls inside this box.

StartInsertFormulasDataView
A1fxPreparing your workbook…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Enter your formula in the sheet, then check.

About this exercise

A 80-row dataset on one sheet, 3 answer cells, about 8 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.

How to solve it

  • G2=AND(D2<>"",D2<$J$2,F2="")
  • J3=COUNTIF(G2:G81,TRUE)
  • J4=SUMIF(G2:G81,TRUE,E2:E81)

A conditional-formatting rule of the "use a formula" kind is just a formula that returns TRUE or FALSE for each row, so writing it in a helper column first shows you exactly which rows the rule will colour. AND needs all three tests to hold: D2<>"" (there is a due date), D2<$J$2 (it is in the past relative to the report date) and F2="" (no payment yet). The $ signs lock J2 so every row compares against the same report date when you fill down, while D2 and F2 stay relative and move row by row. COUNTIF and SUMIF then count and total the TRUE rows.

Pitfall 1: in the rule dialog the formula is written for the top-left cell of the selection (A2) and Excel shifts it for every other cell, so the column references need a $ ($D2, $F2) — otherwise the cells in column B would test E and G instead. Pitfall 2: an empty cell compares as 0, so D2<$J$2 is TRUE for an invoice without a due date — 3 invoices here; D2<>"" (or ISNUMBER(D2)) keeps them out. COUNTIFS(D2:D81,"<"&J2,F2:F81,"") gets this right on its own, because COUNTIFS skips blank cells for a "<" criterion. Pitfall 3: < versus <= — an invoice due on the report date (row 77) is not overdue yet; and a paid invoice is never overdue, even a late one, so a rule on the due date alone would also colour the 31 paid invoices whose due date has passed (16 of them were paid late). Alternatives: =IF(AND(...),TRUE,FALSE) is the same with an explicit IF; =(D2<>"")*(D2<$J$2)*(F2="") returns 1 or 0 — enough inside a formatting rule, where any non-zero number counts as TRUE, but this column is checked for TRUE/FALSE; =COUNTIFS(D2:D81,"<"&J2,F2:F81,"") and =SUMIFS(E2:E81,D2:D81,"<"&J2,F2:F81,"") count and total without the helper column. Reporting: TODAY() instead of a typed report date keeps the list live, but the file then changes every day and cannot be reproduced for an audit — many controllers keep the report date in a cell exactly as here. Windows shortcuts: Alt, H, L, N opens New Rule and Alt, H, L, R manages the rules; on Mac use Home → Conditional Formatting → Manage Rules.

Other ways that work

  • =AND(ISNUMBER(D2),D2<$J$2,ISBLANK(F2)) — the same tests with ISNUMBER and ISBLANK
  • =IF(AND(D2<>"",D2<$J$2,F2=""),TRUE,FALSE) — explicit IF around the same test
  • =COUNTIFS(D2:D81,"<"&J2,F2:F81,"") and =SUMIFS(E2:E81,D2:D81,"<"&J2,F2:F81,"") — count and amount without the helper column
  • =SUMPRODUCT(--G2:G81) — counts the TRUE values by turning them into 1s

Common mistakes

=AND(D2<$J$2,F2="") without the due-date test: an empty due date counts as 0, so the 3 invoices with no terms (rows 21, 45, 64) show TRUE and the count in J3 comes out 27 instead of 24.

How we grade

Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.

Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.

In these programs

  • Excel test in 7 days, Level 2 planDay 5
  • Excel test in 7 days, Level 3 planDay 2
  • Excel for Data AnalystsDay 6

Related exercises

  • Highlight the top 10 % of salesIntermediate, ~8 min
  • Drop-down list of regions (data validation)Intermediate, ~7 min
  • Orders over $1,000 in West with FILTERExpert, ~10 min

Function reference

Official documentation

Next exercise

Highlight the top 10 % of sales