Contar celdas por color en Excel

Contar celdas determinado color en Excel

El color de relleno de una celda es ampliamente utilizado por los usuarios de Excel ya que, al resaltarla con un color diferente, podremos identificarla fácilmente en pantalla.

En ocasiones el color significará que el dato pertenece a alguna categoría específica, o tal vez algún estatus o acción que debemos tomar basados en dicho color. El asunto es que, cuando tenemos varias celdas con un color de relleno, es casi inevitable que llegue el momento en que necesitemos contarlas por color.

La mayoría de los usuarios buscan de inmediato alguna función de Excel que les ayude a contar por color, pero pronto se encuentran con que no existe una función que haga dicha tarea.

Y es que, ninguna de las funciones de Excel puede acceder a la información de formato de una celda. No existen funciones para saber si la letra es negrita, o para conocer el tipo de fuente utilizada, así que tampoco existe una función para conocer el color de la fuente o el color de relleno de una celda.

Cuando me refiero a que no existen funciones de este tipo, me refiero a las funciones provistas de manera predeterminada por Excel. Sin embargo, es posible construir nuestra propia función que nos ayude a contar las celdas por color.

Construir una nueva función no es cosa de novatos, pero tampoco es una tarea de altísima complejidad, así que en esta ocasión te mostraré los pasos necesarios para que puedas crear esa función que cuente las celdas por color.

Descargar libro de trabajo

Lo primero que debes saber es que será necesario utilizar programación VBA para crear la nueva función. Pero no te preocupes si no sabes programar, ya que te mostraré cada una de las acciones a tomar.

Código VBA para contar por color

El código VBA de la función ya lo he programado con anterioridad y lo encuentras a continuación, así que solo deberás copiarlo y pegarlo en el lugar adecuado.

Function CONTARCOLOR(celdaOrigen As Range, rango As Range)
    
    Application.Volatile
    
    'Variables
    Dim celda As Range
             
    'Recorremos todas las celdas del rango
    For Each celda In rango
        
        'Compara la propiedad Interior.Color
        If celda.Interior.color = celdaOrigen.Interior.color Then
            CONTARCOLOR = CONTARCOLOR + 1
        End If
        
    Next celda

End Function

Después de copiar el código deberás abrir el Editor de Visual Basic, y para eso tienes dos opciones:

  • Pulsar el atajo de teclado ALT + F11
  • Hacer clic en el botón Visual Basic de la pestaña Programador

Una vez que se abre la ventana del Editor de Visual Basic, deberás hacer clic en en el menú Insertar > Módulo.

Contar celdas por color en Excel

Se insertará un nuevo módulo de código, que llevará un nombre como Módulo1, y en el panel de la derecha deberás pegar el código de la función tal como lo muestra la siguiente imagen:

Contar el número de celdas con un color específico

Con estos pasos hemos creado y habilitado la nueva función que lleva por nombre CONTARCOLOR y cuyo uso explicaré a continuación.

Ejemplo para contar celdas por color

La función CONTARCOLOR tiene dos argumentos, el primero de ellos es la referencia a una celda que tendrá el color de relleno que queremos contar y el segundo argumento es el rango con las celdas que vamos a evaluar.

La siguiente imagen muestra las celdas del rango A1:A10 con diferentes colores de relleno y la celda D1 que tiene el color azul el cual nos interesa contar.

Contar celdas por color de relleno

Para contar las celdas de color azul en el rango A1:A10, debo utilizar la función CONTARCOLOR de la siguiente manera:

=CONTARCOLOR(D1, A1:A10)

El primer argumento es la celda que contiene el color que estamos buscando y el segundo argumento es el rango de celdas. Ingresaré la fórmula anterior en la celda D2 y al pulsar Entrar obtendremos el siguiente resultado:

Contar celdas por color de fondo

No es indispensable tener una celda diferente para especificar el color que deseamos contar. He utilizado la celda D1 solo para ejemplificar el uso de los dos argumentos, pero nada impide que utilicemos una celda dentro del rango como el primer argumento. Considera la siguiente fórmula:

=CONTARCOLOR(A2, A1:A10)

