Validación de datos y listas desplegables en Excel

El problema que nadie admite: cada quien escribe lo que quiere

Abra cualquier archivo de Excel que hayan tocado cinco personas distintas. Fíjese en la columna de ciudad. ¿Qué ve? “Bogotá”, “bogota”, “BOGOTÁ”, “Bogota D.C.”, “Bogota ” (con un espacio al final, el clásico invisible). Cinco formas de escribir lo mismo. Y ahora intente hacer una tabla dinámica con eso. Buena suerte.

La verdad es que la mayoría de los desastres en Excel no los causan las fórmulas complicadas. Los causa la digitación libre. Gente bien intencionada escribiendo “Medelin” un lunes a las 6 de la tarde. ¿Le suena familiar?

La validación de datos existe para eso: ponerle barandas a las celdas. Usted decide qué se puede escribir y qué no. Y la lista desplegable convierte la digitación en un simple clic. Cero errores de ortografía. Vamos a dominarla.

Qué es la validación de datos (y por qué le va a salvar la vida)

La validación de datos es una regla que usted le pone a una celda o a un rango: “aquí solo acepto esto”. Puede ser una lista de opciones, un número entre dos valores, una fecha dentro de un rango, un texto de cierta longitud. Si alguien intenta meter algo distinto, Excel lo frena en seco con un mensaje de error.

A mi juicio, esta es una de las funciones más subestimadas de Excel. Todo el mundo aprende SUMA y BUSCARV, pero casi nadie valida sus datos de entrada. Y después se preguntan por qué los reportes no cuadran. El dato malo entra por la puerta de adelante, con permiso y todo.

¿Dónde vive esta herramienta? En la cinta de opciones: Datos > Validación de datos. Ahí se abre un cuadro de diálogo con tres pestañas: Configuración, Mensaje de entrada y Mensaje de error. Las tres importan. Ya verá por qué.

Su primera lista desplegable en cinco pasos

Empecemos con lo básico. Suponga que tiene una columna “Ciudad” y quiere que solo se pueda elegir entre Bogotá, Medellín, Cali, Barranquilla y Bucaramanga.

1. Seleccione las celdas donde irá la lista (por ejemplo, B2:B50).
2. Vaya a Datos > Validación de datos.
3. En “Permitir”, elija Lista.
4. En “Origen”, escriba las opciones separadas por punto y coma: Bogotá;Medellín;Cali;Barranquilla;Bucaramanga. (Ojo: en algunas configuraciones regionales el separador es la coma. Si le da error, pruebe con coma.)
5. Acepte. Listo: cada celda ahora muestra una flechita con sus opciones.

De ahora en adelante, nadie podrá escribir “Medelin”: Excel no lo permite y muestra el mensaje de error. Paz mental en una flechita.

Un detalle importante: si su lista es larga —digamos, los 32 departamentos de Colombia más el distrito capital— no la escriba a mano en el cuadro de “Origen”. Mejor póngala en una columna aparte de la hoja (o en otra hoja) y en “Origen” seleccione ese rango, por ejemplo =Ciudades!$A$2:$A$34. Así la lista queda documentada, visible y fácil de editar.

Listas que se actualizan solas (el truco de los profesionales)

Aquí viene la primera mejora seria. Si su lista de opciones crece con el tiempo —nuevos productos, nuevos clientes, nuevas sedes—, no quiere estar editando el rango a mano cada mes. La solución: convertir su lista en Tabla (selecciónela y pulse Ctrl + T).

Cuando la fuente de la validación apunta a una columna de tabla, por ejemplo =Productos[Nombre], la lista desplegable se actualiza sola cada vez que usted agrega una fila a la tabla. Agrega “Silla ejecutiva negra” a la tabla de productos y, de una, aparece en el desplegable. Sin tocar la validación.

Listas dependientes: el desplegable que piensa

Ahora subamos de nivel. ¿Qué pasa si tiene dos columnas: “Departamento” y “Ciudad”, y quiere que al elegir “Antioquia” el segundo desplegable solo muestre ciudades de Antioquia? Eso se llama lista dependiente (o en cascada), y es más fácil de lo que parece.

El método clásico usa la función INDIRECTO. Funciona así:

1. Cree rangos con nombre para cada grupo de ciudades. Seleccione las ciudades de Antioquia y nómbrelas Antioquia (Fórmulas > Definir nombre). Haga lo mismo con Cundinamarca, Valle_del_Cauca, etc. (Sin espacios ni tildes en los nombres, por favor: Excel no los acepta en nombres de rango.)
2. En la columna Departamento, ponga una validación de lista normal con los departamentos.
3. En la columna Ciudad, ponga validación de lista con origen =INDIRECTO(A2), donde A2 es la celda del departamento.

Cambia el departamento y la lista de ciudades cambia sola. Así de directo.

Ojo: INDIRECTO es una función volátil, o sea que se recalcula con cualquier cambio en la hoja. En archivos pequeños no pasa nada; en archivos enormes con cientos de INDIRECTOs, la lentitud se nota.

Más allá de las listas: validar números, fechas y texto

