Validación de datos en Excel

El arte de evitar errores antes de que ocurran

Introducción ⬆️

La validación de datos es una de esas funciones de Excel que parecen sencillas, pero que cambian completamente la calidad de una hoja cuando se usa con gracia y habilidad.

Su objetivo no es corregir errores después de que aparezcan, sino impedir que se introduzcan. En ficheros compartidos, inventarios, formularios de captura o informes periódicos, una buena validación actúa como un filtro de entrada: ayuda al usuario, reduce ambigüedades y hace que las fórmulas posteriores trabajen con datos más fiables.

La idea central de este artículo es sencilla: una hoja de cálculo profesional no debería limitarse a calcular; también debería guiar al usuario. Validar es diseñar comportamiento (¡menuda frase!).

Fundamentos de la validación ⬆️

Excel permite restringir el tipo de dato o los valores que se introducen en una celda. En el cuadro de Validación de datos se configuran reglas, mensajes de entrada y avisos de error.

Las pestañas menos aprovechadas son “Mensaje de entrada” y “Mensaje de error”. La primera añade a la celda una pequeña ayuda contextual. La segunda permite explicar el error con un texto claro, en vez de dejar al usuario frente a un mensaje genérico (y, para algunos, totalmente críptico).

Validaciones básicas: la primera línea de defensa de nuestros datos ⬆️

Cuando pensamos en la calidad de una hoja de Excel solemos centrarnos en fórmulas, tablas dinámicas o gráficos. Sin embargo, muchas veces los errores aparecen mucho antes: en el momento en que alguien introduce un dato incorrecto.

La validación de datos es una de las herramientas más sencillas y a la vez más potentes de Excel, ya que permite controlar qué información puede escribirse en una celda. Con unos pocos clics podemos evitar fechas imposibles, números fuera de rango o textos excesivamente largos.

Las validaciones más básicas son las de número, fecha, hora y longitud de texto.

=HOY()

=RESIDUO(A1*24*60;30)=0

Listas desplegables. Las básicas ⬆️

De todas las opciones de Validación de datos de Excel, la más utilizada probablemente sea la validación de tipo Lista. Su objetivo es muy sencillo: permitir que el usuario seleccione un valor de una lista predefinida en lugar de escribirlo manualmente.

Gracias a ello conseguimos datos más consistentes, evitamos errores ortográficos y aceleramos la introducción de información.

¿Qué es una validación de lista?

Cuando configuramos una validación de tipo Lista, Excel muestra una pequeña flecha desplegable en la celda.

Por ejemplo, en una base de datos de clientes podemos querer que la provincia solo pueda ser una de estas opciones:

• Barcelona
• Madrid
• Valencia
• Sevilla
• Zaragoza

En lugar de permitir que cada usuario escriba libremente:

• Barna
• Barcelona
• BARCELONA
• Barcelona.

todos seleccionarán exactamente el mismo valor.

Crear una lista sencilla

Supongamos que queremos permitir únicamente tres estados para un pedido.

1. Seleccionamos las celdas.
2. Datos → Validación de datos.
3. En «Permitir» elegimos Lista.
4. En «Origen» escribimos: Pendiente;En proceso;Finalizado

(Nota: algunas configuraciones regionales usan coma en lugar de punto y coma).

A partir de ese momento aparecerá un desplegable con esas tres opciones.

Crear una lista basada en un rango

La forma anterior es rápida, pero tiene una limitación: si queremos añadir nuevos elementos debemos modificar la validación.

Lo habitual es mantener la lista en una hoja auxiliar.

Ejemplo:

Y configurar la validación usando:

=$A$1:$A$3

Si añadimos nuevos elementos al rango, podremos actualizar fácilmente la lista.

Ventajas de las listas desplegables

Evitan errores de escritura

Sin validación podemos encontrar:

• Madrid
• MADRID
• Madird
• Madrd

Con una lista solo existe una versión válida.

Aceleran la introducción de datos

Un par de clics suele ser más rápido que escribir.

Mejoran filtros y tablas dinámicas

Al estar los datos uniformizados:

• Los filtros funcionan mejor.
• No aparecen categorías duplicadas.
• Los informes son más fiables.

