Aller au contenu
GridBelt
  • Test Excel
  • Exercices
  • Programmes
  • Tarifs
Se connecter
Commencer le test gratuit
  • Test Excel
  • Exercices
  • Programmes
  • Tarifs
Se connecterCréer un compte
Commencer le test gratuit
GridBelt

Entraînez-vous sur Excel dans votre navigateur.

Test Excel

  • Niveau 1 : Débutant
  • Niveau 2 : Intermédiaire
  • Niveau 3 : Expert
  • Test Excel en 7 jours
  • Classement
  • Test Excel en entretien d’embauche
  • Évaluation Excel d’Indeed

Exercices

  • Tous les exercices
  • Programmes
  • Pour débutants

Guides

  • Formules Excel
  • Raccourcis Excel

Entreprise

  • À propos
  • Tarifs
  • Comment nous corrigeons

© GridBelt. Tous droits réservés.

  • Politique de confidentialité
  • Conditions d’utilisation
  • Politique de remboursement

Excel est une marque commerciale de Microsoft Corporation. Indeed, TestGorilla, SHL et les autres marques sont la propriété de leurs détenteurs respectifs. GridBelt est un site d’entraînement indépendant ; il n’est affilié ni à Microsoft, ni à TestGorilla, ni à SHL, ni à Indeed, et n’est ni cautionné ni sponsorisé par ces sociétés.

  1. Exercices
  2. /
  3. Conditional formatting

Conditional formatting practice: highlight overdue invoices with a formula rule

  • Intermédiaire
  • ~8 min
  • Conditional formatting

Énoncé en anglais

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.

Ce que vous devez faire

  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.

Cellules de réponse

Correction

Saisissez votre formule dans la grille, appuyez sur Enter, puis cliquez sur Vérifier. Nous corrigeons la valeur de chaque cellule de réponse et la formule qui la produit.

Tâches

  1. Non vérifiéIn 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. Non vérifiéIn J3, count the overdue invoices (for example COUNTIF over your TRUE/FALSE column, or COUNTIFS on the dates and the empty payment cells).
  3. Non vérifiéIn J4, total the amounts (E2:E81) of the overdue invoices.

Ordinateur recommandé pour les tâches dans la feuille de calcul. La feuille défile à l’intérieur de ce cadre.

StartInsertFormulasDataView
A1fxPréparation de votre classeur…
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

Saisissez votre formule dans la feuille, puis vérifiez.

À propos de cet exercice

Un jeu de données de 80 lignes sur une feuille, 3 cellules de réponse, environ 8 minutes. Corrigé dans votre navigateur : la valeur et la formule qui la produit, avec des indications sur la correction à apporter.

Comment le résoudre

  • 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.

Autres méthodes valables

  • =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

Erreurs fréquentes

=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.

Comment nous corrigeons

Toute formule qui produit le bon résultat est acceptée, sauf si la tâche nomme une fonction : une autre fonction rapporte alors la moitié des points. Nous comparons les valeurs avec une petite tolérance et ignorons la casse et les espaces dans le texte. Un nombre correct saisi à la main au lieu d’une formule rapporte la moitié des points. Les tâches sur plusieurs cellules rapportent tous les points avec 100 % de cellules justes, la moitié à partir de 50 %. Lorsque c’est pertinent, nous modifions aussi une cellule d’entrée sur une copie de votre feuille et vérifions que votre formule suit.

Réponses calculées selon les règles d’Excel 365 et vérifiées dans notre correcteur le 16 novembre 2026. Auteur : GridBelt team.

Dans ces programmes

  • Excel test in 7 days, Plan de niveau 2Jour 5
  • Excel test in 7 days, Plan de niveau 3Jour 2
  • Excel for Data AnalystsJour 6

Exercices associés

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

Référence de la fonction

Documentation officielle

Exercice suivant

Highlight the top 10 % of sales