2007/08/17

Como colorear celdas automaticamente en excel


¿Cuantas veces te pasó que quisiste colorear una celda en excel de un color dependiendo si tenia mas que un valor y de otro color si tenia otro valor?
¿Alguna vez quisiste saber inmediatamente si un valor es bueno o malo al solo actualizar una planilla?

¡Este truco de Microsoft Excel es tu solución!

Para colorear una celda según su valor y ponerle el color que quieras, y que esto se actualice automáticamente, solo hay que seguir los siguientes pasos:

1) Necesitas tener una planilla de excel, puede ser de la complicación que quieras, para este ejemplo tomamos una muy simple:



2) En segundo lugar tenes que seleccionar las celdas a las que les vas a aplicar el coloreado automático, en el caso que tengas una planilla que crezca indefinidamente podes optar por seleccionar toda la columna. Para este ejemplo seleccioné toda la columna.


Selección de columnas finitas


Selección de toda la columna

3) Luego tenes que ir al menú Formato -> Formato Condicional



4) Se va a abrir una nueva ventana que te va a permitir entre dos opciones, "Valor de Celda" o " Fórmula"
Valor de celda te permite elegir un rango de valores entre los que va a estar una celda, pero en el caso que quieras tener una condición que depende de otras celdas, por ejemplo, la diferencia entre dos celdas, es bueno tener a mano la posibilidad de agregar formulas de la complejidad que queramos:




5) Veamos el caso de una formula, usamos la formula SI que es un condicional, el formato es super simple: =SI(condición;valor devuelto ok; valor devuelto no ok)
Donde condición es una ecuación, en el caso de ejemplo decimos que si la celda B1 es mayor que cinco (5), entonces devuelve VERDADERO, sino devuelve FALSO.

La ecuación es así:
=SI(B1>5;VERDADERO;FALSO)



Como puede funcionar algo así? es simple, al poner B1, que en el caso de ejemplo es la primer celda seleccionada, Excel automaticamente varía la función para cada celda, por ejemplo la celda B7 va a tener una ecuación:
=SI(B7>5;VERDADERO;FALSO)

Si hubieras seleccionado solamente las celdas con valores:


Habrías visto un comportamiento diferente según lo que pusieras:


Poniendo B1



Como veras, hay valores menores que cinco (5) marcados!, esto pasa porque si pones B1 y tu selección empieza en B2, B2 va a tener la formula:

=SI(B1>5;VERDADERO;FALSO)
y B7 tendría la formula:
=SI(B8>5;VERDADERO;FALSO)

¿Se entiende?

6) Para terminar, vas a poder agregar hasta tres condiciones diferentes para tres colores y formulas diferentes, en este ejemplo, si el valor de B esta entre 5 y 10, se pone amarillo, si es mayor que 10, se pone verde.



Fijate como cambio la formula de arriba para que no pise la de abajo (ya que 10 es mayor que 5 y cumple la primer formula siempre)

El resultado final es así:




¿Y si quiero aplicar el formato por columna para todo la hoja como hago?
Simple! Excel toma los nombres de celdas con un símbolo de "$" previo a ellos como inamovibles, o sea, si tenes la función
=SI($B$8>5;VERDADERO;FALSO)
Todas las planillas seleccionadas van a usar el mismo valor, por lo tanto, si queres que la planilla de ejemplo marque toda la linea como amarilla si B es mayor que 5, tenes que:

a) Seleccionar todo el libro b) En la formula escribir:
=SI($B1>5;VERDADERO;FALSO)
Con el $B lo que decís es "siempre mirar la columna B para los valores, no usar ni A ni ninguna otra, no importa que columna estés)

¡Cualquier duda la contesto desde los comentarios!

