40 tipos de validación de datos que no te puedes perder en Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

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.

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.

  1. Seleccionar la celda o rango deseado. Donde aplicar la validación de datos.

  2. 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.

    Ventana de validación de datos Excel
  3. Ir a la Pestaña: Configuración.

  4. Ir a: Permitir / Seleccionar: Personalizada.

  5. Ir a: Fórmula / Escribir la fórmula deseada (considerando A1).

  • TEXTO
  1. ESCRIBIR SOLO TEXTO.

    =ESTEXTO (A1)
  2. ESCRIBIR TEXTO EN MAYÚSCULA.

    =IGUAL(A1;MAYUSC(A1))
  3. ESCRIBIR TEXTO EN MINÚSCULA.

    =IGUAL(A1;MINUSC(A1))
  4. ESCRIBIR PRIMERA LETRA DE CADA PALABRA EN MAYÚSCULA RESTO EN MINÚSCULA.

    =IGUAL(A1;NOMPROPIO(A1))
  5. ESCRIBIR SOLO CARACTERES DE A-Z, a-z.

    =ESNUMERO(SUMAPRODUCTO(HALLAR(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1);"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))
  6. ESCRIBIR VALOR QUE COMIENZA POR AA-.

    =CONTAR.SI(A1;"aa-*")
  7. ESCRIBIR VALOR QUE COMIENZA POR AA- (MAYÚSCULA).

    =IGUAL(IZQUIERDA(A1;3);"AA-")
  8. ESCRIBIR VALOR QUE FINALIZA POR -AA.

    =IGUAL(IZQUIERDA(A1;3);"AA-")
  9. ESCRIBIR VALOR QUE INCLUYA AA.

    =CONTAR.SI(A1;"*aa*")
  10. EL QUINTO CARACTER ES UNA L MAYÚSCULA.

    =IGUAL("L";EXTRAE(C7;5;1)
  11. 2 CRITERIOS, COMIENZA POR TLM E INCLUYE 6 CARACTERES.

    =Y(IZQUIERDA(A1;3)="TLM";LARGO(A1)>=6)
  12. EVITAR ESPACIOS EN CELDA.

    =A1=SUSTITUIR(A1;" ";"")
    =LARGO(A1)=LARGO(SUSTITUIR(A1;" ";""))
    =A1=ESPACIOS(A1)
  13. VALIDAR CUENTA DE EMAIL.

    =O(ESNUMERO(COINCIDIR("*@*.???";A1;0));ESNUMERO(COINCIDIR("*@*.??";A1;0)))
  14. EVITAR 2 O MÁS @.

    =SUMA(LARGO(A1)-LARGO(SUSTITUIR(A1;"@";"")))=1
  15. ESCRIBIR SOLO FÓRMULAS

    =ESFORMULA(A1)
  • NÚMEROS
  1. ESCRIBIR SOLO NÚMEROS

    =ESNUMERO(A1)
  2. ESCRIBIR SOLO NÚMEROS PARES

    =(RESIDUO(A68;2)=0)
  3. NÚMERO CON 3 DÍGITOS O MENOS

    =Y(LARGO(A1)<=3;ESNUMERO(A1))
  4. 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)=VERDADERO
  5. VALORES DE 1 A 500, O DE 700 A 799

    =O(Y(A1>=1;A1<=500);Y(A1>700;A1<=799))
  6. VALIDAR NÚMERO DE TELÉFONO (ESPAÑA)

    =Y(ESNUMERO(A1);LARGO(A1)=9)
  7. 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
  1. LIMITAR LA FECHA AL AÑO ACTUAL

    =AÑO(A1)=AÑO(HOY())
  2. LIMITAR LA FECHA AL MES ACTUAL

    =MES(A1)=MES(HOY())
  3. FECHA QUE NO SEA SÁBADO NI DOMINGO

    =Y(DIASEM(A1;11)<>6;DIASEM(A1;11)<>7)
  4. FECHA DE LUNES A VIERNES

    =Y(DIASEM(A1)<>1;DIASEM(A1)<>7)
  5. ADMITE FECHA DE HOY A 30 DÍAS

    =Y(A1>HOY();A1<=(HOY()+30))
  6. VALIDA LA FECHA DE NACIMIENTO, SEGÚN NÚMERO DE AÑOS (F1)

    =SI(A1<=(HOY()-(365*F1));VERDADERO;FALSO)
  7. VALIDA LA HORA ACTUAL

    =NSHORA(HORA(AHORA());MINUTO(AHORA());0)=A1
  • EVITAR VALORES REPETIDOS
  1. EVITAR REPETIDOS ENTRE CELDAS

    =B1<>A1
  2. EVITAR REPETIDOS EN COLUMNA

    =CONTAR.SI($A$2:$A$10;A2)=1
  3. EVITAR REPETIDOS EN FILA

    =CONTAR.SI($A$2:$F$2;A2)=1
  4. EVITAR REPETIDOS ENTRE COLUMNAS

    =CONTAR.SI($A$2:$A$9;B2)<>1
  5. EVITAR REPETIDOS ENTRE COLUMNAS SEGÚN CELDA

    =CONTAR.SI(A2;B2:B9)<>1
  6. EVITAR REPETIDOS EN RANGO

    =CONTAR.SI($A$1;$D$9:$A$2)=1
  • OTROS
  1. ESCRIBIR HOMBRE O MUJER

    =O(A1="Hombre";A1="Mujer")
  2. PROTEGER CELDA DE ESCRITURA

    =ESBLANCO(A1)
  3. ESCRIBIR SI ES FILA PAR

    =ES.PAR(A1)
  4. ESCRIBIR SI ES FILA IMPAR

    =ES.IMPAR(A1)
  5. LISTA CON RANGO DINÁMICO

    =DESREF(A2;0;0;CONTARA(A:A);1)
  6. 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)

Nivel de dificultad: Avanzado Excel Avanzado

24 comentarios en “40 tipos de validación de datos que no te puedes perder en Excel

  • 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.

  • 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.

  • 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.

  • 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.

  • 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, 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

  • 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

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.