Saltar al contenido
GridBelt
  • Prueba de Excel
  • Práctica
  • Programas
  • Precios
Iniciar sesión
Empezar la prueba gratuita
  • Prueba de Excel
  • Práctica
  • Programas
  • Precios
Iniciar sesiónCrear cuenta
Empezar la prueba gratuita
GridBelt

Practica Excel en tu navegador.

Prueba de Excel

  • Nivel 1: Principiante
  • Nivel 2: Intermedio
  • Nivel 3: Experto
  • Prueba de Excel en 7 días
  • Clasificación
  • Prueba de Excel para entrevistas
  • Evaluación de Excel de Indeed

Práctica

  • Todos los ejercicios
  • Programas
  • Para principiantes

Guías

  • Fórmulas de Excel
  • Atajos de Excel

Empresa

  • Acerca de
  • Precios
  • Cómo corregimos

© GridBelt. Todos los derechos reservados.

  • Política de privacidad
  • Términos de uso
  • Política de reembolso

Excel es una marca comercial de Microsoft Corporation. Indeed, TestGorilla, SHL y otras marcas son propiedad de sus respectivos titulares. GridBelt es un sitio de práctica independiente y no está afiliado a Microsoft, TestGorilla, SHL ni Indeed, ni respaldado ni patrocinado por ellos.

  1. Práctica
  2. /
  3. Conditional formatting

Conditional formatting practice: highlight overdue invoices with a formula rule

  • Intermedio
  • ~8 min
  • Conditional formatting

Enunciado en inglés

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.

Qué tienes que hacer

  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.

Celdas de respuesta

Corrección

Escribe tu fórmula en la cuadrícula, presiona Enter y luego haz clic en Comprobar. Evaluamos el valor de cada celda de respuesta y la fórmula que lo produce.

Tareas

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

Para las tareas de hoja de cálculo se recomienda una pantalla grande. La hoja se desplaza dentro de este recuadro.

StartInsertFormulasDataView
A1fxPreparando tu libro…
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

Escribe tu fórmula en la hoja y luego comprueba.

Sobre este ejercicio

Un conjunto de datos de 80 filas en una hoja, 3 celdas de respuesta, unos 8 minutos. Se corrige en tu navegador: el valor y la fórmula que lo produce, con comentarios que te dicen qué arreglar.

Cómo resolverlo

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

Otras formas válidas

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

Errores frecuentes

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

Cómo corregimos

Se acepta cualquier fórmula que produzca el resultado correcto, salvo que la tarea nombre una función: entonces otra función obtiene media puntuación. Comparamos los valores con una pequeña tolerancia y, en el texto, ignoramos mayúsculas y espacios. Un número correcto escrito a mano en lugar de una fórmula obtiene media puntuación. Las tareas de varias celdas obtienen la puntuación completa con el 100% de las celdas correctas y media puntuación desde el 50%. Cuando importa, también cambiamos una celda de entrada en una copia de tu hoja y comprobamos que tu fórmula se adapta.

Respuestas calculadas con las reglas de Excel 365 y comprobadas en nuestro corrector el 16 de noviembre de 2026. Autor: GridBelt team.

En estos programas

  • Excel test in 7 days, Plan del nivel 2Día 5
  • Excel test in 7 days, Plan del nivel 3Día 2
  • Excel for Data AnalystsDía 6

Ejercicios relacionados

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

Referencia de la función

Documentación oficial

Siguiente ejercicio

Highlight the top 10 % of sales