Cómo crear una lista desplegable en Excel (la única guía que necesita)

Una lista desplegable es una manera excelente de darle al usuario la opción de seleccionar de una lista predefinida.

Se puede utilizar para que un usuario complete un formulario o mientras crea paneles interactivos de Excel.

Las listas desplegables son bastante comunes en sitios web / aplicaciones y son muy intuitivas para el usuario.

Ver video: crear una lista desplegable en Excel

En este tutorial, aprenderá a crear una lista desplegable en Excel (solo se necesitan unos segundos para hacer esto) junto con todas las cosas increíbles que puede hacer con ella.

Cómo crear una lista desplegable en Excel

En esta sección, aprenderá los pasos exactos para crear una lista desplegable de Excel:

1. Usando datos de celdas.
2. Introducción de datos manualmente.
3. Usando la fórmula OFFSET.

# 1 usando datos de celdas

Supongamos que tiene una lista de elementos como se muestra a continuación:

Estos son los pasos para crear una lista desplegable de Excel:

1. Seleccione una celda en la que desee crear la lista desplegable.
2. Vaya a Datos -> Herramientas de datos -> Validación de datos.
3. En el cuadro de diálogo Validación de datos, dentro de la pestaña Configuración, seleccione Lista como criterio de validación.
   • Tan pronto como seleccione Lista, aparecerá el campo de origen.
4. En el campo de origen, ingrese = $ A $ 2: $ A $ 6, o simplemente haga clic en el campo de origen y seleccione las celdas con el mouse y haga clic en Aceptar. Esto insertará una lista desplegable en la celda C2.
   • Asegúrese de que la opción desplegable En la celda esté marcada (que está marcada de forma predeterminada). Si esta opción no está marcada, la celda no muestra un menú desplegable, sin embargo, puede ingresar manualmente los valores en la lista.

Nota: Si desea crear listas desplegables en varias celdas a la vez, seleccione todas las celdas donde desea crearlas y luego siga los pasos anteriores. Asegúrese de que las referencias de celda sean absolutas (como $ A $ 2) y no relativas (como A2, A $ 2 o $ A2).

# 2 Ingresando datos manualmente

En el ejemplo anterior, las referencias de celda se utilizan en el campo Fuente. También puede agregar elementos directamente ingresándolos manualmente en el campo de origen.

Por ejemplo, supongamos que desea mostrar dos opciones, Sí y No, en el menú desplegable de una celda. Así es como puede ingresarlo directamente en el campo de fuente de validación de datos:

• Seleccione una celda donde desea crear la lista desplegable (celda C2 en este ejemplo).
• Vaya a Datos -> Herramientas de datos -> Validación de datos.
• En el cuadro de diálogo Validación de datos, dentro de la pestaña Configuración, seleccione Lista como criterio de validación.
  • Tan pronto como seleccione Lista, aparecerá el campo de origen.
• En el campo de origen, ingrese Sí, No
  • Asegúrese de que la opción desplegable En la celda esté marcada.
• Haga clic en Aceptar.

Esto creará una lista desplegable en la celda seleccionada. Todos los elementos enumerados en el campo de origen, separados por una coma, se enumeran en diferentes líneas en el menú desplegable.

Todos los elementos ingresados ​​en el campo de origen, separados por una coma, se muestran en diferentes líneas en la lista desplegable.

Nota: Si desea crear listas desplegables en varias celdas a la vez, seleccione todas las celdas donde desea crearlas y luego siga los pasos anteriores.

# 3 Usando fórmulas de Excel

Además de seleccionar de las celdas e ingresar datos manualmente, también puede usar una fórmula en el campo de origen para crear una lista desplegable de Excel.

Cualquier fórmula que devuelva una lista de valores se puede utilizar para crear una lista desplegable en Excel.

Por ejemplo, suponga que tiene el conjunto de datos como se muestra a continuación:

Estos son los pasos para crear una lista desplegable de Excel usando la función DESPLAZAMIENTO:

• Seleccione una celda donde desea crear la lista desplegable (celda C2 en este ejemplo).
• Vaya a Datos -> Herramientas de datos -> Validación de datos.
• En el cuadro de diálogo Validación de datos, dentro de la pestaña Configuración, seleccione Lista como criterio de validación.
  • Tan pronto como seleccione Lista, aparecerá el campo de origen.
• En el campo Fuente, ingrese la siguiente fórmula: = OFFSET ($ A $ 2,0,0,5)
  • Asegúrese de que la opción desplegable En la celda esté marcada.
• Haga clic en Aceptar.

Esto creará una lista desplegable que enumera todos los nombres de frutas (como se muestra a continuación).

Nota: Si desea crear una lista desplegable en varias celdas a la vez, seleccione todas las celdas donde desea crearla y luego siga los pasos anteriores. Asegúrese de que las referencias de celda sean absolutas (como $ A $ 2) y no relativas (como A2, A $ 2 o $ A2).

