¿Qué es la Validación de Datos en Excel? Una Guía Completa para Añadir, Editar y Eliminar Reglas de Validación de Datos en Excel

Comprender y aplicar los principios de validación de datos es esencial para los analistas de datos noveles y los especialistas experimentados. En este tutorial, exploraremos cómo realizar la validación de datos en Excel. Cubriremos las distintas técnicas de validación de datos disponibles, y demostraremos cómo añadir, editar y eliminar reglas de validación de datos.

Si quieres repasar los conceptos básicos de Excel antes de empezar, incluidas las principales fórmulas, operadores, funciones matemáticas y mucho más, explora nuestra Hoja de trucos sobre fórmulas de Excel.

¿Qué es la validación de datos en Excel?

La validación de datos en Excel se refiere a una función específica que controla los valores que pueden entrar en una celda. Ofrece una serie de técnicas que pueden utilizarse para comprobar tanto los valores existentes como para crear reglas que se apliquen a unos.

Para encontrar la función de Validación de Datos en Excel, seguimos estos pasos:

1. Abre la pestaña Datos.
2. Ve al grupo Herramientas de Datos.
3. Pulsa el botón Validación de Datos.

Abrir la función Validación de Datos

Aparece la siguiente ventana emergente en la pantalla.

Ventana emergente de Validación de Datos

Más adelante en este tutorial, exploraremos cómo configurar esta ventana con más detalle, incluyendo cómo modificar y eliminar nuestras reglas de validación de datos. Primero, aprendamos más sobre la validación de datos. 

¿Por qué es importante la validación de datos?

La validación de datos es el proceso de comprobar la exactitud y coherencia de los datos que se han introducido o se van a introducir en una base de datos para su posterior almacenamiento y procesamiento. Con la validación de datos, nos aseguramos de que los datos se ajustan a criterios específicos y predefinidos. Estos criterios pueden incluir el tipo de datos correcto, valores dentro de un rango especificado y elementos de datos con la longitud adecuada.

Una sólida validación de datos es imprescindible por muchas razones. Protege contra los errores de introducción de datos y garantiza que la información utilizada en los análisis sea fiable y válida. Veamos con más detalle las principales razones de la validación de datos.

Calidad de los datos

La validación de datos impide que se almacenen en la base de datos valores inexactos o incoherentes. Esto reduce los posibles errores, aumenta la calidad general de los datos y mejora el sentido de su análisis.

La importancia de tener datos de alta calidad está perfectamente encapsulada en el concepto GIGO, popular en informática y que significa garbage-in y garbage-out. Esencialmente, analizar datos defectuosos conduce a resultados erróneos y engañosos.

Veamos algunos ejemplos en los que no se aplicaron las reglas de validación de datos y nuestros datos tenían valores extraños.

• Registros de la Universidad: Una universidad no puede tener un número decimal de estudiantes; sólo son válidos los números enteros.
• Historiales médicos: La temperatura corporal máxima de una persona no puede ser superior a 50°C.
• Entradas por edad: Un campo de edad sólo debe aceptar valores realistas, normalmente entre 0 y 120.
• Direcciones de correo electrónico: Un campo de correo electrónico debe seguir un formato específico, que incluye un símbolo "@". 
• Cuentas de inventario: Las cantidades de producto en un sistema de inventario no deben ser negativas.

Recuerda que, aunque la validación de los datos asegura su integridad, no garantiza su corrección absoluta. Por ejemplo, en una tabla de gatos y perros, una regla de validación puede garantizar que sólo se permiten entradas de "gato" o "perro", pero no puede evitar que un gato se etiquete como perro por error.

Integridad de los datos y cumplimiento

La validación de datos es crucial para mantener la integridad de los datos y garantizar el cumplimiento de los reglamentos y normas del sector.

En sanidad, el cumplimiento de la HIPAA es obligatorio para proteger la información de los pacientes. Del mismo modo, las empresas que operan en la UE deben adherirse al GDPR, garantizando la privacidad y seguridad de los datos de las personas. 

Limpieza y preprocesamiento de datos

