Validación de datos personalizada en Excel: cómo crear listas a medida

Imagina la hoja de pedidos de tu tienda: cada fila necesita un código que empiece por una letra concreta, un email de cliente que no se repita y una fecha de entrega que no caiga en domingo. La validación de datos de la cinta te deja poner listas desplegables y rangos de números, pero cuando la regla depende de lo que escribiste en otra celda necesitas la opción Personalizada, que admite una fórmula que devuelve VERDADERO o FALSO. Al terminar este tutorial sabrás crear reglas a medida con Y, O, CONTAR.SI, IZQUIERDA, HOY y BUSCARX, y entenderás por qué a veces la regla deja pasar datos malos. La opción Personalizada está en Excel desde 2007; las funciones concretas que uses dentro pueden requerir versiones posteriores, y eso lo aclaramos en cada caso.

Qué es la validación personalizada y cuándo la lista no te basta

La herramienta Validación de datos de la cinta vive en Datos > Herramientas de datos y limita lo que se puede escribir en una celda: un número entero entre dos valores, una fecha posterior a otra, un texto con una longitud máxima o un valor tomado de una lista desplegable. Esas opciones resuelven lo más común.

Cuando la regla no encaja en ninguna de las opciones prefabricadas, entra la opción Personalizada. Ahí escribes una fórmula que devuelve VERDADERO para aceptar la entrada y FALSO para rechazarla. Eso abre un abanico enorme: códigos que empiezan por una letra concreta, valores que no pueden repetirse en la columna, fechas que solo son válidas si en la celda de al lado hay un tipo de día concreto, o importes que dependen de la categoría elegida.

La fórmula se evalúa en cada cambio de la celda validada. Si devuelve VERDADERO, Excel deja la entrada; si devuelve FALSO, muestra el aviso que hayas configurado. La regla se aplica a la celda activa y, al copiarla o arrastrarla, las referencias se mueven igual que en cualquier fórmula, lo que tiene su truco y lo verás más adelante.

La ruta en la cinta y las tres pestañas del cuadro

Para llegar a la herramienta, abre la hoja donde quieres poner la regla, selecciona las celdas y entra en la cinta.

Al pulsar, se abre un cuadro con tres pestañas: Configuración (lo que se permite), Mensaje de entrada (lo que se ve al seleccionar la celda) y Mensaje de error (lo que pasa al escribir algo que no vale). En todas ellas el botón principal es Aceptar y abajo a la izquierda está Borrar todos, que elimina cualquier regla de la selección.

Configuración: Permitir > Personalizada y la fórmula

La pestaña Configuración es donde se elige la regla. La lista desplegable Permitir ofrece, entre otras, las opciones Número entero, Decimal, Lista, Fecha, Hora, Longitud de texto y Personalizada. Al elegir Personalizada, el cuadro cambia: solo queda un campo Fórmula y desaparece la lista de condiciones. Ahí escribes la expresión que devolverá VERDADERO o FALSO.

Mensaje de entrada: la pista que aparece al clicar la celda

La pestaña Mensaje de entrada define el cartelito que aparece junto a la celda cuando el usuario la selecciona. Si marcas Mostrar mensaje de entrada al seleccionar la celda, escribes un Título corto y un Mensaje de entrada más largo. Sirve para indicarle a quien rellena la hoja qué se espera ahí, sin esperar a que se equivoque.

Si desmarcas la casilla, la celda se comporta como una normal: no aparece ningún cartel al seleccionarla.

Mensaje de error: qué pasa al escribir algo que no vale

La pestaña Mensaje de error define lo que ocurre al pulsar Entrar con un valor que la fórmula rechaza. Tiene tres Estilos:

• Detener: el valor no entra y la celda vuelve al contenido anterior. Es el más estricto y el más habitual.
• Advertencia: muestra un cuadro que avisa, pero deja Aceptar y seguir con el valor, o Cancelar y volver atrás.
• Información: muestra un aviso informativo, pero el valor entra igualmente.

La versión de Excel que necesitas para usar fórmulas aquí

La opción Personalizada de la lista Permitir está en Excel desde 2007. La limitación, si la hay, viene de las funciones que escribas dentro de la fórmula: BUSCARX (XLOOKUP) existe desde Excel 2021 y Microsoft 365; en Excel 2019 o 2016 usar BUSCARX devuelve #¿NOMBRE? y la regla deja de funcionar. HOY (TODAY) y la mayoría de las funciones de texto y lógicas están en todas las versiones. Si tu Excel es antiguo, revisa la lista de funciones admitidas antes de componer la fórmula.

Las funciones que más se usan dentro de la fórmula personalizada

Las fórmulas de validación suelen construirse con un puñado de funciones. Estas son las que verás una y otra vez:

