Listas desplegables en Excel

Muchos usuarios de Excel que apenas empiezan a usar esta aplicación me preguntan cómo pueden crear listas desplegables en celdas. Siempre se sorprenden de lo fácil que es y hoy veremos cómo hacerlo, incluyendo alguna variante interesante.

Cuando utilizarlas

En ocasiones, luego de crear una hoja de cálculo, nos vemos en la necesidad de restringir las entradas de los usuarios. Esto para que se mantenga su funcionalidad dentro de los parámetros que hayamos establecido o simplemente para evitar los errores que se comenten con frecuencia durante la entrada manual de datos.

Una de las formas comunes de restringir dichas entradas es mediante el uso de listas desplegables. Estas nos permiten establecer un listado de valores, predeterminado por el diseñador de la hoja, para que los usuarios simplemente seleccionen un valor entre las posibles opciones. Veamos como funciona.

Lista desplegable en Excel Office 365

En el ejemplo vemos que en la celda A5 tenemos una lista desplegable con los países que más visitan este blog. Lograr esto será nuestro objetivo.

Cómo crearlas

Es muy sencillo, es una de las opciones para validar datos incluida por defecto en Excel. Accederemos a dicha opción desde la pestaña Datos, sección Herramientas de datos, y finalmente abrir el cuadro de diálogo «Validación de datos…» (Alt ,D, VV, V).

Como crear listas desplegables en Excel Office 365

Una vez que estemos en dicho cuadro de diálogo, en la primera pestaña Configuración / Criterio de validación, elegiremos bajo «Permitir» la opción «Lista». Seguidamente debemos seleccionar el origen de nuestra lista, el cual no será más que un simple rango dentro de nuestro libro de trabajo. El listado origen puede estar en cualquier hoja de nuestro libro.

Noten que en el ejemplo anterior utilicé una lista en la misma hoja para efectos ilustrativos, pero mi preferencia y recomendación es colocar todas las validaciones en una hoja aparte de nuestros datos. En dicha hoja deberán estar todos nuestros listados origen.

Lista desplegable actualizable

Sobre nuestro ejemplo anterior podemos realizar una mejora muy sencilla que consiste en crear una lista desplegable que se actualice automáticamente apenas agreguemos nuevos datos a nuestra lista origen.

Podemos hacerlo de distintas formas, pero hoy veremos cómo lograrlo al convertir nuestra lista origen en una Tabla. Como sabemos, las tablas de Excel se expanden automáticamente al incluir nuevos datos, esto significa que podemos valernos de dicha funcionalidad y en lugar de seleccionar un rango fijo como en nuestro ejemplo inicial, podemos hacer referencia a dicha tabla (y cualquiera de sus columnas) para obtener los valores de nuestra lista desplegable.

Crear lista desplegable actualizable en Excel Office 365

Como observamos, para que la lista origen sea reconocida correctamente utilizando la referencia estructurada de la tabla, debemos utilizar la función INDIRECTO. Si desconoces esta función, te recomiendo sigas el vínculo anterior bajo su nombre para que te enteres cómo funciona.

Lista desplegable dependiente

En esta variante tendremos una segunda lista cuyo valor dependerá de la selección realizada por el usuario en una lista principal. Veamos cómo funciona un pequeño ejemplo lo que queremos lograr.

Listas desplegables dependientes en Excel Office 365

En nuestro ejemplo, una lista desplegable de Provincias, Departamentos o Estados depende de la selección realizada en la lista desplegable de Países. Al cambiar la selección del país, el campo Prov./ Dpto / Edo se reinicia para evitar que exista una selección de país con provincia incorrecta. Para lograr esto utilizaremos nombres de rango y un poco de código VBA.

Los pasos para completar la tarea son los siguientes:

  • Crear listas dependientes para cada elemento de la lista principal.
  • Nombrar cada lista dependiente de forma exacta a su elemento correspondiente en la lista principal.
  • La lista principal será creada de la forma que ya vimos arriba.
  • La lista dependiente se creará con una referencia a la celda que incluye la primera lista (utilizando la función INDIRECTO).
  • Se incluirá código VBA que se ejecutará al ocurrir el evento «Worksheet_Change».

Como podemos observar en la imagen debajo, para completar el primer paso he creado una matriz con todos los valores que necesitaré en una segunda hoja que he llamado «Validación». Los países de la lista principal como encabezados de columna y todos los elementos dependientes como valores.

Paso 1 Lista desplegable dependiente en Excel

Para el segundo paso, notemos en la imagen anterior que cada listado de valores dependientes ha sido nombrado de forma exacta al elemento de la lista principal o encabezado de columna.