Buenas prácticas

Mantener las listas en una hoja oculta

Muchos usuarios crean una hoja llamada Listas o Parámetros donde almacenan todos los valores de las validaciones.

Esto facilita enormemente el mantenimiento del libro.

Ordenar alfabéticamente

Cuanto más larga sea una lista, más útil resulta mantenerla ordenada.

Evitar duplicados

Antes de usar un rango como origen de una lista, conviene eliminar posibles duplicados.

Ejemplos típicos de uso

Las listas desplegables son ideales para:

• Provincias.
• Países.
• Departamentos.
• Estados de pedidos.
• Prioridades (Alta, Media, Baja).
• Meses del año.
• Categorías de producto.
• Comerciales o vendedores.
• Tipos de incidencia.

Listas desplegables complejas: mucho más que una lista de valores ⬆️

La lista desplegable es el uso más popular de la validación, pero no tiene por qué limitarse a tres valores escritos a mano o tomados de un rango fijo de valores.

Una lista básica puede escribirse directamente, pero lo recomendable en modelos mantenibles es usar una tabla, un rango con nombre o una matriz dinámica. Así, cuando aparece un nuevo valor, la lista crece sin tener que volver a configurar la validación.

=ORDENAR(UNICOS(TablaCiudades[Ciudad]))

Si la fórmula anterior se coloca en una celda auxiliar, la validación puede apuntar al rango derramado con el operador #.

=H2#

También se pueden crear listas dependientes, por ejemplo: primero seleccionas País y después Ciudad. En Excel moderno, FILTRAR permite resolver este escenario de forma más clara que el clásico INDIRECTO.

=FILTRAR(TablaCiudades[Ciudad];TablaCiudades[País]=B2)

Mensajes de entrada y alertas de error: la parte más humana de las validaciones ⬆️

Muchos usuarios conocen las validaciones de datos por sus listas desplegables o por las restricciones que impiden introducir valores incorrectos. Sin embargo, una de las características más interesantes y menos utilizadas son los mensajes de entrada y las alertas de error.

Estas opciones transforman una simple restricción en una experiencia guiada para el usuario, reduciendo dudas y mejorando la calidad de los datos introducidos.

Mensajes de entrada

Los mensajes de entrada aparecen automáticamente cuando el usuario selecciona una celda que tiene validación.

Su objetivo es explicar qué dato se espera antes de que el usuario escriba nada.

Ejemplo

Supongamos una celda destinada a registrar la fecha de una reunión.

Título: Fecha de la reunión

Mensaje: Introduzca una fecha comprendida entre hoy y los próximos 30 días.

Al situarse sobre la celda, Excel mostrará un cuadro similar a:

Cuándo utilizarlos

Son especialmente útiles cuando:

• La validación tiene reglas poco evidentes.
• Existen formatos específicos.
• Hay usuarios poco familiarizados con la hoja.
• Deseamos evitar errores antes de que ocurran.

Algunos ejemplos prácticos

• Código de producto. Debe contener exactamente 8 caracteres.
• Porcentaje de descuento. Introduzca un valor entre 0 y 20.
• Estado del pedido. Seleccione una opción de la lista desplegable.

Alertas de error

Las alertas de error aparecen cuando el usuario intenta introducir un dato que no cumple las reglas definidas.

A diferencia del mensaje de entrada, se muestran después de producirse el error.

Excel dispone de tres estilos de alertas.

Tipo 1: Alto

Es la opción más estricta.

Si el dato no cumple la validación, Excel no permite introducirlo.

Ejemplo

La celda admite únicamente números entre 1 y 100.

El usuario escribe: 150

Y obliga a corregir el valor.

Cuándo usarla

• Fechas obligatorias.
• Códigos internos.
• Importes.
• Datos críticos para informes.

En general, cuando los errores no pueden ser aceptados bajo ningún concepto.

Tipo 2: Advertencia

Es una opción intermedia. Excel avisa de que el valor parece incorrecto, pero permite conservarlo si el usuario insiste.

Ejemplo

Un empleado introduce una edad de: 125

Se muestra:

El usuario puede:

• Corregir el dato.
• Ignorar la advertencia y continuar.

