Cómo hacer una lista desplegable en Excel paso a paso

Imagina una hoja de pedidos en la que cada cliente escribía el producto a mano. Con el tiempo, "Cuaderno", "cuaderno" y "Cuaderno " aparecen mezclados y la hoja se vuelve difícil de filtrar. Una lista desplegable en Excel obliga a elegir entre las opciones correctas. Si además esas opciones vienen de un Google Forms, la lista crece sola cada vez que alguien contesta con un producto que aún no estaba.

Al terminar este tutorial sabrás crear el desplegable con Validación de datos, traer las respuestas de Google Forms a Excel de dos formas (descargando el archivo o con Power Query) y mantener la lista limpia con UNICOS. La parte de UNICOS funciona en Excel 2021, 2024 y Microsoft 365; el resto está en todas las versiones de escritorio desde 2007.

Qué resuelve una lista desplegable en Excel y qué cambia si la alimentas con un formulario

Una lista desplegable convierte una celda en un pequeño menú: en lugar de escribir, se elige. Eso elimina los errores de escritura ("Bolígrafo" frente a "boligrafo"), acelera la captura de datos y permite después filtrar y resumir con BUSCARV o tablas dinámicas sin pelearse con los textos. La lista puede escribirse a mano y quedarse fija, o apuntar a un rango de celdas que se actualiza cuando algo cambia.

La pieza que marca la diferencia es alimentar la lista con las respuestas de un Google Forms. Hoy la tienda tiene cuatro productos, mañana son veinte. Si la lista está escrita a mano, hay que entrar en Validación de datos y reescribir el Origen cada vez. Si la lista apunta a un rango que se rellena solo desde las respuestas del formulario, el desplegable muestra los productos nuevos sin tocar la configuración. Es el mismo menú de siempre, pero que crece con el negocio.

La pieza de Excel que arma la lista: validación de datos

En Excel, las listas desplegables las crea Validación de datos, una herramienta de la pestaña Datos. Permite limitar lo que se puede escribir (números, fechas, una longitud de texto, una lista o una fórmula), mostrar un mensaje al seleccionar la celda y mostrar un aviso al escribir algo que no vale. No valida lo que ya estaba escrito ni lo que se pega encima con Ctrl+C y Ctrl+V: pegar puede saltarse la validación, así que después hay una sección dedicada a eso.

Al pulsar Validación de datos se abre un cuadro con tres pestañas: Configuración (qué se permite), Mensaje de entrada (qué se muestra al elegir la celda) y Mensaje de error (qué pasa al escribir algo que no vale).

Permitir = Lista y el cuadro Origen

La pestaña Configuración es el corazón del desplegable. Su primer campo es Permitir, una lista con opciones: Número entero, Decimal, Lista, Fecha, Hora, Longitud de texto y Personalizada. Para crear un menú se elige Lista. Al hacerlo aparece debajo el campo Origen, que admite dos cosas: valores escritos a mano separados por punto y coma (Cuaderno;Bolígrafo;Mochila;Calculadora) o la referencia a un rango de celdas ($F$2:$F$5). En ambos casos, la casilla Celda con lista desplegable debe quedar marcada para que se vea la flechita a la derecha de la celda; sin esa casilla, Excel valida pero no muestra el menú.

El mensaje de entrada y el aviso de error

La pestaña Mensaje de entrada sirve para mostrar un rótulo flotante junto a la celda cuando alguien la selecciona: tiene un Título (negrita) y un Mensaje de entrada (cuerpo). Si se dejan vacíos, no aparece nada y la celda se comporta como una cualquiera. Es útil para explicar qué hay que poner ("Elige el producto del catálogo"), pero no es obligatorio.

La pestaña Mensaje de error es la que decide qué pasa al escribir algo que no está en la lista. Estilo tiene tres valores: Detener (no deja escribir el valor y muestra un error), Advertencia (lo deja tras confirmar) e Información (lo deja sin más). Para una lista de productos, lo habitual es Detener: si el producto no está en el catálogo, no debe entrar en la hoja.

De Google Forms a una hoja de Excel: el camino de las respuestas

Google Forms no guarda los datos en un archivo de Excel: los guarda en una hoja de Google Sheets enlazada al formulario. Cada respuesta añade una fila en esa hoja con la marca de tiempo, las respuestas y, si la pregunta era desplegable, el texto exacto de la opción elegida. Para llevar esos datos a Excel hay dos caminos: descargar la hoja como .xlsx y abrirla en Excel, o enlazarla desde Power Query para que se refresque sola.

Lo que entra siempre es la respuesta en texto plano. Lo que no entra es la lista en sí: Forms no exporta la lista de opciones, solo las respuestas. Si alguien contesta con la opción "Otro" y la escribe a mano, ese texto llega tal cual y puede ensuciar la lista de UNICOS. Por eso, antes de pasar a Excel, conviene revisar la pregunta del formulario y limitar las opciones a las del catálogo.

Crear el formulario con la pregunta desplegable

