Si alguna vez intentaste que una lista desplegable cambiara automáticamente según lo que elegiste en otra lista, ya sabes que no es tan directo como parece. La mayoría de tutoriales te enseñan métodos que funcionan solo en ciertos casos — o que simplemente no escalan. En este post te muestro el método que sí funciona en cualquier versión de Excel, sin importar cuántas categorías tengas.
Listas Desplegables Dependientes en Excel: El Método que Siempre Funciona
🎁 Descarga el archivo de ejemplo
Escribe tu correo electrónico para recibir gratis el archivo para practicar.
*Al enviar mi correo electrónico, acepto recibir noticias y ofertas. Puedo darme de baja en cualquier momento.
Por qué los otros métodos fallan
Hay dos enfoques que vas a encontrar por ahí antes de llegar al correcto.
El primero es crear una columna por categoría y asignarle un nombre definido a cada rango. Funciona con cinco categorías. Con veinte, se vuelve un caos.
El segundo es usar la función FILTRAR dentro de Validación de Datos. El problema: Excel actualmente no acepta funciones de matrices dinámicas en Validación de Datos. Puedes hacer el truco del rango desbordado con #, pero eso solo funciona si tienes un formulario con una sola lista dependiente — no en una tabla de captura con múltiples filas.
La estructura correcta de los datos
Antes de cualquier fórmula, la tabla tiene que estar bien armada. Todo en una sola tabla: ID de categoría, nombre de categoría y subcategoría en columnas consecutivas, con los valores repetidos por fila. Olvídate de las columnas por categoría — eso no escala.
Cómo funciona DESREF para esto
DESREF (OFFSET en inglés) te permite moverte desde una celda de referencia hacia abajo y hacia la derecha, y devolver un rango de N filas. Para las listas dependientes, la lógica es esta:
- Referencia: celda A1 de la hoja de categorías
- Filas a bajar: la posición donde aparece por primera vez la categoría elegida (con COINCIDIR)
- Columnas a la derecha: 2, porque la subcategoría está dos columnas a la derecha
- Alto del rango: cuántas veces se repite esa categoría (con CONTAR.SI)
La fórmula completa concentra todo esto en una sola expresión que puedes pegar directamente en Validación de Datos.
Los pasos en concreto
- Convierte tu rango de datos a Tabla de Excel para que crezca automáticamente
- Usa INDIRECTO en la primera lista para mostrar las categorías únicas desde la tabla
- Aplica COINCIDIR para encontrar en qué fila aparece por primera vez la categoría seleccionada
- Usa CONTAR.SI para saber cuántas subcategorías tiene esa categoría
- Arma la función DESREF con esos dos resultados como argumentos de filas y alto
- Copia la fórmula resultante y pégala en Validación de Datos de la columna de subcategorías
Una sola fórmula. Sin nombres definidos. Sin columnas auxiliares por categoría.
El veredicto
DESREF lleva años en Excel y sigue siendo la solución más sólida para listas dependientes en tablas de captura. Las matrices dinámicas son potentes, pero tienen límites que todavía no han resuelto — y este es uno de ellos.
Si quieres dominar este tipo de soluciones y aplicarlas en tu trabajo desde la primera semana, en Deztaca tenemos clases en vivo cada semana donde resolvemos exactamente este tipo de casos, con archivos descargables y acceso a todo el catálogo de cursos.