Cuándo usarla

• Datos excepcionales pero posibles.
• Situaciones donde pueden existir casos especiales.
• Controles de calidad no críticos.

Tipo 3: Información

Es la opción más flexible.

Excel informa de que el dato incumple la norma, pero permite guardarlo fácilmente.

Ejemplo

El usuario puede aceptar el mensaje y continuar trabajando.

Cuándo utilizarla

• Recomendaciones.
• Buenas prácticas.
• Controles no obligatorios.

Personalizando los mensajes

Tanto los mensajes de entrada como las alertas admiten:

• Título.
• Texto explicativo.

Un mal mensaje:

Un buen mensaje:

Cuanto más específico sea el mensaje, menos incidencias recibirás de los usuarios.

Un truco poco conocido

Los mensajes admiten saltos de línea.

Por ejemplo:

El resultado es mucho más legible y profesional.

Recomendaciones para formularios profesionales

Si quieres que una hoja parezca una auténtica aplicación:

✅ Utiliza mensajes de entrada en todos los campos importantes.

✅ Personaliza siempre las alertas de error.

✅ Explica al usuario qué debe hacer, no solo lo que ha hecho mal.

✅ Evita mensajes genéricos como:

• Error
• Valor no válido

y sustitúyelos por mensajes concretos:

Introduzca una fecha de entrega comprendida entre hoy y el final del año.

En resumen

Los mensajes de entrada enseñan al usuario qué debe introducir antes de escribir.

Las alertas de error le ayudan cuando comete un error.

Combinados adecuadamente, convierten una simple hoja de Excel en un formulario guiado, mucho más intuitivo, profesional y resistente a errores. De hecho, en muchos casos aportan más valor al usuario que la propia regla de validación.

El verdadero poder: las fórmulas personalizadas ⬆️

La opción Personalizada convierte la validación en un motor lógico. Cualquier fórmula que devuelva VERDADERO o FALSO puede funcionar como regla.

La clave es recordar que la fórmula se evalúa desde la primera celda del rango seleccionado. Por eso las referencias relativas y absolutas importan tanto como en el formato condicional.

Ejemplos prácticos que sorprenden⬆️

Una cuantas validaciones de datos sumamente prácticas que resuelven problemas reconocibles con fórmulas relativamente compactas.

Código postal español

Cinco caracteres y todos numéricos

=Y(LARGO(A2)=5;ESNUMERO(--A2))

Texto de una sola palabra

Rechaza espacios normales después de normalizar

=ESERROR(HALLAR(" ";ESPACIOS(A2)))

Correo razonable

Comprueba @ y punto posterior

=Y(ESNUMERO(HALLAR("@";A2));ESNUMERO(HALLAR(".";A2;HALLAR("@";A2))))

Número primo

Usa SECUENCIA para probar divisores

=Y(A2>1;SUMA(--(RESIDUO(A2;SECUENCIA(A2-1;;1))=0))=2)

Palíndromo

Compara el texto con su inversión

=A2=UNIRTEXTO("";;EXTRAE(A2;LARGO(A2)-SECUENCIA(LARGO(A2))+1;1))

La limpieza de datos como mecanismo de validación ⬆️

Muchas validaciones realmente valiosas no comprueban si un dato “parece correcto”, sino si está limpio.

Los espacios al inicio, al final, los espacios dobles y el carácter 160 son enemigos habituales en datos copiados desde webs, PDFs, correos o sistemas externos. Visualmente parecen espacios normales, pero pueden romper BUSCARX, coincidencias, Power Query o relaciones entre tablas.

=A2=ESPACIOS(A2)

La fórmula anterior detecta espacios externos y dobles, pero no siempre captura el carácter 160. Para eso conviene añadir una regla específica:

=NO(ESNUMERO(HALLAR(CARACTER(160);A2)))

Base para nombres de archivo válidos

