Crear una lista desplegable dependiente dinámica en Excel (paso a paso)
En este tutorial, te guiaremos paso a paso para crear una lista desplegable dependiente que muestre opciones basadas en los valores seleccionados en la primera lista desplegable. En otras palabras, crearemos una lista de validación de datos en Excel basada en el valor de otra lista.
Crear una lista desplegable dependiente dinámica
Crear una lista desplegable dependiente en 10 segundos con una herramienta práctica
Crear una lista desplegable dependiente dinámica en Excel 2021, Excel 365 y versiones más recientes
Algunas preguntas que podrías hacer sobre este tutorial
Descarga gratuita del archivo de muestra 
Video: Crear una lista desplegable dependiente en Excel
Crear una lista desplegable dependiente dinámica
Paso 1: Escribe las entradas para las listas desplegables
1. Primero, escribe las entradas que deseas que aparezcan en las listas desplegables, cada lista en una columna separada.
Ten en cuenta que los elementos de la primera columna (Producto) servirán como nombres de Excel para las listas dependientes más adelante. Por ejemplo, aquí Fruta y Verdura serán los Nombres para las columnas B2:B5 y C2:C6 respectivamente.
Ver captura de pantalla:
2. Luego, crea tablas para cada lista de datos.
Selecciona el rango de la columna A1:A3, haz clic en "Insertar" > "Tabla", luego en el cuadro de diálogo Crear Tabla, marca la casilla "Mi tabla tiene encabezados". Haz clic en "Aceptar".
Luego repite este paso para crear tablas para las otras dos listas.
Puedes ver todas las tablas y las referencias a rangos en el Administrador de nombres (presiona "Ctrl" + "F3" para abrirlo).
Paso 2: Crear nombres de rango
En este paso, necesitas crear "Nombres" para la lista principal y cada lista dependiente.
1. Selecciona los elementos que aparecen en la lista principal ("A2:A3").
2. Luego ve al "Cuadro de nombres" que está junto a la "Barra de fórmulas".
3. Escribe el nombre en él, aquí lo nombramos como "Producto".
4. Presiona la tecla "Enter" para completar.
Luego repite los pasos anteriores para crear Nombres por separado para cada lista dependiente.
Aquí se nombra la segunda columna (B2:B5) como Fruta, y la tercera columna (C2:C6) como Verdura.
Puedes ver todos los nombres de rango en el Administrador de nombres (presiona "Ctrl" + "F3" para abrirlo).
Paso 3: Agregar la lista desplegable principal
A continuación, agrega la lista desplegable principal (Producto), que es una lista desplegable de validación de datos normal, no una lista desplegable dependiente.
1. Primero, crea una tabla.
Selecciona una celda ("E1"), y escribe el encabezado de la primera columna ("Producto"), y pasa a la siguiente celda de columna ("F1"), escribe el encabezado de la segunda columna ("Ítem"). Esta tabla contendrá las listas desplegables.
Luego selecciona estos dos encabezados ("E1" y "F1"), haz clic en la pestaña "Insertar", y selecciona "Tabla" en el grupo Tablas.
En el cuadro de diálogo Crear Tabla, marca la casilla "Mi tabla tiene encabezados" y haz clic en "Aceptar".
2. Selecciona la celda "E2" donde deseas insertar la lista desplegable principal, haz clic en la pestaña "Datos" y ve al grupo "Herramientas de datos" para hacer clic en "Validación de datos" > "Validación de datos".
3. En el cuadro de diálogo Validación de datos,
- Elige "Lista" en la sección "Permitir",
- Escribe la siguiente fórmula en la barra "Fuente", Producto es el Nombre de la lista principal,
- Haz clic en "Aceptar".
=Product
Puedes ver que se ha creado la lista desplegable principal.
Paso 4: Agregar la lista desplegable dependiente
1. Selecciona la celda "F2" donde deseas agregar la lista desplegable dependiente, haz clic en la pestaña "Datos", y ve al grupo Herramientas de datos para hacer clic en "Validación de datos" > "Validación de datos".
2. En el cuadro de diálogo Validación de datos,
- Elige "Lista" en la sección "Permitir",
- Escribe la siguiente fórmula en la barra "Fuente", E2 es la celda que contiene la lista desplegable principal.
- Haz clic en "Aceptar".
=INDIRECT(SUBSTITUTE(E2," ","_"))
Si E2 está vacío (no has seleccionado ningún elemento en la lista desplegable principal), verás un mensaje emergente como el siguiente, haz clic en "Sí" para continuar.
Ahora se ha creado la lista desplegable dependiente.
Paso 5: Probar la lista desplegable dependiente.
1. Selecciona "Fruta" en la lista desplegable principal ("E2"), luego ve a la lista desplegable dependiente ("F2") y haz clic en el ícono de flecha, verifica si los elementos de frutas están en la lista, luego selecciona un elemento de la lista desplegable dependiente.
2. Presiona la tecla "Tab" para iniciar una nueva fila en la tabla de entrada de datos, selecciona "Verdura", y pasa a la siguiente celda a la derecha, verifica si los elementos de verduras están en la lista, luego selecciona un elemento de la lista desplegable dependiente.
- Si no hay ningún elemento seleccionado en la lista desplegable principal (columna Producto), la lista desplegable dependiente (columna Ítem) no funcionará.
- Si deseas restablecer o borrar el contenido de la lista desplegable dependiente después de cambiar la selección, consulta este artículo How to clear dependent drop down list cell after selecting changed in Excel?, que introduce un código VBA para ayudarte.
- Si deseas crear una lista desplegable de 3 niveles, este artículo te ayudará: How to create multi level dependent drop down list in Excel?.
Crear una lista desplegable dependiente en 10 segundos con una herramienta práctica
"Kutools for Excel" proporciona una herramienta poderosa para crear una lista desplegable dependiente de manera más fácil y rápida:
Paso 1: Escribe las entradas para la lista desplegable
Primero, organiza tus datos como se muestra en la siguiente captura de pantalla:
Paso 2: Aplicando la herramienta Kutools
1. Selecciona los datos que has creado, haz clic en la pestaña "Kutools", y haz clic en "Lista desplegable" para mostrar el submenú, haz clic en "Lista desplegable dinámica".
2. En la "Lista desplegable dependiente":
- Marca el "Modo B" que coincida con tu modo de datos,
- Selecciona el "rango de salida", el número de columnas del rango de salida debe ser igual al número de columnas del rango de datos,
- Haz clic en "Aceptar".
Ahora se ha creado la lista desplegable dependiente.
- "Modo B" admite la creación de un tercer nivel o más en una lista desplegable:
- Si tus datos están organizados como se muestra en la siguiente captura de pantalla, necesitas usar "Modo A", que solo admite la creación de una lista desplegable dependiente de 2 niveles.
- Para más detalles sobre cómo usar Kutools para crear una lista desplegable dependiente, por favor visita este tutorial.
Crear una lista desplegable dependiente dinámica en Excel 2021, Excel 365 y versiones más recientes
Si estás usando Excel 365, Excel 2021 o versiones más recientes, hay otra forma de crear rápidamente una lista desplegable dependiente dinámica utilizando las nuevas funciones "UNIQUE" y "FILTER".
Supongamos que tus datos fuente están organizados como se muestra en la captura de pantalla, sigue los siguientes pasos para crear la lista desplegable dinámica.
Paso 1: Usar fórmula para obtener elementos para la lista desplegable principal
Selecciona una celda, por ejemplo, la celda G3, y usa las funciones UNIQUE y FILTER para extraer los valores únicos de la lista "Producto" que será la fuente de la lista desplegable principal, y presiona la tecla "Enter".
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Paso 2: Crear la lista desplegable principal
1. Selecciona una celda donde deseas colocar la lista desplegable principal, por ejemplo, la celda "D3", haz clic en la pestaña "Datos", y ve al grupo "Herramientas de datos" para hacer clic en "Validación de datos" > "Validación de datos".
2. En el cuadro de diálogo "Validación de datos",
- Elige "Lista" en la sección "Permitir",
- Escribe la siguiente fórmula en la barra "Fuente",
- Haz clic en "Aceptar".
=$G$3#
Ahora se ha creado la lista desplegable principal.
Paso 3: Usar fórmula para obtener elementos para la lista desplegable dependiente
Selecciona una celda, por ejemplo, la celda H3, usa la función FILTER para filtrar los elementos basados en el valor en la celda "D3" (el elemento seleccionado en la lista desplegable principal), presiona la tecla "Enter".
=FILTER(B3:B20, A3:A20=D3)
Paso 4: Crear la lista desplegable dependiente
1. Selecciona una celda que colocará la lista desplegable dependiente, por ejemplo, la celda "E3", haz clic en la pestaña "Datos", y ve al grupo "Herramientas de datos" para hacer clic en "Validación de datos" > "Validación de datos".
2. En el cuadro de diálogo "Validación de datos",
- Elige "Lista" en la sección "Permitir",
- Escribe la siguiente fórmula en la barra "Fuente",
- Haz clic en "Aceptar".
=$H$3#
Ahora se ha creado correctamente la lista desplegable dependiente.
Cuando agregues nuevos elementos o hagas cambios en A3:A20, las listas desplegables se actualizarán automáticamente.
Ordenar la lista desplegable alfabéticamente
Si deseas ordenar los elementos en la lista desplegable alfabéticamente, puedes usar la siguiente fórmula en la tabla de preparación.Para la lista desplegable principal (la fórmula en la celda G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))Para la lista desplegable dependiente (la fórmula en la celda H3):
=SORT(FILTER(B3:B20, A3:A20=D3))Ahora ambas listas desplegables están ordenadas alfabéticamente de la A a la Z.
Para ordenar alfabéticamente de la Z a la A, utiliza la siguiente fórmula:
Para la lista desplegable principal (la fórmula en la celda G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)Para la lista desplegable dependiente (la fórmula en la celda H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)Algunas preguntas que podrías hacer:
1. ¿Por qué insertar una tabla para cada lista de datos?
Insertar una tabla para la lista de datos te ayudará a actualizar automáticamente la lista desplegable según los cambios en la lista de datos. Por ejemplo, al agregar 'Otros' en la primera lista de datos, la lista desplegable principal se actualizará automáticamente con 'Otros'.
2. ¿Por qué usar una tabla para colocar las listas desplegables?
Cuando presionas la tecla Tab para agregar una nueva línea a la tabla, las listas desplegables también se agregarán automáticamente en la nueva línea.
3. ¿Cómo funciona la función INDIRECT?
La función INDIRECT se utiliza para convertir una cadena de texto en una referencia válida.
4. ¿Cómo funciona la fórmula INDIRECT(SUBSTITUTE(E2&F2," ",""))?
Primero, la función SUBSTITUTE reemplaza texto con otro texto. Aquí se usa para eliminar los espacios de los nombres combinados (E2 y F2). Luego, la función INDIRECT convierte la cadena de texto (los contenidos combinados por E2 y F2) en una referencia válida.
Las mejores herramientas de productividad para Office
Impulsa al máximo tu dominio de Excel con Kutools para Excel y experimenta una eficiencia sin precedentes. Kutools para Excel ofrece más de300 funciones avanzadas para potenciar la productividad y ahorrar tiempo.Haz clic aquí para obtener la función que más necesitas...
Office Tab aporta una interfaz de pestañas a Office y hace tu trabajo mucho más sencillo
- Habilita la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abre y crea varios documentos en nuevas pestañas de la misma ventana, en vez de en nuevas ventanas.
- ¡Aumenta tu productividad hasta un50% y reduce cientos de clics de ratón cada día!
Tabla de contenido
- Video: Crear una lista desplegable dependiente en Excel
- Crear una lista desplegable dependiente dinámica
- Crear una lista desplegable dependiente en 10 segundos
- Crear una lista desplegable dependiente dinámica en Excel 365/2021/Nuevas versiones
- Preguntas frecuentes
- Artículos relacionados
- Las mejores herramientas de productividad para Office
- Comentarios