Blog de Ninja ExcelLista desplegable ExcelCómo hacer una lista desplegable en Excel

Cómo hacer una lista desplegable en Excel

Carteles con opciones

Cómo crear una lista desplegable en Excel

¿Qué es una lista desplegable?

Las LISTAS DESPLEGABLES son una característica de la validación de datos en Excel.  Una lista desplegable en una celda es un menú o lista que se abre y muestra los diferentes valores para rellenar la celda.  Así, al seleccionar un elemento de la lista, la celda se rellena automáticamente.

  • Finalidad:  Automatizar y facilitar el rellenado de celdas limitando las opciones o elementos al rellenar la celda.
  • Ventajas:  La entrada de datos es más rápida y se evitan errores al restringir el valor de una celda a los elementos de una lista desplegable.
  • Uso:  Al llenar una celda que tiene asociada una lista desplegable puedes hacer click en la flecha de la derecha de la celda para desplegar la lista y elegir una opción o puedes escribir tú mismo la palabra.  Si optas por la segunda opción, en general sólo te dejará ingresar los elementos de la lista desplegable.

Elementos que contiene una lista desplegable

Una lista desplegable contiene 4 elementos.  La siguiente figura muestra cada uno de ellos.

Elementos de una lista desplegable de excel
  1. Tema de la lista desplegable

Nos muestra a qué categoría pertenecen los elementos que se encuentran dentro de la lista.  En este caso, el tema es “País” por lo tanto sabemos que dentro de la lista aparecerán diferentes países.

  1. Celda que contiene la lista desplegable

En esta celda se encuentra la lista por lo tanto si hacemos click en alguna opción, aparecerá en esta celda.

  1. Botón de la lista

Nos indica que en la celda hay una lista desplegable, si hacemos click en el botón aparecerá una lista con todas las opciones disponibles.

  1. Lista de opciones

Es una ventana que nos indica los diferentes valores de la lista y de los cuales sólo se podrá elegir uno solo.

¿Cómo insertarlas?

Considera que tienes un formulario en que cada persona debe rellenar sus datos y quieres que en la sección de “País” se tenga sólo ciertas opciones.

  1. Abre la hoja de cálculo de Excel
Listas desplegables en Excel muestra la la hoja de cálculo
  1. Selecciona la celda o las celdas en las que quieras crear tu lista.  En este caso, seleccionamos la celda C6 ya que es ahí donde debe ir el país.
Celda en donde insertaremos una lista desplegable de excel
  1. Haz click en Datos > Validación de datos.
Donde se encuentra la validación de datos en Excel para crear la lista desplegable en excel.
  1. En el cuadro de dialogo “Validación de datos” haz click en “Lista”.
Cómo crear un menú de opciones o lista desplegable en Excel
  1. Junto a “Origen” debes insertar las opciones que se encontrarán en la lista, elige una opción:
  • Lista a partir de un intervalo: elige las celdas que quieras incluir en la lista.
Lista desplegable en excel con datos en diferentes celdas
  • Lista de elementos: introduce los elementos separados por puntos y comas y sin espacios.
Lista desplegable en excel escribiendo los datos a utilizar

Tip Ninja:  Notar que al ingresar las opciones escribiéndolas manualmente en “Origen”, la lista se vuelve sensible a las mayúsculas y minúsculas.  Por lo que al ingresar “argentina” Excel nos mostrará un error diciendo que lo ingresado no se encuentra en la lista predefinida.  Para evitar esto, te recomendamos utilizar la opción de agregar las celdas que contienen los datos.

  1. La celda seleccionada tendrá una flecha hacia abajo.  Para quitarla, desmarca “Celda con lista desplegable”.
Celda que contiene la lista desplegable tendrá una flecha que se puede quitar o eliminar
  1. Si introduces en una celda de datos que no coinciden con ningún elemento de la lista, aparecerá un aviso.  Si quieres que los usuarios puedan introducir elementos de la lista, selecciona “Mensaje de error” y luego desmarca “Mostrar mensaje de error si se introducen datos no válidos”.
Permitir en la lista desplegable de excel que se puedan introducir datos que no coincidan con los elementos de la lista.  Desmarcar "Mostrar mensaje de error si se introducen datos no válidos"

Tip Ninja: En la misma pestaña de “Mensaje de error” puedes elegir el “Estilo” del mensaje. Si eliges “Advertencia” o “Información” te deja ingresar un elemento fuera de la lista pero con una advertencia o ventana informativa, respectivamente.

  1. Haz click en “Aceptar”.  ¡Listo!  Para cambiar el color de una celda en función de la opción seleccionada, usa el formato condicional.
