Listas desplegables de varios niveles en Excel

Las listas desplegables de Excel son una herramienta muy sencilla de utilizar que nos ayuda a completar datos en Excel, puedes hacer esta herramienta mucho más potente creando listas desplegables dependientes de varios niveles en Excel. Si quieres aprender cómo hacer una lista desplegable en Excel que dependa de otra continúa leyendo.

https://youtu.be/cAszRyT5sWE



Ayúdanos a seguir creando contenido gratuito con una donación




Ordena los datos de los niveles

Una de las tareas más importante en la creación de listas desplegables dependientes y que te puede ahorrar mucho tiempo es ordenar correctamente los datos a partir de los que crearás las listas desplegables.

En este caso vamos a crear listas desplegables de varios niveles, es decir que el segundo desplegable dependerá del valor seleccionado en el primer desplegable.

Escribe en una misma fila los valores que aparecerán en el primer desplegable y debajo de cada uno de esos valores coloca los valores que deben aparecer en el segundo desplegable si esa opción ha sido la elegida en el primer desplegable.

listas desplegables de varios niveles

Crea tablas para las listas desplegables

El siguiente paso es convertir cada uno de los rangos de datos en tablas.

Para ello selecciona el rango con el ratón, incluido su encabezado. En la pestaña Insertar y en la parte izquierda selecciona la opción Tabla. También puedes utilizar el atajo de teclado CTRL+Q para crear la tabla.

Aparecerá una pequeña ventana emergente en la que debes habilitar la opción “La tabla tiene encabezados”. Pulsa Aceptar.

Automáticamente se mostrarán las herramientas de tabla en la cinta de opciones. En caso de que no se muestre, clica con el ratón en alguna celda de la tabla y seguidamente selecciona la pestaña Diseño.

En la cinta de opciones de herramientas de tabla, en la parte izquierda cambia el nombre de la tabla (por defecto aparece Tabla1) y asígnale el nombre del encabezado de la tabla que has creado. En nuestro primer ejemplo le asignaremos el nombre Ropa.

❗💡 Debes tener en cuenta que tanto para los nombres de Tablas como para los nombres de rangos de celdas no se pueden utilizar espacios. Por lo que si uno de tus valores de los dependerá un segundo desplegable los tiene una opción es utilizar guiones bajos. Por ejemplo, Gafas_de_sol.

Haz lo mismo con el resto de tablas.

Si quieres aprender más sobre las Tablas de Excel y sus ventajas visita el tutorial: Tablas en Excel y sus ventajas

Aplicar nombre a un rango de celdas

Otra opción es aplicar el nombre del encabezado a un rango de celdas. En ese caso solo tienes que seleccionar los valores de cada grupo del segundo desplegable y en el cuadro de nombres aplicarle el nombre del primer nivel al que pertenece.

❗💡 La ventaja de utilizar las tablas en lugar de los nombres para un rango de celdas es que al añadir nuevos valores en esos rangos no se actualizan automáticamente en el desplegable y tendrías que volver a definirlo. En cambio, utilizando Tablas de Excel los cambios se reflejan de forma automática en el desplegable.

Comprobar que los nombres son correctos

Es muy importante que los nombres asignados a las tables coincidan exactamente con los del primer nivel para que el desplegable funcione correctamente.

Puedes comprobar los nombres en la pestaña Formulas → Administrador de nombres.

Se abrirá una ventana en la que se muestran los nombres asignados tanto a Tablas como a rangos de celdas en ese libro de Excel.

Puedes modificar cualquier nombre desde esa misma tabla clicando sobre el botón Modificar.

Listas desplegables primer nivel

Como vas a comprobar la parte más sencilla es insertar los desplegables en Excel.

Clica con el ratón sobre la celda donde quieres insertar el primer desplegable. En la pestaña Datos clica en la opción Validación de datos.

En la ventana emergente que aparece clica en el botón del primer desplegable y elige la opción Lista.