Un famoso principio afirma que los analistas de datos dedican aproximadamente el 80% de su tiempo a preparar los datos para su uso y sólo el 20% al análisis en sí. Dado que los analistas de datos suelen dedicar una cantidad desproporcionada de su tiempo a limpiar y preprocesar los datos, las reglas de validación de datos aplicadas al principio del flujo de trabajo pueden ahorrar mucho tiempo.  

Toma de decisiones

El análisis de datos validados conduce a conclusiones fiables y perspicaces que, a su vez, ayudan a tomar decisiones basadas en datos más informados. A largo plazo, la validación de datos ahorra tiempo y dinero a la empresa y da lugar a un negocio más sano, rentable y eficiente.

Diferentes técnicas de validación de datos en Excel

Excel tiene diferentes enfoques de validación de datos para adaptarse a diferentes necesidades. Por comodidad, dividámoslos en grupos y caractericemos cada grupo.

Restringir el tipo de datos y el rango/longitud

En Excel, es posible seleccionar entre los siguientes tipos de datos de Excel: Whole number, Decimal, Date, Time, y Text.

Creemos ahora una tabla para considerar la aplicabilidad de determinadas condiciones lógicas a nuestros distintos tipos de datos. Las condiciones lógicas incluyen: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, y less than or equal to.

• Entre y no entre: Estas condiciones se utilizan generalmente con valores numéricos, fechas y horas. Cuando se aplica a text, between y not between se refiere a la longitud, o número de caracteres, de una celda.

• Igual a y No Igual a: Pueden aplicarse a todos los tipos de datos. En text, busca coincidencias exactas.

• Mayor que, Menor que, Mayor o igual que y Menor o igual que: Estas condiciones son adecuadas para los tipos de datos whole number, date, y time, pero no son directamente aplicables a text a menos que se tenga en cuenta la longitud del texto o, en algunos casos concretos, el orden alfabético.

Crear listas desplegables

A veces, puede ser necesario limitar las opciones de determinadas celdas de Excel a un conjunto predefinido de valores. Consideremos nuestro ejemplo anterior, en el que sólo los gatos y los perros son entradas válidas en una tabla. La mejor forma de crear una regla de validación de datos para evitar que los cocodrilos y otros animales entren en nuestra tabla es crear una lista desplegable para cada celda relevante. Exploremos los pasos detallados.

1. Introduce los elementos que quieres que aparezcan en tu lista desplegable. También puedes ordenar los elementos de la lista seleccionando toda la lista, haciendo clic con el botón derecho del ratón, seleccionando Ordenar y eligiendo un método.

Introducir los elementos de una lista desplegable Introducir los elementos de una lista desplegable

2. Convierte tu lista en una tabla de Excel. Seleccionamos cualquier celda de su rango y pulsamos Ctrl+T. Este paso es importante porque, si añades o eliminas elementos de tu lista, cualquier lista desplegable dependiente se actualizará automáticamente.

Convertir la lista de un desplegable en una tabla de Convertir la lista de un desplegable en una tabla

3. Selecciona la celda o celdas de Excel de la hoja de cálculo donde quieras añadir la lista desplegable.
4. Abre la ventana emergente Validación de Datos. Abre la pestaña Datos. Ve al grupo Herramientas de Datos y pulsa el botón Validación de Datos.
5. En la pestaña Configuración, en el campo Permitir , selecciona Lista. Deja marcada la casilla desplegable En celda. Deja marcada la casilla Ignorar espacios en blanco si la celda o celdas de Excel donde quieres añadir la lista desplegable pueden permanecer vacías; de lo contrario, desmarcala:

Establecer una lista desplegable como método de validación de datos

6. Coloca el cursor en la casilla Fuente, selecciona el rango de tu lista y pulsa OK. Tienes que omitir la fila de cabecera para evitar que sea una opción de selección.

Seleccionar la fuente de una lista desplegable en Seleccionar la fuente de una lista desplegable

7. Selecciona la celda o celdas de Excel para las que creaste esta validación de datos. Ahora verás un triángulo desplegable a la derecha de esa celda. Tras hacer clic en el triángulo, puedes seleccionar el valor que necesitas para esa celda.

Abrir una lista desplegable en una celda de .Abrir una lista desplegable en una celda

Aplicar fórmulas personalizadas

