Cómo hacer una lista desplegable en Excel con validación

Para crear una lista desplegable en Excel, selecciona las celdas, ve a Datos > Validación de datos, pon Permitir en Lista, escribe los elementos en Origen separados por comas (North,South,East,West) o selecciona el rango que los contiene, y pulsa Aceptar. Cada celda muestra ahora una flecha con esas opciones, y se rechaza cualquier otra entrada.

FórmulasRestablecer

E2fx

	A	B	C	D	E	F
1	Rep	Region	Sales		Region	Sales
2	Ana	North▾	120		North▾NorthSouthEastWest	360
3	Ben	South▾	85
4	Cara	North▾	240
5	Dan	East▾	60
6	Eve	South▾	150

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.

F2 muestra 360, el total de North. Elige North en B3 y F2 crece con los 85 de Ben. E2 también tiene una lista desplegable: elige South ahí y F2 muestra el total de South. Una lista para el dato de entrada y una fórmula que lo lee, en este caso SUMAR.SI (SUMIF en inglés), es el uso más habitual de una lista desplegable.

Cómo crear una lista desplegable, paso a paso

1. Selecciona las celdas que deben llevar la lista, por ejemplo B2:B6.
2. Ve a Datos > Validación de datos (grupo Herramientas de datos). En Excel en inglés para Windows la secuencia de teclas es Alt, A, V, V.
3. En la pestaña Configuración, pon Permitir en Lista.
4. En Origen, escribe los elementos separados por comas, North,South,East,West, o haz clic en el cuadro y selecciona en la hoja el rango con los elementos, lo que escribe =$F$2:$F$5.
5. Deja marcada Celda con lista desplegable (sin ella no hay flecha, solo la comprobación).
6. Pulsa Aceptar.

Para abrir la lista con el teclado, selecciona la celda y pulsa Alt+Flecha abajo (Windows) u Opción+Flecha abajo (Mac). En Excel para Microsoft 365, escribir las primeras letras en la celda reduce la lista a los elementos que coinciden.

Dos pestañas opcionales del mismo cuadro: Mensaje de entrada muestra una indicación al seleccionar la celda, y Mensaje de error decide qué pasa cuando alguien escribe un valor que no está en la lista. Con el estilo Grave (el predeterminado) la entrada se rechaza; con Advertencia o Información se admite después de un aviso. Desmarca Mostrar mensaje de error si se escriben datos no válidos para dejar que se escriba cualquier cosa y seguir ofreciendo la lista.

Los elementos escritos se separan con el separador de listas de la configuración regional del equipo. Con la configuración de España, que usa coma decimal, es el punto y coma: North;South;East;West; con la de México, la coma. Pasa lo mismo con las fórmulas, y la tabla también acepta las fórmulas escritas en español, con punto y coma (o con comas, como en México): =SUMAR.SI(B2:B6;E2;C2:C6).

Lista desplegable a partir de un rango de celdas

Una lista escrita en el cuadro queda oculta y hay que editarla ahí. Una lista en celdas es más fácil de mantener: cambia una celda y cambian todas las listas desplegables que la usan. Aquí las regiones están en E2:E5 y la lista desplegable de B2:B6 usa ese rango como origen.

FórmulasRestablecer

B2fx

	A	B	C	D	E
1	Rep	Region	Sales		Regions
2	Ana	North▾NorthSouthEastWest	120		North
3	Ben	South▾	85		South
4	Cara	North▾	240		East
5	Dan	East▾	60		West
6	Eve	South▾	150

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.

Cambia E5 de West a Central y abre cualquier flecha de la columna B: la lista ofrece Central en lugar de West. Los valores ya elegidos en la columna B no cambian.

Para usar un rango de otra hoja, que es la forma habitual de mantener las listas fuera de la vista, escribe en Origen el nombre de la hoja: =Lists!$A$2:$A$5. Para que la lista crezca al añadir un elemento al final, convierte antes los elementos en una tabla (selecciónalos, Insertar > Tabla) y luego selecciona la columna de la tabla como origen: la referencia se amplía con la tabla.

Una lista desplegable dinámica con UNICOS

Cuando los elementos deben salir de los propios datos (cada región que aparece en una columna, una vez cada una), construye la lista con una fórmula y apunta la lista desplegable al resultado. =ORDENAR(UNICOS(B2:B8)) en G2 derrama las regiones distintas en orden alfabético.

FórmulasRestablecer

E2fx

	A	B	C	D	E	F	G
1	Rep	Region	Sales		Pick	Sales	Regions
2	Ana	North	120		South▾EastNorthSouthWest	235	East
3	Ben	South	85				North
4	Cara	North	240				South
5	Dan	East	60				West
6	Eve	South	150
7	Fay	West	95
8	Gus	East	110

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.

G2 derrama East, North, South, West, y la lista desplegable de E2 ofrece esas cuatro. Cambia B7 a Central y Central aparece tanto en el desbordamiento como en la lista.

En Excel, pon como Origen de la lista desplegable =$G$2#. El # después de una celda significa "todo el desbordamiento de esta fórmula", así que la lista siempre tiene exactamente la longitud del resultado, sin filas vacías al final. La referencia al desbordamiento necesita Excel 365 o 2021; la celda de origen puede estar en otra hoja (=Lists!$A$2#). Si la columna de datos tiene celdas vacías, UNICOS devuelve un 0 para ellas; déjalas fuera con =ORDENAR(UNICOS(FILTRAR(B2:B100;B2:B100<>""))). La página de UNICOS explica la función con detalle.

