Cómo crear y ordenar una lista de datos en Excel (con ejemplos)

Cuando varias personas rellenan la misma hoja de gastos, las palabras se escogen como cada uno quiere. Una escribe "Comida", otra "comida", otra "COMIDA"; al final tienes tres categorías para lo mismo y ningún total que cuadre. La herramienta que lo resuelve se llama Validación de datos y está en Excel desde la versión 2007, así que la tienes en cualquier Excel de escritorio, en Excel para la web y en Mac.

Una lista desplegable es exactamente lo que parece: una flechita al lado de la celda que, al hacer clic, muestra las opciones que tú decides. Quien rellena la hoja elige una y se acabó el problema. En este tutorial partes de una hoja de gastos casera y terminas con tres tipos de listas (escrita directamente, desde un rango y dependiente de otra lista) más el arreglo de los dos tropiezos más comunes.

Qué es una lista desplegable y cuándo conviene usarla

Imagina una hoja compartida con tu pareja o con tus compañeros de piso para anotar los gastos de cada mes. Cada uno escribe la categoría a su manera, y al querer hacer totales la cosa se complica: hay "Comida", "comida", "COMIDAS", "supermercado"… Una lista desplegable obliga a elegir entre los valores que tú decides de antemano, y los datos quedan limpios para siempre.

Conviene usarla cuando una columna debe contener siempre uno de unos pocos valores: categoría de gasto, departamento, provincia, tipo de cliente, estado de un pedido. Si los valores posibles cambian a menudo o son cientos, una lista desplegable deja de ser práctica y conviene otro sistema (una tabla auxiliar con búsqueda, por ejemplo).

La diferencia entre "lista desplegable" y "lo que la gente escribe" salta a la vista al primer filtrado: con lista desplegable filtras una vez; sin ella, persigues cada variante a mano.

Dónde está Validación de datos y qué opciones tiene

La herramienta vive en Datos > Herramientas de datos > Validación de datos. Al pulsar se abre un cuadro con tres pestañas que lo controlan todo.

En la pestaña Configuración decides qué se permite escribir en la celda (una lista, un número, una fecha, una longitud de texto o una fórmula Personalizada) y, según lo que elijas, los campos que aparecen debajo cambian. En la pestaña Mensaje de entrada escribes el globo que aparece al seleccionar la celda, con un Título y un Mensaje. En la pestaña Alerta de error escribes el aviso que sale al escribir algo que no vale, y eliges su estilo: Detener (no deja escribir el valor), Advertencia (lo deja si el usuario acepta) o Información (lo deja sin más).

Permitir: el tipo de dato que acepta la celda

La lista Permitir del cuadro Validación de datos es la pieza central. Estos son los valores que ofrece y un ejemplo de cada uno.

Para una lista desplegable de categorías, lo que quieres es Lista casi siempre. Los demás valores sirven para validar números, fechas o un texto que dependa de otra celda (eso es Personalizada).

El mensaje de entrada y la alerta de error

La pestaña Mensaje de entrada muestra un globo junto a la celda cuando alguien la selecciona. Tienes Título (la línea de arriba, corta) y Mensaje (la explicación de abajo). Si la dejas vacía, no sale globo.

La pestaña Alerta de error sale al intentar escribir algo que no vale. Tiene Título y Mensaje, más un desplegable Estilo con tres opciones.

Detener es el más estricto: no deja escribir el valor. Advertencia pregunta al usuario si insiste. Información avisa y deja pasar. Para una lista desplegable, Detener es lo habitual.

Una lista desplegable con tus categorías escritas a mano

La forma más rápida de tener una lista es escribir las opciones directamente en el propio cuadro. En la hoja "Gastos" anotas la fecha, el concepto y el importe en tres columnas, y la categoría va en B con su desplegable.

1. Selecciona B2:B10.
2. Abre Datos > Herramientas de datos > Validación de datos.
3. En la pestaña Configuración, elige Lista en Permitir.
4. En Origen escribe las categorías separadas por punto y coma: Comida;Transporte;Ocio;Hogar.
5. Pulsa Aceptar.

Al volver a la hoja, cada celda de B tiene su flechita y muestra esas cuatro opciones. Si alguien escribe "comida" en minúsculas, Excel no lo corrige automáticamente, así que conviene escribir la lista siempre tal cual la quieres (con mayúscula inicial).

Una lista que apunta a un rango de la hoja

Escribir las opciones a mano tiene un problema: si quieres añadir una categoría, tienes que volver al cuadro y cambiar el Origen. La solución es poner las categorías en una columna aparte y que el Origen apunte a ese rango.

1. Escribe las categorías en F2:F6.
2. Selecciona B2:B10.
3. Abre Datos > Herramientas de datos > Validación de datos.
4. En Origen escribe =$F$2:$F$6 (las referencias absolutas con dólar son importantes al copiar la validación a otras celdas).
5. Pulsa Aceptar.

Ahora la lista de B se nutre de F. Si cambias "Ocio" por "Cultura" en F4, el desplegable de B4 muestra "Cultura" en vez de "Ocio". El problema aparece con las categorías nuevas.

Con un rango fijo que no crece solo

