Zum Inhalt springen
GridBelt
  • Excel-Test
  • Übungen
  • Programme
  • Preise
Anmelden
Kostenlosen Test starten
  • Excel-Test
  • Übungen
  • Programme
  • Preise
AnmeldenKonto erstellen
Kostenlosen Test starten
GridBelt

Excel üben – direkt im Browser.

Excel-Test

  • Stufe 1: Anfänger
  • Stufe 2: Fortgeschrittene
  • Stufe 3: Experten
  • Excel-Test in 7 Tagen
  • Bestenliste
  • Excel-Test im Bewerbungsverfahren
  • Excel-Test von Indeed

Übungen

  • Alle Übungen
  • Programme
  • Für Anfänger

Ratgeber

  • Excel-Formeln
  • Excel-Tastenkombinationen

Unternehmen

  • Über uns
  • Preise
  • So bewerten wir

© GridBelt. Alle Rechte vorbehalten.

  • Datenschutzerklärung
  • Nutzungsbedingungen
  • Rückerstattungsrichtlinie

Excel ist eine Marke der Microsoft Corporation. Indeed, TestGorilla, SHL und andere Marken sind Eigentum ihrer jeweiligen Inhaber. GridBelt ist eine unabhängige Übungsplattform, steht mit Microsoft, TestGorilla, SHL oder Indeed in keiner Verbindung und wird von keinem dieser Unternehmen unterstützt oder gesponsert.

  1. Übungen
  2. /
  3. Conditional formatting

Conditional formatting practice: highlight overdue invoices with a formula rule

  • Fortgeschrittene
  • ~8 Min.
  • Conditional formatting

Übungstext auf Englisch

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.

Ihre Aufgabe

  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.

Antwortzellen

Feedback

Geben Sie Ihre Formel in die Tabelle ein, drücken Sie Enter und dann „Prüfen“. Wir bewerten den Wert in jeder Antwortzelle und die Formel dahinter.

Aufgaben

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

Für Tabellenaufgaben empfehlen wir einen Computer. Die Tabelle scrollt innerhalb dieses Rahmens.

StartInsertFormulasDataView
A1fxIhre Arbeitsmappe wird vorbereitet…
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

Geben Sie Ihre Formel in die Tabelle ein und prüfen Sie dann.

Über diese Übung

Ein Datensatz mit 80 Zeilen auf einem Tabellenblatt, 3 Antwortzellen, etwa 8 Minuten. Bewertet in Ihrem Browser: der Wert und die Formel dahinter, mit Feedback, das die Korrektur benennt.

So lösen Sie die Aufgabe

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

Andere Wege, die funktionieren

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

Häufige Fehler

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

So bewerten wir

Jede Formel, die das richtige Ergebnis liefert, wird akzeptiert, außer die Aufgabe nennt eine Funktion: Dann bringt eine andere Funktion die halbe Punktzahl. Wir vergleichen Werte mit einer kleinen Toleranz und ignorieren bei Text Groß- und Kleinschreibung sowie Leerzeichen. Eine richtige Zahl, die statt einer Formel von Hand eingetippt wurde, bringt die halbe Punktzahl. Aufgaben mit mehreren Zellen bringen die volle Punktzahl, wenn 100% der Zellen stimmen, und die halbe ab 50%. Wo es darauf ankommt, ändern wir außerdem eine Eingabezelle in einer Kopie Ihrer Tabelle und prüfen, ob Ihre Formel mitzieht.

Antworten nach den Regeln von Excel 365 berechnet und am 16. November 2026 in unserem Bewertungssystem geprüft. Verfasst von GridBelt team.

In diesen Programmen

  • Excel test in 7 days, Plan für Stufe 2Tag 5
  • Excel test in 7 days, Plan für Stufe 3Tag 2
  • Excel for Data AnalystsTag 6

Ähnliche Übungen

  • Highlight the top 10 % of salesFortgeschrittene, ~8 Min.
  • Drop-down list of regions (data validation)Fortgeschrittene, ~7 Min.
  • Orders over $1,000 in West with FILTERExperten, ~10 Min.

Funktionsreferenz

Offizielle Dokumentation

Nächste Übung

Highlight the top 10 % of sales