Podemos crear validación de datos personalizada con fórmulas y funciones, te proponemos los 40 tipos de validación de datos que no te puedes perder en Excel.
La validación de datos permite limitar el tipo de datos que pueden escribirse en una celda.
Seleccionar la celda o rango deseado. Donde aplicar la validación de datos.
Ir a Cinta de opciones
Pestaña: Datos Grupo: Herramienta de datos Validación de datos Validación de datos 
. Mostrará la ventana de validación.
Ir a la Pestaña: Configuración.
Ir a: Permitir
Seleccionar: Personalizada.Ir a: Fórmula
Escribir la fórmula deseada (considerando A1).
- TEXTO
ESCRIBIR SOLO TEXTO.
=ESTEXTO (A1)ESCRIBIR TEXTO EN MAYÚSCULA.
=IGUAL(A1;MAYUSC(A1))ESCRIBIR TEXTO EN MINÚSCULA.
=IGUAL(A1;MINUSC(A1))ESCRIBIR PRIMERA LETRA DE CADA PALABRA EN MAYÚSCULA RESTO EN MINÚSCULA.
=IGUAL(A1;NOMPROPIO(A1))ESCRIBIR SOLO CARACTERES DE A-Z, a-z.
=ESNUMERO(SUMAPRODUCTO(HALLAR(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1);"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))ESCRIBIR VALOR QUE COMIENZA POR AA-.
=CONTAR.SI(A1;"aa-*")ESCRIBIR VALOR QUE COMIENZA POR AA- (MAYÚSCULA).
=IGUAL(IZQUIERDA(A1;3);"AA-")ESCRIBIR VALOR QUE FINALIZA POR -AA.
=IGUAL(IZQUIERDA(A1;3);"AA-")ESCRIBIR VALOR QUE INCLUYA AA.
=CONTAR.SI(A1;"*aa*")EL QUINTO CARACTER ES UNA L MAYÚSCULA.
=IGUAL("L";EXTRAE(C7;5;1)2 CRITERIOS, COMIENZA POR TLM E INCLUYE 6 CARACTERES.
=Y(IZQUIERDA(A1;3)="TLM";LARGO(A1)>=6)EVITAR ESPACIOS EN CELDA.
=A1=SUSTITUIR(A1;" ";"")=LARGO(A1)=LARGO(SUSTITUIR(A1;" ";""))=A1=ESPACIOS(A1)VALIDAR CUENTA DE EMAIL.
=O(ESNUMERO(COINCIDIR("*@*.???";A1;0));ESNUMERO(COINCIDIR("*@*.??";A1;0)))EVITAR 2 O MÁS @.
=SUMA(LARGO(A1)-LARGO(SUSTITUIR(A1;"@";"")))=1ESCRIBIR SOLO FÓRMULAS
=ESFORMULA(A1)
- NÚMEROS
ESCRIBIR SOLO NÚMEROS
=ESNUMERO(A1)ESCRIBIR SOLO NÚMEROS PARES
=(RESIDUO(A68;2)=0)NÚMERO CON 3 DÍGITOS O MENOS
=Y(LARGO(A1)<=3;ESNUMERO(A1))ESCRIBIR 3 LETRAS MAYÚSCULA Y 3 NÚMEROS
=Y(LARGO(A1)=6;IGUAL(A1;MAYUSC(A1));ESERROR(VALOR(EXTRAE(A1;1;1)));ESERROR(VALOR(EXTRAE(A1;3;1)));ESERROR(VALOR(EXTRAE(A1;4;3)))=FALSO)=VERDADEROVALORES DE 1 A 500, O DE 700 A 799
=O(Y(A1>=1;A1<=500);Y(A1>700;A1<=799))VALIDAR NÚMERO DE TELÉFONO (ESPAÑA)
=Y(ESNUMERO(A1);LARGO(A1)=9)VALIDAR NÚMERO PORCENTAJE
Permitir = Decimal; Insertar el valor como decimal: 1 para 100%, 0,5 para 50%. Escribir el número en la validación como porcentual.
- FECHA Y HORA
LIMITAR LA FECHA AL AÑO ACTUAL
=AÑO(A1)=AÑO(HOY())LIMITAR LA FECHA AL MES ACTUAL
=MES(A1)=MES(HOY())FECHA QUE NO SEA SÁBADO NI DOMINGO
=Y(DIASEM(A1;11)<>6;DIASEM(A1;11)<>7)FECHA DE LUNES A VIERNES
=Y(DIASEM(A1)<>1;DIASEM(A1)<>7)ADMITE FECHA DE HOY A 30 DÍAS
=Y(A1>HOY();A1<=(HOY()+30))VALIDA LA FECHA DE NACIMIENTO, SEGÚN NÚMERO DE AÑOS (F1)
=SI(A1<=(HOY()-(365*F1));VERDADERO;FALSO)VALIDA LA HORA ACTUAL
=NSHORA(HORA(AHORA());MINUTO(AHORA());0)=A1
- EVITAR VALORES REPETIDOS
EVITAR REPETIDOS ENTRE CELDAS
=B1<>A1EVITAR REPETIDOS EN COLUMNA
=CONTAR.SI($A$2:$A$10;A2)=1EVITAR REPETIDOS EN FILA
=CONTAR.SI($A$2:$F$2;A2)=1EVITAR REPETIDOS ENTRE COLUMNAS
=CONTAR.SI($A$2:$A$9;B2)<>1EVITAR REPETIDOS ENTRE COLUMNAS SEGÚN CELDA
=CONTAR.SI(A2;B2:B9)<>1EVITAR REPETIDOS EN RANGO
=CONTAR.SI($A$1;$D$9:$A$2)=1
- OTROS
ESCRIBIR HOMBRE O MUJER
=O(A1="Hombre";A1="Mujer")PROTEGER CELDA DE ESCRITURA
=ESBLANCO(A1)ESCRIBIR SI ES FILA PAR
=ES.PAR(A1)ESCRIBIR SI ES FILA IMPAR
=ES.IMPAR(A1)LISTA CON RANGO DINÁMICO
=DESREF(A2;0;0;CONTARA(A:A);1)SI EN A1 ESCRIBIMOS 1, LA CELDA B1 SÓLO PERMITE 1,2 O 3; SI EN A1 ESCRIBIMOS 2, LA CELDA B2 PERMITE CULQUIER VALOR
=O(Y($A$1=1;O($B$1=1;$B$1=2;$B$1=3));$A$1=2)
Excel Avanzado
muy interesante gracias
Excelente aporte muy bueno, aunque había realizado algunos este esta muy completo
Esta muy bueno, escribo estas formulas en excel 2016, y me marcan error, asi como las que tiene escritas.
Me ayudas.
Hola Oscar,
Recuerda que según el idioma que tengas deberás cambiar el punto y coma (;) por coma (,) y las dobles comillas (") por comilla simple y al contrario.
Saludos.
Intenta cambiar los ";" por ","
Muchas gracias por el excelente aporte... mil gracias..
pero parece que hay un pequeño error:
Escribir número par
=es.par(a1)
la funcion es.par, entrega un verdadero si el valor de la celda es par.... y no si la fila es par
igualmente en la funcion es.impar, entrega un verdadero si el valor de la celda es impar.
Hola Claudia,
La función ES.PAR muestra verdadero o falso en una celda, pero en validación de datos muestra el formato deseado.
Saludos.
como podría dar realizar una validación para que solo me acepte el formato: ###-#######-#
Hola Yamira,
La validación con esa condición sería muy compleja con funciones.
Aplica un formato de celda (para los guiones) y una validación para sólo números y guion.
Saludos.
Muy interesante, pero no localizo lo que necesito. Estoy intentando poner una fórmula para que, en función del dato que está en la celda anterior me aparezca un desplegable de una lista u otro. En la celda de referencia inicial pueden aparecer 7 datos diferentes, y en función de dicho dato en la celda de destino necesito que aparezca un desplegable u otro. ¿Es posible hacerlo?
Gracias de antemano.
Hola Arantxa,
Revisa el post siguiente.
Saludos.
Buenos días, muy interesante el post, tengo un problema y es que tengo un horario de clases por decir tengo el primer semestre de una carrera con sus materias y necesito que cuando le ingrese el salon y la hora de clase si se repiten estos datos me salga un mensaje o algo que me avise cuando hay cruce de horarios.
Gracias por favor necesito ayuda.
Hola Juan,
Puedes realizar un Diagrama de Gantt.
Saludos.
Como hago que en una lista desplegable que hice con validación de datos sólo se pueda seleccionar una vez.
Hola Mary Carmen,
La única solución es con macros.
Saludos.
Buenas tardes la formula 4
escribir caracteres de A-Z no funciona
Hola Luis,
Revisa la función escrita que coincida con la indicada.
Saludos.
Buen día; tal vez ustedes me puedan ayudar;
Estoy tratando de validar en varias hojas una formula Personalizada sin embargo se me presentan dos problema en el campo de Fórmula:
1. No me deja editar una formula que ya ingresé
2. Me está restringiendo la cantidad de caracteres para el ingreso de la formula, a pesar que en una hoja si se ingresó toda la formula ahora no la puedo editar y si la borro los valores me restringe los caracteres.
Hola Saul,
Necesitaríamos más detalles...
Saludos.
Buen día, espero puedan brindarme su apoyo, necesito hacer una lista desplegable desde validación de datos que venga desde una tabla de tal modo que cuando se actualice la tabla a si mismo la lista, ¿es posible?
Gracias
Hola Alex,
El valor de la lista es constante a no ser que se este obteniendo de una rango o nombre, en ese caso si se actualiza al abrir el libro o al actualizarlo.
Saludos.
Hola, me ha sido muy difícil validar datos mayores o iguales entre dos columnas.
Te copio el ejemplo
Aplique validación de datos para la Columna "2018" permitiendo solo valores mayores o iguales que el Mejor Nacional 2018 menos 10 unidades; Configure un mensaje de entrada y un mensaje de error de tipo advertencia. Active los circulo de validación e indique cuantos valores aparecen marcados como error: _______
El mejor nacional 2018 está en otra columna.
Intenté con símbolos y escribiendo MAYOR.O.IGUAL.. pero me toma hasta los nombres de las columnas
Hola María,
Aplica la validación en una celda =A2>=((fórmula del mejor nacional)-10).
Copia la celda y pegado especial como validación de datos.
Saludos.
buenas como hago para en una celda solo acepte texto pero que ni acepte ni numeros , ni letras asi: Ejemplo
pedro123 al ingresar este nombre con numero me genere error
Hola Carlos,
Aplica la opción: CARACTERES DE A-Z, a-z.
Saludos.
no me funciono...
Hola Ulises,
Sin más información no podemos ayudarte.
Saludos.
Muchas gracias! tu aporte me sirvió muchísimo para una hoja de calculo que estaba realizando, llevaba horas sin poder hacerlo, muchas gracias!
buenas tardes, un favor saben si se puede unir columnas concatenar.
Hola Javier,
Si te refieres a combinar desde la validación de datos, la respuesta es NO.
Si te refieres a combinar directa columnas, es necesario realizarlas fila a fila (copiar formato).
Si se combina entre 2 columnas (considera ambas columnas como una única celda).
Saludos.
Hola,
Que fórmula necesito para dar formato de latitud y longitud, es decir N 19º 25' 57"
Muchas gracias
Hola Adriana,
Revisa la siguiente entrada.
Saludos.
Hola! Tengo una función en VBA que me valida si una cadena es un CBU bancario válido o no, y quisiera usarla en la validación personalizada, pero no me lo permite, me da un mensaje de error "No se puede encontrar uno de los rangos especificados". No se pueden usar funciones propias en la validación o hay algo que no estoy teniendo en cuenta?
Hola Jorge,
El error te indica que la fórmula es incorrecta y está mal escrita.
Saludos.
Hola, en la validacion de datos de una hoja de calculo de google, dentro de la opcion de validacion de datos a partir de un intervalo, me gustaria tener la posibilidad de que uno de los valores estuviera en blanco (vacío), tal como aparece en excel cuanto utilizas filtros. En excel el valor lo dejo con la siguiente formula: ="" . Por lo menos me permite dejar un valor vacio, pero no asi, en spreadsheet. Cuando dejo un valor vacio en la lista de posibilidades google lo omite en el desplegable y no puedo seleccionarlo. Crees que me puedas ayudar con eso. Algun truquillo.
De ante mano, te agradezco tu respuesta.
Hola gracias por su informacion que tanto nos ayuda a los que no sabemos, quiero pedirle de favor que me ayude a comprender como puedo poner en una validación de datos 2 acciones o dos funciones, por ejemplo que la misma validación, me realice 1. solo mayúsculas y solo de una lista especifica que yo le indique, me podrá apoyar, muchas gracias.
Hola Miguel Angel,
Las 2 acciones que requieres no se pueden aplicar en la validación de datos, deberás de recurrir a macros.
Los valores de la lista los puedes escribir en mayúscula para que al seleccionar el valor los muestre así. Si permite que si el valor se escribe en minúscula sea aceptado.
Saludos.
hola necesito resolver un tema:
quiero realizar la validación de un registro tal que dicho registro,numérico, sea aceptado solo si está dentro de un rango de registros numéricos definidos por dos columnas; esto es , comienza en una columna B y termina en una columna C.
Dicho rango es consecutivo sin repetidos en la serie.
Desde ya muchas gracias por la información y la atención.
Saludos desde Argentina.
Hola Roberto,
Aplica la fórmula SI con la función Y (para las 2 condiciones).
Saludos.
buen dia
Agradezco me colaboren con una funcion en excell que me resuelva la siguiente regla
- No debe permitir mas de 2 caracteres especiales iguales consecutivos y los caracteres especiales son:
&./#°'
En total son 6 caracteres especiales
gracias y quedo atento a sus respuestas
Hola Andrés,
Aplica en la validación de datos la fórmula:
=Y(ESNUMERO(SUMAPRODUCTO(HALLAR(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1);"&./#°")));LARGO(A1)>=2)
Saludos.
Hola buenos dias
Disculpe necesito una formula que me ayude a lo siguiente:
No debo de poner numeros iguales en celdas consecutivas puedo repetir un numero las veces que sea pero que no este en la celda siguiente, que formula puedo usar?
Gracias
incorrecto:
1
22
22
Correcto
22
1
22
Hola Viviana.
Aplica una validación de datos tipo lista en el rango deseado como =A1<>A2
Saludos.
Buenas tardes
Me pueden ayudar con una pregunta
tengo una base de datos
con cuatro datos diferentes y realice una validación,
para encadenar a Primero ocupe "indirecto....el segundo no se como hacerlo Ejemplo;
Nombre Primera Segunda Tercera Cuarta
Juan A B C D
Pepe B I A D
Validación---- Lista----Origen ...Nombre
Validación---Lista .....=indirecto(Celda de nombre) hasta allí todo bien ......como enlazo el segundo para que se abra con nombre y así el tercero y cuarto
Solo con validación
gracias
Hola Walther,
Revisa la siguiente entrada.
Saludos.
Me podrían ayudar con esto: Validar el rango para que permita ingresar un código de almacén que tenga 8 caracteres, los dos primeros AR y el resto que sean números.
Funciones: Y() DERECHA() IZQUIERDA() LARGO() ESNUMERO()
VALOR() convierte un texto en numero. gracias
Hola Paulina,
Aplica la fórmula: =Y(IZQUIERDA(A1;2)="AR";LARGO(A1)=8)
Saludos.
Buenos días.
Por favor me ayudan a incluir una condición para una celda determinada: que no deje digitar fórmulas. Que solo deje digitar números. La función ESNUMERO no sirve para resolver mi necesidad, pues si el resultado de la fórmula es un número, lo deja pasar. Lo que necesito es que no permita escribir fórmulas: sólo digitar números. Gracias.
Hola Dario,
Aplica la función ESFORMULA.
Saludos.
Hola, tengo una consulta
como crear una formula para que me permite ingresar solo números que empiecen con 01
ejemplo:
solo número de telefono, pero debes anteponer 01
Hola Alex,
1. Aplica al rango de los números formato de celda fecha.
2. Aplica la validación personalizada: =CONTAR.SI(A1;"01*")
Saludos.
Hola, gracias por la información compartida.
Por favor necesitaría una ayuda de ustedes con una validación que no logro concretar: en una sección de mi hoja de excel, se introducen números reales, pero necesito que para los valores que contienen decimales, solo permita ingresar hasta un decimal.
Por ejemplo que permita 200 o 200.2 pero no 200.25
Muchas gracias
Hola Ernesto,
Aplica en el rango formato de celda tipo: número con 1 decimal.
Saludos.
Hola, necesito validar una lista con datos que solo empiezen con HZ y HX, para validar solo un dato si se puede pero cuando intento con dos me los rechaza ambos, la formula que utilizo es =IZQUIERDA(AN,2)="HZ" cuando intento poner la segunda condición no me deja, le eh intentado con contarsi, Y, encontrar y buscarv pero nada.
Gracias por el soporte
Hola Karla,
Puedes aplicar una validación con: =O(CONTAR.SI(A1;"HZ*");CONTAR.SI(A1;"HX*"))
Saludos.
Buenos días, tengo una columna que debe cumplir la condición que sean 9 caracteres y no se puedan introducir espacios. Cuando aplico la formula =Y(LARGO(C7)=LARGO(SUSTITUIR(C7;" ";""));LARGO(C7)=9) en una celda funciona perfectamente (me dice si los caracteres introducidos son "verdadero" o "falso". Ahora, cuando aplico esta misma formula en validación de datos, opción personalizada y aplico a todas las celdas, destildo "omitir blancos" me da error. Ya sea si introdujo 9 caracteres (correctos), 10, 5, con y sin espacios (incorrectos). ¿Podrías ayudarme a identificar qué estoy haciendo mal, por favor?
Hola Mercedes,
Aplica la fórmula: =Y(LARGO(A1)=9;A1=ESPACIOS(A1))
Saludos.