En el apartado Origen clica en botón de la derecha y selecciona con el ratón solo los encabezados de las tres tablas que has creado.

Haz clic en Aceptar y comprueba que el primer desplegable funciona correctamente.

Listas desplegables de varios niveles

Para conseguir que tu segundo desplegable dependa del valor seleccionado en el primer desplegable tienes que utilizar la función INDIRECTO.

Función INDIRECTO | Devuelve una referencia especificada por un valor de texto.

Clica con el ratón sobre la celda donde quieres insertar el segundo desplegable. En la pestaña Datos clica en la opción Validación de datos.

Elige la opción lista en el desplegable de Permitir y en el campo Origen escribe =INDIRECTO(  y selecciona la celda donde insertaste el primer desplegable. A continuación, cierra el paréntesis.

En el ejemplo: =INDIRECTO($G$2)

La celda G2 es donde se encuentra el primer desplegable.

Haz clic en Aceptar y comprueba que los valores del segundo desplegable cambian en función del valor seleccionado en el primer desplegable.

Listas desplegables de varios niveles: 3 o más.

Si quieres construir desplegables de 3 o más niveles solo tienes que crear un nuevo nivel de tablas donde los encabezados deben coincidir con el nivel inmediatamente superior del que dependen. Aplicar correctamente los nombres.

En el momento de crear el desplegable a partir del segundo nivel siempre debes utilizar la función INDIRECTO como en el punto anterior y en Origen seleccionar la celda del desplegable inmediatamente anterior.

Limpiar automáticamente los desplegables al cambiar el primer nivel

Por último vamos a ver cómo hacer que tus listas desplegables de varios niveles se vean más profesionales haciendo que al cambiar el valor del primer desplegable automáticamente se eliminen los valores del resto de desplegables de la búsqueda anterior.

Utilizaremos una macro muy sencilla que solo tienes que pegar en tu hoja. Este es el código:

Mostrar la pestaña Programador

Para utilizar las macros de Excel necesitas tener visible la pestaña Programador dentro de tu cinta de opciones.

Si no visualizas esta pestaña dentro de tu cinta de opciones debes activarla primero. Para ello, clica con el botón derecho del ratón sobre cualquiera de las pestañas y en el desplegable elige la opción Personalizar cinta de opciones.

En el recuadro de la derecha habilita la opción Programador o Desarrollador y pulsa Aceptar.

Comprueba que la pestaña se ha incluido en tu cinta de opciones.

Aplica la macro

Para crear la macro:

Ve a la pestaña Programador →clica sobre la opción Visual Basic.

En la barra izquierda clica con el botón derecho del ratón sobre la hoja donde quieres que se ejecute y elige la opción Ver código.

En la hoja que se ha abierto a la derecha pega el siguiente código:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$I$3" Then
    Range("J3:K3") = ""
    End If
End Sub

Básicamente lo que hace este código es valuar si la celda donde has insertado el primer desplegable cambia. En caso de que cambie hace que la celda del siguiente desplegable quede vacía.

En el ejemplo:

  • El primer desplegable está en la celda I3. Para personalizar el código para una hoja nueva solo tienes que cambiar el valor $I$3 por el nombre de tu celda.
  • El segundo y tercer desplegable están en las celdas J3 y K3. Por lo que deberás cambiar el rango J3:K3 por el que corresponda. Si solo tienes un segundo desplegable escribe únicamente el nombre de la celda. Ejemplo (“J3”).

Guarda el libro habilitado para Macros

Para que tu listas desplegables de varios niveles funcionen con la macro debes guardar correctamente el libro de Excel.

❗💡 Recuerda que si utilizas la macro debes guardar el libro de Excel como un libro habilitado para macros. En el momento de guardar despliega en tipos y escoge la opción “Libro habilitado para macros”.

Plantilla descargable

Puedes descargarte la plantilla del ejercicio aquí:

Esta plantilla es un recurso creado por Saber Programas y su uso está sujeto a derecho de autor y propiedad intelectual.