Crear lista desplegable en Excel muestra el menú de opciones creado

Tip Ninja: Si quieres aprender a utilizar el formato condicional, te recomendamos visitar este blog.

Si quedaste con alguna duda sobre cómo crear un menú desplegable en Excel, te recomendamos ver el siguiente video.

¿Cómo modificar una lista desplegable?

Excel permite modificar las listas desplegables creadas.  En nuestra lista de países, queremos agregar “Estados Unidos” y eliminar “Venezuela”, para esto debes seguir los siguientes pasos:

  1. Selecciona las celdas que quieras cambiar, en este caso, la celda que contiene nuestra lista desplegable.
  2. Haz click en Datos > Validación de datos.
  3. Para cambiar las opciones que aparecen, modifica los elementos junto a “Origen”.  En este caso, eliminamos Venezuela y agregamos Estados Unidos.
Modificar o cambiar una lista desplegable en Excel
  1. Haz click en “Aceptar” ¡Listo, ya está modificada!
Lista desplegable modificada en Excel

¿Cómo ocultar los datos de origen?

Cuando creas una lista a partir de datos que se encuentran en celdas, pero no quieres que se vean, debes seguir los siguientes pasos.

  1. Los datos que ingresarás en la lista desplegable deben encontrarse en una nueva pestaña.  En el formulario, escribimos la lista de países en la pestaña llamada “Países”.
Ocultar los datos de origen de una lista desplegable en Excel
  1. Creas la lista desplegable utilizando los datos de la pestaña creada.
Crear lista desplegable con datos en otra pestaña para luego eliminar los datos de origen
  1. Ahora, debemos ocultar esta nueva pestaña.  Haz click derecho en el nombre de la pestaña y haz click en “Ocultar”.
Cómo ocultar una pestaña en excel para una lista desplegable
  1. ¡Listo, ya tienes tu formulario con la lista de los datos oculta!

Eliminar una lista desplegable

Para eliminar una lista desplegable de Excel debes seguir los siguientes pasos:

  1. Selecciona la celda donde tienes la lista desplegable.  En nuestro ejemplo, la celda C6.
  2. Anda a la pestaña de “Datos” y haz click en “Validación de datos”.
  3. Selecciona “Borrar todo” y haz click en “Aceptar”.
Cómo eliminar una o varias listas desplegables en excel pasos

Tip Ninja: si quieres eliminar todas las celdas con listas desplegables puedes hacer click en “Aplicar estos cambios a otras celdas con la misma configuración” en la misma ventana de validación de datos.

Lista desplegable dinámica

Existe una fórmula que puedes usar para que la lista desplegable se vaya ampliando de manera automática cuando agregas un elemento al final de la lista de datos.

  1. Ve a la hoja donde tienes formulario y haz click en la celda que la que queremos crear la lista desplegable. En este caso es la C6.
  2. En la sección “Origen” escribe: =DESREF(‘Países’!$A$1;0;0;CONTARA(‘Países!$A:$A);1)
Lista desplegable dinámica en excel
  1. Haz click en “Aceptar”.

¿Qué hace la función DESREF?

La función RESREF (OFFSET en inglés) en Excel retorna una celda o un conjunto de celdas para un número definido de filas y columnas.

Sintaxis: RESREF(referencia; filas; columnas; [altura]; [ancho])

  • referencia: celda a partir de la cual se selecciona
  • filas: es el número de filas, hacia arriba o hacia abajo, al que desea que haga referencia la celda.
  • columnas: es el número de columnas, hacia la derecha o izquierda, al que desea que haga referencia la celda.
  • [altura]: es el alto, en número de filas, que se desea que tenga la referencia. Es opcional.
  • [ancho]: es el ancho, en número de columnas, que se desea que tenga la referencia. Es opcional.

¿Qué hace la función CONTARA?

La función CONTARA (COUNTA en inglés) cuenta la cantidad de celdas que no vacías en un intervalo.

Sintaxis: CONTARA (valor1;[valor2];…)

  • valor1: celda a partir de la cual se contar
  • valor2,…: otros valores que se quieren contar. Es opcional.

De esta manera, al escribir DESREF(‘Países!$A$1,0,0,CONTARA(‘Países!$A:$A),1), queremos decir:

  • referencia =  ‘Países!$A$1: Selecciona A1 de la hoja de opciones de países (referencia)
  • filas = 0: No moverse hacia arriba ni abajo
  • columnas = 0: No moverse hacia los lados
  • altura = “CONTARA(‘Países!$A:$A)”): el largo de la columna debe ser la cantidad de celdas no vacías en la columna A.
  • ancho = 1: Sólo una columna.
  1. Así, al agregar elementos debajo de la original, se van a incorporar automáticamente a la lista desplegable
