13.3. FORMATO CONDICIONADO

FORMATO CONDICIONADO 

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 selccionar 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 posteriormentre 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 utilzando 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.

Por ahora estudia estos conceptos básicos y en los siguiente días analizaremos algunas de las opciones que tenemos para aplicar el formato condicional.

 

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 condicionalespecificada. 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 condicionalpueda 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 veremos cómo aplicar formato condicional a aquellas celdas que son únicas en nuestra lista de datos ó por el contrario, aplicar formato condicional a aquellas celdas que se repiten.

Fomato condicional a valores duplicados

En primer lugar deberás seleccionar el rango de datos al cual se le aplicará el formato condicional:

Después deberás ir a la ficha Inicio y en el grupo Estilos hacer clic sobre Formato Condicional y posteriormente desplegar el menú Resaltar reglas de celdas y elegir la opción Duplicar valores.

Se mostrará el cuadro de diálgo Duplicar Valores.

Deja las opciones predeterminadas y haz clic en Aceptar. Todos los valores son resaltados excepto el día Sábado porque es el único de toda la lista que no se repite:

Formato condicional a valores únicos

Ahora vamos a invertir el formato, es decir, queremos que el formato se aplique a los valores únicos de nuestros datos y en este caso significará que solamente la celda del día Sábado quedará con formato. De nuevo selecciona el rango de datos y haz clic en la ficha Inicio en el grupo Estilos selecciona el comando Formato condicional y a continuación en Administrar reglas.

Eso mostrará el cuadro de diálogo Administrador de reglas de formato condicionales.

En el cuadro de diálogo podrás observar la regla que acabamos de crear. Haz clic sobre la regla para seleccionarla y posteriormente clic sobre el botón Editar regla lo cual mostrará el cuadro de diálogo Editar regla de formato. Sobre este nuevo cuadro de diálogo selecciona la opción único dentro de la lista desplegable.

Haz clic en el botón Aceptar. Y de nuevo haz clic sobre Aceptar y se aplicará la nueva regla sobre los datos dejando solamente al día Sábado con un formato especial por ser el único valor que no se repite.

El formato condicional es una característica muy poderosa de Microsoft Excel que te ayudará a presentar adecuadamente los datos.

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 disminuar 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 nigún argumento, el cuadro de diálogo de esta regla solamente solicta 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 estás creando un calendario de proyecto ó un reporte a veces es de utilidad resaltar los días que son fines de semana. Eso lo puedes hacer fácilmente y rápido aplicando una regla de formato condicional. En primer lugar deberás tener listos los datos:

Ahora selecciona todo el rango de fechas y en la ficha Inicio haz clic sobre Formato condicional y selecciona la opciónNueva regla:

Regla de formato condicional para fines de semana

Esto mostrará el cuadro de diálogo Nueva regla de formato y deberás seleccionar la opción Utilice una fórmula que determine las celdas para aplicar formato:

En el cuadro de fórmula deberás colocar la siguiente fórmula:

=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 en la sección Relleno selecciona el color de tu preferencia para marcar los fines de semana:

Finalmente la regla de formato queda 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:

Resaltar filas alternas

Muy a menudo cuando presentamos los datos en una tabla muy grande se dificulta distinguir la información que pertenece a la misma fila. Para poder ayudar en la lectura se puede resaltar el fondo de las filas alternas con un color especial de manera que pueda distinguirse la información entre filas diferentes. Por supuesto que puedes alcanzar este formato utilizando el funcionamiento implícito de las tablas, pero si quieres probar una opción diferente continúa leyendo.

Crear la regla de formato condicional

En primer lugar deberás seleccionar los datos a los que darás formato.

Ve a la ficha Inicio y dentro de Formato condicional selecciona la opción Nueva regla lo cual mostrará el cuadro de diálogoNueva regla de formato.

 

Fórmula para resaltar filas alternas

IMPORTANTE: Si ya has instalado el SP1 de Office 2010, la función RESTO regresa su nombre a RESIDUO.

Selecciona 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 función:

=RESTO(FILA(),2)=0

Lo que ésta fórmula significa es que cualquier fila que sea par se le aplicará un formato diferente. En caso de que quieras que sean las filas impares las que reciban el formato diferente entonces la función deberá ser:

=RESTO(FILA(),2)=1

Por otro lado, si lo que quieres es resaltar columnas, entonces puedes utilizar la siguiente función:

=RESTO(COLUMNA(),2)=0

Una ventaja de este método es que podrías resaltar cada 3 filas, solamente utiliza la siguiente fórmula:

=RESTO(FILA(),3)=0

Finalmente debes elegir el formato de las filas para lo cual debes dar clic en el botón Formato y se mostrará el cuadro de diálogo Formato de celdas. Para este ejemplo elegiremos un color de relleno diferente.

Después haz clic en el botón Aceptar y posteriormente de nuevo en Aceptar para cerrar el cuadro de diálogo Editar regla de formato. Los datos que habían sido seleccionados recibirán el formato de acuerdo a la regla recién creada.