Con el Origen puesto en $F$2:$F$6, Excel mira exactamente esas cinco celdas. Si escribes "Mascotas" en F7, queda fuera y el valor no entra en la lista desplegable. La categoría existe en la hoja, pero el desplegable no la enseña.

Tienes dos arreglos: ampliar el Origen a $F$2:$F$7 (o más) cada vez que crezca la lista, o convertir el rango en una tabla para que crezca sola.

Convertir la lista en una tabla para que crezca sola

Una tabla de Excel se expande automáticamente al añadir una fila justo debajo, y los rangos con nombre de tabla se actualizan al añadir datos. El Origen de la validación, si apunta a una columna de la tabla, también.

1. Selecciona F2:F6 con los datos (incluye el encabezado en F1 si quieres).
2. Pulsa Ctrl+T para crear la tabla. Confirma que los datos tienen encabezados.
3. Selecciona B2:B10 y abre Validación de datos.
4. En Origen escribe =INDIRECTO("Tabla1[Categoria]") (sustituye Tabla1 por el nombre real que Excel haya puesto a tu tabla, lo ves en la pestaña Diseño de tabla).
5. Pulsa Aceptar.

=INDIRECTO("Tabla1[Categoria]")

Al añadir "Mascotas" en F7, la tabla crece hasta F7 sola. La fórmula =INDIRECTO("Tabla1[Categoria]") lee la columna completa, así que "Mascotas" aparece en el desplegable de B sin tocar el cuadro de Validación.

Listas desplegables que dependen de otra lista

A veces la categoría y el detalle van de la mano: eliges provincia y luego quieres ver solo las ciudades de esa provincia. Se monta con dos listas y la función INDIRECTO (INDIRECT en inglés), que convierte un texto en una referencia. La función INDIRECTO está en todas las versiones de Excel desde 2007.

Las celdas B2 y B3 son las dos listas. F2:I6 contiene la lista de provincias (F2 es el encabezado "Madrid", y debajo sus ciudades; y así con cada provincia). El nombre de cada lista de ciudades es exactamente el nombre de la provincia en B2, sin espacios.

1. En B2 crea la primera lista con Origen =$F$2:$I$2 (los nombres de las provincias).
2. En B3 crea la segunda lista con Origen =INDIRECTO($B$2).

=INDIRECTO($B$2)

Ahora, si en B2 eliges "Madrid", la lista de B3 muestra solo las ciudades de la columna F (Alcorcón, Móstagan, Madrid). Si eliges "Barcelona", B3 muestra Badalona, Barcelona y Hospitalet. INDIRECTO convierte "Madrid" en la referencia a Madrid!$A$1 (o a un rango con ese nombre, según cómo lo montes) y lee de ahí.

Por qué la lista deja pasar valores incorrectos

La Validación de datos tiene dos límites que conviene saber, porque son los que más sorprenden: no mira lo que ya estaba escrito cuando la activas, y un pegado puede saltarse la validación. La buena noticia es que tiene una herramienta para cazar los valores malos que ya están en la hoja.

Pegar encima se salta la validación

Si copias una celda con Ctrl+C y la pegas con Ctrl+V en una celda con lista desplegable, el valor se pega sin comprobación. La validación no se aplica a lo que ya está en la celda antes de pegar.

Aquí B5 contiene "Coche", que no está en la lista Comida;Transporte;Ocio;Hogar. Si copiaste ese valor de otra hoja y lo pegaste, la celda no protestó al pegar.

1. Selecciona cualquier celda de la hoja Gastos.
2. Abre Datos > Herramientas de datos > Validación de datos.
3. Pulsa Rodear con un círculo datos no válidos.
4. Excel marca con un círculo rojo todas las celdas que no cumplen la validación (incluido B5).

La lista no muestra las categorías nuevas

El otro tropiezo típico: la lista de F crece, pero el Origen de la validación sigue siendo el rango antiguo y las categorías nuevas no aparecen.

El Origen es $F$2:$F$6 y "Mascotas" está en F7, fuera del rango. Al abrir la flecha de B2, "Mascotas" no sale.

Solución: o bien amplía el Origen a $F$2:$F$10 para que entren las próximas, o bien conviertes F2:F6 en una tabla con Ctrl+T y usas =INDIRECTO("Tabla1[Categoria]") como Origen, que crece solo. Las dos opciones están vistas más atrás.

Lo que ya sabes hacer con las listas desplegables en Excel

• Crear una lista desplegable directamente en el cuadro Origen, escribiendo las opciones separadas por punto y coma.
• Apuntar el Origen a un rango de celdas con referencias absolutas para que la lista se alimente de ahí.
• Convertir ese rango en una tabla con Ctrl+T para que la lista crezca sola al añadir categorías.
• Montar listas dependientes con la función INDIRECTO, que muestra solo las opciones relacionadas con lo elegido en la primera lista.
• Detectar valores ya pegados que no cumplen la validación con Rodear con un círculo datos no válidos.

El siguiente paso natural es combinar esta lista con Formato condicional (Inicio > Estilos > Formato condicional) para resaltar, por ejemplo, los gastos de Ocio mayores de 50, y tener a la vez la entrada limpia y el aviso visual de los importes altos.

Dudas al usar listas desplegables en Excel