Validación de Datos en Excel: Listas Desplegables y Reglas que Frenan los Datos Erróneos

Todo informe roto empieza con una errata. Alguien escribe Nrte en lugar de Norte y un SUMAR.SI.CONJUNTO devuelve en silencio un número un 40% más bajo. Alguien escribe 500 donde quería poner 5 y una previsión sale con un cero de más. Alguien escribe 12/01/2026, Excel lo guarda como texto y todo el eje de fechas se viene abajo.

El instinto es arreglarlo después — una pasada de limpieza, un ESPACIOS, una columna auxiliar. La Validación de datos lleva la solución río arriba. Pone la regla en el punto de entrada, donde un valor equivocado cuesta un segundo corregir en lugar de una hora localizar.

Consejo: La Validación de datos está en la pestaña Datos, en el grupo Herramientas de datos. El atajo de teclado es Alt → A → V → V, que merece la pena aprender si configuras más de un par de reglas.

1) Qué Hace Realmente la Validación de Datos (y Qué No)

La Validación de datos es una regla asociada a una celda que se comprueba cuando alguien escribe en esa celda y pulsa Intro. Esa frase contiene todas las limitaciones que conviene conocer.

Lo que hace:

• Bloquea o advierte sobre entradas escritas que incumplen la regla
• Ofrece una lista desplegable para que el valor correcto esté a un clic
• Muestra un aviso antes de escribir y un mensaje tras un fallo

Lo que no hace:

Esa tabla no es una lista de errores; es el diseño. La validación es un quitamiedos, no un candado. Si necesitas una garantía real — que nadie pueda guardar nunca un valor incorrecto aquí — necesitas proteger la hoja, y aun así alguien decidido con copiar y pegar dará la vuelta.

Usada para lo que sirve, en cambio, elimina la inmensa mayoría de los errores reales de entrada de datos, porque la inmensa mayoría son erratas honestas de personas que querían introducir el valor correcto.

2) Tu Primera Lista Desplegable

🎯 Escenario: La columna Región solo debería contener Norte, Sur, Este u Oeste. Ahora mismo es texto libre.

Configuración de Datos:

• Columna A: ID de Pedido
• Columna B: Fecha
• Columna C: Región
• Columna D: Comercial
• Columna E: Producto
• Columna F: Cantidad

Selecciona C2:C9, abre Datos → Validación de datos y en la pestaña Configuración pon Permitir en Lista. En el cuadro Origen, escribe los valores separados por comas:

North,South,East,West

Deja marcada Celda con lista desplegable, pulsa Aceptar y todas las celdas del rango tendrán una flecha. Pulsa Alt + ↓ sobre una celda validada para abrir la lista desde el teclado — sin ratón.

Tres cosas que todo el mundo falla al principio:

1. Usa comas, no punto y coma. El cuadro Origen usa comas como separador independientemente de tu configuración regional. Escribir North;South crea una única opción llamada "North;South".
2. Sin espacios detrás de las comas. North, South crea una opción que empieza literalmente por un espacio, y que luego no coincidirá con "South" en ninguna búsqueda que escribas.
3. 255 caracteres como máximo. El cuadro Origen escrito es un campo de texto con un límite estricto. Cuatro regiones caben de sobra; cuarenta productos no.

Trampa: Una lista escrita es invisible para el resto del libro. Nada más puede contarla, ordenarla ni comprobarla, y actualizarla implica reabrir el diálogo en cada rango que la usa. Escribe una lista solo si es realmente permanente y realmente corta — Sí,No es buen candidato. Todo lo demás va en celdas.

Mini ejercicio: Añade un desplegable Paid,Pending,Refunded a una nueva columna Estado en G. Luego prueba a escribir paid en minúsculas — se aceptará, porque las listas de validación no distinguen mayúsculas de minúsculas.

3) Listas que Viven en Celdas (y Crecen Solas)

🎯 Escenario: La lista de productos cambia cada trimestre. Quieres añadir un producto en un único sitio y que todos los desplegables del libro lo recojan.

Pon la lista en un sitio real — idealmente una hoja llamada Listas a la que nadie baja. Supongamos que los productos están en Listas!$A$2:$A$6. En el cuadro Origen, haz referencia a ellos:

=Listas!$A$2:$A$6

