Actualizar una lista desplegable automáticamente en Excel

Cómo actualizar automáticamente una lista desplegable en Excel

Cuando creamos una lista desplegable en Excel, definimos un rango de celdas como el origen de los datos, y cada vez que necesitamos agregar o quitar elementos a la lista debemos modificar la referencia de dicho rango.

Esto hace que la actualización de los elementos de una lista sea un proceso manual que puede quitarnos tiempo. Sin embargo, existen un par de técnicas que podemos utilizar para automatizar la actualización de los elementos de una lista desplegable en Excel.

Descargar libro de trabajo

Lista desplegable basada en una tabla

El primer método, y el más simple de todos, es que nuestros datos de origen estén en una tabla. De esta manera, Excel se encargará de actualizar automáticamente los elementos de la lista desplegable. A continuación, muestro los pasos para crear este tipo de lista:

  1. Una vez que has ingresado los datos de origen dentro de una hoja, deberás seleccionarlos y pulsar el comando Insertar > Tabla.
    Actualizar lista desplegable en Excel automáticamente
  2. Excel identificará el rango de datos y mostrará un cuadro de diálogo para confirmar la creación de la Tabla. Deberás asegurarte de que Excel haya detectado adecuadamente el encabezado de los datos y pulsar Aceptar.
    Cómo actualizar automáticamente una lista desplegable en Excel
  3. Ahora que la tabla ha sido creada, deberás seleccionar la celda, o celdas, donde se creará la lista desplegable e ir a la pestaña Datos > Herramientas de datos > Validación de datos.
    Modificar o actualizar listas desplegables en Excel
  4. En el cuadro de diálogo Validación de datos, deberás elegir la opción Lista y posteriormente hacer clic en el botón de selección de rango.
    Lista desplegable con actualización automática en Excel
  5. En seguida deberás seleccionar los elementos de la tabla recién creada, sin incluir el encabezado de columna.
    Celda con lista desplegable automática
  6. Deberás terminar la selección del rango y hacer clic en el botón Aceptar para crear la lista desplegable.
    Lista desplegable automática en Excel
  7. Al agregar un nuevo elemento en la tabla, Excel actualizará automáticamente los elementos de la lista desplegable.
    Agregar o quitar elementos automáticamente de una lista desplegable

Aunque el método descrito anteriormente es el más sencillo para crear una lista desplegable basada en una tabla, tiene una restricción, y es que la tabla debe estar en la misma hoja donde se encuentra la lista desplegable.

Si queremos olvidarnos de las restricciones y evitar cualquier problema con la actualización de la lista, puedes seguir los mismos pasos anteriores y crear la lista utilizando la siguiente fórmula:

=INDIRECTO("Tabla1[Categorías]")

La función INDIRECTO se encargará de traducir la cadena de texto en una referencia hacia la columna Categorías de la Tabla1 y de esta manera la lista quedará vinculada a la tabla de nuestro ejemplo. Recuerda que al crear tu lista deberás indicar el nombre de tu tabla y columna. La siguiente imagen muestra la ubicación exacta donde debemos colocar esta fórmula:

Lista desplegable con ajuste automático en Excel

Aunque esta segunda alternativa puede tomarte algunos segundos adicionales para ingresar la fórmula, funcionará correctamente sin importar la hoja donde se encuentre la tabla. Al implementar adecuadamente cualquiera de los dos métodos anteriores, la lista desplegable se actualizará automáticamente al momento de agregar o remover filas en la tabla.

Lista desplegable basada en una fórmula

Las tablas de Excel fueron introducidas hasta la versión 2007, así que la solución que revisamos en la sección anterior no era posible en las primeras versiones de la hoja de cálculo.

Por esa razón, encontrarás varias publicaciones que hablan sobre la solución que se utilizaba antes de que existieran las tablas y que está basada en una fórmula que utiliza las funciones CONTARA y DESREF de la siguiente manera:

=DESREF(F2, 0, 0, CONTARA(F:F)-1)

El objetivo de la función CONTARA es devolver la cantidad de celdas que no están vacías en la columna F. De esta manera, si agregamos un nuevo elemento a la columna F, la función CONTARA incrementará automáticamente el número de celdas contadas.

En esta fórmula he restado el valor 1 al resultado de la función CONTARA porque necesito descontar la celda ocupada por el encabezado de columna. En caso de que no tengas un encabezado, deberás remover dicha resta.

La función DESREF se encarga de crear una referencia al rango que comenzará en la celda F2 y que incluirá el número de filas devuelto por la función CONTARA menos 1. Esta fórmula la colocamos como el origen de la lista desplegable:

Lista desplegable con rango dinámico

De esta manera, la lista se actualizará cada vez que se agrega o se quita un elemento en la columna F. Debido al comportamiento de la función CONTARA, es indispensable que toda la columna donde se encuentran los elementos de la lista sea utilizada solamente para almacenar dicha información.