Las listas son lo más visible, pero la validación de datos hace mucho más. En “Permitir” tiene estas opciones:

• Número entero / Decimal: perfecto para cantidades y precios. Puede exigir, por ejemplo, que el valor esté entre 1 y 1000. Adiós a la factura de “-5 unidades”.
• Fecha: exija fechas dentro de un rango, como “entre el 1 de enero y el 31 de diciembre de 2026”. Ideal para reportes de un período.
• Longitud del texto: útil para códigos. Si sus códigos de producto siempre tienen 8 caracteres, ponga “longitud igual a 8” y se acabó el código mocho.
• Personalizada: aquí puede meter una fórmula. Por ejemplo, =ESNUMERO(A2) o algo más elaborado como =Y(A2>0;A2<100). Es la puerta a validaciones a medida.

Ejemplo real: una planilla de gastos donde “Valor” debe ser un decimal mayor que cero. Configure: Permitir = Decimal, Datos = mayor que, Mínimo = 0. Simple. ¿Cuántas planillas ha visto usted sin esa regla? Yo, demasiadas.

Los mensajes: la diferencia entre frustrar y guiar

Recuerde las tres pestañas del cuadro de validación. La mayoría solo usa “Configuración” e ignora las otras dos. Error.

Mensaje de entrada: aparece al seleccionar la celda, antes de escribir nada. Úselo como instrucción amable: “Elija la ciudad de la lista desplegable”. Evita errores antes de que pasen.

Mensaje de error: aparece cuando alguien intenta meter un dato inválido. No deje el mensaje genérico de Excel (“El valor no coincide con las restricciones…”), que suena a regaño de sistema. Escriba algo humano: “Esa ciudad no está en la lista. Use la flecha desplegable para elegir una opción válida.” Sus usuarios —o su yo del futuro— se lo agradecerán.

También elige el estilo de la alerta: Detener (bloquea del todo), Advertencia (pregunta pero deja continuar) o Información (solo avisa). Para datos críticos, Detener; si hay excepciones legítimas de vez en cuando, Advertencia.

Los tres pecados que rompen la validación (y cómo evitarlos)

La validación de datos tiene enemigos. Conózcalos:

1. Pegar valores encima. Este es el asesino número uno. Si alguien copia celdas de otro archivo y las pega sobre sus celdas validadas, la validación se borra. Desaparece. Como si nunca hubiera existido. ¿La defensa? Combine la validación con protección de hoja (Revisar > Proteger hoja), bloqueando las celdas validadas para que solo acepten escritura directa, no pegado. O al menos advierta a sus usuarios: “no peguen aquí, escriban”.

2. Arrastrar el controlador de relleno. Al arrastrar, la regla se copia también. Si solo quiere copiar los valores, use Pegado especial > Valores.

3. Creer que corrige datos viejos. La validación solo frena datos nuevos. Para cazar los errores que ya estaban, use Datos > Validación de datos > Rodear con un círculo los datos no válidos: Excel marca con óvalos rojos todo lo que viola las reglas.

Y ya que hablamos de dejar los datos impecables: una vez que sus listas están validadas y sin errores, comparar dos listas para encontrar qué le falta a cada una se vuelve un ejercicio de segundos, no una cacería de diferencias ortográficas.

Trucos rápidos que casi nadie conoce

• Copiar la validación a otro rango: copie la celda validada, seleccione el destino y use Pegado especial > Validación. Solo viaja la regla, no el contenido.
• Ver todas las celdas con validación: Inicio > Buscar y seleccionar > Validación de datos. Excel selecciona de una todas las celdas que tienen reglas. Perfecto para auditar un archivo heredado.
• También existe en Google Sheets (Datos > Validación de datos), hasta con chips de colores. Y ordenar y filtrar datos en Google Sheets funciona de maravilla cuando las columnas vienen validadas desde el origen.

Dónde brilla de verdad la validación

Para cerrar, tres escenarios donde la validación de datos pasa de “detalle bonito” a “imprescindible”:

Formatos y reportes compartidos. Si usted diseña una planilla que van a llenar veinte personas, la validación no es opcional. Es su póliza de seguro contra el caos. Cada lista desplegable que pone es una hora de limpieza que se ahorra después.

Bases para dashboards. Un dashboard es tan confiable como sus datos de origen. Si las categorías llegan validadas —sin variantes, sin errores de digitación—, los gráficos salen bien a la primera. Por eso quienes arman un dashboard que se actualiza solo suelen empezar por aquí: validando las entradas.

Formularios internos. Solicitudes, registros, inventarios: todo formulario en Excel merece validación. Es la diferencia entre un archivo profesional y uno donde cada quien hizo lo que quiso.

La documentación oficial de Microsoft tiene la guía completa de validación de datos en Excel por si quiere profundizar. Pero con lo de hoy —listas desplegables, listas dependientes, reglas numéricas y mensajes personalizados— ya tiene el 90% de lo que se usa en el mundo real. Abra ese archivo caótico que tiene por ahí y póngale barandas. Su yo del próximo mes se lo va a agradecer.