60 comentarios:

  1. Muy bueno este tutorial, me surgen algunas dudas pero no tengo claro ni en lo que dudo. He conseguido hacer lo que quería con valor de celda ya que con la fórmula me parece una locura!!

    Muchas gracias.

    ResponderEliminar
    Respuestas
    1. hola.. perdón, pero en que parte encuentro en exel el menú formato?

      Eliminar
  2. Muy buena! Me ahorró mucho tiempo! Gracias!!!

    ResponderEliminar
  3. Anonimo:
    Es cuestion de pensar un poco, la formula es muy simple!! Cualquier cosa consultame.

    ResponderEliminar
  4. Muchas gracias!!!!!

    Llevaba tiempo tras una solución como esta.

    ResponderEliminar
  5. Muchas gracias!!!!

    Llevaba tiempo tras una solución como esta.

    ResponderEliminar
  6. Hola, te escribo desde Canadá.
    Trabajo en el diseño de tableros electrónicos para distribución de señal de TV Satelital en edificios.
    Diseñé una tabla de Excel para calcular la pérdida de señal dependiendo del largo y tipos de cables empleados entre pisos, así como la cantidad de componentes electrónicos, pero resultó ser tan larga que a veces pasaba desapercibido algún valor. Ahora con este "truco" en el cambio de color de las celdas que he aprendido gracias a tus consejos, el trabajo se me ha facilitado como no tienes idea, pues cada vez que ingreso un valor, ya tengo una señal de color que me advierte. Muchísimas gracias.
    Gregory.

    ResponderEliminar
  7. Quisiera saber cómo hacer para colorear también la celda de la izquierda (aunque no tenga sentido a nivel valores). Es decir, que 1, 2, 4, 6 y 10 de agosto (en el ejemplo citado) aparezcan con los respectivos colores - dependiendo de los valores de la columna de la derecha.

    Muchas gracias!

    ResponderEliminar
  8. Con la formula elegis vos en que celda mirar los datos, pero afecta el color de la/s celda/s que tienen el formato condicional.

    Fijate que yo estaba cambiando los colores de la columna "B" y aun asi tuve que indicarle que mire los valores de la columna "B", no los toma por hecho sino que le tenes que indicar vos, asi que si haces lo mismo en la columna "A" y le decis que mire la columna "B", te va a funcionar igual.

    Saludos

    ResponderEliminar
  9. Wally: felicitaciones por esta formula, es muy simple pero eficaz y facilita mucho el trabajo cuando quieres resaltar un resultado especifico. Gracias Jaime (Colombia)

    ResponderEliminar
  10. ¡Muchas gracias! Fue de mucha ayuda.

    ResponderEliminar
  11. MUY BUENO!!!!!!!!!!!!!!! Muchisimas Gracias

    ResponderEliminar
  12. Wally, Muy Util todo esto¡¡
    Pregunta: quiero referenciar los valores a otra hoja o libro y no me lo permite.
    Esto quiere decir que si en un hoja( Hoja1)tengo un valor ( -10),en la hoja2 no puedo reflejarlo con el condicional
    Como se soluciona?

    ResponderEliminar
  13. muy buen aporte, me sirvió para disipar mis dudas.

    ResponderEliminar
  14. ¡excelente funcion! aplicable con valor agregado al mantenimiento.
    Pero mas excelente la actitud de compartirlo en la red.

    ResponderEliminar
  15. Mil gracias tenia un archivo con 45000 filas y con eso pues ya quedo.

    ResponderEliminar
  16. hola muchas gracias por tu ejemplo me sirvio mucho pero como hago lo mismo para excel 2007 gracias

    ResponderEliminar
  17. MUCHAS GRACIASSS HAS SIDO DE MUCHISIMA AYUDA!!!

    ResponderEliminar
  18. Hola, mira en mi trabajo tenemos muchos productos pero en un mismo producto hay una cierta cantidad que se vence mas pronto q el resto, entonces lo que quiero es q al buscar un producto( escribiendo su nombre en una celda) me aparesca su fecha de vencimiento más proximo junto con la cantidad que tiene esa fecha. Te agradecería tu ayuda.

    ResponderEliminar
  19. Mr. Excel, para eso necesitas crear alguna macro VBS (Visual Basic)

    Sino lo mas comodo es usar la funcion de Filtro de excel, te permite filtrar por maximos y minimos, y ordenar por diferentes columnas...

    ResponderEliminar
  20. Sigo todos los pasos y no me lo colorea, te paso una impresion de la pantalla a ver si se te ocurre por que puede ser esto.

    http://img192.imagevenue.com/img.php?image=27468_Colorear_122_67lo.JPG

    Desde ya muchas gracias

    ResponderEliminar
  21. Lo que sucede es que no estas seleccionando la columna (por un lado)

    Ademas los valores que tienes no son numericos, "004" no es un numero y seguramente Excel no lo esta pudiendo comparar.

    Lo que podes hacer es seleccionar la columna, ir a propiedades y cambiar el formato de la columna a NUMERICO, seguramente ahi te funcionará

    ResponderEliminar
  22. y si en ves de un numero kisiera k al escribir una palabra en una celda automaticamente cambiara de color

    ResponderEliminar
  23. Esa pregunta esta cubierta en el punto 4, selecciona "Valor de la Celda" "Igual" o "Contiene" el valor que quieres que haga cambiar de color, para que funcione en toda una columna la debes tener seleccionada mientras le agregas la regla.

    ResponderEliminar
  24. Hola buen dia, yo tengo una duda, tengo una tabla ya con valores como para rellenar, quisiera que en una columna ir poniendo numeros y que me fuera coloreando celdas conforme al numero, por decir si meto 4 en la primera celda que me coloree 4 celdas siguientes del rango que le especifique

    ResponderEliminar
  25. Gracias desde Costa Rica!!!!!!!

    ResponderEliminar
  26. Exelente , te felicito sencillo y practico con ilustraciones y sin tanta terminologia técnica, realmente me sirvio mucho y una vez mas te felicito gracias por la ayuda.

    ResponderEliminar
  27. En la formula utilizas numeros mayor que 5 menor que 10 pero si lo que tengo son letras y es la misma ejemplo tengo L quiero que cuando la digite esa celda sea roja y cuando vuelva hacerlo tambien con la L quiero que sea verde como hago

    ResponderEliminar
  28. Tengo una duda que no puedo resolver desde hace mucho tiempo: Una hoja excel que contiene datos numericos. No puedo hacer que coloree automáticamente los 21 valores mayores.

    ResponderEliminar
  29. Wally porque no contestaste para Excel 2007 no se puede verdad?

    ResponderEliminar
  30. Instalen en sus pc office 2007, el formato condicional tiene las fórmulas listas... es cosa de hacer sólo un par de click para ver lo que tu quieras...

    CMVC

    ResponderEliminar
  31. Hola, si yo quisiera pintar un total de 25 numeros, osea por ej: 00-02-04-06-08-20-22-24..... como se hace??

    ResponderEliminar
  32. ayer aplique y esta super facil. gracias
    Luis

    ResponderEliminar
  33. Hola: y si quisiera que por ejemplo, se resaltaran los valores mas altos de la columna 'VALOR' de tu ejemplo, SIEMPRE Y CUANDO el total de esa columna supere otro valor?

    Es decir, tu columna valor suma 59. Digamos que tengo un numero que es 50. Entonces como el total es mayor que 50, se deben resaltar, por ejemplo, las dos más altas.

    Pero si el total fuera, por ejemplo 49, no se debe resaltar nada.

    ¿cómo le hago? Gracias.

    ResponderEliminar
  34. estaba pensando en darle la respuesta a un usuario que se llama Anonimo Pero hay como 30 como le hacemos.

    ResponderEliminar
  35. y SI EN LUGAR DE DECIR DE QUE FUERA MAYOR QUE UN NUMERO ... YO QUISIERA LA CONDICION QUE CUANDO: EL NUMERO DE LA CELDA x SEA MAYOR QUE EL NUMERO DE LA CELDA w ME MARQUE OTRO COLOR???.... Y LA MISMA FORMULA APLICARLA A TODA LA COLUMNA.... DE UNA VEZ.... ??? COMO LE HAGO...

    ResponderEliminar
  36. ola gracias por esta informacion que nos de todos los dias
    en serio uno aprende de maxiado.......................................... graciiias..........

    ResponderEliminar
  37. Muchas Gracias wallys.. su ejemplo es muy claro y practico, para los pricipiantes..saludos.

    ResponderEliminar
  38. Un tutorial muy completo y muy biene explicado
    Gracias por la información

    ResponderEliminar
  39. Una consulta, he podido hacer el ejemplo y me resulto bien, pero hay forma de hacer la condicional con celdas que están en otra hoja?....
    Lo intente, pero me salio un mensaje diciendo, que solo podía hacer la condicional con celdas de la misma hoja...

    ResponderEliminar
  40. hola una pregunta donde veo la opcion de formato en excel 2007 no la ubico no se como sacarla porfa me puedes ayudar y decirme donde la ubico

    ResponderEliminar
  41. Cordial Saludo

    he tratado por todos los medios de averiguar como se hace para colorear una linea con dos condiciones ej: tengo el cuadro de ventas y unas de sus columnas son fecha(dia/mes/año) y numero de ventas y deseo colorear toda la linea del vendedor que vendio mas unidades en una fecha determinada.

    ResponderEliminar
  42. Muy bueno! Muchas gracias

    ResponderEliminar
  43. Tengo una pregunta, ojala me puedan ayudar , quisiera que solo una casilla determinada , me dé una alerta en colores y sombreadocuando excedo de un monto previamente determinado ocuando llega a cero este onto , es posible hacer esto.?? he intentado colocando las formulas pero no me sale, he hecho lo que la lógica me dicta en este caso y tampoco me aparece lo que quiero , Por favor ayudenme!!

    ResponderEliminar
  44. Muchas Gracias el primer sitio donde lo explican de manera clara y sencilla :)

    ResponderEliminar
  45. gracias, me ayudaron de muy buen grado

    ResponderEliminar
  46. Hola, mull vuen tradajo, pero una pregunta se posria acer que pusiera color a mas casillas y no solo a una

    ResponderEliminar
  47. Muchas gracias! me sirvio mucho..-

    ResponderEliminar
  48. Gracias amigo me sirvió bastante

    ResponderEliminar
  49. Hola,¿esta fórmula sirve para rellenar ciertas frases, por ejenplo las lineas delos totales de autosuma?
    Gracias

    ResponderEliminar
  50. Tengo una pregunta... sirve la misma formula pero para colorear fechas?

    ResponderEliminar
  51. hola necesito en un formulario de vacaciones hecho en excel crear un boton que se llame vacaciones y cada vez que le doy clip se active y yo pueda ir a la tabla y colocar ese mismo formato de ese boton en una celda en excel de ese mismo formulario

    ResponderEliminar
  52. Gracias por la oportunidad. Quisiera consultar si es posible cambiar el color de una celda si se cumple una condicion en otra. Quiero decir:

    Si la celda "A1" es igual o mayor que 41230 (o una fecha) entonces cambiar a color rojo en la celda "B2"
    Gracias anticipadas

    ResponderEliminar
  53. Por supuesto, en lugar de B pones A y listo, siempre y cuando estés parado en la celda B

    ResponderEliminar
  54. SOS EL PUTO AMO!!, MUCHAS GRACIAS!

    ResponderEliminar