Desde Excel 2010 puedes apuntar a otra hoja directamente así. El problema es que el rango es fijo: añade un sexto producto en A7 y ningún desplegable lo verá.

Opción A — una Tabla (la recomendable). Convierte el rango en Tabla con Ctrl + T y llámala tblProductos. Una Tabla crece automáticamente cuando escribes debajo. El cuadro Origen, sin embargo, rechaza las referencias estructuradas escritas directamente — =tblProductos[Producto] se rechaza con una queja sobre operadores de referencia. La vía fiable añade un paso:

1. Fórmulas → Administrador de nombres → Nuevo
2. Nombre: ListaProductos
3. Se refiere a: =tblProductos[Producto]

Y luego usa el nombre en el cuadro Origen:

=ListaProductos

Ahora la Tabla crece, el nombre la sigue y todos los desplegables del libro se actualizan sin trabajo adicional.

Opción B — un rango derramado (Microsoft 365). Si tu lista debe estar sin duplicados u ordenada, genérala con una fórmula:

=ORDENAR(UNICOS(tblPedidos[Producto]))

Ponla en Listas!$C$2, deja que se derrame y apunta el cuadro Origen al rango derramado con el operador #:

=Listas!$C$2#

El desplegable se redimensiona solo a medida que cambian los datos de origen — una lista que se mantiene sola de verdad.

Resultado: Añadir "Base de conexión" al origen actualiza todos los desplegables del archivo, al instante, sin reabrir ningún diálogo.

Trampa: Los nombres definidos y las referencias derramadas funcionan entre hojas. Las referencias directas =Listas!$A$2:$A$6 también — pero si alguien elimina después la hoja Listas, todas las reglas degradan en silencio a aceptar cualquier cosa. Nombrar el rango al menos hace visible la dependencia en el Administrador de nombres.

4) Listas Dependientes: La Segunda Sigue a la Primera

🎯 Escenario: Eliges North en la columna C y el desplegable de Comercial de la columna D debería ofrecer solo a Alice y Dana — no a todos los comerciales de la empresa.

El método clásico con INDIRECTO. Funciona en todas las versiones de Excel y depende de una convención de nombres:

1. En la hoja Listas, pon los comerciales de cada región en su propia columna.
2. Nombra cada rango de columna exactamente igual que la región a la que pertenece: un rango llamado North, otro South, otro East.
3. Configura el desplegable de Región en C2:C9 como siempre.
4. Para D2:D9, pon Permitir en Lista y usa:

=INDIRECTO($C2)

Fíjate en la referencia mixta: $C2 bloquea la columna pero deja viajar la fila, así que la lista de la fila 5 lee la región de la fila 5. Equivocarse aquí — escribir $C$2 — es el fallo más común, y produce una hoja donde todas las filas ofrecen los comerciales de lo que haya en C2.

Dos reglas que impone la convención de nombres:

• Los nombres no pueden contener espacios. Una región llamada "North East" necesita un rango llamado North_East, y la fórmula pasa a ser =INDIRECTO(SUSTITUIR($C2," ","_")).
• Los nombres no pueden empezar por un dígito. Una categoría llamada "2026 Range" necesita un prefijo, e INDIRECTO tiene que devolvérselo: =INDIRECTO("cat_"&$C2).

El método moderno con FILTRAR (Microsoft 365). Sin convención de nombres. En Listas!$E$2, derrama los comerciales que coinciden desde una tabla de consulta:

=FILTRAR(tblComerciales[Comercial], tblComerciales[Región]=$C2)

Y apunta el cuadro Origen a =Listas!$E$2#. Es más limpio, maneja espacios y dígitos sin ceremonias y sobrevive a que alguien renombre una región. El coste es una celda auxiliar por columna validada, y solo funciona si todas las filas comparten la misma región "actual" — para una lista dependiente fila a fila, INDIRECTO sigue siendo la opción más práctica.

Trampa: Ninguno de los dos métodos limpia un valor obsoleto. Pon la región en North, elige Alice, y luego cambia la región a South — Alice se queda en la columna D, ahora no válida, y no salta ninguna alerta. La sección 8 explica cómo encontrarlos.

5) Números, Fechas y Longitud de Texto

🎯 Escenario: La cantidad debe ser un número entero entre 1 y 100. Las fechas deben caer dentro del año en curso y no estar en el futuro.

