La validación de datos es una funcionalidad de Excel que te permitirá establecer limitaciones sobre los tipos de datos así como sobre los valores a introducir en una celda determinada.
Tendrás el poder de obligar a un usuario de tu hoja de cálculo a introducir un valor correcto para las necesidades que persigue la hoja. Esto evitará infinidad de errores no deseados y aumentará notablemente la productividad del usuario de la hoja.
Cuando creas una nueva hoja de cálculo, podrás introducir cualquier tipo de dato con cualquier valor en las celdas de la misma. Esto está bien en muchos casos. Sin embargo, al utilizar esos datos con determinadas funciones o cálculos te puedes encontrar con problemas si los datos que utilizas no son del tipo adecuado, o no tienen los valores necesarios para alcanzar la solución del problema que te planteas.
Si te dedicas a desarrollar hojas de cálculos para otros usuarios, esta funcionalidad de Excel te será de vital ayuda. Créeme. Los usuarios por desconocimiento o incluso por "ganas de molestar", si les dejas la posibilidad de cometer un error al introducir un dato para realizar un cálculo, lo cometerán.
En este artículo, te mostraré las diferentes posibilidades que te ofrece Excel para obligar al usuario a introducir un dato de un determinado tipo y con un determinado valor.
Navegación por el artículo
Para hacerte mucho más fácil tu aventura por este artículo, tienes a continuación los enlaces a cada apartado para que consultes aquello que necesites y puedas saltarte lo que ya dominas. De todas formas, échale un vistazo a todo porque siempre pongo trucos y explico cosas que puede que no sepas aún.
¿Cómo proceder a validar datos en Excel?
Ya sabes cuál es el objetivo de utilizar validaciones de datos ahora te voy a mostrar cómo hacerlo.
Encontrarás todas las opciones de validación de datos en el grupo de herramientas de datos
La opción de este menú que debes escoger es muy evidente pues tiene ese mismo nombre. Validación de datos. Pulsando en este menú accederás al cuadro de diálogo de idéntico nombre que puedes ver a continuación:
Como ya te comenté antes, las celdas, por defecto, permiten cualquier valor. Esto queda reflejado en la imagen anterior, al abrir este cuadro de diálogo con el foco activo en cualquier celda de tu hoja de cálculo.
Dependiendo del criterio de validación que selecciones en el desplegable estarás estableciendo limitaciones al tipo de dato que se puede introducir en la celda. Cambiando este criterio observarás como también se modifica el aspecto de este cuadro de diálogo para ofrecerte las diferentes posibilidades de validación que tiene la opción seleccionada.
A continuación puedes ver todas las opciones. Haz clic sobre las imágenes para ampliar y ver con más detalle:
Validación de datos numéricos.
Para validar datos numéricos tienes dos opciones: Números enteros y decimales.
En todos los tipos de validaciones de datos encontrarás junto a la caja donde seleccionas el criterio un check box con el título Omitir blancos.
Cuando entras en modo edición sobre una celda con validación de datos, este check box marca la diferencia entre permitir al usuario dejar la celda en blanco pulsando Enter (check seleccionado) u obligarle a introducir algún dato o salir del modo edición pulsando Esc (check no seleccionado).
Para introducir la validación de datos en el caso de datos numéricos debes seleccionar el método de validación a procesar en el desplegable etiquetado como Datos, tal y como puedes ver en la siguiente imagen. Tanto para Número entero como para Decimal las opciones son iguales:
Sólo para las dos primeras opciones; entre y no está entre; obtendrás dos cajas donde introducir los valores mínimo y máximo entre los que quieres acotar tu validación.
El resto de las opciones sólo dispondrán de una caja que mostrará una etiqueta como Valor, Máximo o Mínimo en la que deberás introducir el valor que va a validar la celda.
EJEMPLO
Así, si deseas que en una celda sólo se puedan introducir número enteros positivos ó 0, deberías realizar la siguiente configuración en el cuadro de diálogo:
Mientras en la celda introduzcas valores enteros positivos o cero las cosas irán bien. Si intentas introducir texto, números negativos o números decimales (aunque sean positivos) obtendrás un mensaje de error genérico como el que te muestro a continuación:
Este mensaje de error lo puedes personalizar para ofrecer al usuario una información más precisa sobre el error que está cometiendo y ayudarlo a introducir una dato correcto.
Para modificar el mensaje de error, en el cuadro de diálogo de validación de datos, debes seleccionar la solapa de Mensaje de error.
Cuando pulses sobre ella, accederás a las siguientes opciones:
Lo primero que te encuentras es un check box que te permitirá decidir si deseas que se muestre mensaje de error o no. Si decides desmarcarlo, si introduces un valor no permitido el contenido de la celda se mostrará como puedes ver en la siguiente imagen:
Lo habitual es que quieras mostrar un mensaje que ayude al usuario que está trabajando con la hoja de cálculo. Por lo tanto, deberías configurar el mensaje de error para que ofrezca información que lleve al usuario a no cometer más errores en la introducción de datos. Una opción, para el caso que te estoy mostrando podría ser la siguiente:
En el apartado de Estilo puedes seleccionar la forma de comunicar la información de error. Puedes elegir entre una señal de Detención, Advertencia o Información.
Detener: Para evitar que se introduzcan datos no válidos en la celda.
Adverencia: Indica a los usuarios que han introducido un dato no válido. pero les permite introducirlos.
Información: Para informar a los usuarios que ha introducido datos no válidos, pero les permite escribirlos.
En el apartado Título deberás introducir la información que deberá aparecer en la barra de título del mensaje de error.
En Mensaje de error debes introducir la información que ayudará al usuario a solucionar el problema que tiene a la hora de introducir los datos.
El mensaje configurado se mostraría así:
Puedes prevenir y orientar al usuario de tus hojas de cálculo para que no cometa errores al introducir datos en una celda con validación de datos. Para ello puedes utilizar la opción Mensaje de entrada.
Para configurarlo debes acceder a la solapa de igual nombre que te señalo en la siguiente imagen:
Accederás a las opciones de configuración del mensaje de entrada:
Al igual que con el mensaje de error, en el mensaje de entrada tienes un check box para seleccionar si deseas mostrar este mensaje o no. Por defecto, viene marcado.
De forma similar al mensaje de error, aquí tendrás que configurar el campo Título para indicar el contenido de la barra de título del mensaje de entrada y en contenido del mismo los dispones en Mensaje de entrada.
Cuando lo tengas configurado y pongas el foco sobre la celda con validación observarás que aparece sobreimpresionado el mensaje de entrada que introdujiste. En nuestro ejemplo podría ser así:
Validando contra referencias
En el ejemplo que te he mostrado valido el contenido de la celda para números enteros mayor o igual que 0. En este caso el valor es introducido manualmente.
En lugar de introducir un valor numérico con límite de las validaciones puedes utilizar referencias a otras celdas cuyos datos funcionarán como valores límites de tu validación.
En este ejemplo utilizo referencias a las celdas A1 y A2 para establecer los límites de la validación.
Lo más interesante de utilizar referencias en las validaciones es que no te encuentras limitado a unos valores fijos que, si deseas modificar, deberás cambiar nuevamente en el cuadro de diálogo de validaciones.
Ahora, los límites de la validación se modifican cambiando el contenido de los datos de las celdas referenciadas. En este caso A1 y A2.
De igual forma, puedes utilizar nombres definidos como valores para establecer límites en validaciones. Si no sabes como definir nombres te recomiendo este artículo en el que aprenderás como asignar nombres a celdas y rangos de celdas. Definir nombres en Excel.
Tanto si utilizas referencias, como si usas nombres, para referenciar las celdas donde se encuentran los límites de tus validaciones, no olvides el signo "=" delante de la referencia o el nombre de celda o rango, cuando lo introduzcas en el cuadro de diálogo de validación.
Validando fechas y horas
En el caso de querer validar fechas y horas, la mecánica es idéntica a la utilizada en datos numéricos (enteros y decimales)
La diferencia estriba sólo en el tipo de dato que estás validando. Te asegurarás que el usuario introduzca una fecha o una hora correcta tanto en el tipo de dato como en el valor del mismo si le impones alguna validación específica.
Voy a aprovechar este apartado para demostrarte que también puedes utilizar funciones para establecer los valores que van a especificar tu validación.
EJEMPLO
Imagina que quieres obligar a un usuario a introducir una fecha entre el comienzo del año actual y la fecha de hoy, de forma que el usuario no pueda introducir ninguna fecha anterior al 1 de enero, ni posterior al día actual.
En la imagen anterior puedes observar como he configurado la validación de la celda C5. En esta celda sólo se podrá introducir una fecha válida entre "inicio" y HOY().
- "inicio" es un nombre definido para la celda A1. De esta forma, cambiando su contenido estarás modificando el límite inferior del rango de fechas permitidas. Ahora mismo situada en 1/1/2016.
- HOY() es una función de Excel que devuelve la fecha actual del sistema. Si no tienes ningún problema con la fecha del sistema debería ser la de hoy mismo. Así, si intentas introducir una fecha posterior a hoy pues obtendrás el pertinente mensaje de error.
Validación de longitud del texto
En algunas ocasiones te puedes ver en la necesidad de limitar la cantidad de caracteres que un usuario puede introducir en una celda que va a contener texto.
Para solucionarlo tienes la opción de validación de longitud de texto.
Las condiciones de limitación son idénticas a las vistas hasta ahora con datos numéricos, fechas y horas, por lo que no tendrás mayor problema para configurarlo. La única diferencia estriba en que ahora limitarás el número de caracteres.
Puede ser muy útil cuando necesitas tener cuidado con las longitudes de los textos en las celdas que posteriormente van a ser exportadas a una base de datos donde los campos tienen limitaciones de tamaño.
Validación de lista
La validación de datos por medio de una lista permite un ahorro significativo de tiempo a la hora de introducir los datos en las celdas validadas. Para ello, puedes programar la celda para que muestre una lista desplegable que el usuario puede activar para seleccionar el valor adecuado.
Esto, además del evidente ahorro de tiempo que significa para el usuario que rellena la celda, a ti como propietario de la hoja de cálculo te asegura que el dato escrito se haga de una forma concreta. Esto es vital a la hora de realizar otras actividades con tu hoja de cálculo como puede ser las búsquedas. Yo siempre utilizo validaciones de lista en aquellas celdas que voy a utilizar como argumento "Valor buscado" en la función BUSCARV.
Puedes revisar el artículo que escribí hace algunas semanas sobre BUSCARV y la sincronización de datos en tus hojas de cálculo con Internet.
Cuando seleccionas lista en el cuadro de diálogo validación de datos el cuadro te mostrará las siguientes opciones, tal y como puedes observar en esta imagen:
El check box que indica celda con lista desplegable, te permitirá crear el desplegable en la celda mostrándote los datos que introduzcas en Origen.
EJEMPLO
Imagina que quieres ayudar a un usuario a introducir los nombres de los agentes comerciales de su empresa, de forma que no tenga que introducirlos, sino seleccionarlos del desplegable. La configuración de la celda con la validación tipo lista quedaría tal que así:
Como puedes observar he introducido toda la lista en el campo Origen y he separado cada nombre del siguiente con un punto y coma.
NOTA: Es conveniente que cuando ofrezcas listas con nombres, éstas se encuentren ordenadas alfabéticamente para facilitar al usuario la localización del nombre que está buscando. Tanto más importante, cuanto más grande es la lista de validación.
El resultado en la celda de la hoja de cálculo se parecerá bastante al de la siguiente imagen.
Como puedes ver en la imagen anterior, ayudas al usuario en la introducción de datos pues tan solo tiene que seleccionar un elemento de la lista. Además, te aseguras que ese nombre esté bien escrito, tal y como ha sido introducido en el campo origen de la lista. Ahora puedes utilizar los contenidos de todas estas celdas con seguridad en los argumentos de cualquier función. No habrá sorpresas con espacios o nombres incorrectamente introducidos por el usuario.
El usuario también podrá introducir datos en la celda pero obtendrá el mensaje de error de validación en aquellas ocasiones en las que no introduzca alguno de los elementos de la lista tal y como se escribieron en Origen. Distingue entre mayúsculas y minúsculas así que tendrás que escribirlo exactamente igual.
Puedes colocar los datos de tu lista en cualquier rango de celdas de tu libro de Excel y asignarle un nombre. Para ver como definir nombres a rangos de celdas en Excel pulsa aquí.
En la siguiente imagen puedes observar una validación con lista desplegable a un rango definido con el nombre Estado_civil y que contiene las opciones Casado/a, Separado/a, Soltero/a, Viudo/a. Como puedes ver el nombre del rango se coloca en el Origen tras el símbolo igual.
Por supuesto, siempre puedes utilizar rangos o celdas como datos de validación a establecer en el campo Origen utilizando para ello las referencias a esas celdas. No olvides asegurarte que quedan introducidas como referencias absolutas para evitar que se desplacen si copias o rellenas estas celdas validadas a otra parte.
Si colocas el rango con los datos en otra hoja, debes acordarte de introducir la referencia completa incluyendo el nombre de la hoja también. Para evitar esto, yo suelo definir nombres con alcance de libro o globales como me gusta llamarlos. Así, los puedo utilizar en cualquier hoja de mi libro.
Validación personalizada
Te puedes encontrar en situaciones en las que no puedas validar los datos con ninguna de las opciones que te he explicado hasta ahora. Para solucionarlo Excel te da la opción de utilizar validaciones personalizadas.
Para introducir una validación de datos personalizada, debes seleccionar esta opción en el cuadro Permitir, tal y como te muestro en la siguiente imagen:
Como puedes observar, el desplegable para elegir el tipo de datos queda inhabilitado. Sólo podrás utilizar la caja correspondiente a Fórmula. Es este sitio deberás introducir la fórmula que validará los datos que se introducirán en la celda
EJEMPLO
En este ejemplo vamos a controlar los valores de una celda para que cumplan que sean números pares y mayores o igual a 10. La configuración de la celda con la validación Personalizada quedaría tal que así:
Voy a explicar un poco la fórmula por si tuvieras algún problema para comprenderla:
La función Y devuelve un VERDADERO siempre que todos sus argumentos sean verdaderos y FALSO en el caso de que cualquiera de ellos no se cumpla. En el ejemplo tienes dos argumentos:
- ES.PAR(B5): En este caso, la función devuelve verdadero si el dato introducido en la celda B5 es par y falso si no lo es. La celda B5 es la propia celda en la que estamos haciendo la validación. Así se verifica el dato introducido en la celda validada.
- B5>=10: Esta comparación permitir verificar si el dato introducido en la celda B5 es mayor o igual a 10 devolviendo verdadero si se cumple y falso si no lo hace.
Al tener estos dos argumentos en la función Y, pues sólo permitirá escribir datos que sean pares y mayores o iguales a 10. En caso de que no se cumpla alguno de los dos argumentos te aparecerá el mensaje de error pertinente.
Localizar celdas con validación de datos
Existe una forma rápida para marcar todas las celdas que tienen algún tipo de validación de datos.
Esta funcionalidad es muy útil cuando tienes una hoja de cálculo que nos has creado tú y desconoce su funcionamiento. Así, puedes descubrir todas las celdas de la hoja que tienen algún tipo de validación programada.
para ello debes hacer los siguiente:
- En la solapa Inicio desplegar Buscar y seleccionar en el grupo de herramientas Modificar.
- Seleccionar la opción Ir a...
- Dentro del cuadro de diálogo pulsar sobre el botón Especial.
- Marcar la opción Celdas con validación de datos y pulsar Aceptar.
Observarás como se sombrean todas las celdas que tengan alguna validación en la hoja de cálculo.
Aplicar validación de datos a celdas con datos ya escritos
Te preguntarás que ocurre si estableces validación a una celda que contiene un valor no permitido por la propia validación. Y si no te lo preguntabas pues yo te lo cuento.
Pues no pasará nada. El dato no válido permanecerá en la celda y no se mostrará ningún tipo de mensaje.
Para localizar todas las celdas en la que suceda este hecho con el objetivo de cambiar esos datos erróneos debes utilizar la opción Rodear con un círculo datos no válidos del desplegable Validación de datos del grupo de Herramientas de datos en la solapa datos.
Un vez seleccionada esta opción, todas aquellas celdas con datos no válidos para sus validaciones de datos aparecerán rodeados con un círculo rojo como se muestra en la siguiente imagen:
En cuanto modifiques el dato no válido, que cumpla con los criterios de validación establecidos para esa celda, el círculo rojo desaparecerá por sí solo.
Si finalmente no vas a cambiar los datos no válidos y quieres ocultar los círculos rojos debes escoger la última opción del desplegable indicado Borrar círculos de validación.
Eliminar una validación de datos
Es muy posible que en un momento determinado tengas las necesidad de quitar la validación de una celda.
Para ello debes seleccionar la celda y abrir el cuadro de diálogo de validación de datos.
La validación de datos desaparecerá en el momento que pulses sobre el botón Borrar todos.
Lo que ocurrirá es que el cuadro de diálogo de validación de datos volverá a su estado general cuando no hay validaciones y mostrará en Permitir la opción Cualquier valor.
¿Conocías las validaciones de datos?
¿Qué te ha parecido este artículo?
¿Puedes añadir algún ejemplo que te parezca interesante mostrar a los lectores de tusitioexcel.com. ¡Participa en los comentario!
¿Habías utilizado alguna vez las validaciones de datos?
Necesito tu feedback para mejorar el artículo y los que vendrán de aquí en adelante. Utiliza la siguiente sección de comentarios para contar todo lo que consideres oportuno.
Autor José R. Ansó
Ingeniero Téc. Telecomunicaciones de profesión y formador de vocación. Fascinado por las hojas de cálculo. Puedes conectar conmigo en:
Hola!
Disculpa.. necesito hacer la validación de una celda que contiene una suma.. cuando el valor de la suma sea menor a 70 no hay problema, pero si es mayor quiero que mande una alerta para poder modificar las celdas que se suman, como podría hacerlo?
De antemano, gracias, buen día!
Hola Maricela. Bienvenida
En el caso que me planteas puedes utilizar el formato condicional, por ejemplo cambiando el fondo de la celda de la fórmula a color rojo cuando se cumpla que el contenido de la celda, es decir, el resultado de la suma sea superior a 70.
Saludos.
es buenísimo ya que desconocía completamente esa función y muchas otras, espero contar con mas ejemplos para así ir practicando, quiero hacerle una pregunta ese ebock dice que e gratis el libro que valor tiene digo en Bolívares, ya que tengo y no tengo tarjeta de crédito, ya que no la tengo afiliada a cadivi cosa que es imposible para muchos de a pie si no estamos enroscados
gracias por su atención espero poder adquirir este libro y poner así mas en practica excel para pulirme un poco mas
Hola de nuevo Edgar.
Como te puse en el anterior comentario, el ebook es totalmente gratuito.
Espero que lo disfrutes.
Saludos.
Hola Edgar.
Me alegro mucho de que te haya gustado.
El ebook es totalmente gratuito. Tan solo tienes que seguir las instrucciones para descargarlo.
Saludos.
COMO LIMITAR CELDAS CON UN VALOR ESPECÍFICO POR EJEMPLO TENGO EN UNA COLUMNA QUE AGREGAR NUMEROS ENTRE 1 Y 5 DONDE SOLO CINCO CELDAS PUEDEN TENER EL 5, SOLO 5 EL 4, SOLO 5 EL 1 Y SOLO 5 EL DOS, LOS RESTANTES SOLO NUMERO 3
Hola Fernanda. Gracias por participar.
No alcanzo a ver lo que necesitas.¿Podrías ser un poco más explícita?
Saludos
Hola. Es posible configurar la lista desplegable de forma que el botón con la flecha aparezca siempre y no sólo cuando hago click sobre la celda?
Gracias
Buenos días, Benjamín. Bienvenido.
Esta es una cuestión que nunca me había planteado.
Hasta donde yo sé, no se puede. El botón con la flecha aparece justo en la celda que se encuentra a su derecha y para poder visionarlo, la celda con validación debe ser la celda activa.
Para destacarlas, por ejemplo, te recomendaría utilizar un color de relleno que las diferencie del resto.
Saludos y muchas gracias por participar.
Buenas tardes, podria crear una regla de validación para que en una columna se pudieran introducir unicamente cifras con dos decimales maximo. ????
Gracias de antemano y enhorabuena por la web.
Hola Mariluz. Bienvenida.
En el caso que me indicas yo no utilizaría validaciones. En su lugar usaría los formatos personalizados.
Te pongo a continuación un enlace a un artículo muy detallado en el que podrás encontrar lo que buscas.
https://www.tusitioexcel.com/basico/formatos-personalizados-excel/
Gracias por participar.
Saludos
¿Cómo puedo hacer una validación que cumpla con estos requisitos: Deberá iniciar el código forzosamente con las letras HZ (Ejemplo HZ-001, HZ123, HZ 01).?
Hola Estefanía, bienvenida y gracias por participar.
Seguro que existe muchas variantes posibles para solucionar tu problema.
A primera vista se me ocurre hacer una validación personalizada con la siguientes fórmula:
=SI(HALLAR(“HZ”;B7)<>1;FALSO();VERDADERO())
En ella la función SI devuelve verdadero sólo cuando en la celda de validación (B7 en este ejemplo) empieze con la cadena “HZ”, de lo contrario devuelve falso y por tanto no cumple con la validación.
Espero que te sirva.
Saludos.
Muchas gracias, lo intentaré!
Un placer Estefanía.
Pásate por aquí y coméntanos como te fue.
Saludos y gracias por participar.
Buenas Tardes, digamos que tengo una lista de 5 opciones en una celda y quiero que en otra celda me permita escribir solo cuando coloque dos de ellas. Cómo hará la validación?
De ante mano gracias y espero tengas la respuesta.
Hola Antonio, bienvenido.
Gracias a ti por participar.
En cuanto a la cuestión que planteas con esta funcionalidad de Excel no es posible hacerla, pues si validas contra la lista te permitirá cualquier valor de la misma y no algunos de ellos solamente.
Si me dieras más detalles de lo que quieres hacer quizás podría orientarte como conseguirlo.
Saludos.
Explicación muy buena, breve y concisa. Gracias.
Muchas gracias a ti por tu comentario.
Saludos y bienvenido.
Excelente
Muchas gracias Wilson y bienvenido.