Ir al contenido
Jossua Servicios
Excel

Validación de datos y listas desplegables en Excel sin errores

Aprende a crear listas desplegables, listas dependientes por región y mensajes de error para que cada celda acepte solo datos válidos.

Por Jossua5 de noviembre de 20264 min de lectura
Formulario de Excel con listas desplegables de región y ciudad y un aviso de validación

En una distribuidora de Manta, los vendedores llenan a diario un formato de pedidos. Unos escriben “Manta”, otros “MANTA” y otros “Manta ” con un espacio al final. Al mes, el reporte por ciudad tiene cuatro variantes y los totales no cuadran. El problema no es la fórmula: la hoja acepta cualquier cosa.

La validación de datos convierte una celda libre en una celda controlada: listas, fechas, números enteros o una regla propia. También muestra ayuda al seleccionar la celda y un aviso claro cuando alguien escribe algo incorrecto.

Separador de argumentos. En este artículo uso punto y coma (;), que es lo habitual en Excel en español. Si tu equipo está configurado con coma como separador de listas, cambia cada ; por ,.

1. Crear la primera lista desplegable

El caso más común es una columna de ciudades con opciones cerradas.

  1. Escribe las opciones en H2:H5: Manta, Portoviejo, Guayaquil y Quito. Si conviertes el rango en tabla con Ctrl + T, la lista puede crecer sin reescribir la referencia.
  2. Selecciona las celdas donde se elegirán las ciudades, sin el encabezado.
  3. Ve a Datos → Validación de datos. En Configuración, elige Permitir: Lista.
  4. En Origen, selecciona el rango o escribe =$H$2:$H$5.
  5. Deja marcada Lista desplegable en la celda y pulsa Aceptar.

Si las opciones están en otra hoja, asigna un nombre al rango (por ejemplo Ciudades) y úsalo en Origen. Es la forma más segura y funciona en todas las versiones.

2. Una lista que se actualiza con los datos

Si los clientes cambian cada semana, no reescribas el origen. En Excel para Microsoft 365 y Excel 2021, con la tabla Ventas y su columna Cliente, escribe en H2:

=UNICOS(Ventas[Cliente])

El resultado se derrama hacia abajo. En la validación usa =$H$2#: el signo # abarca todo el rango generado. Si aparece un cliente nuevo, la lista lo incluye sola. En esta guía de FILTRAR, ORDENAR y UNICOS hay más ejemplos.

3. Listas dependientes: la ciudad según la región

En la columna E se elige la región y en la F solo deben aparecer sus ciudades: Costa → Manta, Portoviejo y Guayaquil; Sierra → Quito, Cuenca y Loja; Oriente → Tena, Puyo y Macas. Si el vendedor elige Costa, no debe poder escoger Quito.

Si tienes Excel 365 o 2021, guarda el catálogo en una tabla Catalogo con las columnas Región y Ciudad. Luego:

  1. Crea la lista de regiones en E2.
  2. Selecciona F2:F500 y abre Validación de datos.
  3. Elige Lista y escribe esta fórmula en Origen.
=FILTRAR(Catalogo[Ciudad]; Catalogo[Región]=$E2)

$E2 fija la columna, pero no la fila, para que cada fila mire su región. Al elegir Sierra en E5, F5 solo muestra Quito, Cuenca y Loja.

En versiones anteriores se usan nombres definidos e INDIRECTO. Define Costa, Sierra y Oriente, cada uno apuntando a sus ciudades, y en F2 escribe:

=INDIRECTO($E2)

El texto de E2 debe coincidir con el nombre del rango, así que usa nombres de una sola palabra, sin espacios ni tildes. Si prefieres Region_Costa, construye el nombre:

=INDIRECTO("Region_"&$E2)

Prueba la lista dependiente antes de compartir el archivo. Cambia la región y revisa qué pasa con la ciudad. La validación impide escribir opciones ajenas, pero no borra valores anteriores: si cambias la región después de elegir la ciudad, esa ciudad puede quedarse.

4. Mensajes que guían y errores que explican

En la misma ventana hay dos pestañas que casi nadie usa: Mensaje de entrada y Mensaje de error.

En Mensaje de entrada escribe un título y una ayuda breve: “Elige la región” y “Selecciona una opción; si tu ciudad no aparece, avisa a Sistemas”. Marca la casilla para mostrarlo al seleccionar la celda.

En Mensaje de error eliges el estilo: Detener rechaza el valor, Advertencia pregunta si deseas continuar e Información solo avisa. Para columnas que alimentan reportes, usa Detener. Escribe el mensaje en positivo: “Elige una ciudad de la lista. Si no está, revisa la región”. Evita “Valor no válido”, que no dice nada.

5. Reglas con fórmula: más allá de las listas

En Permitir, la opción Personalizada acepta una fórmula que debe devolver VERDADERO. Tres ejemplos:

  • Factura sin repetir. En B2:B500, =CONTAR.SI($B$2:$B$500; B2)=1. El rango absoluto se queda fijo y B2 cambia por fila.
  • Fecha no futura. En una columna de pagos, =C2<=HOY().
  • Cantidad entera y positiva. Elige Número entero y mayor que 0. No necesitas fórmula.

La fórmula se escribe para la primera celda del rango, igual que en el formato condicional.

Errores frecuentes

  • La lista no aparece. Revisa que Permitir sea Lista y que Lista desplegable en la celda esté marcada.
  • La validación se borró al copiar. Pegar datos encima reemplaza el formato. Vuelve a aplicarla o usa Pegar especial → Validación.
  • El origen en otra hoja no funciona. Define un nombre para el rango y usa ese nombre.
  • INDIRECTO da error o lista vacía. El nombre no coincide con el texto de la celda, o tiene espacios o tildes.
  • Se pegan valores inválidos. La validación no detiene lo que se pega desde otro archivo; valida en el origen o usa Rodear con un círculo los datos no válidos.

Una hoja bien validada no es más rígida: es más rápida de llenar y más confiable de analizar. Define listas, mensajes y reglas en cada columna y tus reportes dejarán de pelearse con errores de tipeo. Si vas a resumir esos datos, combínala con una tabla dinámica.

JossuaConsultor en Business Intelligence y Analítica de Datos. Manta, Ecuador.

Comentarios

Todavía no hay comentarios. Si tienes una duda sobre este tema, escríbela aquí y te respondo.

Deja tu comentario

Tu correo no se publica. Los campos con * son obligatorios.

Sigue con Excel

Otros artículos del mismo tema.