En este caso, la celda A2 tiene el color azul que queremos contar, así que podemos indicar dicha celda como el primer argumento de la función y el resultado será el mismo.

Cómo contar celdas por color en Excel

El uso de esta función es muy simple y solo deberás asegurarte de indicar en su primer argumento una celda que tenga el color que te interesa contar. Recuerda que Excel tiene una gama de millones de colores y por lo tanto debes tener mucho cuidado ya que podrías ver en pantalla colores muy parecidos cuando en realidad son distintos.

Consideraciones sobre la nueva función

Para terminar la explicación de esta nueva función quiero mencionar algunas consideraciones importantes que deberás recordar en todo momento:

  • El libro donde se encuentra la nueva función deberá guardarse como un Libro habilitado para macros o de lo contrario perderás la funcionalidad.
  • La función que acabamos de crear solo puede ser utilizada en el libro de Excel donde has copiado el código. Si quieres utilizar la función en otro libro, entonces deberás copiar de nuevo el código en dicho libro. Existen métodos para hacer que una función de este tipo esté disponible en todos los libros del equipo, pero eso será un tema para otra publicación.
  • Esta función solo trabajará adecuadamente en Excel 2007 y versiones posteriores. Lamentablemente no funcionará en Excel 2003 o versiones anteriores y la explicación detallada la encontrarás en la última sección de este artículo.
  • El color que estamos evaluando es el color que se aplica a una celda con el comando Color de relleno y no nos referimos al color de formato condicional el cual requiere de un código VBA diferente y por lo tanto será tratado en otra publicación.

A continuación, explicaré con mayor detenimiento el código de la función para aquellos usuarios que están interesados en el tema de la programación en Excel.

Explicación del código VBA de la función

He decidido incluir esta sección para aquellos que están un poco más familiarizados con la programación en Excel y quieren leer una explicación más detallada sobre el código de la función UDF que acabamos de crear.

En primer lugar, tenemos la declaración de la función la cual acepta dos argumentos, celdaOrigen y rango.

Function CONTARCOLOR(celdaOrigen As Range, rango As Range)

Ambos argumentos son del tipo Range ya que el primer argumentos tendrá una referencia a la celda que tiene el color que vamos a contar y el segundo argumento será la referencia al rango que será evaluado.

La siguiente línea de código es una instrucción que se utiliza para marcar una función como volátil, lo cual quiere decir que la función será recalculada cada vez que se efectúa un cálculo en la hoja.

Application.Volatile

Esta instrucción es muy conveniente ya que ocasiona que el resultado de la función sea actualizado automáticamente al momento de realizar un cambio de color en las celdas. En la siguiente línea tenemos la declaración de la variable celda que nos ayudará al momento de recorrer el rango evaluado.

Utilizamos el bucle For Each para recorrer cada una de las celdas del rango y puedes notar que la instrucción hace referencia a la variable rango que es el segundo argumento de la función.

For Each celda In rango

Por último, tenemos la instrucción más importante de la función y es la comparación de los colores. El color de relleno está almacenado en la propiedad Interior.color y por esa razón se hace la comparación del color de la celda original y el color de la celda del rango que estamos evaluando.

If celda.Interior.color = celdaOrigen.Interior.color Then

Si los valores de las propiedades son iguales, quiere decir que los colores son iguales y por lo tanto aumentamos el valor de CONTARCOLOR en uno.

CONTARCOLOR = CONTARCOLOR + 1

De esa manera recorremos todas las celdas del rango y tendremos el resultado final en CONTARCOLOR cuyo valor es devuelto automáticamente por la función por tener el mismo nombre.

La propiedad Interior.color

Antes de terminar la explicación del código VBA debo decir que la propiedad Interior.color está disponible a partir de Excel 2007, así que esta macro no funcionará para versiones anteriores.

A partir de Excel 2007 tenemos millones de colores disponibles para elegir el color de relleno de una celda, pero eso no era posible anteriormente. En Excel 2003 y versiones previas solo era posible elegir entre una paleta de 56 colores y dicho color era almacenado en la propiedad Interior.ColorIndex.

