Listas desplegables y validación de datos: que nadie escriba lo que no debe
Escribe las opciones en una columna (mejor en otra hoja), selecciona las celdas donde irá la lista y ve a Datos › Herramientas de datos › Validación de datos. En la pestaña Configuración elige Permitir: Lista y, en Origen, selecciona el rango de opciones (por ejemplo =Listas!$A$2:$A$5). Deja marcada Celda con lista desplegable y pulsa Aceptar.
Compartes una hoja de pedidos y, en la columna Estado, cada uno escribe lo que quiere: «Enviado», «enviado», «Env.», «ENVIADO ya». Luego intentas filtrar o hacer una tabla dinámica y te salen cuatro estados que en realidad son uno. El problema no es la gente: es que la hoja deja escribir cualquier cosa.
La validación de datos es como un portero en la puerta de cada celda: solo deja pasar lo que cumple tus reglas. Su versión más útil es la lista desplegable, una flecha que ofrece las opciones permitidas para elegir con un clic.
En esta guía creas una lista desplegable que crece sola, añades un mensaje de ayuda y un aviso de error, y aprendes a limitar importes, fechas y duplicados.
Qué vas a aprender
- Crear una lista desplegable a partir de un rango en otra hoja
- Añadir un mensaje de ayuda y un mensaje de error a medida
- Limitar importes, fechas o evitar duplicados con una fórmula
- Encontrar los datos que ya estaban mal antes de poner la regla
Paso a paso
Escribe las opciones en una hoja aparte
Crea una hoja llamada Listas y escribe un encabezado (Estado) y, debajo, una opción por celda: Pendiente, Enviado, Entregado y Cancelado. Tenerlas aparte evita que alguien las borre sin querer y te permite reutilizarlas.
Escríbelas exactamente como quieres que aparezcan, en el orden en que se mostrarán en la lista.
Convierte el rango en una tabla (Insertar › Tabla). Así, cuando añadas una opción nueva debajo, todas las listas basadas en ella se actualizan solas.
Selecciona las celdas que llevarán la lista
Ve a la hoja Pedidos y selecciona la columna de estados, desde D2 hasta la última fila que vayas a usar (por ejemplo D2:D200). No incluyas el encabezado.
Si trabajas con una tabla de Excel, basta con seleccionar las celdas de la columna: las filas nuevas heredarán la validación.
Abre Datos › Herramientas de datos › Validación de datos
En la pestaña Datos, grupo Herramientas de datos, haz clic en Validación de datos. Si pulsas la flecha del botón, elige Validación de datos... en el menú. Se abre el cuadro Validación de datos.
Si el botón aparece gris, la hoja está protegida o tienes una celda en modo edición. Pulsa Esc o desprotege la hoja.
Elige Permitir: Lista y selecciona el origen
En la pestaña Configuración, despliega Permitir y elige Lista. Haz clic en el cuadro Origen, ve a la hoja Listas y selecciona A2:A5 (sin el encabezado). Excel escribe =Listas!$A$2:$A$5.
Deja marcadas Omitir blancos (permite dejar la celda vacía) y Celda con lista desplegable (sin ella no aparece la flecha).
Origen de la lista: las cuatro opciones de la hoja Listas, con referencia absoluta.
Para listas cortas que no van a cambiar puedes escribir las opciones directamente en Origen, separadas por punto y coma: Sí;No.
Añade un mensaje de ayuda en Mensaje de entrada
Pasa a la pestaña Mensaje de entrada. Deja marcada Mostrar mensaje de entrada al seleccionar la celda y escribe un Título (Estado del pedido) y el Mensaje de entrada (Elige un estado de la lista). Aparecerá como una notita amarilla al seleccionar la celda: instrucciones justo donde hacen falta.
Decide qué pasa si alguien escribe otra cosa
En la pestaña Mensaje de error, el Estilo decide lo estricta que es la regla:
- Alto (icono rojo): no deja guardar un valor fuera de la lista.
- Advertencia: avisa y pregunta si quieres continuar.
- Información: solo informa y acepta el valor.
Elige Alto, escribe un título y un mensaje que diga qué se puede escribir, no solo que está mal. Pulsa Aceptar.
Prueba la lista
Haz clic en D4: aparece la flecha a la derecha de la celda y el mensaje de ayuda. Elige Entregado. Con el teclado, Alt+↓ abre la lista sin tocar el ratón.
Ahora escribe «entregado ya» y pulsa Intro: sale tu mensaje de error y Excel no acepta el valor. Las mayúsculas no importan: «entregado» sí se acepta.
Limita también importes, fechas y duplicados
La lista es solo una opción de Permitir. Las otras resuelven muchos errores de tecleo:
- Decimal entre
0y5000en la columna Importe: evita un 45000 por un 450. - Fecha mayor o igual que
=HOY(): impide fechas de entrega pasadas. - Longitud del texto: por ejemplo, un código postal de 5 caracteres.
- Personalizada: cualquier fórmula que devuelva
VERDADERO. SeleccionandoA2:A200,=CONTAR.SI($A$2:$A$200;A2)=1impide repetir un número de pedido.
Solo acepta el número de pedido si no aparece ya en la columna.
Encuentra los datos que ya estaban mal
La validación solo vigila lo que se escribe a partir de ahora: lo que ya estaba en las celdas no se revisa. Para verlo, ve a Datos › Validación de datos (flecha del botón) › Rodear con un círculo datos no válidos. Excel dibuja un círculo rojo alrededor de cada valor que incumple la regla. Corrígelos y quita los círculos con Borrar círculos de validación.
Trucos de experto
- Listas que dependen de otra. Si en
B2eliges Fruta y quieres queC2ofrezca solo frutas, crea un nombre definido Fruta para ese rango y usa=INDIRECTO(B2)como origen. Más sobre nombres en nombres definidos y LET. - Lista sin repetidos que se actualiza sola. En Microsoft 365, si en
Listas!C2escribes=ORDENAR(UNICOS(Pedidos!B2:B200)), puedes usar=Listas!$C$2#como origen: el#toma todo el resultado, crezca lo que crezca. - Copia solo la validación. Copia una celda con la regla, selecciona el destino y usa Inicio › Pegar › Pegado especial... › Validación. No toca ni los valores ni el formato.
- Combínala con colores. Una regla de formato condicional que pinte de rojo los Cancelado y de verde los Entregado convierte la columna en un semáforo.
- Blíndala. Para que nadie cambie ni quite la regla, protege la hoja dejando desbloqueadas solo las celdas de entrada.
Errores comunes y cómo arreglarlos
Alguien pega datos encima y la validación desaparece.
Pegar con Ctrl+V sustituye también la regla de la celda. Pide que se pegue con Pegado especial › Valores, o vuelve a aplicar la validación y revisa con Rodear con un círculo datos no válidos.
El encabezado aparece como una opción más de la lista.
El origen incluye la fila 1. Edita la regla y deja solo las opciones, por ejemplo =Listas!$A$2:$A$5.
No aparece la flecha de la lista.
Revisa que esté marcada la casilla Celda con lista desplegable en la pestaña Configuración.
Añades una opción nueva y no sale en la lista.
La escribiste fuera del rango de origen. Amplía el rango o, mejor, convierte las opciones en una tabla para que crezca sola.
En Excel para Mac el camino es Datos › Validación de datos, con las mismas tres pestañas (Configuración, Mensaje de entrada y Mensaje de error) y las mismas opciones de Permitir.
Preguntas frecuentes
¿Cómo quito una lista desplegable?
Selecciona las celdas, abre Datos › Validación de datos y en Permitir elige Cualquier valor. Pulsa Aceptar. Los valores ya escritos se conservan.
¿Por qué Excel me deja escribir algo que no está en la lista?
O el estilo del mensaje de error es Advertencia o Información (que permiten continuar), o la casilla Mostrar mensaje de error si se introducen datos no válidos está desmarcada.
Escribí Sí;No en Origen y sale todo junto en una opción. ¿Qué pasa?
Tu Excel usa la coma como separador de listas (pasa en México y otros países). Escribe Sí,No. Lo mismo vale para las fórmulas de esta página: cambia ; por ,.
¿Puedo ordenar las opciones alfabéticamente?
La lista muestra las opciones en el mismo orden que el origen. Ordena el rango de la hoja Listas (Datos › Ordenar y filtrar) y la lista cambiará al instante.
Fuentes oficiales
Documentación de Microsoft consultada para esta guía.
Se guarda solo en este navegador.
Sigue aprendiendo
Tablas de Excel: el formato que hace que filtros, fórmulas y gráficos funcionen solos
Convierte tus datos en una tabla de Excel: filtros, totales y fórmulas que se amplían solos cuando añades filas. Paso a paso y con ejemplos, en 6 minutos.
Formato condicional: que Excel resalte solo lo importante
Aprende a usar el formato condicional de Excel para resaltar ventas, duplicados y valores clave con colores, barras e iconos. Paso a paso, en 5 minutos.
Proteger hojas y celdas: que solo se pueda escribir donde toca
Desbloquea las celdas de entrada y protege la hoja en Excel para que nadie borre tus fórmulas por error. Paso a paso, con contraseña opcional y sus límites.
Nombres definidos y LET: fórmulas que se entienden al leerlas
Pon nombre a celdas y rangos y usa LET para que tus fórmulas se lean como una frase, sin $ ni cálculos repetidos. Paso a paso con un ejemplo de factura.
FILTRAR, ORDENAR y UNICOS: fórmulas que devuelven listas enteras
Usa FILTRAR, ORDENAR y UNICOS para crear listas filtradas, ordenadas y sin duplicados que se actualizan solas, y arregla #¡DESBORDAMIENTO!. Paso a paso.