Ahora ya conoces un truco interesante del formato condicional para resaltar filas alternas en Excel. Obtén más información sobre la función RESTO.

Buscar valores usando formato condicional

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 entontrar 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 las 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.

Referencias absolutas y relativas

Una referencia en Excel identifica a una celda (o rango de celdas) dentro de una hoja de trabajo y le hace saber a Excel en dónde buscar los valores que se quieren utilizar en una fórmula. Pero ¿Cuál es la diferencia entre las referencias absolutasy las referencias relativas?

Referencias relativas en Excel

Una referencia relativa es cuando Excel puede modificar libremente dicha referencia para ajustarla al utilizarla dentro de una fórmula. Por ejemplo, si la fórmula de la celda D1 es la siguiente:

=C1*2

Si arrastramos el controlador de relleno hacia abajo, Excel copiará la fórmula y la ajustará de manera que la referencia se modifique automáticamente conforme va avanzando la fila.

En este ejemplo la referencia C1 de la fórmula se fue incrementando automáticamente a C2, C3, C4 y C5 conforme fue cambiando de fila.

Referencias absolutas en Excel

Ahora analicemos el caso de las referencias absolutas. Una referencia es absoluta cuando Excel no la puede ajustar para adaptarse a la fórmula conforme cambia de fila o de columna. Las referencias absolutas permanecen constantes sin importar a dónde se copie la fórmula y se definen utilizando el símbolo “$”. Por ejemplo, la referencia $A1 significa que en esta referencia la columna A será siempre fija mientras que la  fila podría ajustarse automáticamente. Por otro lado, la referencia A$1 significa que la fila 1 permanecerá siempre fija. Si quieres que tanto la columna como la fila permanezcan siempre fijas la referencia debe ser $A$1.

Con un ejemplo similar al anterior veamos lo que sucede cuando hacemos que la referencia sea absoluta. Nota que ahora la fórmula de la celda D1 está escrita de la siguiente manera:

=$C$1*2

Observa que sin importar a qué fila se copió la fórmula, la referencia siempre se mantiene hacia $C$1. Es decir, aún cuando se halla copiado la fórmula a filas diferentes la referencia dentro de la fórmula permaneció constante. Es importante que entiendas la diferencia entre estos dos tipos de referencias porque te permitirá escribir fórmulas efectivas en Excel.

¿Qué es el formato de celda en MS Excel?

El formato de celdas sirve para modificar el tipo de letra, tamaño, espaciado, entre otras características de las celdas y su contenido.

El formato de las celdas está concentrado en un solo cuadro de dialogo. Para acceder a ésta se selecciona del menúFormato la opción Celdas o se presiona <Ctrl+1>. Al hacerlo aparece la siguiente ventana: 

 

¿Cómo dar formato a Números?

Separador de número

El separador de Número sirve para asignar una notación especial a las celdas que contengan números. Se tienen varias Categorías y en cada una están distintos Tipos. Según el tipo de datos numéricos que se tengan es el formato que se puede elegir. Además, se pueden modificar estos formatos en la barra de herramienta Número con los comandos números en la casilla “Formato de Número”.

También se pueden crear nuevos códigos seleccionando la opción Personalizada y escribiendo el Tipo.

Algunas de las operaciones que se realizan en este separador tienen equivalencia en la barra de herramientas de “Formato de Número”.

Elegir o cambiar formato de Fuente de un texto en Excel

A través este separador se cambia las Fuentes, Estilos yTamaño de las celdas. 

En este cuadro de dialogo seleccionaremos la Fuente, su Estiloy Tamaño. Podremos subrayarlo y escoger el tipo. Además, se puede elegir efectos de Tachado, Superíndice o Subíndice. Si seleccionamos Fuente normal, MS Excel 2007 utiliza las definiciones que acabamos de realizar como el tipo normal de fuente que utilizará.

En la barra de herramientas de Fuente se tienen también algunas de estas acciones, como se muestra en la siguiente imagen:

 

Formato de Relleno y Protección de una celda

Separador Relleno

Con este separador se le da color o diseño a las celdas seleccionadas.

A las celdas se les puede asignar un Color en el fondo y unTrama para que resalte su contenido.

El color del fondo de la celda, se puede elegir también con labarra de herramientas Fuente como se presento en elartículo anterior precisamente con el comando “Color de relleno”.

Separador proteger

La protección de una celda, sirve para evitar que se realicen cambios o que no se vea el contenido de la celda.

Seleccione Bloqueada para bloquear la celda y Oculta para que su contenido no se vea.

El bloquear y ocultar una celda no basta para que éstas estén ocultas o protegidas. Una vez que se seleccionaron alguna de estas dos opciones, del menú Herramientas seleccione el comando Proteger. Esta opción permite Proteger hoja,Proteger libro o Proteger y compartir libro.