No todo necesita una lista. El desplegable Permitir tiene cinco tipos de regla más, y todos aceptan un mínimo y un máximo — escritos o leídos de una celda.

Número entero para F2:F9:

Permitir: Número entero
Datos:    entre
Mínimo:   1
Máximo:   100

Escribir 0, 250 o 2,5 queda rechazado. Elige Número entero en lugar de Decimal a conciencia — un pedido de 2,5 portátiles es justo el tipo de error de dedo que conviene bloquear.

Fecha para B2:B9, usando fórmulas como límites:

Permitir: Fecha
Datos:    entre
Inicio:   =FECHA(2026,1,1)
Fin:      =HOY()

Construir los límites con FECHA() en lugar de escribir 01/01/2026 evita la ambigüedad día/mes que castiga a los equipos internacionales. HOY() se evalúa en el momento de la entrada, así que la regla avanza sola cada día — sin mantenimiento.

Esta regla tiene un efecto secundario muy útil: rechaza el texto que parece una fecha. Si alguien pega un valor que Excel guardó como texto, no es una fecha, así que falla. Esa sola regla previene la mayoría de los problemas de "por qué no se ordenan mis fechas" en un libro compartido.

Longitud de texto para el ID de Pedido en A2:A9:

Permitir: Longitud del texto
Datos:    igual a
Longitud: 8

Rudimentario pero eficaz — ORD-1001 tiene ocho caracteres, ORD-101 tiene siete, y el segundo queda frenado.

Sobre "Omitir blancos". Viene marcado por defecto y significa: una celda vacía cumple la regla. No significa que la celda pueda dejarse vacía solo a veces, y desmarcarlo no hace obligatoria la entrada — una celda que nadie toca nunca llega a validarse. No hay forma de forzar una entrada con Validación de datos; eso requiere una comprobación con fórmula en otro sitio o VBA.

La sorpresa relacionada: si tu Origen apunta a un rango con celdas en blanco, esos blancos aparecen como opciones vacías en el desplegable, y desmarcar "Omitir blancos" no los elimina. Recorta el rango de origen, o genéralo con UNICOS/FILTRAR.

6) Reglas Personalizadas: Cualquier Fórmula que Devuelva VERDADERO

🎯 Escenario: Los ID de pedido deben ser únicos, empezar por ORD- y terminar en cuatro dígitos.

Pon Permitir en Personalizada y obtienes un único cuadro de fórmula. La regla es simple: escribe una fórmula que devuelva VERDADERO para una entrada válida, relativa a la celda superior izquierda de tu selección.

Unicidad — la regla personalizada más útil que existe. Selecciona A2:A9 e introduce:

=CONTAR.SI($A$2:$A$1000, A2)=1

Resultado: Escribir un ID de pedido que ya existe queda rechazado. Léelo como cuenta cuántas veces aparece este valor en la columna; una entrada válida aparece exactamente una vez — ella misma. Fíjate en el rango de origen absoluto y el A2 relativo: esa pareja es lo que hace que la regla viaje bien por la columna.

Comprobación de formato — combina funciones de texto con Y:

=Y(IZQUIERDA(A2,4)="ORD-", LARGO(A2)=8, ESNUMERO(VALOR(DERECHA(A2,4))))

Tres condiciones que deben cumplirse todas: el prefijo correcto, la longitud total correcta y cuatro caracteres finales que sean realmente numéricos. ORD-1001 pasa; ORD-100A e INV-1001 no.

Bloquear espacios al principio y al final — el error invisible que rompe todas las búsquedas que escribirás:

=A2=ESPACIOS(A2)

Lógica entre columnas — una regla que lee otra celda de la misma fila. Si los pedidos de Keyboard se limitan a 10 pero el resto a 100:

=SI($E2="Keyboard", Y(F2>=1, F2<=10), Y(F2>=1, F2<=100))

Bloquear fechas futuras sin usar la regla Fecha:

=B2<=HOY()

Trampa: Las reglas personalizadas se escriben relativas a la celda activa de la selección al abrir el diálogo. Si seleccionas A2:A9 pero la celda activa resulta ser A5, Excel interpreta tu fórmula como si estuviera escrita para A5 y la desplaza en todas las demás filas. Selecciona siempre el rango empezando por su celda superior izquierda — haz clic en A2 y luego extiende con Mayús + clic.

7) Mensajes de Entrada y Mensajes de Error