• Y (AND): =Y(cond1;cond2;...) devuelve VERDADERO solo si todas se cumplen. Sirve para exigir varias condiciones a la vez.
• O (OR): =O(cond1;cond2;...) devuelve VERDADERO si al menos una se cumple.
• NO (NOT): =NO(cond) invierte un VERDADERO en FALSO y al revés.
• LARGO (LEN): =LARGO(texto) cuenta caracteres, útil para longitudes de códigos.
• IZQUIERDA (LEFT): =IZQUIERDA(texto;n) toma los primeros n caracteres, útil para validar prefijos.
• HOY (TODAY): =HOY() devuelve la fecha actual, útil para plazos y vencimientos.
• IGUAL (EXACT): =IGUAL(texto1;texto2) compara dos textos respetando mayúsculas y minúsculas.
• REPETIR (REPT): =REPETIR(texto;veces) repite un texto, útil para patrones tipo CC-XXXX.
• BUSCARX (XLOOKUP): =BUSCARX(valor; matriz_busqueda; matriz_devolucion) busca un valor y devuelve otro de la misma fila, ideal para cruzar con una tabla de categorías.
• CONTAR.SI (COUNTIF): =CONTAR.SI(rango; criterio) cuenta cuántas veces aparece un valor, la base de las reglas anti-duplicados.

En todas, el primer argumento es la condición principal y los siguientes afinan. Combinándolas se cubre el 90 % de las validaciones a medida.

Validar un código que debe empezar por una letra y medir su longitud

Volvamos a la hoja Pedidos de tu tienda. La columna A guarda el código de cada pedido y quieres que todos empiecen por la letra C y tengan exactamente 8 caracteres: una C seguida de 7 dígitos. Vas a montar el libro con esta hoja y la regla se la aplicaremos al rango A2:A20.

La regla se apoya en dos funciones: IZQUIERDA revisa la primera letra y LARGO cuenta los caracteres. La fórmula que escribirás en el cuadro es:

=Y(IZQUIERDA(A2;1)="C";LARGO(A2)=8)

1. Selecciona el rango A2:A20 de la hoja Pedidos.
2. Abre Datos > Herramientas de datos > Validación de datos.
3. En la pestaña Configuración, elige Personalizada en la lista Permitir.
4. En el cuadro Fórmula, escribe =Y(IZQUIERDA(A2;1)="C";LARGO(A2)=8).
5. Pulsa Aceptar.

Comprobar el resultado al escribir un código bueno y uno malo

Ahora vas a probar la regla con casos reales. Mantén A2 seleccionada.

1. Escribe C0001234 y pulsa Entrar: la celda acepta el valor, porque empieza por C y tiene 8 caracteres.
2. Vuelve a seleccionar A2, escribe P0001234 y pulsa Entrar: aparece el cuadro "Código no válido" con el estilo Detener. El valor no entra y A2 conserva el contenido anterior.
3. Edita la regla (Datos > Validación de datos sobre A2:A20) y cambia la fórmula a =Y(IZQUIERDA(A2;1)="C";LARGO(A2)=6). Acepta.
4. Escribe en A2 el valor C12345: ahora la regla lo acepta porque tiene 6 caracteres y empieza por C.

Impedir que se repita un valor en una columna

La hoja Clientes guarda los datos de cada comprador y la columna B es el email. Quieres que ningún email se repita, porque cada cliente es único. La fórmula que necesitas es CONTAR.SI, que cuenta cuántas veces aparece un valor en un rango. Si el conteo es 1, el email es único; si es mayor, ya estaba.

La fórmula para B2 es:

=CONTAR.SI($B$2:$B$200;B2)=1

1. Selecciona el rango B2:B200 de la hoja Clientes.
2. Entra en Datos > Herramientas de datos > Validación de datos.
3. En Permitir, elige Personalizada.
4. En Fórmula, escribe =CONTAR.SI($B$2:$B$200;B2)=1.
5. En la pestaña Mensaje de error, deja el estilo Detener y un texto como "Este email ya está dado de alta".
6. Pulsa Aceptar.

Cuando escribas ana@correo.com en B7, CONTAR.SI cuenta 2 ocurrencias en $B$2:$B$200, la regla devuelve FALSO y Excel bloquea la entrada.

La referencia mixta que se queda fija al arrastrar

La fórmula mezcla dos referencias: $B$2:$B$200 (con dólares, absoluta) y B2 (sin dólares, relativa). La primera es el rango sobre el que CONTAR.SI cuenta: debe cubrir toda la columna válida, así que se mantiene fija al copiar la regla. La segunda es la celda que se está validando en cada fila: al copiar la regla a B3, se convierte en B3; al copiarla a B4, en B4, y así sucesivamente. Si hubieras escrito $B$2 en ambos lados, la regla siempre compararía B2 consigo misma y aceptaría cualquier duplicado en B3 o B4.

