Una de las funciones, o utilidades de Excel menos aprovechadas es el formato condicional, y esta es una grandísima herramienta para analizar datos con formato condicional
Los condicionales nos sirven para establecer alarmas visuales en nuestras hojas de Excel que nos dan una gran información y además, con muy poquito esfuerzo.
Para diferenciar un poco, vamos a separar formato condicional en las opciones básicas y las opciones “Pro”
Con los condicionales, vamos a ver como al establecer los formatos automáticos, nuestros datos nos van a aportar información de mucho más valor que nos va a facilitar la toma de decisiones.
¡En este tutorial descubrirás como analizar datos con formato condicional como un verdadero profesional!
Paso a paso Básico para analizar datos con Formato condicional.
Nuestra primera hoja de hoy, tiene dos columnas, por un lado el mes y por otro, el registro de ventas de cada mes:
Vamos a resaltar con un fondo y color de fuente (ahora vamos a modificar sólo estas opciones), con el formato condicional.
a) Buscamos valores superiores/inferiores
En primer lugar, vamos a obtener los valores superiores a 60.000
Seleccionamos todos los valores de nuestras ventas y después nos vamos a la cinta de inicio > Estilos > Formato condicional.
Tenemos que seleccionar los valores mayores que…
Enseguida, nos pide el valor orientativo y la opción por defecto de formato que queremos dar a nuestras celdas:
Automáticamente nuestra tabla nos dice cuales son los meses más fuertes en ventas con el formato que hemos elegido.
Ya veis qué meses son los más potentes, están por encima de 60.000.
Los condicionales son muy potentes y fáciles de configurar, lo que es maravilloso, es que podemos dar más de una regla a cada celda.
En este caso, seleccionamos de nuevo las mismas celdas y vamos a ver cuales son los valores por debajo de 20.000.
Y el resultado es inmediato:
Podéis hacer pruebas y observad cómo actualiza inmediatamente a los nuevos valores
a) Encontrar valores Top y Bottom
Hemos borrado las reglas anteriores de nuestra tabla y ahora vamos a poner reglas para que nos localice los valores Top y los inferiores
Empezamos como en el paso anterior, seleccionamos nuestras celdas, vamos a formato condicional > Reglas para valores superiores e inferiores.
Por defecto, nos indica 10 superiores, pero lo modificamos y elegimos el formato.
Ya tenemos el Top 3 de los meses con más ventas.
Como siempre, cualquier cambio en los valores, lo actualiza automáticamente y siempre nos marcará el Top 3 de los valores superiores.
a) Encontrar valores entre otros
Volvemos a borrar las reglas y comenzamos de nuevo
Seleccionamos los valores y volvemos a la primera opción que hemos utilizado para ver los superiores a 60.000.
Como son valores intermedios y parar terminar el semáforo, cogemos la opción amarilla.
Y ya lo tenemos.
Ya que tenemos el análisis básico, hemos visto como aplicando los formatos condicionales, podemos obtener mucha más información de un simple vistazo que con las tablas de valores.
Vamos a pasar a ver las opciones PRO.
Paso a Paso PRO para analizar datos con Formato condicional.
a) Análisis con proporción de color
En nuestra nueva hoja, vamos a seleccionar los valores de cada línea de negocio, en este ejemplo, vamos a mostrar aplicando sobre Formación Online, vamos a formato condicional > Barras de datos y vemos que pasa:
El resultado en nuestros datos es el que sigue.
Como el mes más potente en ventas es junio, lo toma como punto de partida, es el 100% de la barra de datos, el resto de la serie de meses se rellena proporcionalmente en base al mes más alto.
Vamos a hacer la prueba con el total de los valores de ventas de las tres líneas de negocio que estamos estudiando.
Analizamos en conjunto y vemos que la consultoría es la línea más fuerte de nuestro negocio y como punto más potente de lo que va de año, el mes de junio. Ahora nos han cambiado las barras de datos de Formación Online, son mucho más bajas ya que estamos observando la proporción respecto al más grande, junio de consultoría.
Como vemos, con muy poquitos clicks hemos dado un gran aporte de valor a nuestros datos.
a) Iconos
Vamos a formatear nuestros datos para qué con una regla condicional nos aparezca una serie de iconos (elegiremos flechas) indicando los valores más altos, bajos o intermedios; lo vemos.
Así tenemos…
Seguro que ahora os surgen un par de preguntas a vuela pluma, a nosotros sí:
- ¿Dónde hace el corte de una flecha a otra?
- ¿Puedo quitar algún icono?
Vamos por partes.
Con todas las celdas seleccionadas, vamos a formato condicional > Administrar reglas y ahí Editar regla.
Y aquí vamos a tener la respuesta a nuestras dos preguntas.
Desde esta ventana podemos cambiar los rangos superiores e inferiores y además hacer que un icono deje de aparecer.
Aceptamos y aplicamos cambios.
Y vemos el resultado mucho más limpio y nos aporta muchísima información.
Bonus Track
a) Aplicar condicionales con fórmulas
Podemos hacer que el formato aplique a celdas de la misma fila aunque no sean estas en las que estén los valores.
Ahora, nos vamos a meter en harina.
En nuestra hoja Básico, seleccionamos los valores de los meses y sus ventas, el rango A2:B13 y aplicamos formato condicional > Nueva regla. Vamos a crear un formato totalmente personalizado.
Podemos establecer una fórmula para los condicionales
Si estudiamos un poco esta fórmula, lo que va a hacer Excel es resaltar los valores de la fila más alta de las que comprenden el rango (porque el rango empieza en la fila 2 que coincide con $B2), de ahí hacia abajo, fila a fila, comprobará las celdas del rango y aplicar el formato a toda la fila
Podemos ver las reglas aplicadas en administrar reglas de formato condicional.
a) Encontrar duplicados
Ahora sí, vamos a rizar el rizo.
Nos ponemos en situación, imaginad que estáis actualizando un listado de facturas y como todos sabemos, el número es unívoco, no podemos repetir el número de factura. Actualizamos el listado y no sabemos si es totalmente correcto, si aplicamos un formato, podemos encontrar rápidamente las facturas repetidas.
Hemos cambiado la tabla, ahora es
Seleccionamos nuestros valores de número de factura (A2:A13) y aplicamos formato condicional como antes
¡Quieto todo el mundo!
No hay sustos, es muy sencillo
Vamos a aplicar el condicional a las celdas que cumplan esa regla, que es:
Del rango $A$2:$A$13, Excel cuenta las veces que se repite el valor de cada fila (A2) y cuando lo encuentra más de 1 vez, le aplica el formato
Tened muy presente las referencias relativas o absolutas, gracias a las que el formato condicional será aún más potente de lo que ya podemos utilizarlo.
a) Modificar y borrar condicionales
En el video pasamos muy rápido por encima, pero a veces nos cuesta encontrar esta opción.
Para borrar los condicionales o modificarlos tenemos que:
- Seleccionar las celdas que queremos modificar o borrar el formato.
- Nos vamos a Formato condicional y ahí
Podemos elegir borrar las reglas de las celdas que tenemos seleccionadas, o las otras opciones, según nos haga falta.
Además, en el mismo apartado del listado, dónde se borran las reglas, podemos entrar a la modificación de las reglas, en Administrar reglas.
Bueno y hasta aquí este pedazo de articulo!, una pasada verdad? Con ello ya sabemos como analizar datos con formato condicional. Te dejo el fichero que hemos usado en el vídeo para que puedas practicarlo ahora mismo.
Analizar datos con formato condicional
Si te ha gustado ni te imaginas lo que te espera dentro de la academia! Más de 20 cursos disponibles, mis soporte para todas tus dudas y plantillas y recursos premium, así que no lo dudes y únete al club! Somos más de 300 personas dentro de la academia.
Nos vemos en el próximo tutorial.