Una regla que rechaza en silencio resulta irritante. Una regla que se explica resulta útil. Las dos pestañas restantes del diálogo son donde ocurre eso, y ambas se saltan sistemáticamente.

Mensaje de entrada aparece como un pequeño aviso al seleccionar la celda, antes de que nadie escriba. Úsalo para aquello que la gente fallaría de otro modo:

Título:  Cantidad
Mensaje: Solo unidades enteras, de 1 a 100. Para pedidos de más de 100, divide en varias líneas.

Mensaje de error aparece tras una entrada fallida, y su ajuste de Estilo es la decisión importante:

Casi todo el mundo lo deja todo en Grave, que es lo correcto para reglas estructurales — una región que no es una región romperá un SUMAR.SI.CONJUNTO. Pero Grave aplicado a una regla flexible enseña a la gente a esquivar tu hoja: pegarán valores, o se harán una copia paralela sin control. Si una cantidad de 150 es inusual pero legítima, Advertencia es el ajuste honesto.

Elijas lo que elijas, escribe el mensaje. El texto por defecto es un genérico "El valor introducido no es válido", que no le dice al usuario nada sobre qué sí sería válido. Compara:

Por defecto: El valor introducido no es válido.
Mejor:       La región debe ser una de: North, South, East, West.
             Usa la flecha del desplegable, o pulsa Alt+Flecha abajo.

El segundo consigue que la hoja se rellene bien. El primero consigue que te escriban preguntando qué le pasa al archivo.

8) Encontrar los Datos que Ya Incumplían las Reglas

🎯 Escenario: Has añadido validación a una hoja con 4.000 filas existentes. Las reglas no revisan el historial — así que, ¿cuántos datos antiguos no son válidos?

Rodear con un círculo datos no válidos. Abre la flecha junto al botón Validación de datos en la pestaña Datos y elige Rodear con un círculo datos no válidos. Excel dibuja un óvalo rojo alrededor de cada celda cuyo valor actual incumple su regla. Es la auditoría más rápida de Excel, y casi nadie sabe que existe.

Tres cosas que conviene saber:

• Los círculos son solo visuales. Desaparecen al guardar y cerrar, y se van uno a uno según se corrige cada valor.
• Solo se rodean las primeras 255 celdas no válidas. En una hoja muy dañada, corrige una tanda y vuelve a ejecutarlo.
• Borrar círculos de validación, en el mismo menú, los quita todos.

Localizar qué celdas tienen reglas. Pulsa F5 → Especial → Validación de datos → Todos, y Excel selecciona todas las celdas validadas de la hoja. Elige Iguales y selecciona todas las que comparten la regla de la celda activa — la forma más rápida de responder a "¿se aplicó esa regla a toda la columna?".

Auditar sin tocar la hoja. Para tener un recuento permanente en lugar de un vistazo puntual, pon un CONTAR.SI en una esquina:

=SUMAPRODUCTO(--(CONTAR.SI(ListaProductos, E2:E9)=0))

Resultado: El número de entradas de Producto que no aparecen en la lista aprobada. Cualquier cosa por encima de 0 es trabajo de limpieza y, a diferencia de los círculos rojos, esta cifra sobrevive al guardado.

9) Copiar y Pegar: Cómo Desaparecen las Reglas en Silencio

Esta es la sección que explica por qué la validación "dejó de funcionar" en una hoja que nadie admite haber tocado.

Pegar sobre una celda validada sustituye su regla. No solo el valor — el conjunto completo de atributos de la celda, validación incluida. Copia una celda sin validación de cualquier sitio, pégala sobre C5, y C5 deja de tener desplegable de Región. No se muestra ningún aviso. La regla simplemente ha desaparecido, y la próxima persona que escriba Nrte ahí lo conseguirá.

Peor aún: el valor pegado nunca se comprueba, así que un pegado puede introducir datos no válidos y borrar la regla que los habría detectado, en una sola pulsación.

Qué hacer al respecto:

• Pegar solo valores — Ctrl + Alt + V y luego V — pega el valor y deja intactos el formato y la validación del destino. Enseña este único atajo y la mayor parte del problema desaparece.
• Pegado especial → Validación de datos copia solo las reglas de un rango a otro. Es la forma correcta de extender una regla existente a filas nuevas, y mucho más segura que reabrir el diálogo y volver a teclear el origen.
• Usa una Tabla. Las filas nuevas añadidas al final de una Tabla heredan automáticamente la validación de las filas superiores — sin arrastrar, sin reaplicar.
• Protege la hoja (Revisar → Proteger hoja) con las celdas validadas bloqueadas. La protección bloquea el pegado en sí, que es la única defensa fiable. Ojo: aquí trabaja una herramienta distinta — la validación guía, la protección obliga.

