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!

67 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.

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

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

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

    ResponderBorrar
  4. Muchas gracias!!!!!

    Llevaba tiempo tras una solución como esta.

    ResponderBorrar
  5. Muchas gracias!!!!

    Llevaba tiempo tras una solución como esta.

    ResponderBorrar
  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.

    ResponderBorrar
  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!

    ResponderBorrar
  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

    ResponderBorrar
  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)

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

    ResponderBorrar
  11. MUY BUENO!!!!!!!!!!!!!!! Muchisimas Gracias

    ResponderBorrar
  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?

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

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

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

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

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

    ResponderBorrar
  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.

    ResponderBorrar
  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...

    ResponderBorrar
  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

    ResponderBorrar
  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á

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

    ResponderBorrar
  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.

    ResponderBorrar
  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

    ResponderBorrar
  25. Gracias desde Costa Rica!!!!!!!

    ResponderBorrar
  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.

    ResponderBorrar
  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

    ResponderBorrar
  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.

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

    ResponderBorrar
  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

    ResponderBorrar
  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??

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

    ResponderBorrar
  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.

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

    ResponderBorrar
  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...

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

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

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

    ResponderBorrar
  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...

    ResponderBorrar
  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

    ResponderBorrar
  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.

    ResponderBorrar
  42. 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!!

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

    ResponderBorrar
  44. gracias, me ayudaron de muy buen grado

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

    ResponderBorrar
  46. Muchas gracias! me sirvio mucho..-

    ResponderBorrar
  47. Gracias amigo me sirvió bastante

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

    ResponderBorrar
  49. Tengo una pregunta... sirve la misma formula pero para colorear fechas?

    ResponderBorrar
  50. 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

    ResponderBorrar
  51. 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

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

    ResponderBorrar
  53. SOS EL PUTO AMO!!, MUCHAS GRACIAS!

    ResponderBorrar
  54. como quedaría la formula si lo que quiero es colorear una fila donde pongo tareas y al final le pongo, por ejemplo cumplido, para que me lo ponga en verde, en ejecucion amarillo y rojo para cancelado... porque no me sale
    desde ya muchas gracias

    ResponderBorrar
  55. Excelente ya rellene mi tabla de muchos formatos condicionales :P. Gracias por el aporte

    ResponderBorrar
  56. Buen dia. tengo un formulario, pero quiero que al seleccionar un dato de una lista desplegable, se rellene automaticamente el resto de celdas, con la información de una lista predefinida en otra hoja. esto se puede?

    ResponderBorrar
  57. Buen día,me podrían ayudar con un archivo que tiene varios precios en diferentes columnas según su proveedor, quiero resaltar el mejor precio por fila, pero solo lo puedo hacer en formato condicional de fila por fila y no es viable para mi, al querer pegar formato condicional solo me da el valor de la primera celda, ya quiete candados “$” intente diferentes modos de pegado y nada como puedo hacerlo?

    ResponderBorrar
  58. Buen día,me podrían ayudar con un archivo que tiene varios precios en diferentes columnas según su proveedor, quiero resaltar el mejor precio por fila, pero solo lo puedo hacer en formato condicional de fila por fila y no es viable para mi, al querer pegar formato condicional solo me da el valor de la primera celda, ya quiete candados “$” intente diferentes modos de pegado y nada como puedo hacerlo?

    ResponderBorrar
  59. Hola necesito de tu ayuda fíjate que busque en el Excel y no encontré lo de formato condicional, mira me interea ya que lo que quiere hacer en mi tabla de Excel es comparar 7 opciones con una puntuación máxima donde el mayor de estos comparados con la máxima deberá ponerse en verde como estará la máxima, y el resto de rojo me podrias ayudar :)

    ResponderBorrar
  60. buenas tardes disculpa espero y me puedas ayudar. en una hoja de excel un listado con formato condicional que cambia el color de la celda segun el valor numerico se le asigno un valor numerico a cada color y cambia con cnforme cambia el numero. en otra tengo un croquis de ciertas areas la que tengo que hacer es que haciendo referencia a mi listado de la hoja 1 tiene que cambiar automiticamente de color en mi hoja 2 pero solo de color solo nesesito ver el en croquis el color que se le esta asignando por el formato condicional de la hoja 1 ya que en el tengo que escribir en cada celda el nombre del area como un croquis normal entonces queria saber como le puedo hacer para que cuando cambie de numero y de color en mi listado se cambie automaticamente en mi hoja dos en el croquis pero solo de color y que puda escribir en ella

    ResponderBorrar