A partir de Excel 2007 se creó la propiedad Interior.color ya que era imposible almacenar millones de colores en la propiedad Interior.ColorIndex que se utilizaba anteriormente. Por esa razón el código de nuestra función no trabajará en Excel 2003 y versiones anteriores a menos que cambies la propiedad utilizada.

Espero que esta función sea de mucha utilidad para esos casos en los que necesites contar las celdas por color en Excel.

78 comentarios en “Contar celdas por color en Excel

  1. José vivas

    Excelente, hace un tiempo que necesitaba está función y aún más para contar formatos condicionales con imágenes como ✓ o barras. Graciasssss

    1. Moises Ortiz Autor

      Hola José, tal como lo describo en el artículo, esta función NO servirá para contar por colores de formato condicional.
      Sólo es útil para contar por colores que se han aplicado con el comando Color de relleno.

  2. Álvaro g

    Excelente artículo. Me sirvió para un trabajo que estoy realizando

  3. Carlos

    Muy bueno, esto demuestra la creatividad y flexibilidad que podemos dar a nuestro trabajo.

  4. José MRC

    Espectacular. Muy últil. ¿Podrían sacar también una para SUMAR el contenido de las celdas de un mismo color?

    1. Moises Ortiz Autor

      Puedes convertir esta función en una suma con tan solo cambiar una línea de código. Encuentra la siguiente línea:
      CONTARCOLOR = CONTARCOLOR + 1

      Deberás reemplazarla por:
      CONTARCOLOR = CONTARCOLOR + celda.Value

  5. Rosana

    Muy bueno!!! Excelente sitio de consulta y aprendizaje!

  6. Roberto M.

    Excelente de muy buen uso.

  7. Daniel

    Muy bueno me ayudó en mi trabajo

  8. PaC

    Muy buen artículo. Me gustaría más artículos tan buenos y novedosos como este.

  9. José Luis

    Visualiza nuevos horizontes de uso

  10. JOSE AUGUSTO SILVA BARANDIARAN

    Excelente, necesitaba utilizarla porque trabajo con informaciones que se programan y después van llegando pero no en forma continua sino aleatoria y se necesita saber cuantas faltan en determinada fecha. Te agradeceré proporcionarme información de contar pero por color de fuente.

  11. Devid

    Muy buen trabajo lo estoy poniendo en práctica..

  12. Misael

    Muchas gracias, me ayudó mucho en el trabajo.

  13. Omar Vázquez

    Muy buen consejo, será de mucha utilidad para mi trabajo diario. muchas gracias por estos trucos.

  14. Luis

    Excelente articulo, gracias, me sirve mucho…

  15. Pablo

    Excelente! Muchas Gracias por su ayuda

  16. Manuel Torres

    Excelente función y muy práctica, gracias por comaprtirla!!!

  17. Jesus Camero

    Muy buena la explicacion, yo trababjo mucho con colores en celdas para controlar los tipos de dotacion del personal.

  18. Oscar

    Excelente artículo, muy útil. Sería muy bueno que por favor, incluyeras la función de contarcolor para formato condicional. Gracias.

  19. Libardog...

    Eres un genio… Gracias Moises…

  20. Victor Flores

    Me Parece muy interesante y practica esta función de contar color y la explicación muy sencilla de aprender y captar los procedimientos de forma analitica y no de forma mecanica. Felicitaciones por su excelente trabajo y estoy agradecido por su aporte al aprendizaje de esta valiosa herramienta de Excel.

  21. Rubén Reyes R.

    muy interesante el tema de los colores y su aplicación

  22. Luis Carreón

    EXCELENTE TUTORIAL, TU EXPLICACIÓN DE MARAVILLA, GRACIAS POR EL APORTE.

  23. Jose Manuel

    Que maravilla, estoy encantado y deseando que saques la versión con colores de los formatos condicionales.

  24. Zera

    Muy bueno. Siempre espero la publicacion de un nuevo típ de Excel para seguir aprendiendo. Este me es muy útil para mis tareas. Gracias Moisés.

  25. José Jesús

    Excelente herramienta, me sirvió para un trabajo que estoy realizndo

  26. thalia

    muy interesante me ayudo en mucho!

  27. Ma. Lucía Duque

    Hola buenos días.gracias por este maravilloso vídeo eres genial muchas gracias chao hasta la proxima

  28. David Santiago

    Entendido y claro. Muchas gracias Moisés por transmitir este conocimiento práctico para beneficio de todos, es muy loable de tu parte. Excelente aporte!
    Una humilde opinión: para el caso de sumar el contenido de celda con colores es bueno cambiar el código y por ende la función, el cual podría ser SUMARCOLOR, así aprovechamos las dos.

  29. Hernan

    Práctico y útil, especialmente cuando se trabaja con volúmenes grandes de datos

  30. Javier con Office 2000

    Hola. ¿Y para los que tenemos un Office 2003 o inferior (2000 en mi trabajo)? ¿Sería cambiar solo donde pone Interior.color por Interior.ColorIndex? Me dá que es más complejo… Saludos y gracias.

    1. Moises Ortiz Autor

      Javier, con ese cambio debería ser suficiente.

  31. Argimiro Yangua

    Excelente articulo, me sirvió de mucha utilidad, gracias

  32. OCTAVIO

    EXCELENTE APORTACION Y MUY UTIL….. GRACIAS MOISÉS….

  33. Luis Fonsaca

    Excelente ejemplo, sigo aprendiendo Visual Basic gracias por esta buena idea Saludos

  34. Toribio

    Gracias, es un excelente ejemplo.

  35. Manaury Paulino Aybar

    MUY BUENA LA OBSERVACIÓN. PARA ESTO ES OBLIGATORIO SABER DE PROGRAMACIÓN. PORQUE SIN EL CÓDIGO NO ES POSIBLE REALIZAR LA TAREA.

  36. Oscar

    La función es efectiva y sencilla, gracias

  37. Andrés bl

    Me parece muy buena pero si seria interensante que publicaran una que sirva para contar un determinado tipo de formato condicional

  38. julio

    muy bueno el tutorial muchas gracias me sirvió para mi trabajo

  39. Rugal

    Moises GENIAL Excelente y didáctico aporte. Realizaré los cambios en los cuadros de mantencion de datos. Esta formula me ahorrara + del 50% del tiempo en cada actualización. Gracias.

  40. Huasho

    Excelente aportación. Nos proporcionas muchas cosas de utilidad. Saludos

  41. Javier con Office 2000

    Javier con Office 2000: Hola. ¿Y para los que tenemos un Office 2003 o inferior (2000 en mi trabajo)? ¿Sería cambiar solo donde pone Interior.color por Interior.ColorIndex? Me dá que es más complejo… Saludos y gracias.

    Resp. Moises Ortiz (Autor): Javier, con ese cambio debería ser suficiente.
    —————————
    CORRECTO, funciona perfectamente

  42. ricardo argueyes

    Muy buena la explicación sobre la function contarcolor, gracias moisés por todo lo que nos das. Saludos de Ricardo.

  43. Ruben

    Como siempre muy útiles tus publicaciones, nos ayuda muchísimo en nuestro trabajo.Muchas gracias

  44. Welner

    ¡Es lo que necesitaba para completar mis tareas! Gracias mil

  45. Luis Hernando

    Excelente función. es muy util

  46. Héctor Cabrera Becerra

    El separador entre el argumento a buscar y el rango es ; y no , como aparece en el insgtructivo

    1. Moises Ortiz Autor

      Hola Héctor, gracias por tu comentario pero eso no es un error.
      El uso de la coma (,) o del punto y coma (;) depende de la configuración regional del equipo.

  47. Daniel

    Excelente articulo y gracias por explicar la programacion

  48. CLAUDIA ALEJANDRA CASTILLO CHAVARRO

    Excelente Moisés, muchas gracias por compartirnos tus conocimientos.

  49. IGNACIO

    MUCHAS GRACIAS DE NUEVO MOI POR TUS GRANDES APORTACIONES A LA COMUNIDAD CONTABLE Y ADMINSTRATIVA. QUE TENGAS UN EXCELENTE DIA.

  50. JOSE JAIRO OCAMPO F

    Excelente opción. uso mucho la colores en Excel para identificar datos estadísicos. Mucho me pregunté por una opción de esta clase.
    Gracias Moisés.

  51. Luis Marañón

    Excelente lo voy aplicar con mis alumnos Gracias Mil.

  52. Eduardo García

    Agradezco tu apoyo al proporcionarnos herramientas que facilita nuestro trabajo.

  53. Rafael feliz

    tremendo apoyo no sabia nada de excel mil gracias por la ayuda

  54. Rafael

    Gracias por introducir esta macro (código VBA), para que nosotros nos aprovechemos de ella.

  55. Fátima

    Excelente articulo. Gracias por compartir tus conocimientos.

  56. Jorge Ramirez

    Gracias amigo por toda la informacion que envias, me ha servido de mucho, porque soy un principiante

  57. Jorge

    No esperaba contar por color. Novedoso.
    Gracias

  58. Verónica

    ¡Excelente! En la pc que estoy utilizando funcionó bien, a pesar de tener instalado Excel 2003. Sólo tuve que cambiar la seguridad de macros a través de la siguiente ruta: Herramientas -> Opciones -> Pestaña seguridad -> Botón Seguridad de macros…-> Nivel de seguridad -> Bajo -> Aceptar -> Aceptar Guardar -> Cerrar el programa -> Volví a abrir el archivo y listo. Con la función volátil es con la que no tuve mucha suerte porque no actualiza automáticamente sino que me tengo que meter en la fórmula y teclear enter cuando cambio algo en el rango. Igual un millón de gracias. Saludos

  59. Alfredo Márquez

    EXCELENTE INFORMACIÓN Y ADEMAS PRACTICA,GRACIAS

  60. Julio Obregon

    Es una función que que sirve de mucha ayuda. Excelente muchas gracias por tu valioso aporte.

  61. Doris Bedoya

    Hago cosas de la oficina en excel, aunque no muy avanzadas y no imagine que fuera tan facil. Gracias

  62. JOSE MANUEL AGNDIS

    Buen trabajo y aportación estimado Moisés.

  63. Edgardo

    Gracias por tu invalorable aportes de tus conocimientos, lo bueno de que aclaras nuestras dudas ¡¡¡ Muy bueno !!! Saludos

  64. ARLEX FALLA

    Muchas gracias por los datos, son excelentes.

  65. Juan Carlos S

    Moises sos un campeon de Excel. Dime si existe alguna manera de que la macro se ajsute para que funcione con formatos condicionales. Seria muy pero muy util. Yo intente hacerlo colocand las mismas reglas condicoinales tanto al rango como a la celda de referencia y la formula cuenta todos los datos del rango.

    1. Moises Ortiz Autor

      Juan, el cambio es simple pero no es obvio. No puedo explicarte el detalle de la macro en este comentario, pero puedo decirte que el color de formato condicional se almacena en la propiedad Interior.Color de la colección FormatConditions de un objeto Range: FormatConditions(i).Interior.Color
      Debes recorrer dicha colección para conocer el color de formato condicional de una celda.

  66. Antonio

    Se me hace un excelente herramienta de trabajo, así como todas las publicaciones que nos hacen llegar

  67. Martin Fierro

    Excelente curso Moises, gracias por los tips que son útiles.

  68. miguel

    Moisés
    Excelente como siempre y UTILISIMO!!!! pues permite contar en planillas que otros hicieron visualmente y nosotros queremos los casos en números.

  69. Gerardo

    Excelente trabajo el que haces con éste sitio, es muy útil para los que creemos que sabemos excel. Gracias y Felicidades

  70. Patricia Gabriela Aguilera

    Felicitaciones por todos sus tutoriales. Son excelentes!
    Adicionalmente es muy halagador ver que lee cada uno de los comentarios y da respuesta a los mismos!… Por personas como Ud es que uno no deja de enamorarse cada dia mas de excel.

  71. Isaías

    Super excelente, el sitio es de mucha ayuda

  72. byron victor

    excelente para los trabajos en excel

  73. Jorge

    Muchas gracias por todo Moíses.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Solo se aceptarán comentarios relacionados con el artículo publicado.