Hacer que un dato dependa de otro: solo válidos los días laborables

La hoja Citas agenda visitas y tiene la fecha en B y el tipo de día en C (L, M, X, J, V, S, D). Quieres que la fecha sea válida si cae en lunes a viernes, o si en C has escrito literalmente "Festivo" para los días no laborables. Aquí entra la función DIASEM (WEEKDAY), que devuelve el día de la semana de una fecha: con el segundo argumento 2, lunes es 1 y domingo es 7.

La regla, aplicada a B2:B30, es:

=O(DIASEM(B2;2)<6;C2="Festivo")

1. Selecciona B2:B30 en la hoja Citas.
2. Abre Datos > Herramientas de datos > Validación de datos.
3. En Permitir, elige Personalizada.
4. Escribe =O(DIASEM(B2;2)<6;C2="Festivo") en Fórmula.
5. Acepta.

La fórmula admite la fecha si cae en día laborable (DIASEM de 1 a 5) o si en C está escrito "Festivo". Si el usuario teclea un sábado sin poner "Festivo" en C, la regla devuelve FALSO y el estilo Detener bloquea la entrada.

Por qué la validación personalizada deja pasar datos malos

La validación personalizada funciona muy bien mientras la fórmula cubra el rango. Cuando algo se desajusta, la regla deja de proteger. Estos son los cuatro tropiezos más habituales con la hoja que has ido construyendo.

Pegar encima destruye la regla

Copias A2 de la hoja Pedidos y la pegas en B5 de Clientes. Al hacerlo, el contenido entra sin que la validación de Clientes lo revise, porque pegar puede saltarse la validación. Peor aún: la regla de Validación de datos sobre B5 también puede desaparecer, sustituida por la regla de Pedidos al pegar con Ctrl+V.

Para localizar los datos huérfanos, vuelve a Validación de datos sobre el rango B2:B200 y pulsa Rodear con un círculo datos no válidos. Los emails duplicados o los que no cumplen la regla aparecen marcados en rojo.

La fórmula devuelve FALSO en una celda y VERDADERO en otra

La regla se piensa siempre para la primera celda del rango. Si copiaste la fórmula a mano con referencias que no eran mixtas, la validación de B2 puede mirar $B$2:$B$200;B2, pero la de B3 puede haber quedado con $B$2:$B$200;B2 también, en lugar de B3. El resultado: la celda B3 compara el email de B3 contra el rango, pero siempre cuenta B2, así que un duplicado en B3 pasa desapercibido.

El arreglo es entrar en Validación de datos sobre cada bloque y revisar la fórmula, o borrar la regla y volver a crearla sobre el rango completo desde cero.

La función de la fórmula no existe en tu versión

Si escribes =BUSCARX(...) dentro de la validación y tu Excel es 2019, la regla entera devuelve #¿NOMBRE? y deja pasar cualquier valor. La función existe desde Excel 2021 y Microsoft 365. En versiones anteriores, sustituye BUSCARX por la combinación INDICE + COINCIDIR o por BUSCARV si el dato está a la derecha del valor buscado.

El mensaje de error es Estilo Detener y bloquea hasta corregir

Si elegiste Detener y la regla se cumple con un valor que no esperabas (por ejemplo, porque la celda de apoyo C2 está vacía y la fórmula devuelve VERDADERO por error), el usuario verá el error y no podrá seguir hasta corregir. Cambia el estilo a Advertencia mientras depuras: Excel te avisará, pero dejará pasar el valor para que compruebes qué está pasando. Cuando la regla esté afinada, vuelve a Detener para mantener la integridad.

Lo que ya sabes hacer con la validación personalizada

• La opción Personalizada de Validación de datos acepta una fórmula que devuelve VERDADERO o FALSO y se aplica a la celda o rango seleccionado.
• Las funciones Y, O y NO combinan condiciones; IZQUIERDA, LARGO y IGUAL validan textos; DIASEM y HOY validan fechas.
• CONTAR.SI con un rango absoluto y una referencia relativa es la fórmula anti-duplicados más usada.
• Las pestañas Mensaje de entrada y Mensaje de error dan pistas al usuario; el estilo Detener es el que bloquea, Advertencia e Información solo avisan.
• Pegar con Ctrl+V puede saltarse la regla; Rodear con un círculo datos no válidos localiza los valores que ya no cumplen la validación.

El siguiente paso natural es Formato condicional sobre las mismas hojas: en Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Duplicados, puedes pintar de rojo los emails repetidos de Clientes o los códigos mal formados de Pedidos, y ver de un vistazo qué filas hay que revisar después de pegar datos de otra hoja.

Datos comprobados en octubre de 2026.

Dudas al usar la validación personalizada en Excel