19 de November de 2009 | 
Autor: NIVEL: AVANZADO
Descargar archivo de ejemplo. La primera hoja tiene la versión terminada, la segunda solo los datos para trabajar con este tutorial.
La Validación de datos es una herramienta extremadamente útil especialmente cuando
compartimos un archivo de Excel con diferentes usuarios y necesitamos limitar las opciones de datos que se deben ingresar en las celdas.
Por ejemplo, podemos necesitar que los usuarios completen el campo “Producto”, nos muestre una lista de los productos a elegir.
O también se busca limitar la posibilidad de cifras incorrectas.
Por ejemplo, que Excel nos muestre un mensaje de error cuando el número ingresado está fuera de los parámetros deseados.
En estos casos, Validación es la herramienta que resuelve nuestros problemas.
Vamos a utilizar el ejemplo de la derecha.
Tenemos solamente dos columnas: Productos y Ventas. Necesitamos limitar que solo puedan seleccionarse determinados productos (que pusimos en la columna D) y que las ventas sean valores enteros mayores que cero.
En Excel 2003 debemos ir al Menú “Datos” y después seleccionar “Validación …”
En Excel 2007 se hace desde la pestaña “Datos” y el menú “Validación de datos”.
PASOS
Para seguir los pasos, va a ser más fácil si descargas el archivo que acompaña este tutorial (el link está al inicio del post).
A. Validación de Productos.
1. Seleccionamos las celdas a las cuales queremos limitar la selección y que muestre el menú con las
opciones disponibles. Seleccionamos entonces las celdas A2:A14
2. En la pestaña de Datos, al oprimir “Validación de datos” se despliega un nuevo menú con tres opciones:
- Validación de datos: Es donde ingresamos los criterios y restricciones.
- Rodear con un círculo datos no válidos: Rodea con un círculo rojo los datos de las celdas a las cuales les asignamos una restricción pero que por alguna razón tienen datos que están fuera de estos rangos.
- Borrar círculos de validación: Simplemente deja de mostrar los círculos del punto anterior.
3. Seleccionamos Validación de datos. Se despliega un menú como el de la siguiente figura.
Tenemos 3 pestañas:
- Configuración: En la cual configuramos qué valores están permitidos.
- Mensaje de entrada (opcional): si queremos que nos muestre un mensaje cuando se selecciona la celda con la validación.
- Mensaje de error (opcional): Mensaje cuando se ingresa un valor fuera de los permitidos. A su vez tenemos tres Estilos para manejar los errores.
- Grave: Directamente NO deja ingresar datos fuera de los indicados.
- Advertencia: Cuando se ingresa un valor fuera de los establecidos, nos avisa de esta condición, pero da la posibilidad de ingresarlo o rechazar.
- Información: Simplemente avisa que el valor no corresponde a los asignados.
Vamos pestaña por pestaña.
4. Configuración.
Por defecto nos indica permitir “Cualquier valor”. En nuestro ejemplo, tenemos una lista específica con los productos que necesitamos que estén disponibles.
Seleccionamos la opción “Lista” lo cual nos habilita a mostrar la lista desplegable (como la que se ve en la primera imagen de este tutorial) y el origen donde se encuentran los datos disponibles.
En este caso, el Origen son las celdas =$D$2:$D$6 (debemos tener las referencias fijas si no tendremos problemas al querer copiar la validación en otras celdas).
5. Mensajes de error
Para no extenderme mucho más, simplemente pondré un Mensaje de Error en el cual indico:
- Estilo: Advertencia (como se menciona en el punto 3)
- Título (lo que se mostrará en la barra azul del mensaje): ingreso “Error de datos”
- Mensaje de error: “El producto no está dado de alta”.
ACEPTAR !!!
Y Listo! Veremos ahora cuando vamos a alguna de las celdas que acabamos de validar (de la columna A), en cuanto hacemos click aparece inmediatamente el botón característico del menú desplegable.
B. Validación de Ventas.
Los pasos son idénticos al anterior. Solo cambia el criterio. En lugar de seleccionar de una lista, debemos ingresar un rango de valores aceptables.
Seleccionadas las celdas de la columna B (B2:B14), selecciono “Número entero” (si quisiéramos un rango que permita cualquier tipo de número debemos seleccionar “Decimal”), y fijo el mínimo en cero y el máximo en cualquier número muy grande (no estamos validando en este ejemplo un máximo, así que puede ser 99999999999999).
Más adelante, publicaré la versión Avanzada en la cual definiremos una lista que se pueda ajustar automáticamente cada vez que se agrega un producto nuevo sin necesidad de cambiar el Origen de las celdas (mencionado en el punto 4).
Te recomiendo ver también el truco sobre cómo utilizar listas desplegables.
¿Te gustó este tutorial? ¿Tenés alguna recomendación o tema que te gustaría ver? No te olvides dejar tus comentarios y ayudame a dar a conocer este blog compartiendolo con tus amigos!
Buscá más trucos en: trucosdeexcel.com
Seguime por Twitter, usuario “trucosdeexcel”.
Mandá tus sugerencias o preguntas a: excelman@trucosdeexcel.com
Publicado en 
Etiquetas: 
VERY GOOD THE TRUCS OF EXCEL THANK YOU
Una consulta sobre esto:
He hecho una tabla con mucha información, de forma que en varias columnas aplico validación de datos por medio de listas desplegables. El problema está en que cuando pincho en cada celda con lista desplegable, las fuentes de esas listas son tan minúsculas que es imposible leerlas, y no se puede diferenciar cada entrada de la lista. He aumentado el tamaño de la fuente de las celdas, pero no sirve.
Tengo W.Vista.
¿Cómo hago para cambiar el formato de la lista?
Muchísimas gracias
por cierto alguien sabe como validar texto? para q solo texto pueda aparecer jaja me mato la cabeza con eso
Buena pregunta!! En las opciones de validación, vas a Permitir:Fórmula; Y en fórmula ingresas ESTEXTO(referencia celda). Ejemplo, ESTEXTO (A1).
La función ESTEXTO devuelve verdadero si el contenido es texto (y por lo tanto Validación lo acepta) y si es un número, la función devuelve FALSO y por lo tanto la validación da error.
Me imagino que debes haber cambiado el zoom para que entre toda la información en la pantalla. En este caso, Excel mantiene el tamaño de fuente de las opciones disponibles de validación que es 8 o 9.
No he encontrado aún y no sé si sea posible cambiar el tamaño.
Una solución podría ser, en lugar de cambiar el zoom, reducir el tamaño de todo el resto (ejemplo, pasarlo a 8 o 7) para que entre la información que necesitas en la pantalla sin necesidad de cambiar el zoom.
Extacto, cambié el zoom. He hecho lo que dices y mejor. Muchas Gracias!!!
buenas Yo soy una estudiante de administracion en la u y mi profe nos complica porque nos pide siempre los pasoscon el teclado lo mas reducido como pudiera hacer la validacion de datos en excel gracias y disculpen
Muy buena pregunta! Más adelante le dedicaré el tiempo que merece este tema, pero mientras tanto te adelanto que, si en Excel 2007 en español oprimes la secuencia de teclas Alt S V V, te abre el menú de validación de datos.
O, si utilizas esta función con frecuencia, tal vez te sea conveniente tenerlo en la barra de Accesso Rápido así estás a UN click de distancia.
Necesito saber como validar datos y que de como respuesta una frase. me explico es un ejercicio sobre calificaciones. si esta entre 1 y 3,9 debe aparecer la frase “Insuficiente”, si esta entre 4 y 4,9 debe aparecer la frase “Suficiente”…etc,etc.
Gracias de antemano
Gracias por tu consulta. Justamente el tutorial de esta semana trata de cómo asignar “calificaciones” según rangos de notas.
tengo una lista con los siguientes valores:
Factura
Boleta de Venta
todo está bien cuando elijo un valor de la lista
el problema viene cuando en la celda en lugar de elegir escribo
por ejemplo escribo: FACTURA
entonces me muestra error
escribo también como otro ejemplo: FacTUra
entonces también me da error
cómo hacer para que cuando escriba en la celda, ignore las mayúsculas y/o minúsculas, y si el texto escrito coincide con lo que se lee
formatee el texto tan igual como se encuentra en la lista
vale decir:
si escribo: FACTURA
luego de dar un enter aparezca en la celda: Factura
gracias
Si implica un cambio en la misma celda, requiere programar una macro, lo que excede la temática general de este blog.
Tienes funciones de texto que te pueden ayudar, pasando los valores a otras celdas, las funciones: NOMPROPIO (Deja en mayúscula solo la primera letra de cada palabra y el resto en minúscula), MAYUSC y MINUSC que respectivamente convierten a todas las letras en mayúsculas y minúsculas.
Sin embargo, me llamas la atención lo que dices porque Validación de Datos reconoce que es el mismo texto cuando se mezclan mayúsculas y minúsculas.
alguien me puede desir como poner un solo boton en la ventana de error ke aparese al principio solo kiero ke aparesca reintentar i no ke aparesca:aceptar, cancelar , ayuda
como se pueden hacer que mis datos de una hoja de calculo aparezcan en otra tabla de datos mensuales ?? de excel modificando solo la primera plantilla??????
Tengo una curiosidad con la validación de datos, uso excel 2007, el problema es…. tengo una hoja denominada Auxliar, al rango A1:A100 le asigne el nombre de TIPOS. Pues bien si al definir la validación en otra celda de otra hoja distinta del mismo libro como origen =Tipos , la validación no actua pero si el despleglable. Ahora bien si en el origen defino =Auxliar!$A$2:$A$100, funciona correctamente la validación y el despleglable. Acaso no se pueden usar nombre de rango para facilitar la tarea?, o es que hay algo que no hago correctamente.
Alguna sugerencia.
Gracias anticipadas.
hola
gracias! aqui explicar mucho mejor este tema…
seguire consultando tus tuto…
chaooooooooo
esta madre esta bien chida
hola soy carlos bautista gomez, soy contador de profesion
me consulta es:
tengo 24 columas y 8500 filas son datos de los alumnos de un colegio que estan por grados,¿como el colegio-grado?tener como respuestaq los alumnos
gracias
hola,carlos bautista gomez de peru-ucayali-atalaya t saluda
tengo 24 columas y 8500 filas son datos de los alumnos de los colegios x grado
existe un formato en la cual, con tan solo ingresar el grado y el numero del colegio, esta debe reflejar los alumnos que hay? ¿como hacer?
como incorporar un icono de ordenar alfabeticamente las celas?
hola carlos bautista desde peru-ucayali-atalaya para hacer mi consulta
tengo 24 columnas y 8000 filas, son datos de los alumnos x colegio x grado
con digitar el colegio y el grado, que se optenga los datos d elos alumnos?
gracias x su apoyo x anticipado
Hola, Tengo una hoja con muchos datos en algunas celdas aplico formulas basicas como multiplicaciones o restas. como puedo hacer para validar una celda y que cuando alquien intente introducir texto, numeros o simbolos aparezca un mensaje diciendo que en esa celda no se puede hacer NADA porque lo que va ahi es el resultado de una función.
Gracias de antemano.
Gracias Excelmam por tan valiosos áportes.
Tengo una pregunta relacionada con este ejercicio…
Tenemos ya la lista de productos, cierto, perooooo en vez de la columna ventas le colocamos precio. La idea es que cuando yo selecciono manzanas, en la celda del frete me coloque el valor de las manzanas y si selecciono naranjas, me coloque en la celda del frente el valor de las naranjas.
Nuevamente, muchas gracias
buenas tardes,
por favor ayudenme con esta novedad!
tengo una lista de 5 temas pero cada tema tiene 3 subtemas cuando hago la lista de validacion de datos por los 5 temas esta correcto!! mi consulta es! podriamos hacer una celda adicional para que cuando cojamos un tema de los 5 solo se reflejen los 3 subtemas de cada tema???
quedaria muy agradecido que me ayuden con este requerimiento.
gracias
Hola
mmmmmmmmmmmmmmmm
Hola:
mi pregunta es la siguiente:
tengo en una columna con los datos validos, digamos grupos de venta. En la columna adyacente, necesito tambien una validacion, pero no que me entregue la lista completa de los vendedores, si no solo la lista de los vendedores asignados a ese grupo de venta. Ya he usado listas desplegables dependientes en otro caso pero no se si se puede usar esta logica para el caso que planteo, ni como debiese hacerlo…
gracias
Hola,
Necesito ayuda:
Tengo una tabla en la que voy cargando datos diariamente para realizar unos seguimientos. Tengo en una columna “Maquina”, en otra “Tipo Pieza” y en otra cantidad.
Quiero que cuando seleccione de la lista de máquinas la que necesito me restrinja el tipo de pieza que pueda elegir, es decir, que si yo marco la máquina “A” y en esta máquina solo puede fabricar el Tipo de Pieza “1″ y “2″, no me deje seleccionar de la lista el Tipo de Pieza “3″ que se fabriqua en la máquina “B”. Y al revés igual, que si elijo máquina “B” sólo me deje elegir “3″ y no la “1″ y “2″.
No se si es posible realizar esto que quiero hacer.
Gracias
hola, alguien sabe si esq me piden q valide una fecha donde solo me permita ingresar datos q sean del mes de abril, pero cualkier dia o año…gracias anticipadamente..
Ah.. si saliendo un poco del tema del blog.. si alguien sabe como trabajar con la funcion buscar con un cuadro de doble entrada…
Tengo una duda, todo lo que se explica sobre la validación de datos está muy claro, pero yo tengo la lista de valores en otra hoja, e incluso en otro archivo, al querer registrarlo en origen me marca error, me urge arreglar esto, me puedes ayudar por favor.
Gracias
USO EXCEL 2003 Y TENGO UNA CELDA (D1) CUYO VALOR AUMENTA CON UNA FORMULA QUE ACUMULA EL % DE AVANCE DE VARIAS ACTIVIDADES, Y QUIERO DARLE UNA VALIDACION DE DATOS PARA QUE ESTA NO EXCEDA EL 100% SIN EMBARGO LA VALIDACION SOLO FUNCIONA CUANDO SE INTRODUCE ALGUN VALOR SUPERIOR A 100% EN LA CELDA (D1 ) POR TECLADO MAS NO LO HACE CON LA ACCION DE LA FORMULA. COMO HAGO PARA RESOLVER ESTE DILEMA???. POR FAVOR NECESITO AYUDA URGENTE. DE ANTEMANO GRACIAS.
Excelman, ante todo quisiera felicitarte por la pagina, laverdad que es muy buena. Quisiera hacerte una pregunta sobre validacion dedatos con respecto a hacer listas desplegables, quisiera saber como hacerpara que cuando escriba un nombre nuevo en la lista deplegable que ovbiamenteno esta, se agregue automaticamente al rango de datos y ovbiamente a la listadesplegable. La cosa es asi, tengo determinados productos a los cuales doyentradas y salidas y los selecciono con la lista desplegable para que nohalla errores en los datos (nombres), pero nose como hacer cuando tengo unproducto nuevo, seria, como hacer sin modificar el rango de validacion dedatos. Espero haberme explicado. Desde ya muchas gracias y disculpa la molestia.Atte. Cristian R. Sponton