Validación de datos y listas desplegables en Excel sin errores
Aprende a crear listas desplegables, listas dependientes por región y mensajes de error para que cada celda acepte solo datos válidos.

En una distribuidora de Manta, los vendedores llenan a diario un formato de pedidos. Unos escriben “Manta”, otros “MANTA” y otros “Manta ” con un espacio al final. Al mes, el reporte por ciudad tiene cuatro variantes y los totales no cuadran. El problema no es la fórmula: la hoja acepta cualquier cosa.
La validación de datos convierte una celda libre en una celda controlada: listas, fechas, números enteros o una regla propia. También muestra ayuda al seleccionar la celda y un aviso claro cuando alguien escribe algo incorrecto.
Separador de argumentos. En este artículo uso punto y coma (;), que es lo habitual en Excel en español. Si tu equipo está configurado con coma como separador de listas, cambia cada ; por ,.
1. Crear la primera lista desplegable
El caso más común es una columna de ciudades con opciones cerradas.
- Escribe las opciones en H2:H5: Manta, Portoviejo, Guayaquil y Quito. Si conviertes el rango en tabla con
Ctrl + T, la lista puede crecer sin reescribir la referencia. - Selecciona las celdas donde se elegirán las ciudades, sin el encabezado.
- Ve a Datos → Validación de datos. En Configuración, elige Permitir: Lista.
- En Origen, selecciona el rango o escribe
=$H$2:$H$5. - Deja marcada Lista desplegable en la celda y pulsa Aceptar.
Si las opciones están en otra hoja, asigna un nombre al rango (por ejemplo Ciudades) y úsalo en Origen. Es la forma más segura y funciona en todas las versiones.
2. Una lista que se actualiza con los datos
Si los clientes cambian cada semana, no reescribas el origen. En Excel para Microsoft 365 y Excel 2021, con la tabla Ventas y su columna Cliente, escribe en H2:
=UNICOS(Ventas[Cliente])
El resultado se derrama hacia abajo. En la validación usa =$H$2#: el signo # abarca todo el rango generado. Si aparece un cliente nuevo, la lista lo incluye sola. En esta guía de FILTRAR, ORDENAR y UNICOS hay más ejemplos.
3. Listas dependientes: la ciudad según la región
En la columna E se elige la región y en la F solo deben aparecer sus ciudades: Costa → Manta, Portoviejo y Guayaquil; Sierra → Quito, Cuenca y Loja; Oriente → Tena, Puyo y Macas. Si el vendedor elige Costa, no debe poder escoger Quito.
Si tienes Excel 365 o 2021, guarda el catálogo en una tabla Catalogo con las columnas Región y Ciudad. Luego:
- Crea la lista de regiones en E2.
- Selecciona F2:F500 y abre Validación de datos.
- Elige Lista y escribe esta fórmula en Origen.
=FILTRAR(Catalogo[Ciudad]; Catalogo[Región]=$E2)
$E2 fija la columna, pero no la fila, para que cada fila mire su región. Al elegir Sierra en E5, F5 solo muestra Quito, Cuenca y Loja.
En versiones anteriores se usan nombres definidos e INDIRECTO. Define Costa, Sierra y Oriente, cada uno apuntando a sus ciudades, y en F2 escribe:
=INDIRECTO($E2)
El texto de E2 debe coincidir con el nombre del rango, así que usa nombres de una sola palabra, sin espacios ni tildes. Si prefieres Region_Costa, construye el nombre:
=INDIRECTO("Region_"&$E2)
Prueba la lista dependiente antes de compartir el archivo. Cambia la región y revisa qué pasa con la ciudad. La validación impide escribir opciones ajenas, pero no borra valores anteriores: si cambias la región después de elegir la ciudad, esa ciudad puede quedarse.
4. Mensajes que guían y errores que explican
En la misma ventana hay dos pestañas que casi nadie usa: Mensaje de entrada y Mensaje de error.
En Mensaje de entrada escribe un título y una ayuda breve: “Elige la región” y “Selecciona una opción; si tu ciudad no aparece, avisa a Sistemas”. Marca la casilla para mostrarlo al seleccionar la celda.
En Mensaje de error eliges el estilo: Detener rechaza el valor, Advertencia pregunta si deseas continuar e Información solo avisa. Para columnas que alimentan reportes, usa Detener. Escribe el mensaje en positivo: “Elige una ciudad de la lista. Si no está, revisa la región”. Evita “Valor no válido”, que no dice nada.
5. Reglas con fórmula: más allá de las listas
En Permitir, la opción Personalizada acepta una fórmula que debe devolver VERDADERO. Tres ejemplos:
- Factura sin repetir. En B2:B500,
=CONTAR.SI($B$2:$B$500; B2)=1. El rango absoluto se queda fijo y B2 cambia por fila. - Fecha no futura. En una columna de pagos,
=C2<=HOY(). - Cantidad entera y positiva. Elige Número entero y mayor que 0. No necesitas fórmula.
La fórmula se escribe para la primera celda del rango, igual que en el formato condicional.
Errores frecuentes
- La lista no aparece. Revisa que Permitir sea Lista y que Lista desplegable en la celda esté marcada.
- La validación se borró al copiar. Pegar datos encima reemplaza el formato. Vuelve a aplicarla o usa Pegar especial → Validación.
- El origen en otra hoja no funciona. Define un nombre para el rango y usa ese nombre.
- INDIRECTO da error o lista vacía. El nombre no coincide con el texto de la celda, o tiene espacios o tildes.
- Se pegan valores inválidos. La validación no detiene lo que se pega desde otro archivo; valida en el origen o usa Rodear con un círculo los datos no válidos.
Una hoja bien validada no es más rígida: es más rápida de llenar y más confiable de analizar. Define listas, mensajes y reglas en cada columna y tus reportes dejarán de pelearse con errores de tipeo. Si vas a resumir esos datos, combínala con una tabla dinámica.
Comentarios
Todavía no hay comentarios. Si tienes una duda sobre este tema, escríbela aquí y te respondo.



Deja tu comentario