En el tercer paso, crearemos una lista principal de la forma usual ya vista al inicio. Como vemos en la imagen debajo, mi preferencia es darle un nombre al rango conformado por los encabezados de mi columna de validación. De esta forma, sólo tengo que hacer referencia a su nombre y ya tendré los valores disponibles para mi lista desplegable. Para nombres de rango, en este caso, no es necesario utilizar la función INDIRECTO.

Paso 2 Lista Desplegable en Excel Office 365

Posteriormente, crearé la lista dependiente siguiendo los pasos usuales y con la función INDIRECTO crearé una referencia a la celda que ya tiene como validación mi lista principal.

Paso 3 Lista desplegable dependiente Excel 2016 Office 365

Hasta aquí ya prácticamente está listo. Para finalizar, sólo falta incorporar la funcionalidad de reinicio en la lista dependiente para cada cambio en la lista principal. Hay varias formas de hacer esto, pero la que aconsejo y mi preferencia es utilizar el siguiente código:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("A5")) Is Nothing Then
          Range("C5").ClearContents
    End If
End Sub

En caso de que no entiendan el código porque no están familiarizados con la programación de eventos en VBA, no se preocupen, no es un concepto difícil de aprender. Con VBA podemos crear código que responda a eventos de la hoja como cambiar la selección, activar y desactivar hojas, entre muchos otros. Esto es lo que he aplicado para completar esta parte de nuestra tarea. Observen que en este caso el evento es Worksheet_Change, el cual no es otro que modificar la hoja.

El código anterior deben incorporarlo a la hoja correspondiente siguiendo los pasos descritos por la siguiente imagen:

Paso 5 Lista desplegable dependiente Excel 2016 Office 365

Y ya con esto hemos completado la lista desplegable dependiente. Estos mismos pasos podemos aplicarlos para varias listas dependientes o listas dependientes de más de un nivel, ahora es cuestión de cada uno adaptar esto a su caso particular. Vale notar que el archivo debe ser guardado con extensión «.xlsm», de forma que podamos guardar el código VBA que hemos incluido.

Bien, entonces para resumir, hoy hemos visto:

  • Cómo crear listas desplegables simples.
  • Cómo crear listas desplegables actualizables automáticamente.
  • Cómo crear listas desplegables dependientes.

Espero que les haya sido útil y puedan aplicarlo en sus tareas diarias con Excel. Si tienen alguna duda adicional relacionada con el tema de esta entrada, pueden dejarla en los comentarios y con todo gusto les responderé. Hasta la próxima entrada.

10 Comments

  1. Hola como podría realizar el tercer nivel de una lista Desplegable dependiente de dos listas anteriores en la que en el segundo nivel hay nombres que se repiten.
    Muchas gracias
    A ver si me podéis ayudar

    Responder

    1. A ver si he captado la idea correctamente. En el segundo nivel tenéis nombre repetidos respecto al nivel anterior?

      Responder

        1. Perfecto. En este caso veo una opción sencilla de implementar. Necesitas crear un segundo rango (tu rango «repetido»), pero esta vez incluirás algo adicional a su nombre. Digamos, tendrás un “Rango1” como tu rango principal y un “Rango12” como tu rango “repetido”. Ese “2” al final deberá ser agregado a la función INDIRECTO cuando crees la tercera lista. Por ejemplo, si el rango a tu referencia principal “Rango1” está en B2, entonces la función de validación de datos para la tercera lista sería:

          =INDIRECTO(B2&”2”).

          Tu nueva lista verá el siguiente resultado: =INDIRECTO(“Rango12”), haciendo referencia así a tu rango repetido.

          Me ha parecido muy interesante tu duda, así que pronto actualizaré la entrada para incluir esta sugerencia.

          Saludos!

          Responder

          1. Muchas gracias Carlos esa es una opción pero quería utilizar la misma fórmula que he utilizado para el Rango 1 que es un desref con coincidir y buscar.
            Lo único que no consigo que me traiga los datos condicionados a dos ítems, debe ser algún error que estoy haciendo en la fórmula.

          2. Nos alegra ayudar y ver qué tienes entre manos una solución distinta, pero si todavía no lo has logrado, también puedes compartir algún ejemplo para mirar en detalle lo que quieres lograr y quizás así podamos ayudarte mejor.

  2. hola. una lista desplegable. En Origen un rango. Cuando abro el desplegable de la lista, siempre va al valor blanco porque la celda esta a blanco. Como hacer para que vaya al primer valor de la lista.

    Responder

    1. Para este caso la solución es algo más compleja y extensa de explicar en un comentario, pero te daré una pista. Vas a necesitar crear un segundo rango que “elimine” los espacios vacíos de forma dinámica. Intentaré incluir la solución en este artículo o uno nuevo.

      Responder

  3. Enhorabuena por el impulso de ayudar a otras personas a solucionar sus problemas. Me reconozco en ese impulso. ¡Ánimo!

    Responder

Responder a carloosnCancelar respuesta

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.