Formatear filas automáticamente

Recientemente me preguntaron cómo configurar el formato condicional para formatear una fila según el valor de una columna. En esta publicación, demostraré cómo crear una regla de formato condicional que formatea automáticamente una fila en función de un único valor de columna para todas las filas de la hoja de trabajo. ¡Gracias Karla por tu pregunta!

Objetivo

Antes de pasar a la mecánica, confirmemos nuestro objetivo. Tenemos una hoja con un montón de transacciones, como esta:

Y queremos formatear cada factura vencida… es decir, cada fila donde el valor de la columna E es Sí . Pero en lugar de formatear las filas manualmente, queremos definir una regla de formato condicional para que Excel formatee las filas Sí de esta manera:

Vamos a por ello.

Video

Detalles

Usaremos estos pasos:

• Seleccione rango y observe la celda activa
• Aplicar formato condicional
• Pegar nuevas facturas el próximo mes

¡Hagamos esto!

Seleccione rango y observe la celda activa

Primero, queremos seleccionar el rango que deseamos formatear. Por ejemplo, si solo queremos aplicar la regla de formato condicional al rango B7:E24, seleccionamos ese rango. Si queremos aplicar la regla de formato condicional a todas las filas de las columnas B:E, seleccionamos esas columnas.

En este caso, queremos aplicar la regla a todas las filas de las columnas B:E, por lo que seleccionamos las columnas B:E de esta manera:

Ahora que hemos seleccionado el rango, es momento de anotar la celda activa . Esto es muy importante. Si ya ha intentado aplicar formato condicional al rango y descubre que no funciona como se esperaba, este es probablemente el pequeño detalle que necesita para que funcione.

Notarás que aunque se selecciona un rango, dentro de ese rango hay una (y solo una) celda activa . A menudo es la celda superior izquierda del rango (pero no siempre, solo depende de cómo seleccione el rango). En la captura de pantalla anterior, el rango seleccionado es B:E mientras que la celda activa es B1.

Ahora que hemos observado la celda activa (B1), podemos completar con éxito el siguiente paso.

Aplicar formato condicional

Con el rango B:E seleccionado, usamos el comando Inicio Formato condicional Nueva regla .

En el cuadro de diálogo Nueva regla de formato resultante , optamos por Usar una fórmula para determinar qué celdas formatear .

El siguiente paso es escribir una fórmula que devuelva VERDADERO para cada celda dentro del rango que debe formatearse. Ahora, mientras escribe la fórmula en el cuadro de diálogo, debe imaginar que la está escribiendo en la celda activa . Cuando haces clic en Aceptar, Excel esencialmente completará esa fórmula de formato de arriba a abajo en el rango seleccionado. ¡Es por eso que notamos la celda activa en el paso anterior!

Esto también significa que debemos comprender la diferencia entre referencias de celda absolutas y relativas. Una referencia de celda completamente absoluta ($A$1) NO CAMBIA a medida que la fórmula se completa hacia abajo o hacia la derecha. Sin embargo, una referencia de celda totalmente relativa (A1) sí lo hace. Además de referencias totalmente absolutas ($A$1) y totalmente relativas (A1), también tenemos referencias mixtas ($A1 y A$1).

Así es como funciona todo. Desea considerar la parte de fila y columna de la referencia de forma independiente. Si la referencia de la columna es absoluta ($A), entonces no cambia ni se actualiza a medida que completa la fórmula horizontalmente en las columnas. Si la referencia de la columna es relativa (A), Excel la actualizará (B, C, D,…) a medida que complete o copie la fórmula horizontalmente en las columnas.

Si la referencia de la fila es absoluta ($1), no cambia ni se actualiza a medida que completa o copia la fórmula verticalmente a través de las filas. Si la referencia de la fila es relativa (1), lo es.

Entonces, juntando todo esto, esto es lo que queremos. Queremos que nuestra fórmula de formato formatee toda la fila cuando el valor correspondiente en la columna E sea Sí . Como Excel básicamente completa nuestra fórmula de formato a través de las filas y columnas dentro del rango seleccionado, necesitamos que la referencia de celda se actualice para cada fila pero esté bloqueada en la columna E. Es decir, queremos que la referencia de la columna sea absoluta pero la referencia de la fila. ser relativo, así: $E1.

Entonces, nuestra fórmula de formato, que devolverá VERDADERO cuando el valor en la columna E sea igual a Sí , es esta:

=$E1="Sí"

Como Excel básicamente completa la fórmula de formato, actualizará la referencia de la fila a 2, 3, 4, etc. Pero, como Excel completa correctamente la fórmula de formato, no cambiará la referencia de la columna.

Después de escribir la fórmula de formato, básicamente hacemos clic en el botón Formato y elegimos cualquier formato que queramos aplicar. En este caso, solo usé un relleno de celda claro.

Cuando aplicamos la regla de formato condicional… bam:

¡Lo conseguimos!

El último detalle a discutir es cómo pegar nuevas facturas.

Pegar nuevas facturas el próximo mes

Si escribe nuevas transacciones en el rango de transacciones actual, Excel aplicará correctamente la regla de formato condicional y el formato se aplicará como se esperaba.

Si pega nuevas transacciones (en lugar de escribirlas), podría ver resultados inesperados dependiendo de cómo las pegue. Analicemos esto por un segundo.

Si copia un rango de celdas y realiza un pegado estándar, en realidad está pegando los valores Y muchas otras cosas… incluido el formato. Esto significa que pegará la regla de formato condicional y, por lo tanto, aparecerá como si su hoja de trabajo estuviera rota, así:

Entonces, en lugar de realizar un pegado estándar, desea pegar valores . Esto pegará los valores de la celda pero no sobrescribirá su regla de formato condicional. Verás aplicado el formato esperado, como este:

Sí… ¡lo logramos!

Conclusión

Si tiene alguna pregunta o comentario, u otra forma de lograr este objetivo, compártala publicando un comentario a continuación… ¡gracias!

Archivo de muestra

Formato automático.xlsxDescargar