68 comentarios en “Actualizar una lista desplegable automáticamente en Excel

  1. luz

    Complementario a la creación de listas desplegables muy importante.

  2. Leonor Guzmán Gómez

    Son muy interesantes y se me hacen prácticos las actividades ojala pueda aprender mucho agradecida

  3. BRAYAM SUAREZ

    Excelente le aplixare en mi lugar de trabajo me sirve mucho

  4. Ricardo A Suarez

    Excelente forma de enseñar excel, muy preciso, directo al grano. gracias!

  5. yeSalgad0

    Excelente, mi estimado. Gracias por compartir sus conocimientos.

  6. Alfredo de Larroque

    Muchas gracias por compartir tus conocimientos! Ayudan mucho. Saludos cordiales.

  7. karla

    Moises muchas gracias, es de gran apoyo.

    Saludos

  8. Hernan Parada

    Muy buena opción. Se puede hacer algo similar para los rangos de gráficos?

    1. Moises Ortiz Autor

      Si, cuando creas un gráfico basado en una tabla se actualizará automáticamente.

  9. HECTOR

    Que buena info. Gracias por el aporte.

  10. Francisco

    muy grande su ayuda. muy buena información

  11. Mario

    Muchas gracias, Sr. Ortiz. He aprendido mucho con Ud.

  12. José A. Farfán

    Muy buena explicación. Muy útil. Muchas gracias. Jose A. Farfán

  13. Jorge luis

    Excelente información moy lo aplicaré de inmediato en mi próximo. Trabajo, un fuerte abrazo

  14. Fernando Cortez

    Exelente aporte, normalmente yo amplio el nombre del rango y como uso listas dependientes agregue codigo VBA para que limpie las sublistas

  15. fayote

    Excelente me sirve de mucho de hecho ya la estoy utilizando gracias

  16. Pablo Torre

    Gracias muy didactico y util, los felicito.

  17. Mauricio Ruggieri

    Exelente aporte, me ayudado en mis planillas, yo las tenia manuales no automática. Muchas Gracias

  18. PEDRO FIGUEREDO

    ESPECTACULAR. ME PROVECHOSO ES LA INDICACION. EXITOS.

  19. Claudio

    Excelente metodología y muy clara la explicación…gracias por compartir sus conocimientos!

  20. Manuel Parra clifton

    Que buena buena explicación agradecido, seguramente de vez en cuando tendremos sorpresas agradables y de gran utilidad.

  21. Ángel BC

    Muy buenos aportes, conocía DESREF para con menús desplegables apuntar a un rango u otro (por ejemplo para mostrar un gráfico, pudiendo elegir la visualización en columnas o en líneas).
    ¿sería posible que se actualizara la lista desplegable pero en función de otra variable? Es decir, imaginemos que tenemos una lista con X ciudades de España y Francia y un desplegable previo en el que se elige el país, de modo que en el segundo desplegable sólo se mostrarían las ciudades del país escogido en la primera lista desplegable.
    Muchas gracias de antemano y saludos,

  22. Jose Alfredo Flores Luna

    La formula =DESREF(F2,0,0,CONTARA(F:F)-1) para mi fue necesaria porque mi lista desplegable pertenece a otra hoja con nombre Totales y a una tabla que TIENE OTRAS COLUMNAS, entonces mi formula quedo así: =DESREF(Totales!B$3,0,0,CONTARA(Totales!B:B)-1)
    Mil gracias Moisés.

  23. Luis Alberto

    Moises, muchas gracias por compartir sus conocimientos, este tema es muy importante en mi trabajo, lo aplicare inmediatamente.

  24. Lisandro

    Muy bien. Siempre bien didáctico.

  25. José

    Te felicito Moisés; no solo es erudición sino también capacidad didáctica!

  26. Jacinto

    Muy buena aplicable y sencillo

  27. Susana

    Tus artículos me parecen muy útiles, nos ahorran montones de tiempo. Muchas Gracias por compartir.

  28. Manuel Ndombel

    Muchas gracias por compartir tus conocimientos. Saludos cordiales

  29. Juan Gimeno

    Muy bien explicado. Enhorabuena por la idea de enviar estos consejos vienen de fábula.

  30. Belzart Gestab

    Estimado, seria muy bueno que explicaras como construir listas desplegables mas dinámicas con activex por ejemplo. Que suceda como ocurre con los filtros de excel por ejemplo, que te permite tildar algunos componentes de la lista, etc. He intentado hacerlo pero no me quedaron bien. Saludos,

  31. Adriana Aceves

    Gracias por compartir algo tan valioso como sus conocimientos.

  32. Erick

    Excelente aportación, muy bien explicada. Gracias por compartir tus conocimientos.

  33. TITO

    HOLA BUEN DÍA ES MUY BUENA EL APORTE DE ESTA FORMULA =DESREF(F2, 0, 0, CONTARA(F:F)-1) SOLO QUE PRESENTA UN DETALLE EN LA LISTA CON -1 HACE DESCUENTO EL ULTIMO ÍTEM DE LA LISTA MAS NO QUE DEBERÍA SER LA PRIMERA QUE ES LA CATEGORÍA QUE SE QUIERE QUE SE DESCUENTE.
    SALUDOS

    1. Moises Ortiz Autor

      Tito, la resta -1 no es para descartar el “primero” o el “ultimo” elemento, sino para disminuir la cantidad total de elementos de la columna F.
      Si en el ejemplo, la columna F no tuviera encabezado, la fórmula a utilizar sería la siguiente:
      =DESREF(F1, 0, 0, CONTARA(F:F))

      Observa que no solo removí la resta, sino también la referencia indicada en el primer argumento de la función DESREF. Espero que esto te ayude a entender mejor la lógica de la fórmula para adaptarla a tus datos.

  34. veronica

    me ha sido de mucha utilidad, mil gracias

  35. marco vásquez

    una vez más mil gracias por tan buen aporte

  36. salvador

    Gracias excelente información y muy útil para emplear y combinar con otras funciones

  37. Sergio Rubio

    Excelente tip sobre la lista desplegable, muchas gracias

  38. JORGE E MOLINA S

    EXCELENTE EXPLICACION, MUY UTIL ME SALIO PERFECTO

  39. Ruben Santacruz

    Excelente maestro Moises! Su ayuda siempre a tiempo. Un abrazo.

  40. Verenice Rivero

    Muy bueno lo pondré en practica. Gracias por sus conocimientos.

  41. Luis Fonseca Ruiz

    Excelente ejemplo de validación de datos con lista que se actualiza automáticamente, gracia por el ejemplo

  42. Martín J. Hurtado Fortanell

    Muchas gracias por compartir tus conocimientos Moisés.

  43. Sergio

    Hola Moisés. Gracias por compartir tus conocimientos. Mi consulta es, si se puede dejar un elemento del desplegable para completar. No he logrado hacerlo. Saludos desde Patagonia Argentina.

    1. Moises Ortiz Autor

      Hola Sergio, lo siento, pero eso no es posible. No existe manera de influir en la manera en que se despliega la lista de validación de datos en Excel.
      Una posible alternativa es utilizar otro tipo de lista (ComboBox – ActiveX), pero eso requiere programación VBA y es un tema MUY diferente del que hablo en este artículo.

  44. Nolfi Ceriche

    Muchas gracias, me sirve mucho para aplicarlo en mi trabajo. Excelente

  45. Patricia

    Muchas gracias por la información.

  46. RODOLFO ORJUELA SANCHEZ

    Buen ejemplo y juesto el que estaba buscando, aunque todavía tengo un problema, y se trata de actualizar una lista una segunda lista, a partir de la selección de una primera lista, es decir encadenar 2 listas desplegables.

  47. Medardo Oviedo Plaza

    Como siempre Moisés exclente. Adquirir conocimientos nuevos cada día llena el alma. Muchas gracias. Medardo

  48. Edicson Rodríguez

    Gracias por la información, dicho método lo utilizo cuando debo referenciar varias Tablas en un solo concepto, pero para esto es más sencillo si a la categoría que se va a usar en la Validación de datos se crea un NOMBRE a fin de mantener la selección actualizada, claro está se debe crear la Tabla primero

  49. Ruth

    Gracias por los aportes me sirve mucho en mi trabajo

  50. Alejandro

    Muy valiosa la información compartida, Muchas gracias.

  51. Javier Ortega Sosa

    Excelente video, muchas gracias por compartir

  52. Javier Ortega Sosa

    Tengo una duda y es la siguiente: Como se podría hacer una lista desplegable en donde me mostrara 2 valores por ejemplo: Clave y nombre del producto. Y al momento de elegir un elemento en la celda me colocara la clave del producto. De antemano muchas gracias por sus comentarios así por sus videos me han ayudado bastante.

    1. Moises Ortiz Autor

      Javier, la lista no puede mostrar valores de 2 celdas. Tienes que concatenar los valores en una nueva columna e indicar dicho rango como el origen de datos de la lista. Pero si concatenas los datos, no será posible que la lista extraiga solo una parte. Podrías lograr ese comportamiento con programación VBA, pero ese es un tema que sale por completo del alcance de este artículo e imposible de explicar en este comentario.

  53. Catalina Acosta

    Super didáctico. Eres el Mejor Gracias!

  54. Pedro

    Muy bueno Moises, gracias por compartir tu ayuda

  55. Enrique Ramirez

    Esto amplia como una variable el rango de la búsqueda de la lista desplegable Gracias

  56. RODOLFO ORJUELA SANCHEZ

    Muy bien Moises, fue complicado pero lo logre, porque necesitaba que para la misma lista 1, pudiera seleccionar una tercera lista, a partir de la primera, dejando intacta las segunda lista.

  57. Jesus

    Excelente como siempre. gracias Moisés
    Saludos

  58. Edgardo

    Muchas gracias Moisés por tu aporte, como siempre muy bueno. Saludos

  59. Fernando

    Hola solo faltaría ordenar la labla para que los datos sean fáciles de localizar de otra forma pudiesen quedar revueltos al ir agregando nuevos elementos a la table. Saludos

  60. FELIX ORTEGA

    Excelente y muy buena explicación.. Felicidades…

  61. Jhon

    Muchas gracias, esto me ayuda a seguir simplificando mi trabajo y mejor las hojas de calculo de mis compañeros.

  62. Any

    Buen aporte mil gracias por ayudarnos

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.