• gama: Aquí nos referimos al rango de celdas de Excel al que queremos aplicar una validación de datos personalizada basada en fórmulas.
• uppermost_cell: Aquí nos referimos a la primera celda, o celda superior, de nuestro rango.

Sólo números o texto

El objetivo de las dos fórmulas siguientes es comprobar si el contenido de una celda concreta es un número o un texto. Es útil cuando necesitas diferenciar entre valores numéricos y no numéricos, o si necesitas diferenciar entre valores textuales y no textuales, respectivamente.

=ISNUMBER(uppermost_cell)

=ISTEXT(uppermost_cell)

Sólo texto en mayúsculas o minúsculas

=AND(EXACT(uppermost_cell, UPPER(uppermost_cell)), ISTEXT(uppermost_cell))

=AND(EXACT(uppermost_cell, LOWER(uppermost_cell)), ISTEXT(uppermost_cell))

No hay duplicados

=COUNTIF(range, uppermost_cell)<=1

Sólo valores que empiecen por una subcadena específica

Las siguientes fórmulas comprueban si los primeros n caracteres de una cadena coinciden exactamente. Devolverá un valor de TRUE o FALSE. 

=EXACT(LEFT(uppermost_cell, n), substring)

Sólo días laborables o fines de semana

=WEEKDAY(uppermost_cell, 2)<=5

=WEEKDAY(uppermost_cell, 2)>5

Sólo fechas pasadas

=uppermost_cell<TODAY()

Sin espacios iniciales ni finales

=uppermost_cell=TRIM(uppermost_cell)

Sin espacios

=uppermost_cell=SUBSTITUTE(uppermost_cell, " ", "")

Evitar que se supere el total

=SUM(range)<=total_cell

Para mejorar rápidamente tu nivel de experiencia con las fórmulas en Excel, considera la posibilidad de realizar un curso de Análisis de Datos en Excel completo y orientado a la práctica.

Cómo utilizar la validación de datos en Excel

He dividido esta sección en dos subsecciones para mayor claridad. La primera subsección te mostrará cómo configurar tu libro de trabajo para incorporar reglas de validación de datos. La segunda sección explicará cómo aplicar estas reglas a las celdas que ya contienen datos. Echemos un vistazo. 

Cómo configurar un libro de Excel para reglas de validación de datos

1. Selecciona la celda o celdas de Excel en las que quieras validar los datos.
2. Abre la ventana emergente Validación de Datos. Abre la pestaña Datos. Ve al grupo Herramientas de Datos y pulsa el botón Validación de Datos.
3. En la pestaña Configuración, en el campo Permitir, selecciona el método necesario, que por defecto está establecido en Cualquier valor (lo que significa que no hay validación de datos).

Seleccionar el método necesario

4. Deja marcada la casilla Ignorar espacios en blanco si la celda o celdas de Excel en las que quieres hacer la validación de datos pueden permanecer vacías; de lo contrario, desmárcala.

Tratamiento de las celdas en blanco

5. Si habías seleccionado el método Lista, elige una regla predefinida relevante para tu tipo de datos. Si habías seleccionado el método Personalizado, introduce una fórmula personalizada.

Seleccionar una condición lógica para restringir el rango/longitud de los datos

Introducir los límites inferior y superior para restringir el rango/longitud de los datos

Aplicar una fórmula personalizada

6. En la pestaña Mensaje de entrada, puedes establecer un Título y un Mensaje de entrada. Por defecto, no hay mensaje de entrada. 

Establecer un mensaje de entrada para indicar al usuario el tipo correcto de valores

Comprobación del aspecto del mensaje de entrada

7. En la pestaña Alerta de error, puedes establecer el Estilo de un mensaje de error(Parada, Advertencia o Información), el Título y el Mensaje de error. Por defecto, el mensaje de error es del estilo Detener.

Establecer un mensaje de error

Después de hacer clic en Aceptar, puedes probar que funciona seleccionando cualquier celda vacía de Excel a la que se aplique esta validación de datos e introduciendo un valor no válido. Para comparar, aquí tienes un ejemplo de mensaje de error en cada uno de nuestros estilos. 

Obtención de un mensaje de error del estilo Stop al introducir un valor no válido