Reconstruir una regla que alguien destruyó: selecciona una celda que aún conserve la regla correcta, cópiala, selecciona el rango dañado y usa Pegado especial → Validación de datos. Diez segundos, y sin riesgo de que la regla nueva difiera sutilmente de la antigua.

Lista de Comprobación Rápida (Antes de Fiarte de la Hoja)

• Las listas viven en celdas, no escritas en el cuadro Origen (salvo que sean dos opciones y permanentes)
• El origen es una columna de Tabla a través de un nombre definido, o un rango derramado, para que crezca solo
• Las listas dependientes usan $C2 — columna bloqueada, fila relativa
• Las fórmulas personalizadas se escribieron con la celda superior izquierda activa al abrir el diálogo
• Las fórmulas personalizadas mezclan rangos de origen absolutos con referencias de fila relativas
• Los límites de fecha se construyen con FECHA() u HOY(), nunca escritos como texto
• Cada regla tiene un mensaje de error que dice qué sí está permitido
• El estilo de la alerta refleja la firmeza real de la regla — Advertencia para límites flexibles, Grave para los estructurales
• Rodear datos no válidos se ha ejecutado una vez sobre las filas preexistentes
• El rango de entrada es una Tabla, para que las filas nuevas hereden las reglas
• Quien pegue en esta hoja conoce Ctrl + Alt + V → V

Resumen de Errores Comunes

1. Esperar que la validación detecte valores pegados: nunca lo hace. Pegar salta la regla y la sobrescribe a la vez.
2. Punto y coma en una lista escrita: North;South es una opción, no dos. El cuadro Origen siempre usa comas.
3. Espacios tras las comas: South se ve idéntico en pantalla y no coincide con nada en ninguna búsqueda.
4. Alcanzar el límite de 255 caracteres: una lista escrita larga se trunca en silencio. Llévala a celdas.
5. Referencias estructuradas en el cuadro Origen: =tblProductos[Producto] se rechaza. Envuélvelo en un nombre definido.
6. $C$2 en una lista dependiente: todas las filas muestran la misma sublista. Usa $C2.
7. Nombres con espacios o que empiezan por dígito: INDIRECTO no los encuentra. Sustituye por guiones bajos o añade un prefijo.
8. Valores dependientes obsoletos: cambiar el primer desplegable no limpia el segundo. Rodear datos no válidos los encuentra.
9. Una fórmula personalizada escrita contra la celda activa equivocada: la regla funciona en una fila y se descoloca en el resto.
10. Creer que "Omitir blancos" hace obligatorio un campo: nada en la Validación de datos puede forzar una entrada.
11. Añadir reglas y no auditar el historial: los valores existentes nunca se revisan. Ejecuta Rodear datos no válidos una vez.
12. Alertas Grave en reglas flexibles: la gente rodea las barreras. Advertencia los mantiene dentro de la hoja.

Conclusión

La Validación de datos es el control de calidad más barato de Excel. Un desplegable se configura en treinta segundos y elimina una categoría entera de error de forma permanente — no encontrando erratas más rápido, sino haciéndolas imposibles.

El modelo mental que merece la pena conservar es el de la sección 1: la validación es un quitamiedos, no un candado. Se ocupa del error honesto, que son casi todos. No hace nada contra el pegado, las fórmulas ni el historial. Saber exactamente dónde termina el quitamiedos es lo que separa una hoja que se mantiene limpia de otra que parece protegida y no lo está.

Si te quedas con tres cosas de este artículo: pon tus listas en una Tabla y referencia por nombre, escribe un mensaje de error que nombre los valores válidos, y ejecuta Rodear con un círculo datos no válidos en cualquier hoja que heredes. Esos tres hábitos cubren casi toda la distancia entre una hoja de cálculo con la que se pelea y otra que simplemente se rellena.

Si quieres practicar la entrada de datos limpia, prueba los ejercicios de la app — cada escenario parte de una hoja real, ligeramente rota.