lista desplegable dinámica en excel
lista dinámica en excel
lista desplegable en excel

Listas desplegables dependientes

Una práctica avanzada y muy útil es hacer listas desplegables dependientes. Esto es una lista desplegable que dependa de la opción elegida de otra lista desplegable anterior.

Por ejemplo, en el mismo formulario que en los ejercicios anteriores, ahora quieres agregar el ítem “Ciudad”, pero quieres que se abra una lista distinta si es que se elige Chile o se elige México.

lista desplegable dependiente en excel

Para crear una lista desplegable dependiente sigue los siguientes pasos:

  1. En una tabla aparte o una hoja distinta a la del formulario debes rellenar con las opciones tanto de país como de ciudad7elementos lista despleglable
  2. Asígnale un nombre a cada rango (conjunto) de celdas:
    1. Para esto selecciona una de las listas. Por ejemplo, selecciona todos los países.
    2. Anda a la pestaña de “Fórmulas” y haz click en “Asignar nombre”.
elementos lista desplegable dependiente
  1. Asígnale el nombre de la lista. En este caso sería “Países”. Haz click en “Aceptar”.
elementos lista desplegable dependiente
  1. Hacer lo mismo para todas las listas. Si haces click en “Administrador de nombres” puedes ver todos los rangos de celdas a los que les asignaste nombres.
elementos lista desplegable dependiente
  1. Ahora, selecciona la celda de la lista desplegable “madre” (de la que su respuesta depende la otra lista). En este caso es C6, que es para rellenar países.
  2. Crea la lista desplegable y en la sección “Origen” escribe “=Países” y haz click en “Aceptar”.
lista desplegable dependiente
lista desplegable dependiente
  1. Ahora, anda la lista desplegable que queremos que dependa de la respuesta de la lista desplegable anterior. En este caso es C7, que es para rellenar ciudades.
  2. Anda a la pestaña de “Datos” y haz click en “Validación de datos” y elige “Lista” en la sección de “Permitir”.
  3. En la sección “Origen” escribe “=INDIRECTO($C$6)” y haz click en “Aceptar” .
lista desplegable dependiente

¿Qué hace la función INDIRECTO?

La función INDIRECTO convierte texto en una referencia válida de celda. Así, obtiene el valor de la celda de Países, como “Chile” y lo hace que Excel lo lea como el rango llamado Chile, así se despliega es misma lista.

De esta manera, si elegimos Colombia como país, entonces se despliega la siguiente lista:

lista desplegable dependiente

Si elegimos Perú, entonces se despliega esta lista:

lista desplegable dependiente

Preguntas Frecuentes

¿Cómo copiar y pegar una lista desplegable en Excel?

Si quieres aplicar la lista desplegable a varias celdas sólo debes seleccionar la celda que contiene la lista y arrastrar desde la esquina inferior derecha al resto de las celdas.  De esta forma se aplicará a todas las celdas.

¿Cómo enumerar una lista en Excel?

Para enumerarlas, debemos escribir todas las opciones de la lista desplegable en una nueva pestaña de Excel y luego, al crear la lista desplegable seleccionar esa lista.

Esta lista de datos puede ser ordenada en orden alfabético, enumerarlas con números ascendentes, en desorden y muchas opciones más.

En el siguiente video podrás ver cómo copiar una lista desplegable, además de ver cómo realizar una lista desplegable a partir de una lista de datos.

Preguntas Frecuentes

¿Cómo crear una lista desplegable?

Para crear listas desplegables en Excel utilice la herramienta de Validación de Datos. En Datos > Validación de datos > Elije la opción “Lista” > Selecciona la lista de elementos > Clic en Aceptar y listo

¿Qué es una lista desplegable?

Una lista desplegable en Excel es un selector de opciones que te permite trabajar de forma más eficiencte tus hojas de Excel, con su ayuda podrás desplegar y seleccionar la opción que corresponda para cada celda.

¿Qué ventajas tiene usar listas desplegables?

Ayuda a que los usuarios a que trabajen de forma más eficiente en las hojas de cálculo mediante el uso de listas desplegables en celdas.

Logo ninja excel blanco

La plataforma líder de capacitación en Excel para empresas.

Compañía

Copyrigth © 2022 Ninja Excel.