¿Cómo funciona esta fórmula?

En el caso anterior, usamos una función OFFSET para crear la lista desplegable. Devuelve una lista de elementos de la ra

Devuelve una lista de elementos del rango A2: A6.

Aquí está la sintaxis de la función OFFSET: = OFFSET (referencia, filas, columnas, [altura], [ancho])

Toma cinco argumentos, donde especificamos la referencia como A2 (el punto de partida de la lista). Las filas / columnas se especifican como 0 ya que no queremos compensar la celda de referencia. La altura se especifica como 5 ya que hay cinco elementos en la lista.

Ahora, cuando usa esta fórmula, devuelve una matriz que tiene la lista de las cinco frutas en A2: A6. Tenga en cuenta que si ingresa la fórmula en una celda, selecciónela y presione F9, verá que devuelve una matriz de los nombres de las frutas.

Crear una lista desplegable dinámica en Excel (usando OFFSET)

La técnica anterior de usar una fórmula para crear una lista desplegable también se puede ampliar para crear una lista desplegable dinámica. Si usa la función DESPLAZAMIENTO, como se muestra arriba, incluso si agrega más elementos a la lista, el menú desplegable no se actualizará automáticamente. Tendrá que actualizarlo manualmente cada vez que cambie la lista.

Aquí hay una forma de hacerlo dinámico (y no es más que un pequeño ajuste en la fórmula):

• Seleccione una celda donde desea crear la lista desplegable (celda C2 en este ejemplo).
• Vaya a Datos -> Herramientas de datos -> Validación de datos.
• En el cuadro de diálogo Validación de datos, dentro de la pestaña Configuración, seleccione Lista como criterio de validación. Tan pronto como seleccione Lista, aparece el campo de origen.
• En el campo de origen, ingrese la siguiente fórmula: = COMPENSACIÓN ($ A $ 2,0,0, CONTAR.SI ($ A $ 2: $ A $ 100, ””))
• Asegúrese de que la opción desplegable Dentro de la celda esté marcada.
• Haga clic en Aceptar.

En esta fórmula, he reemplazado el argumento 5 con COUNTIF ($ A $ 2: $ A $ 100, ””).

La función CONTAR.SI cuenta las celdas que no están en blanco en el rango A2: A100. Por lo tanto, la función OFFSET se ajusta para incluir todas las celdas que no están en blanco.

Nota:

• Para que esto funcione, NO debe haber celdas en blanco entre las celdas que están llenas.
• Si desea crear una lista desplegable en varias celdas a la vez, seleccione todas las celdas donde desea crearla y luego siga los pasos anteriores. Asegúrese de que las referencias de celda sean absolutas (como $ A $ 2) y no relativas (como A2, A $ 2 o $ A2).

Copiar pegar listas desplegables en Excel

Puede copiar y pegar las celdas con validación de datos a otras celdas, y también copiará la validación de datos.

Por ejemplo, si tiene una lista desplegable en la celda C2 y desea aplicarla también a C3: C6, simplemente copie la celda C2 y péguela en C3: C6. Esto copiará la lista desplegable y la hará disponible en C3: C6 (junto con el menú desplegable, también copiará el formato).

Si solo desea copiar el menú desplegable y no el formato, estos son los pasos:

• Copia la celda que tiene el menú desplegable.
• Seleccione las celdas donde desea copiar el menú desplegable.
• Vaya a Inicio -> Pegar -> Pegado especial.
• En el cuadro de diálogo Pegado especial, seleccione Validación en Opciones de pegado.
• Haga clic en Aceptar.

Esto solo copiará el menú desplegable y no el formato de la celda copiada.

Precaución al trabajar con la lista desplegable de Excel

Debe tener cuidado cuando trabaje con listas desplegables en Excel.

Cuando copia una celda (que no contiene una lista desplegable) sobre una celda que contiene una lista desplegable, la lista desplegable se pierde.

La peor parte de esto es que Excel no mostrará ninguna alerta o mensaje para que el usuario sepa que se sobrescribirá un menú desplegable.

Cómo seleccionar todas las celdas que tienen una lista desplegable

A veces, es difícil saber qué celdas contienen la lista desplegable.

Por lo tanto, tiene sentido marcar estas celdas dándoles un borde distinto o un color de fondo.

En lugar de verificar manualmente todas las celdas, existe una forma rápida de seleccionar todas las celdas que tienen listas desplegables (o cualquier regla de validación de datos).

• Vaya a Inicio -> Buscar y seleccionar -> Ir a especial.
• En el cuadro de diálogo Ir a especial, seleccione Validación de datos
  • La validación de datos tiene dos opciones: Todo e Igual. Todos seleccionarían todas las celdas que tengan una regla de validación de datos aplicada. Igual seleccionaría solo aquellas celdas que tienen la misma regla de validación de datos que la celda activa.