Listas desplegables dependientes

Una lista dependiente cambia con la elección de otra celda: elige Fruit en A2 y B2 ofrece solo frutas. En Excel 365 y 2021, una fórmula FILTRAR construye la segunda lista: =FILTRAR(E2:E8;D2:D8=A2) devuelve los elementos cuya categoría coincide con A2, y la lista desplegable de B2 usa ese desbordamiento como origen.

FórmulasRestablecer

A2fx

	A	B	C	D	E	F	G
1	Category	Item		Category	Item		Items
2	Fruit▾FruitVegetableBakery	Pear▾		Fruit	Apple		Apple
3				Fruit	Pear		Pear
4				Vegetable	Carrot		Kiwi
5				Vegetable	Leek
6				Bakery	Bread
7				Fruit	Kiwi
8				Bakery	Bagel

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.

Con Fruit en A2, G2 derrama Apple, Pear y Kiwi, y esas son las opciones de B2. Elige Bakery en A2: G2 cambia a Bread y Bagel. B2 sigue diciendo Pear hasta que eliges otra vez, porque una lista desplegable nunca cambia un valor que ya está en la celda. En Excel el origen de B2 es =$G$2#.

En las versiones antiguas de Excel, la forma clásica usa INDIRECTO y rangos con nombre:

1. Pon los elementos de cada categoría en su propia columna, con el nombre de la categoría como encabezado: Fruit en una columna, Vegetable en la siguiente.
2. Selecciona cada columna de elementos y dale el nombre de su categoría en el Cuadro de nombres (a la izquierda de la barra de fórmulas): Fruit, Vegetable, Bakery.
3. Dale a A2 una lista desplegable con el origen Fruit,Vegetable,Bakery.
4. Dale a B2 una lista desplegable con el origen =INDIRECTO(A2). INDIRECTO (INDIRECT en inglés) convierte el texto de A2 en una referencia al rango con ese nombre.

Los nombres deben coincidir exactamente con el texto de la categoría y no pueden contener espacios (usa Dairy_Products, o =INDIRECTO(SUSTITUIR(A2;" ";"_")) en el origen). Hay más sobre INDIRECTO en la página de INDIRECTO.

Buscar el valor del elemento elegido

Una lista desplegable suele ser el dato de entrada de un formulario de pedido o un presupuesto: el usuario elige un producto y una búsqueda completa su precio.

FórmulasRestablecer

B2fx

	A	B	C	D	E	F
1		Product	Price		Product	Price
2	Order	Pear▾ApplePearCarrotBreadMilk			Apple	$1.20
3					Pear	$1.50
4					Carrot	$0.80
5					Bread	$2.40
6					Milk	$1.10

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.

Tu turno: Devuelve en C2 el precio del producto elegido en B2, a partir de la tabla de E:F.

ComprobarVer respuesta

Con Pear elegido, la respuesta es $1.50. Elige otro producto en B2 y el precio lo sigue. =BUSCARX(B2;E2:E6;F2:F6) también funciona; consulta BUSCARV para los argumentos.

Colorear una celda según el elemento elegido

Para colorear la celda según lo que se eligió (verde para Done, rojo para Late), añade una regla de formato condicional a las mismas celdas: selecciona B2:B6, ve a Inicio > Formato condicional > Reglas para resaltar celdas > Es igual a, escribe Late y elige un formato. Para colorear la fila entera, selecciona A2:B6 y usa Nueva regla > Utilice una fórmula que determine las celdas para aplicar formato con =$B2="Late".

FórmulasRestablecer

B3fx

	A	B
1	Task	Status
2	Quote	Done▾
3	Invoice	Late▾OpenDoneLate
4	Order	Open▾
5	Report	Late▾
6	Survey	Done▾

Resaltar B2:B6 donde

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.

B3 y B5 están resaltadas. Elige Late en B4 y también se resalta; elige Done en B3 y el resaltado desaparece. La página de formato condicional explica las reglas con detalle.

Por qué una lista desplegable no funciona

• Celda con lista desplegable está desmarcada en Datos > Validación de datos. La lista sigue restringiendo las entradas, pero no hay flecha.
• La flecha solo aparece en la celda seleccionada. Nada en la cuadrícula marca las demás celdas que tienen una lista; para encontrarlas, usa Inicio > Buscar y seleccionar > Validación de datos.
• El rango de origen tiene celdas vacías, así que la lista muestra líneas en blanco. Selecciona solo las celdas llenas, o usa un origen desbordado (=$G$2#), que no tiene vacíos.
• Elementos escritos con el separador equivocado: North;South en un Excel que usa comas pasa a ser un solo elemento llamado North;South.
• Una lista desplegable guarda un solo valor. Elegir un segundo elemento sustituye al primero; seleccionar varios elementos en una celda necesita una macro de VBA.

Para copiar una lista desplegable a otras celdas sin copiar el valor, copia la celda y usa Inicio > Pegar > Pegado especial > Validación. Para quitar una, selecciona las celdas y elige Datos > Validación de datos > Borrar todos.