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.
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.
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 SubBá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.
Si la guardo como habilitada para macros, entonces no puedo usarla como plantilla?
exelente tutorial muchas gracias una consulta podrias ayudarme a confeccionar un arbol genealogico de 5 generaciones empezando por los padres y terminando en los tataranietos donde se puda poner los conyugues y en caso se tenga mas de un conyugue tambien se pueda poner el otro conyugue y los hijos que se tuvo con este y que esto se pueda reflejar en un grafico que se genere automaticamente si se agregan mas datos mucha gracias
Excelente explicacion, justo lo que andaba buscando. Muchas Gracias
Hola
Muchas gracias por el tutorial, esta muy claro
Queria ademas saber como extender la lista desplegable para visualizarla completa
Buenos días
Me podrías dar la clave para desproteger el libro?
Tengo Excel 2016, al crear la lista despegable no me reconoce el nombre de la tabla, me aparece error
Muchas gracias por el tutorial, pero tengo una pregunta, como hacemos cuando tenemos termiando las listas de desplegables para que funcionen como una base o hija exel???? porque solo me deja cambiar la primera fila cuando añadimos filas solo me deja camiar el tipo pero el resto se queda como la primera columna.
Hola gracias por el tutorial, esta genial, me sirvió para lo que estaba buscando, solo tengo una situación, es referente a la macro, solo aplica para la una celda y una dependiente, no se que debo hacer para que aplique a toda la columna, me pueden ayudar?
Ya lo resolví, Aun así Gracias
Hola… cómo lo resolviste?. Gracias
Buenas tardes, disculpen como podria utilizar el macro que indican en toda una columna, para que cuando vaya añadiendo datos pueda ir modificándose las columnas dependientes de la primera, muchas gracias
Hola y muchas gracias por el excelente tutorial, muy útil!
Solo queria preguntar como puedo modificar la macro en el caso de que las listas desplegables las use en una columna de una tabla, es decir, la macro, antes de borrar las celdas siguientes, debe idenfificar en cual celda se encuentra el cursor para saber cuales celdas borrar.
Muchas gracias
Buena noche gracias por la explicación escrita y visual; descargue la plantilla para seguir los pasos pero esta protegida y no pude continuar…
Hola, primero agradecer este tutotrial. Segundo, tengo un problema respecto a la macro. Quiero aplicar la macro a un lista desplegable de ciudad y comuna. El problema surge cuando quiero ampliar la macro a toda lista, una especie de aplicar el formato a la columna entera, en el ejemplo que tu das seria aplicar la macro a toda la columna «I» cuando se cambia la ciudad, se borren los valores de la columna «J», según corresponda a cada fila.
De antemano agradezco cualquier información ya que soy completamente ignorante respecto al uso de macros.
Buena tarde!
Pudiste solucionar esto? yo tambien me pasa lo mismo
se soluciona al generar la segunda lista desplegable no fijando la fila ejemplo.
lista deplegable 1: seleccionas los titulos.
lista desplegable 2: =indirecto($B2) de esta manera queda fija la columna b y pero se cambia si se cambia de fila)
te adjunto el codigo de VB
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AffectedRange As Range
Set AffectedRange = Intersect(Target, Range(«A:A»))
If Not AffectedRange Is Nothing Then
Dim cell As Range
For Each cell In AffectedRange
Dim rowNumber As Long
rowNumber = cell.Row
Range(«B» & rowNumber).ClearContents
Next cell
End If
End Sub