=SUMAPRODUCTO(--ESNUMERO(HALLAR({"\";"/";":";"*";"?";"<";">";"|"};A2)))=0

Técnicas avanzadas ⬆️

Las funciones modernas de Excel han ampliado mucho lo que se puede hacer con validación de datos sin VBA.

LET para reglas legibles

=LET(txt;A2;
Y(LARGO(txt)>=5;txt=ESPACIOS(txt);NO(ESNUMERO(HALLAR(CARACTER(160);txt))))
)

Matrices dinámicas

ORDENAR, UNICOS y FILTRAR permiten construir orígenes de lista que se actualizan automáticamente. El patrón habitual es generar la lista auxiliar y apuntar la validación al rango derramado.

=ORDENAR(
UNICOS(FILTRAR(TablaProductos[Categoría];TablaProductos[Activo]="Sí"))
)

Nombres definidos

Un nombre definido como EstadosValidos o ListaClientes permite ocultar la complejidad de la fórmula y hacer más legible el modelo.

=EstadosValidos

Trucos poco conocidos ⬆️

La Validación de Datos tiene pequeños comportamientos que dan mucho juego en formación avanzada.

Usar mensajes como ayuda contextual

Una celda puede mostrar instrucciones sin necesidad de comentarios ni notas.

Listas con símbolos

Los emojis y símbolos Unicode convierten estados en elementos visuales.

Validaciones relativas

Una sola regla puede adaptarse a cada fila si usas referencias relativas correctamente.

Validación como formulario sin VBA

Combinada con protección de hoja, mensajes y formato condicional, puede crear interfaces de captura bastante sólidas.

Rangos con nombre dinámicos

Ocultan fórmulas complejas y hacen la validación más fácil de mantener.

Limitaciones que debes conocer ⬆️

La validación de datos es potente, pero no es un sistema de seguridad absoluta.

La conclusión no es evitar Validación de Datos, sino entender su alcance. Para modelos críticos, conviene combinarla con protección de hoja, revisión de datos existentes y, si es necesario, macros o Power Query para auditoría posterior.

Validación de Datos y Formato Condicional: la pareja perfecta ⬆️

La validación impide o avisa. El formato condicional muestra. Juntas, convierten una hoja en una interfaz más clara.

Por ejemplo, una regla puede impedir fechas fuera del ejercicio, mientras el formato condicional resalta visualmente los vencimientos próximos. Otra regla puede aceptar solo “Alta”, “Media” o “Baja”, mientras el formato condicional colorea cada prioridad.

Casos reales de uso ⬆️

La Validación de Datos brilla cuando se aplica a procesos repetitivos y compartidos.

Inventario

SKU sin duplicados, stock no negativo, familias por lista, fechas de reposición válidas.

Gestión comercial

Estados de oportunidad, importes positivos, fechas de cierre, responsables seleccionados de una lista.

Recursos humanos

Fechas de incorporación, centros, departamentos, porcentajes, tipos de contrato.

Control de proyectos

Prioridad, estado, responsable, fecha límite, bloqueo de tareas cerradas.

Carga de datos previa a Power Query

Reducir errores antes de importar, transformar o consolidar datos.

Las 20 validaciones que todo usuario avanzado debería conocer ⬆️

Compendio práctico de fórmulas listas para copiar y adaptar. Las referencias suponen que la primera celda validada es A2.

Detectar celdas que no cumplen la validación ⬆️

Cuando una celda tiene una regla de Validación de datos, Excel impide (normalmente) introducir valores incorrectos. Sin embargo, existen varias formas de «saltarse» la validación:

• Pegando datos desde otra celda.
• Rellenando mediante algunas operaciones de copia.
• Desde VBA o macros.
• Porque los datos ya existían antes de crear la validación.
• Porque la validación depende de otras celdas que pueden cambiar.

Para localizar esos valores problemáticos, Excel puede rodear con un círculo rojo todas las celdas que incumplen la validación

Cómo mostrarlos

1. Ve a Datos.
2. Haz clic en la flecha de Validación de datos.
3. Selecciona Rodear datos no válidos.

Excel dibujará automáticamente un círculo rojo alrededor de todas las celdas que no cumplen las reglas de validación.

Ejemplo práctico

Supongamos que una columna solo admite edades entre 18 y 65 años.

Si aplicas Rodear datos no válidos, Excel marcará con un círculo rojo los valores 17 y 80, ya que incumplen la regla.

Cómo eliminarlos

Tienes dos posibilidades:

• Corregir el dato erróneo (el círculo desaparece automáticamente).
• Ir a Datos → Validación de datos → Borrar círculos de validación.

Cosas curiosas que muchos no saben

✅ Los círculos no modifican los datos, son solo una ayuda visual.
✅ Permiten detectar errores que se han colado mediante copiar/pegar.
✅ Desaparecen al guardar y volver a abrir el libro, por lo que están pensados para tareas de revisión temporal.
✅ No se imprimen directamente.

Consejo top

Combínalos con Formato condicional:

• La validación evita errores futuros.
• Los círculos de validación detectan errores ya existentes.
• El formato condicional puede dejar marcados permanentemente los registros problemáticos.

Es un trío muy potente cuando recibes ficheros de terceros y quieres hacer una auditoría rápida de la calidad de los datos.

Expandir las validaciones ⬆️

Una vez establecida una validación de datos en una celda es posible que deseemos que la misma regla se aplique a un conjunto de celdas.

Seleccionamos todas las celdas que deben tener la misma validación incluida la que acabamos de configurar y pulsamos Validación de datos. Dado que sólo hay una celda con validación (o, habiendo más de una, son validaciones exactamente iguales) el sistema propone la expansión de la validación por todas las celdas. Si aceptamos el mensaje, aparecerá la ventana de configuración que nos mostrará la regla. Cuando la aceptemos se propagará a todas las celdas.

¿Y si añado más celdas?

Aquí debemos diferenciar entra tablas y rangos.

Tablas

Como ya hemos visto en otros artículos, cualquier nueva fila en una tabla hereda todas las características de cada una de las celdas. Si tuvieran validación de datos, también la heredarian.

Rango

Cuando se trata de un rango deberemos diferenciar el lugar dónde se añade:

• En medio del rango: acostumbra a heredar la validación
• Al final del rango: no hereda la validación
  

Seleccionar las celdas con validación de datos ⬆️

En ocasiones necesitamos saber cuales de las celdas de nuestra hoja tienen una validación de datos dado que, a simple vista, es imposible saberlo. Para localizar esas celdas existe una forma muy rápida y poco conocida:

Seleccionar todas las celdas con validación de datos

1. Selecciona el rango que quieres revisar (o toda la hoja con Ctrl+E).
2. Pulsa F5 o Ctrl+I (Ir a).
3. Haz clic en Especial… También puede llegar desde la pestaña Inicio → Edición → Buscar y seleccionar → Ir a especial…
4. Selecciona Validación de datos.
5. Elige una de estas opciones:
   • Todas → selecciona todas las celdas que tienen alguna validación.
   • Mismas → selecciona únicamente las celdas que tienen exactamente la misma validación que la celda activa.

Truco muy útil

Si te sitúas en una celda de una lista desplegable y eliges:

Ir a especial → Validación de datos → Mismas

Excel seleccionará todas las celdas de la hoja que usan esa misma lista desplegable.

Esto resulta ideal para:

• Localizar todas las listas desplegables.
• Cambiar mensajes de error de todas ellas.
• Aplicar formatos.
• Auditar formularios complejos.

Alternativa VBA

Para seleccionar todas las celdas con validación de la hoja activa:

Cells.SpecialCells(xlCellTypeAllValidation).Select

Y para contar cuántas hay:

MsgBox Cells.SpecialCells(xlCellTypeAllValidation).Count

Un detalle curioso: xlCellTypeAllValidation es uno de los tipos de SpecialCells menos conocidos y muy útil para crear herramientas de auditoría de hojas Excel.

Conclusiones ⬆️

La Validación de Datos es una funcionalidad humilde, pero estratégica.

Su valor no está solo en mostrar desplegables, sino en convertir una hoja en un entorno de captura de datos más seguro. Una buena validación reduce errores, mejora la experiencia del usuario y hace que el análisis posterior sea más fiable.

Para usuarios avanzados, el salto de calidad llega con tres ideas: usar listas dinámicas, dominar fórmulas personalizadas y validar limpieza de texto. A partir de ahí, Excel deja de ser una simple cuadrícula y empieza a comportarse como una aplicación.