El formato
condicional en Excel es
una funcionalidad de gran utilidad al momento de realizar el análisis de datos
ya que puedes dar un formato especial a un grupo de celdas en base al valor de
otra celda. Esto
te permitirá aplicar un tipo de fuente específico o un color de relleno
diferente para aquellas celdas que cumplan con ciertas reglas y así poder
identificarlas fácilmente en pantalla.
FUNDAMENTOS DE
FORMATO CONDICIONAL
El formato condicional en Excel es una manera de hacer que la
herramienta aplique un formato especial para aquellas celdas que cumplen con
ciertas condiciones. Por ejemplo, se puede utilizar el formato condicional para
cambiar el color de las celdas que tienen un valor negativo.
Razón para utilizar el formato
condicional
Siempre tienes la opción de aplicar
manualmente un formato a cada una de las celdas que cumplen con una condición,
pero eso se puede convertir en un proceso largo y repetitivo, especialmente si
tienes una tabla de datos muy grande y que cambia frecuentemente. Es por eso
que el formato condicional puede hacer más fácil la tarea de
cambiar automáticamente el formato de la celda que cumple con ciertos
criterios.
Ejemplos
de formato condicional
Formato especial para todos los valores
entre 20 y 30:
Resaltar los valores por debajo del promedio (Para este
ejemplo el promedio es 22.2).
¿Cómo se aplica el formato condicional?
Debes seleccionar las celdas a las que
se les aplicará el formato condicional y después hacer clic en el comando Formato
condicional que
aparece en el grupo Estilos de la ficha Inicio.
Como ya mencioné previamente, al seleccionar alguna de las
opciones del menú, el formato se aplicará para aquellas celdas que cumplan con
cierta condición o regla. Un formato condicional en Excel está siempre basado en una regla que
posteriormente se podrá editar si así lo deseas. Las reglas que se crean para
los formatos condicionales se pueden dividir en dos grandes grupos:
- Reglas basadas en valores de celda: Estas reglas se basan en el mismo valor de
la celda (Mayor que, Menor que, Igual a, Entre, etc.).
- Reglas basadas en fórmulas: Estas reglas ofrecen mayor flexibilidad
porque puedes aplicar un formato especial utilizando una fórmula donde
podrás aplicar una lógica más compleja. Por lo mismo es un poco más
complicado de aprender, pero una vez que lo hagas seré muy intuitivo de
utilizar.
RESALTAR CELDAS
CON FORMATO CONDICIONAL
En el
artículo fundamentos de formato
condicional hemos aprendido las bases sobre este
tema y en esta publicación aprenderemos sobre la primera de las opciones de
menú de formato condicional: Resaltar reglas de celdas.
Esta opción
de menú nos dará la oportunidad de destacar celdas que cumplan con la regla de formato condicional especificada.
Cada opción muestra su propio cuadro de diálogo solicitando los argumentos
necesarios para crear la regla.
Reglas de formato
condicional
La opción Es
mayor que muestra el siguiente cuadro de diálogo:
En el primer
cuadro de texto deberás colocar un número a partir del cual se aplicará el
formato especificado. Para este ejemplo coloqué el número 50 en la caja de
texto, dejé el formato predeterminado y el resultado fue el siguiente:
Las opciones Es
menor que, Es igual a, Texto que contiene, esperan
que ingreses un solo valor con el cual comparar las celdas a las que se
aplicará el formato. La opción Entre muestra un cuadro de
diálogo diferente porque solicita 2 valores que delimitarán el rango de valores
a buscar:
La opción Una
fecha muestra una lista de opciones de donde podrás seleccionar la que
mejor se adapte a tus necesidades.
Por
supuesto, es necesario que tus celdas tengan datos de tipo fecha de manera que
esta regla de formato condicional pueda
encontrar valores coincidentes.
Finalmente la opción Duplicar valores te permitirá resaltar ya
sea los valore únicos ó los valores duplicados dentro de tus datos:
Solamente
debes elegir la opción adecuada de la lista de selección y se aplicará la regla de formato condicional adecuada.
APLICAR FORMATO
CONDICIONAL A VALORES DUPLICADOS O VALORES ÚNICOS
En esta ocasión te mostraré cómo
aplicar formato condicional a aquellas celdas que contienen valores únicos
dentro de una lista de datos o por el contrario, cómo aplicar formato
condicional a valores duplicados en Excel.
Formato
condicional a valores duplicados
Comenzaremos nuestro ejercicio
seleccionando el rango de celdas que contiene los datos a los que aplicaremos
el formato condicional y que en este caso es el rango A2:A26.
Después de haber hecho la selección,
debemos ir a la ficha Inicio, y dentro del grupo Estilos, hacer
clic sobre Formato Condicional y al desplegarse el menú
emergente deberás hacer clic en Resaltar reglas de celdas y
posteriormente elegir la opción Duplicar valores tal como se
muestra en la siguiente imagen.
Al seleccionar esta opción se mostrará
el cuadro de diálogo Duplicar Valores el cual contiene dos
listas de selección y deberás asegurarte de que la primera lista tiene la
selección Duplicados. Para la segunda lista podrás elegir
cualquiera de las opciones disponibles y para nuestro ejemplo dejaré
seleccionada la opción de relleno rojo con texto rojo oscuro.
Al hacer clic en el botón Aceptar, la
mayoría de las celdas de nuestros datos serán resaltadas en color rojo porque
contienen un valor que se repite al menos dos veces. La única celda que no será
resaltada en rojo será las celdas A5 porque es la única que contiene un valor
que no se repite.
De esta manera podrás resaltar valores
duplicados en Excel utilizando el formato condicional de manera que sea fácil
detectar visualmente aquellas celdas que contienen datos duplicados.
En la segunda parte de nuestro
ejercicio modificaremos la regla de formato condicional recién creada de manera
que se aplique el formato condicional a los valores únicos de nuestro listado.
Formato
condicional a valores únicos
Para invertir el funcionamiento de nuestra regla de formato condicional
de manera que el formato se aplique a los valores únicos de nuestros
datos haremos una pequeña modificación. Pero antes, debemos seleccionar de
nueva cuenta el rango de celdas con los datos y hacer clic en la ficha Inicio >
Formato condicional y seleccionaremos la opción Administrar reglas.
Esto mostrará el cuadro de diálogo Administrador
de reglas de formato condicionales donde podrás observar la regla que
acabamos de crear en la sección anterior.
Para editar la regla de formato
condicional debemos seleccionarla y posteriormente hacer clic en el botón Editar
regla lo cual mostrará el cuadro de diálogo Editar regla de
formato. En este nuevo cuadro de diálogo debemos seleccionar la
opción “único” dentro de la lista desplegable tal como se muestra
en la siguiente imagen.
Para finalizar deberás hacer clic en el
botón Aceptar y de nuevo haz clic en Aceptar para cerrar el Administrador de
reglas de formato condicional. Como resultado tendremos una regla que resaltará
solamente la celda A5 con un formato especial por ser el único valor que no se
repite.
Es así como el Formato condicional en
Excel puede ser utilizado para resaltar valores únicos o valores duplicados.
Aunque en este ejercicio he utilizado una misma regla de formato condicional
para demostrar ambas tareas, es totalmente factible crear reglas independientes
que resalten valores duplicados o valores únicos.
Ambos tipos de reglas se crean de la
misma manera, es decir, desde Inicio > Formato condicional > Resaltar
reglas de celdas > Duplicar valores y al abrirse el cuadro de diálogo
deberás elegir la opción “duplicado” o “único” en base al tipo de datos que
deseas resaltar.
REGLAS
SUPERIORES E INFERIORES PARA ANALIZAR DATOS
Después de haber introducido los conceptos
fundamentales de formato condicional, ahora
analizaremos con un poco más de detenimiento algunos tipos de reglas de formato
condicional especiales. Estas reglas se muestran dentro del menú Formato
condicional y dentro de la opción Reglas superiores e
inferiores.
Reglas
superiores e inferiores de formato condicional
La regla 10 superiores te
permitirá resaltar de manera inmediata las celdas que contengan los 10 valores
con mayor valor dentro del rango de celdas. El cuadro de diálogo de esta regla
es el siguiente:
Observa que puedes cambiar el número de
elementos a seleccionar, por lo que en lugar de los 10 superiores podrías
seleccionar los 20 superiores si así lo deseas. La regla 10 inferiores funciona
de manera similar, solo que Excel obtendrá las celdas con menor valor dentro
del rango.
La regla 10% de valores
superiores es similar a las reglas anteriores, pero en lugar de
indicar un número específico de celdas se introduce un porcentaje de celdas a
seleccionar. Por ejemplo, dentro de un rango que contiene 20 celdas, si se
especifica un 10% entonces el formato condicional se aplicará a las 2 celdas
con mayor valor porque el 10% de 20 celdas son 2 celdas. El cuadro de
diálogo es el siguiente:
De la misma manera puedes aumentar o disminuir
el porcentaje. Si especificas un 20% para un rango de 20 celdas, entonces el
formato condicional se aplicará a 4 celdas. La regla 10% de valores
inferiores selecciona el 10% de celdas del rango que tenga el menor
valor.
Finalmente la regla Por encima
del promedio hace dos cosas. En primer lugar calcula el valor promedio
de las celdas del rango seleccionado y posteriormente aplica el formato
condicional a todas aquellas celdas que tienen un valor por encima del promedio
recién calculado. Al no tener ningún argumento, el cuadro de diálogo de esta
regla solamente solicita el formato a aplicar:
La regla Por debajo del
promedio seleccionará todas las celdas que tengan un valor inferior al
promedio. También puedes consultar el artículo Resaltar
celdas con formato condicional para conocer más
sobre otras reglas de formato condicional.
RESALTAR FINES
DE SEMANA CON FORMATO CONDICIONAL
Cuando tienes un listado de fechas en
Excel a veces es de utilidad resaltar los días que pertenecen
al fin de semana, ya sea porque estás creando un calendario laboral o
simplemente porque necesitas conocer las fechas de los sábados y domingos.
Esta tarea la puedes
lograr fácilmente aplicando una regla de formato condicional sobre el
listado de fechas. Para nuestro ejercicio supondremos una lista de fechas como
la siguiente:
El primer paso será seleccionar todo el
rango de fechas y posteriormente ir a la ficha Inicio y hacer clic sobre
Formato condicional para seleccionar la opción Nueva regla:
Formato
condicional para resaltar fines de semana
Se mostrará el cuadro de diálogo Nueva
regla de formato y deberás elegir la opción Utilice una
fórmula que determine las celdas para aplicar formato tal como lo
muestra la siguiente imagen:
En el cuadro de fórmula deberás colocar
lo siguiente:
=DIASEM(A1, 2)
> 5
Ahora solo falta indicar el color del
fondo de las celdas de fin de semana. Haz clic en el botón Formato y elige la
pestaña Relleno donde podrás seleccionar el color de tu preferencia para
resaltar las fechas que cumplan con la fórmula que acabamos de ingresar.
La configuración de la nueva regla de
formato condicional quedará de la siguiente manera:
Al hacer clic sobre Aceptar, las celdas
que contengas una fecha de fin se semana se resaltarán con el color previamente
definido:
Explicación
de la fórmula
En primer lugar debemos recordar que la
regla condicional que acabamos de crear aplicará el formato cuando el resultado
de la fórmula sea verdadero así que conviene analizar detenidamente dicha
fórmula.
De manera predeterminada la función
DIASEM devuelve un número del 1 al 7 para cada
día de la semana comenzando por el día domingo, pero si queremos que el día
lunes sea considerado como el primer día de la semana, entonces debemos colocar
el número 2 como el segundo argumento de la función:
=DIASEM(A1, 2)
De esta manera, el valor numérico
devuelto por la función corresponderá a los días de la semana de la siguiente
manera:
- Lunes
- Martes
- Miércoles
- Jueves
- Viernes
- Sábado
- Domingo
Es por eso que la fórmula de la regla
de formato condicional que acabamos de crear, compara el valor devuelto de la
función DIASEM con el valor 5. Si el valor devuelto es mayor a 5, querrá decir
que tenemos el día sábado o domingo y deberá aplicar el formato condicional.
Si quisieras resaltar solamente el día
domingo, podrías modificar la fórmula de la regla de formato condicional de la
siguiente manera:
=DIASEM(A1, 2)
= 7
Realiza este ejercicio con tu propia
lista de fechas y comprueba lo fácil que es resaltar cualquier día de la semana
en Excel con una regla de formato condicional.
RESALTAR FILAS
ALTERNAS
Muy a menudo, cuando presentamos los
datos en una tabla muy grande, se dificulta distinguir la información que
pertenece a una misma fila. Para ayudar en la lectura de los datos es posible
resaltar las filas alternas en Excel de manera que cada fila tenga un
color diferente que nos permita distinguir fácilmente la información entre cada
una de ellas.
Es posible aplicar este tipo de formato
convirtiendo el rango de datos en una tabla de Excel y que será tan simple como seleccionar el rango de celdas e
ir a la ficha Inicio > Estilos > Dar formato como tabla y
finalmente seleccionar cualquiera de los formatos que tienen un color diferente
para las filas.
Sin embargo, en esta ocasión te
mostraré un método adicional para resaltar filas alternas en Excel utilizando
el formato condicional.
Resaltar
filas alternas en Excel
El primer paso es seleccionar los datos
a los que aplicaremos la regla de formato condicional.
En seguida debemos ir a la ficha Inicio y
dentro de Formato condicional deberás seleccionar la
opción Nueva regla lo cual mostrará el cuadro de diálogo Nueva
regla de formato.
Fórmula para
resaltar filas alternas
Dentro del cuadro de diálogo deberás
seleccionar la opción Utilizar una fórmula que determine las celdas
para aplicar formato y en el cuadro Dar formato a los valores
donde esta fórmula sea verdadera coloca la siguiente fórmula:
=RESIDUO(FILA(),2)=0
Solo en caso de que tengas Excel 2010 y
aún no hayas aplicado el Service Pack para Office 2010, deberás utilizar la
función RESTO en lugar de la función RESIDUO. Las funciones son exactamente las
mismas y solo se debe a un cambio “temporal” en el nombre de la función en
Excel 2010 el cual se corregirá al instalar el Service Pack.
La fórmula que acabamos de introducir
hace que cualquier fila que sea par, dentro del rango de celdas seleccionado,
se le aplicará el formato que elijamos. En caso de que quieras que sean las
filas impares las que tengan el formato especial entonces deberás modificar la
fórmula de la siguiente manera:
=RESIDUO(FILA(),2)=1
Por otro lado, si lo que quieres es
resaltar las columnas alternas dentro del rango, entonces puedes reemplazar la
función FILA por la función COLUMNA:
=RESIDUO(COLUMNA(),2)=0
Una ventaja de este método es que
puedes elegir la cantidad de filas que habrá entre cada cambio de formato. Por
ejemplo, si deseamos resaltar cada 3 filas, podemos utilizar la siguiente
fórmula:
=RESIDUO(FILA(),3)=0
Ya he explicado variantes de la fórmula
que podemos indicar en nuestra regla de formato condicional pero nos hace falta
algo muy importante y es elegir el formato que será aplicado.
Para definir el formato que tendrán las
filas alternas deberás dar clic en el botón Formato que está
por debajo del cuadro de texto donde acabamos de introducir la fórmula. De
inmediato se mostrará un nuevo cuadro de diálogo llamado Formato de
celdas y es aquí donde elegiremos un color de relleno diferente.
De hecho puedes ir a cualquiera de las
pestañas de este cuadro de diálogo (Número, Fuente, Bordes, Relleno) y elegir
el formato que será aplicado a las filas alternas. Para nuestro ejemplo solo
elegiré un color de fondo diferente y al terminar haré clic en el botón Aceptar
y posteriormente de nuevo en Aceptar para cerrar el cuadro de diálogo Editar
regla de formato.
La nueva regla de formato condicional
será aplicada sobre los datos previamente seleccionados y se verán de la
siguiente manera.
Ahora ya conoces dos métodos para
resaltar filas alternas en Excel. Ya sea aplicando un formato de tabla a los
datos o creando una regla de formato condicional que hará uso de la función
RESIDUO para aplicar el formato a las filas alternas.
BUSCAR VALORES
USANDO FORMATO CONDICIONA
Seguramente utilizas las hojas de Excel
para almacenar grandes cantidades de datos, como el catálogo de clientes de la
empresa, el registro de las ventas de productos, etc. Con esa gran cantidad de
información es indispensable encontrar los datos precisos en todo momento.
Buscar en
Excel con formato condicional
En esta ocasión te mostraré cómo
utilizar el formato condicional para buscar dentro de una hoja de Excel y
resaltar las celdas coincidentes. Observa la siguiente imagen:
Ahora ya conoces dos métodos para
resaltar filas alternas en Excel. Ya sea aplicando un formato de tabla a los
datos o creando una regla de formato condicional que hará uso de la función
RESIDUO para aplicar el formato a las filas alternas.
BUSCAR VALORES
USANDO FORMATO CONDICIONA
Seguramente utilizas las hojas de Excel
para almacenar grandes cantidades de datos, como el catálogo de clientes de la
empresa, el registro de las ventas de productos, etc. Con esa gran cantidad de
información es indispensable encontrar los datos precisos en todo momento.
Buscar en
Excel con formato condicional
En esta ocasión te mostraré cómo
utilizar el formato condicional para buscar dentro de una hoja de Excel y
resaltar las celdas coincidentes. Observa la siguiente imagen:
Para este ejemplo utilizaré los datos de la
columna B y utilizaré la celda B2 como el cuadro de búsqueda, es decir, la
celda donde colocaré el término/texto que deseo encontrar en los datos. Ahora
debemos crear la regla que aplique el formato condicional y para eso se deben
seleccionar las celdas con los datos e ir al comando Formato
condicional y seleccionar la opción Nueva regla:
Se mostrará el cuadro de diálogo Nueva
regla de formato:
Fórmula para
encontrar valores
Selecciona la opción Utilice
una fórmula que determine las celdas para aplicar formato. En la caja de
texto coloca la siguiente fórmula, que explicaré en un instante:
=Y($B$2<>"",ENCONTRAR(MINUSC($B$2),
MINUSC(B4)))
También haz clic en el botón Formato…
para seleccionar el formato que se aplicará a las celdas que cumplan la regla.
La fórmula que acabas de escribir
compara el texto de la celda de búsqueda ($B$2) con las celdas que contienen
los datos. La comparación se hace a través de la función ENCONTRAR que compara
ambos valores. Puedes observar también que para ambos valores utilicé la
función MINUSC que los convierte en minúsculas antes de hacer la comparación de
manera que la búsqueda no sea sensible a mayúsculas y minúsculas. Finalmente,
la intención de la función Y es evitar que el formato se aplique a todas las
celdas cuando la celda $B$2 esté vacía.
Ahora haz clic en el botón Aceptar y
podrás comenzar a encontrar los valores que introduzcas en la celda de
búsqueda:
BORRAR REGLAS DE
FORMATO CONDICIONAL
Una vez que has analizado la
información y aplicado algún formato condicional es probable que desees
retornar al estado inicial de la hoja de Excel, es decir, remover cualquier
formato condicional aplicado a los datos.
Eliminar
formato condicional de celdas seleccionadas
Una manera de remover los formatos
condicionales es seleccionar las celdas que tienen el formato que deseas
eliminar y hacer clic sobre Formato condicional y seleccionar
la opción Borrar reglas y posteriormente Borrar reglas de
las celdas seleccionadas.
Excel quitará de inmediato cualquier
formato condicional que esté aplicado sobre las celdas seleccionadas.
Eliminar
formato condicional de todas las celdas
El segundo método para remover los
formatos es seleccionar la segunda opción del menú previamente mencionado: Borrar
reglas de toda la hoja. Al seleccionar esta opción debes estar seguro de
que no necesitas ningún formato condicional en toda la hoja de Excel ya que se
borrarán todos y cada uno de ellos.
Eliminar
algunas reglas de formato condicional
El último método que te mostraré se
debe utilizar en caso de tener varias reglas de formato y solamente querer
remover una sola de ellas. De igual manera debes seleccionar las celdas que
tienen el formato y hacer clic en Formato condicional y
posteriormente en la opción Administrar reglas, lo cual mostrará el
siguiente cuadro de diálogo:
Este cuadro de diálogo contiene todas
las reglas creadas para las celdas seleccionadas. Elige la regla que deseas
borrar y haz clic en el botón Eliminar regla y posteriormente
en el botón Aceptar. Excel removerá la regla seleccionada y dejará el resto de
las reglas tal como estaban siendo aplicadas sobre los datos.