Obtención de un mensaje de error del estilo Advertencia al introducir un valor no válido

Obtención de un mensaje de error del estilo Información al introducir un valor no válido

El mensaje de error por defecto al introducir un valor no válido

Nota: Los mensajes de error de los estilos Advertencia o Información no impiden que la gente introduzca datos no válidos, pero los mensajes de error del estilo Detener sí lo hacen.

Cómo utilizar la validación de datos en Excel en las celdas ya rellenadas

¿Qué pasa si algunas de las celdas de Excel en las que quieres hacer la validación de datos ya están llenas de valores? La función de Validación de Datos permite la validación de datos tanto de entrada como de postentrada. 

1. Selecciona las celdas de Excel en las que quieras hacer la validación de datos. Algunas de esas celdas ya contendrán valores.

Seleccionar las celdas para la validación de datos, incluidas las que ya contienen valores

2. Abre la ventana emergente Validación de Datos. Abre la pestaña Datos. Ve al grupo Herramientas de Datos y abre el botón Validación de Datos. 
3. En la pestaña Configuración, en el campo Permitir, selecciona el método necesario, que por defecto está establecido en Cualquier valor.

Establecer la validación de datos para las celdas seleccionadas

3. Selecciona cualquier celda de Excel de aquellas a las que hayas aplicado esta validación de datos, abre la pestaña Datos, haz clic en la flecha desplegable situada a la derecha del botón Validación de Datos del grupo Herramientas de Datos y selecciona Círculo de datos no válidos.

Rodeando datos no válidos en Excel

Rodea con un círculo los datos no válidos en Excel

4. Ahora puedes modificar cada valor no válido para que satisfaga los criterios impuestos. Después de hacer esto, el círculo alrededor del valor desaparecerá automáticamente. Es importante recordar que si sólo eliminas un valor no válido, el círculo permanecerá.
5. Para eliminar los círculos de validación, selecciona cualquier celda de Excel de aquellas a las que hayas aplicado esta validación de datos, abre la pestaña Datos, ve al grupo Herramientas de Datos, abre el botón Validación de Datos y selecciona Borrar Círculos de Validación. 

Eliminar círculos de validación de datos no válidos

Cómo editar la validación de datos en Excel

1. Selecciona cualquier celda de Excel que ya tenga aplicada la validación de datos.
2. Abre la ventana emergente Validación de Datos. Abre la pestaña Datos. Ve al grupo Herramientas de Datos y pulsa el botón Validación de Datos.
3. Haz las modificaciones que necesites en cualquiera de las pestañas de la ventana Validación de Datos. Si es necesario, revisa las instrucciones de la sección Cómo utilizar la validación de datos en Excel.
4. Ve a la pestaña Configuración y marca Aplicar estos cambios a todas las demás celdas con la misma configuración.
5. Pulsa OK.

Aplicar las modificaciones de validación de datos a todas las demás celdas con la misma configuración

Cómo eliminar la validación de datos en Excel

1. Selecciona cualquier celda de Excel que ya tenga aplicada la validación de datos.
2. Abre la ventana emergente Validación de Datos. Abre la pestaña Datos. Ve al grupo Herramientas de Datos y pulsa el botón Validación de Datos.
3. Ve a la pestaña Configuración y marca Aplicar estos cambios a todas las demás celdas con la misma configuración.
4. Pulsa el botón Borrar todo .
5. Pulsa OK.

Eliminar la validación de datos

Conclusión

Este tutorial trata sobre la definición de validación de datos, sus ventajas, su implementación en Excel, las distintas técnicas disponibles y cómo utilizar, editar y eliminar la validación de datos en Excel.

Si quieres liberar toda la potencia de Excel y aprender a preparar, visualizar y analizar datos mediante ejercicios prácticos, así como descubrir algunas funciones avanzadas, dale una oportunidad a un curso completo y apto para principiantes Fundamentos de Excel. Considera también cursos especializados como Preparación de datos en Excel o Modelización financiera en Excel, que ofrecen formación específica para profundizar en tus conocimientos. Estos recursos te ayudarán a progresar desde la validación básica de datos hasta el dominio de las funciones avanzadas de Excel.