• Haga clic en Aceptar.

Esto seleccionaría instantáneamente todas las celdas que tienen una regla de validación de datos aplicada (esto también incluye listas desplegables).

Ahora puede simplemente formatear las celdas (dar un borde o un color de fondo) para que sean visualmente visibles y no copie accidentalmente otra celda en ellas.

Aquí hay otra técnica de Jon Acampora que puede usar para mantener siempre visible el ícono de la flecha desplegable. También puede ver algunas formas de hacer esto en este video del Sr. Excel.

Creación de una lista desplegable de Excel dependiente / condicional

Aquí hay un video sobre cómo crear una lista desplegable dependiente en Excel.

Si prefiere leer antes que ver un video, siga leyendo.

A veces, puede tener más de una lista desplegable y desea que los elementos que se muestran en el segundo menú desplegable dependan de lo que el usuario seleccionó en el primer menú desplegable.

Se denominan listas desplegables dependientes o condicionales.

A continuación se muestra un ejemplo de una lista desplegable condicional / dependiente:

En el ejemplo anterior, cuando los elementos enumerados en "Menú desplegable 2" dependen de la selección realizada en "Menú desplegable 1".

Ahora veamos cómo crear esto.

Estos son los pasos para crear una lista desplegable dependiente / condicional en Excel:

• Seleccione la celda donde desea la primera lista desplegable (principal).
• Vaya a Datos -> Validación de datos. Esto abrirá el cuadro de diálogo de validación de datos.
• En el cuadro de diálogo de validación de datos, dentro de la pestaña de configuración, seleccione Lista.
• En el campo Fuente, especifique el rango que contiene los elementos que se mostrarán en la primera lista desplegable.
• Haga clic en Aceptar. Esto creará el menú desplegable 1.
• Seleccione el conjunto de datos completo (A1: B6 en este ejemplo).
• Vaya a Fórmulas -> Nombres definidos -> Crear a partir de la selección (o puede usar el atajo de teclado Control + Mayús + F3).
• En el cuadro de diálogo "Crear nombre a partir de la selección", marque la opción Fila superior y desmarque todas las demás. Al hacer esto, se crean dos rangos de nombres ("Frutas" y "Verduras"). El rango de frutas con nombre se refiere a todas las frutas de la lista y el rango de verduras con nombre se refiere a todas las verduras de la lista.
• Haga clic en Aceptar.
• Seleccione la celda donde desea la lista desplegable Dependiente / Condicional (E3 en este ejemplo).
• Vaya a Datos -> Validación de datos.
• En el cuadro de diálogo Validación de datos, dentro de la pestaña de configuración, asegúrese de que Lista esté seleccionada.
• En el campo Fuente, ingrese la fórmula = INDIRECTO (D3). Aquí, D3 es la celda que contiene el menú desplegable principal.
• Haga clic en Aceptar.

Ahora, cuando realiza la selección en el menú desplegable 1, las opciones enumeradas en la lista desplegable 2 se actualizarán automáticamente.

Descargar el archivo de ejemplo

¿Como funciona esto? - La lista desplegable condicional (en la celda E3) se refiere a = INDIRECTO (D3). Esto significa que cuando selecciona "Frutas" en la celda D3, la lista desplegable en E3 se refiere al rango denominado "Frutas" (a través de la función INDIRECTA) y, por lo tanto, enumera todos los elementos de esa categoría.

Nota importante al trabajar con listas desplegables condicionales en Excel:

• Cuando haya realizado la selección y luego cambie el menú desplegable principal, el menú desplegable dependiente no cambiará y, por lo tanto, será una entrada incorrecta. Por ejemplo, si selecciona los EE. UU. Como el país y luego selecciona Florida como el estado, y luego regresa y cambia el país a India, el estado permanecerá como Florida. Aquí hay un gran tutorial de Debra sobre cómo borrar listas desplegables dependientes (condicionales) en Excel cuando se cambia la selección.
• Si la categoría principal tiene más de una palabra (por ejemplo, 'Frutas de temporada' en lugar de 'Frutas'), entonces debe usar la fórmula = INDIRECTO (SUSTITUIR (D3, ”“, ”_”)), en lugar de función INDIRECTA simple mostrada arriba. La razón de esto es que Excel no permite espacios en rangos con nombre. Entonces, cuando crea un rango con nombre con más de una palabra, Excel inserta automáticamente un guión bajo entre las palabras. Por lo tanto, el rango denominado "Frutas de temporada" sería "Frutas de temporada". El uso de la función SUSTITUIR dentro de la función INDIRECTO asegura que los espacios son convertido en guiones bajos.