1. Entra en forms.google.com y crea un formulario nuevo.
2. Añade una pregunta, elige el tipo Opción múltiple.
3. En la esquina inferior derecha de la pregunta hay un conmutador etiquetado Desplegable: actívalo para convertir las opciones de radio en un menú.
4. Escribe las opciones, una por línea en el campo Opciones.
5. En la parte superior del editor pulsa Respuestas y activa el icono de Hojas de cálculo de Google: aparece una hoja enlazada donde caen las respuestas en tiempo real.

Bajar la hoja de respuestas a Excel

1. Abre la hoja de Google enlazada al formulario (se abre al pulsar el icono de Hojas de cálculo).
2. En el menú de la hoja, ve a Archivo > Descargar > Microsoft Excel (.xlsx).
3. Se baja un libro con todas las respuestas en una hoja llamada Respuestas de formulario, sin fórmulas, con los valores tal cual se escribieron.
4. Abre el .xlsx en Excel; los datos están listos para usarse.

Este método es el más sencillo y funciona en cualquier versión de Excel. Su pega es que hay que repetir la descarga cada vez que se quieran datos frescos.

Una lista desplegable escrita a mano y atada a un rango

Empezamos con un libro de pedidos muy pequeño: en la hoja Pedidos, la columna A tiene el cliente y la columna B el producto. En la columna F, a la derecha, escribimos a mano los cuatro productos del catálogo. La idea es que, al elegir la celda B2, aparezca un menú con esos cuatro productos, y que B2 no acepte nada que no esté en la lista.

Origen con los valores uno a uno, separados por punto y coma

1. Selecciona B2:B5 (todas las celdas de producto a la vez).
2. Abre Validación de datos.
3. En Permitir elige Lista.
4. En Origen escribe Cuaderno;Boligrafo;Mochila;Calculadora.
5. Marca Celda con lista desplegable y pulsa Aceptar.

Al volver a la hoja, las celdas B2:B5 muestran una flechita a la derecha. Al desplegarla se ven los cuatro productos. Si se intenta escribir "Carpeta", Excel muestra un aviso de error y no deja avanzar.

Origen igual a un rango de celdas con dólares

La pega de escribir los valores a mano es que cualquier producto nuevo obliga a volver al cuadro de Validación de datos. Atar el Origen a un rango de celdas (en este caso F2:F5) lo evita: se cambia el catálogo en F y la lista lo recoge en cuanto se abre el menú.

1. Selecciona B2:B5.
2. Abre Validación de datos y elige Lista en Permitir.
3. En Origen escribe =$F$2:$F$5 (los dólares son obligatorios: fijan el rango).
4. Acepta.

Los dólares se pueden poner a mano o pulsar F4 (Cmd+T en Mac) con el cursor sobre la referencia. Sin dólares, al copiar la validación de B2 a B3 el Origen se desplazaría a $F$3:$F$6 y se perdería la primera opción. Para esta hoja no importa, pero si arrastras el controlador de relleno de B2 hacia abajo verás que sin dólares el menú va cambiando de rango en cada fila.

La lista que crece sola con UNICOS y una tabla

El paso siguiente es alimentar el desplegable con las respuestas de Google Forms, sin escribirlas a mano. Para eso se importa la hoja de respuestas a una hoja nueva del libro (llamada Hoja2) y se usa UNICOS para sacar los productos distintos. UNICOS existe desde Excel 2021 y Microsoft 365; en versiones anteriores hay que usar la herramienta Quitar duplicados de la pestaña Datos.

Convertir el rango de respuestas en Tabla

Una Tabla de Excel no es solo un formato bonito: cuando se añade una fila debajo, la Tabla la absorbe automáticamente y las fórmulas que la usan se amplían solas. Por eso se convierte el rango de respuestas en Tabla antes de aplicar UNICOS.

1. En Hoja2, haz clic en cualquier celda con datos (A1, por ejemplo).
2. Pulsa Ctrl+T para crear la Tabla.
3. Marca La tabla tiene encabezados y acepta.

La Hoja2 pasa a verse con filas alternas, un menú contextual de Diseño de tabla y un nombre por defecto (Tabla1). A partir de ahora, las respuestas que lleguen de Forms y se peguen en la fila siguiente se incluyen en la Tabla sin hacer nada más.

Sacar los valores únicos con UNICOS y desbordarlos

1. En F2 de la hoja Pedidos escribe la fórmula:

=UNICOS(Tabla1[Producto])

1. Pulsa Entrar.

El resultado se desborda hacia abajo desde F2: en F2 sale "Cuaderno", en F3 "Boligrafo", en F4 "Mochila", en F5 "Calculadora". No hay Repetidos: aunque en la Tabla "Cuaderno" aparezca dos veces, UNICOS lo deja una sola. Si una celda de la lista está ocupada, aparece el error #¡DESBORDAMIENTO! y hay que moverla.

1. Selecciona el rango F2:F5 y, en el Cuadro de nombres (a la izquierda de la barra de fórmulas), escribe ListaProductos y pulsa Entrar. Acabas de crear un rango con nombre.

Enlazar el desplegable al rango dinámico

1. Selecciona B2:B5 en Pedidos y abre Validación de datos.
2. En Permitir elige Lista.
3. En Origen escribe =ListaProductos y acepta.

Ahora añade "Carpeta" en la fila 8 de Hoja2: al volver a abrir la celda B2, la nueva opción aparece en el menú. El desplegable ha dejado de ser una lista estática y crece con las respuestas del formulario.

Traer las respuestas a Excel sin descargar el archivo cada vez

Descargar el .xlsx cada vez que hay respuestas nuevas es tedioso. La alternativa es usar Power Query (integrado en Excel desde 2016) para enlazar la hoja de Google y refrescar los datos con un clic. Lo que se trae es exactamente lo mismo (valores en crudo, sin fórmulas), pero se actualiza sin descargar nada.

Obtener el enlace para publicar en la web

1. En Google Sheets, ve a Archivo > Compartir > Publicar en la web.
2. En la primera lista elige la hoja concreta que tiene las respuestas (por defecto, Hoja 1).
3. En la segunda lista elige Valores separados por comas (.csv).
4. Pulsa Publicar y copia el enlace que aparece.

El enlace tiene esta pinta: https://docs.google.com/spreadsheets/d/.../export?format=csv&gid=0. Cualquiera con el enlace puede ver los valores, así que no lo compartas si la hoja tiene datos sensibles.

Importar el CSV desde Excel

1. En Excel, abre un libro nuevo o el mismo del desplegable.
2. Ve a Datos > Obtener y transformar > Desde la web.
3. Pega el enlace CSV y pulsa Aceptar.
4. En el panel de Power Query, elige el delimitador coma si no se detecta solo y pulsa Transformar datos.
5. En el editor de Power Query puedes borrar columnas que no te sirvan (marca de tiempo, direcciones de correo), cambiar tipos y, en la pestaña Transformar, usar Dar formato > ESPACIOS y MAYÚSC para limpiar textos. Cierra y carga con Cerrar y cargar > Cerrar y cargar en... > Tabla.

Cada vez que cambien las respuestas, pulsa Datos > Consultas y conexiones > Actualizar todo y la hoja de Pedidos se nutre de los datos frescos sin volver a la web de Google.

Por qué la lista no muestra las opciones nuevas o deja pasar valores malos

Tres tropiezos típicos con este montaje: el Origen es un rango fijo y la nueva opción queda fuera, pegar por encima se salta la validación, y las respuestas llegan con espacios de más que duplican la lista.

El Origen es un rango con dólares y la nueva opción quedó fuera

En este ejemplo, el Origen de Validación de datos es $F$2:$F$5. La respuesta nueva "Carpeta" entra en F6, una fila por debajo del rango, así que el desplegable no la ve. La solución es cambiar el Origen a =$F$2:$F$6 o, mejor aún, pasar a la lista con UNICOS y el rango ListaProductos de la sección anterior, que se ajusta solo.

Pegar encima se salta la validación

Validación de datos no se aplica a lo que ya está en la celda ni a lo que se pega encima. Si copias "Carpeta" con Ctrl+C y lo pegas en B2 con Ctrl+V, la celda acepta el valor aunque "Carpeta" no esté en la lista. La forma de detectarlos es Datos > Herramientas de datos > Validación de datos > Rodear con un círculo datos no válidos: Excel marca con un círculo rojo todas las celdas que no cumplen la regla. Para eliminarlos, se corrigen a mano o se quita el círculo desde el mismo menú.

Las respuestas llegan como texto con espacios y sobran en la lista

Si en Google Forms alguien añade un espacio al final de una opción ("Cuaderno "), la respuesta llega así y UNICOS la cuenta como un valor distinto de "Cuaderno": la lista muestra los dos. Lo mismo pasa con las mayúsculas ("cuaderno"). La solución está en Power Query, en la pestaña Transformar: Dar formato > ESPACIOS quita los espacios sobrantes al principio, al final y los duplicados, y MAYÚSC / MINÚSC / Inicial pone todo en el mismo formato. Se aplican antes de cargar la consulta y, así, UNICOS solo ve los valores limpios.

Lo que ya sabes hacer y el siguiente paso

• Crear un desplegable en Excel con Validación de datos y los valores escritos a mano.
• Apuntar el Origen a un rango de celdas con dólares y entender por qué son obligatorios.
• Traer las respuestas de un Google Forms a Excel descargando la hoja como .xlsx.
• Sacar la lista de productos únicos con UNICOS y enlazarla al desplegable con un rango con nombre.
• Enlazar la hoja de Google con Power Query para no descargar el archivo cada vez.

El siguiente paso natural es aplicar Formato condicional sobre la lista de productos únicos: por ejemplo, resaltar en verde los que tienen más de diez pedidos en la hoja de respuestas, para ver de un vistazo qué productos del catálogo son los más vendidos.

Dudas al crear listas desplegables en Excel con Google Forms