Lista desplegable de Excel: crear, editar, eliminar y operaciones más avanzadas
Una lista desplegable es similar a un cuadro de lista que permite a los usuarios elegir un valor de una lista de selección. Este tutorial demostrará las operaciones básicas para la lista desplegable: crear, editar y eliminar la lista desplegable en Excel. Aparte de eso, este tutorial proporciona operaciones avanzadas para la lista desplegable para mejorar su funcionalidad y resolver más problemas de Excel.
Tabla de contenido: [ Ocultar ]
Crear una lista desplegable simple
Para usar una lista desplegable, primero debe aprender a crearla. Esta sección proporciona 6 formas de ayudarlo a crear una lista desplegable en Excel.
Crear una lista desplegable a partir de un rango de celdas
A continuación, demuestre los pasos para crear una lista desplegable a partir de un rango de celdas en Excel. Por favor haz lo siguiente
1. Seleccione un rango de celdas para ubicar la lista desplegable.
Tips: Puede crear una lista desplegable para varias celdas no contiguas al mismo tiempo manteniendo presionada la tecla Ctrl mientras selecciona las celdas una por una.
2. Hacer clic en Datos > Validación de datos > Validación de datos.
3. En el Validación de datos cuadro de diálogo, debajo del Ajustes pestaña, configure de la siguiente manera.
Notas:
Ahora se crea la lista desplegable. Al hacer clic en la celda de la lista desplegable, aparecerá una flecha junto a ella, haga clic en la flecha para expandir la lista y luego podrá elegir un elemento de ella.
Crear una lista desplegable dinámica de la tabla
Puede convertir su rango de datos en una tabla de Excel y luego crear una lista desplegable dinámica basada en el rango de la tabla.
1. Seleccione el rango de datos originales y luego presione el Ctrl + T llaves.
2. Hacer clic en OK en el popping up Crear mesa caja de diálogo. Luego, el rango de datos se convierte en tabla.
3. Seleccione un rango de celdas para colocar la lista desplegable y luego haga clic en Datos > Validación de datos > Validación de datos.
4. En el Validación de datos cuadro de diálogo, debe:
Luego se crean listas desplegables dinámicas. Al agregar o eliminar datos del rango de la tabla, los valores en la lista desplegable se actualizarán automáticamente.
Cree una lista desplegable dinámica con fórmulas
Además de crear una lista desplegable dinámica a partir del rango de la tabla, también puede usar una fórmula para crear una lista desplegable dinámica en Excel.
1. Seleccione las celdas donde mostrar las listas desplegables.
2. Hacer clic en Datos > Validación de datos > Validación de datos.
3. En el Validación de datos cuadro de diálogo, configure de la siguiente manera.
Luego se crean listas desplegables dinámicas. Al agregar o eliminar datos del rango específico, los valores en las listas desplegables se actualizarán automáticamente.
Crear una lista desplegable a partir del rango con nombre
También puede crear una lista desplegable a partir de un rango con nombre en Excel.
1. En primer lugar, cree un rango con nombre. Seleccione el rango de celdas en el que creará el rango con nombre y luego escriba el nombre del rango en el Nombre caja y prensa Participar clave.
2. Hacer clic en Datos > Validación de datos > Validación de datos.
3. En el Validación de datos cuadro de diálogo, configure de la siguiente manera.
Ahora se crea la lista desplegable que usa datos de un rango con nombre.
Crear una lista desplegable desde otro libro de trabajo
Suponiendo que hay un libro de trabajo llamado "Datos fuente", Y desea crear una lista desplegable en otro libro de trabajo en función de los datos de este"Datos fuente”, Haga lo siguiente.
1. Abra el libro de trabajo "SourceData". En este libro de trabajo, seleccione los datos en los que creará la lista desplegable, escriba un nombre de rango en el Nombre cuadro, y luego presione el Participar clave.
Aquí nombro el rango como Ciudad.
2. Abra la hoja de trabajo que insertará en la lista desplegable. Hacer clic Fórmulas > Definir nombre.
3. En el Nuevo nombre cuadro de diálogo, debe crear un rango con nombre basado en el nombre del rango que creó en el libro de trabajo "SourceData", configure de la siguiente manera.
Notas:
4. Abra el libro de trabajo que insertará en la lista desplegable, seleccione las celdas de la lista desplegable y luego haga clic en Datos > Validación de datos > Validación de datos.
5. En el Validación de datos cuadro de diálogo, configure de la siguiente manera.
Ahora las listas desplegables se han insertado en el rango seleccionado. Y los valores desplegables son de otro libro de trabajo.
Cree fácilmente una lista desplegable con una herramienta increíble
Aquí recomiendo encarecidamente el Cree una lista desplegable simple utilidad de Kutools for Excel. Con esta función, puede crear fácilmente una lista desplegable con valores de celda específicos o crear una lista desplegable con listas personalizadas preestablecidas en Excel.
1. Seleccione las celdas que desea insertar en la lista desplegable y luego haga clic en Kutools > La lista desplegable > Cree una lista desplegable simple.
2. En el Crear una lista desplegable simple cuadro de diálogo, configure de la siguiente manera.
Note: Si desea crear una lista desplegable basada en la lista personalizada preestablecida en Excel, seleccione el Listas personalizadas opción en el Fuente sección, elija una lista personalizada en el Listas personalizadas cuadro, y luego haga clic en el OK del botón.
Ahora las listas desplegables se han insertado en el rango seleccionado.
Editar lista desplegable
Si desea editar la lista desplegable, los métodos de esta sección pueden hacerle un favor.
Edite una lista desplegable basada en un rango de celdas
Para editar una lista desplegable basada en un rango de celdas, haga lo siguiente.
1. Seleccione las celdas que contienen la lista desplegable que desea editar y luego haga clic en Datos > Validación de datos > Validación de datos.
2. En el Validación de datos cuadro de diálogo, cambie las referencias de celda en el Fuente cuadro y luego haga clic en el OK del botón.
Edite una lista desplegable basada en un rango con nombre
Supongamos que agrega o elimina valores en el rango con nombre, y la lista desplegable se crea en función de este rango con nombre. Para que aparezcan los valores actualizados en listas desplegables, haga lo siguiente.
1. Hacer clic en Fórmulas > Administrador de nombre.
Tips: Puede abrir el Administrador de nombre ventana presionando el Ctrl + F3 llaves.
2. En el Administrador de nombre ventana, debe configurar de la siguiente manera:
3. Entonces un Microsoft Excel aparece el cuadro de diálogo, haga clic en el Sí Para guardar los cambios.
Luego, se actualizan las listas desplegables basadas en este rango con nombre.
Quitar lista desplegable
Esta sección habla de eliminar la lista desplegable en Excel.
Eliminar lista desplegable con Excel integrado
Excel proporciona una función incorporada para ayudar a eliminar la lista desplegable de la hoja de trabajo. Haz lo siguiente.
1. Seleccione el rango de celdas que contiene la lista desplegable que desea eliminar.
2. Hacer clic en Datos > Validación de datos > Validación de datos.
3. En el Validación de datos cuadro de diálogo, haga clic en Remover todo botón, y luego haga clic OK Para guardar los cambios.
Ahora las listas desplegables se eliminan del rango seleccionado.
Elimina fácilmente listas desplegables con una herramienta increíble
Kutools for Excel proporciona una herramienta útil - Limpiar la restricción de validación de datoss para ayudar a eliminar fácilmente la lista desplegable de uno o varios rangos seleccionados a la vez. Haz lo siguiente.
1. Seleccione el rango de celdas que contiene la lista desplegable que desea eliminar.
2. Hacer clic en Kutools > Evitar escribir > Limpiar las restricciones de validación de datos. Ver captura de pantalla:
3. Entonces un Kutools for Excel Aparece el cuadro de diálogo para preguntarle si borra la lista desplegable, haga clic en el OK del botón.
Luego, las listas desplegables en este rango seleccionado se eliminan inmediatamente.
Agregar color a la lista desplegable
En algunos casos, es posible que deba hacer una lista desplegable codificada por colores para distinguir los datos en las celdas de la lista desplegable de un vistazo. Esta sección proporciona dos métodos para ayudarlo a resolver el problema en detalle.
Agregue color a la lista desplegable con formato condicional
Puede crear reglas condicionales para la celda que contiene la lista desplegable para codificarla por colores. Haz lo siguiente.
1. Seleccione las celdas que contienen la lista desplegable que desea codificar por colores.
2. Hacer clic en Inicio > Formato condicional > Administrar reglas.
3. En el Administrador de tarifas de formato condicional cuadro de diálogo, haga clic en Nueva regla del botón.
4. En el Nueva regla de formato cuadro de diálogo, configure de la siguiente manera.
5. Cuando vuelva a la Administrador de reglas de formato condicional cuadro de diálogo, repita los pasos 3 y 4 anteriores para especificar colores para otros elementos desplegables. Después de terminar de especificar los colores, haga clic en el OK Para guardar los cambios.
A partir de ahora, al seleccionar un elemento de la lista desplegable, la celda se resaltará con el color de fondo especificado según el texto seleccionado.
Agregue color fácilmente a la lista desplegable con una herramienta increíble
Aquí presentamos el Lista desplegable de colores característica de Kutools for Excel para ayudarlo a agregar fácilmente color a la lista desplegable en Excel.
1. Seleccione las celdas que contienen la lista desplegable a las que desea agregar color.
2. Hacer clic en Kutools > La lista desplegable > Lista desplegable de colores.
3. En el Lista desplegable de colores cuadro de diálogo, haga lo siguiente.
Tips: Si desea resaltar filas según la selección de la lista desplegable, elija el Fila de rango de datos opción en el Aplicar a sección, y luego seleccione las filas que resaltará en el Resaltar filas encajonar.
Ahora las listas desplegables están codificadas por colores como se muestra en las capturas de pantalla a continuación.
Resaltar celdas según la selección de la lista desplegable
Resaltar filas según la selección de la lista desplegable
Cree una lista desplegable dependiente en Excel u hoja de Google
Una lista desplegable dependiente ayuda a mostrar las opciones según el valor seleccionado en la primera lista desplegable. Si necesita crear una lista desplegable dependiente (en cascada) en la hoja de cálculo de Excel o en la hoja de Google, los métodos de esta sección pueden hacerle un favor.
Cree una lista desplegable dependiente en la hoja de cálculo de Excel
La siguiente demostración muestra la lista desplegable dependiente en la hoja de cálculo de Excel.
Haz clic ¿Cómo crear una lista desplegable en cascada dependiente en Excel? para ver una guía tutorial paso a paso.
Cree una lista desplegable dependiente en la hoja de Google
Si desea crear una lista desplegable dependiente en la hoja de Google, consulte ¿Cómo crear una lista desplegable dependiente en la hoja de Google?
Cree listas desplegables con capacidad de búsqueda
Para las listas desplegables que contienen una larga lista de elementos en una hoja de trabajo, no es fácil para usted seleccionar un determinado elemento de la lista. Si recuerda los caracteres iniciales o varios caracteres consecutivos de un elemento, puede realizar la función de búsqueda en una lista desplegable para filtrarlo fácilmente. Esta sección demostrará cómo crear una lista desplegable de búsqueda en Excel.
Suponiendo los datos de origen que desea crear, una lista desplegable basada en las ubicaciones en la columna A de Sheet1 como se muestra en la captura de pantalla siguiente. Haga lo siguiente para crear una lista desplegable de búsqueda en Excel con estos datos.
1. En primer lugar, cree una columna auxiliar junto a la lista de datos de origen con una fórmula de matriz.
En este caso, selecciono la celda B2, ingreso la fórmula a continuación y luego presiono el Ctrl + Shift + Participar claves para obtener el primer resultado.
=IFERROR(INDEX($A$2:$A$50,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),$A$2:$A$50)>0,$A$2:$A$50,""),$A$2:$A$50,0),""),ROW(A1))),"")
Seleccione la primera celda de resultado y luego arrastre su Llene la manija hasta llegar al final de la lista.
Note: En esta fórmula de matriz, $ A $ 2: $ A $ 50 es el rango de datos de origen en el que creará la lista desplegable. Cámbielo según su rango de datos.
2. Hacer clic en Fórmulas > Definir nombre.
3. En el Editar nombre cuadro de diálogo, configure de la siguiente manera.
Ahora necesita crear la lista desplegable basada en el rango nombrado. En este caso, crearé una lista desplegable de búsqueda en Sheet2.
4. Abra la Hoja2, seleccione el rango de celdas de la lista desplegable y luego haga clic en Datos > Validación de datos > Validación de datos.
5. En el Validación de datos cuadro de diálogo, haga lo siguiente.
6. Haga clic con el botón derecho en la pestaña de la hoja (Hoja2) y seleccione Ver código desde el menú contextual.
7. En la apertura Microsoft Visual Basic para aplicaciones ventana, copie el siguiente código VBA en el editor de código.
Código VBA: cree una lista desplegable de búsqueda en Excel
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
8. presione el otro + Q llaves para cerrar el Microsoft Visual Basic para aplicaciones ventana.
Ahora se crean las listas desplegables de búsqueda. Si desea recoger un elemento, simplemente ingrese uno o varios caracteres consecutivos de este elemento en la celda desplegable, haga clic en la flecha desplegable y, a continuación, el elemento basado en el contenido ingresado aparecerá en la lista desplegable. Ver captura de pantalla:
Note: Este método distingue entre mayúsculas y minúsculas.
Crear una lista desplegable pero mostrar valores diferentes
Suponiendo que haya creado una lista desplegable, al seleccionar un elemento de ella, desea que se muestre algo más en la celda. Como se muestra en la siguiente demostración, ha creado una lista desplegable basada en la lista de nombres de países, al seleccionar el nombre del país en el menú desplegable, desea mostrar la abreviatura del nombre del país seleccionado en la celda desplegable. Esta sección proporciona el método VBA para ayudarlo a resolver el problema.
1. En el lado derecho de los datos de origen (la columna del nombre del país), cree una nueva columna que contenga la abreviatura de los nombres de los países que desea mostrar en la celda desplegable.
2. Seleccione tanto la lista de nombres de países como la lista de abreviaturas, escriba un nombre en el Nombre cuadro y luego presione el Participar clave.
3. Seleccione las celdas de la lista desplegable (aquí selecciono D2: D8) y luego haga clic en Datos > Validación de datos > Validación de datos.
4. En el Validación de datos cuadro de diálogo, configure de la siguiente manera.
5. Después de crear la lista desplegable, haga clic derecho en la pestaña de la hoja y seleccione Ver código desde el menú contextual.
6. En la apertura Microsoft Visual Basic para aplicaciones ventana, copie el siguiente código VBA en el editor de código.
Código de VBA: muestra diferentes valores en la lista desplegable
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20201027
selectedNa = Target.Value
If Target.Column = 4 Then
selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub
Notas:
7. presione el otro + Q llaves para cerrar el Microsoft Visual Basic para aplicaciones ventana.
A partir de ahora, al seleccionar un determinado nombre de país de la lista desplegable, se mostrará en la celda la abreviatura correspondiente del nombre del país seleccionado.
Cree una lista desplegable con casillas de verificación
Muchos usuarios de Excel tienden a crear una lista desplegable con varias casillas de verificación para que puedan seleccionar varios elementos de la lista con solo marcar las casillas de verificación.
Como se muestra en la siguiente demostración, al hacer clic en la celda que contiene la lista desplegable, aparece un cuadro de lista. En el cuadro de lista, hay una casilla de verificación antes de cada elemento. Puede marcar las casillas de verificación para mostrar los elementos correspondientes en la celda.
Si desea crear una lista desplegable con casillas de verificación en Excel, consulte ¿Cómo crear una lista desplegable con varias casillas de verificación en Excel?.
Agregar autocompletar a la lista desplegable
Si tiene una lista desplegable de validación de datos con elementos grandes, debe desplazarse hacia arriba y hacia abajo en la lista para encontrar el adecuado, o escribir la palabra completa en el cuadro de lista directamente. Si la lista desplegable se puede completar automáticamente al escribir la primera letra en ella, todo será más fácil.
Para hacer que la lista desplegable se complete automáticamente en una hoja de cálculo en Excel, consulte ¿Cómo autocompletar al escribir en la lista desplegable de Excel?.
Filtrar datos según la selección de la lista desplegable
Esta sección demostrará cómo aplicar fórmulas para crear un filtro de lista desplegable para extraer datos basados en la selección de la lista desplegable.
1. En primer lugar, debe crear una lista desplegable con los valores específicos en los que extraerá los datos.
Tips: Siga los pasos anteriores para crear una lista desplegable en Excel.
Cree una lista desplegable con una lista única de elementos
Si hay duplicados en su rango y no desea crear una lista desplegable con la repetición de un elemento, puede crear una lista única de elementos de la siguiente manera.
1) Copie las celdas que creará la lista desplegable basada en Ctrl + C claves y luego péguelas en un nuevo rango.
2) Seleccione las celdas en el nuevo rango, haga clic en Datos > Eliminar duplicados.
3) en el Eliminar duplicados cuadro de diálogo, haga clic en OK del botón.
4) Entonces un Microsoft Excel aparece para decirle cuántos duplicados se eliminan, haga clic en OK.
Ahora que obtiene la lista única de elementos, puede crear una lista desplegable basada en esta lista única ahora.
2. Luego, debe crear tres columnas auxiliares de la siguiente manera.
3. Cree un rango basado en el rango de datos original para generar los datos extraídos con las fórmulas siguientes.
Notas:
Ahora que se crea un filtro de lista desplegable, puede extraer fácilmente datos del rango de datos original según la selección de la lista desplegable.
Seleccione varios elementos de la lista desplegable
De forma predeterminada, la lista desplegable permite a los usuarios seleccionar solo un elemento por vez en una celda. Al volver a seleccionar un elemento en una lista desplegable, se sobrescribirá el elemento seleccionado anteriormente. Sin embargo, si se le pide que seleccione varios elementos de una lista desplegable y los muestre todos en la celda desplegable como se muestra en la siguiente demostración, ¿cómo puede hacerlo?
Para seleccionar varios elementos de la lista desplegable en Excel, consulte ¿Cómo crear una lista desplegable con múltiples selecciones o valores en Excel?. Este tutorial proporciona dos métodos en detalle para ayudarlo a resolver el problema.
Establecer valor predeterminado (preseleccionado) para la lista desplegable
De forma predeterminada, una celda de la lista desplegable se muestra en blanco, la flecha desplegable solo aparece cuando hace clic en la celda. ¿Cómo averiguar qué celdas contienen listas desplegables en una hoja de trabajo de un vistazo?
Esta sección demostrará cómo establecer el valor predeterminado (preseleccionado) para la lista desplegable en Excel. Haz lo siguiente.
Antes de aplicar los dos métodos siguientes, debe crear una lista desplegable y realizar algunas configuraciones de la siguiente manera.
1. Seleccione las celdas de la lista desplegable, haga clic en Datos > Validación de datos > Validación de datos.
Tips: Si ya ha creado la lista desplegable, seleccione las celdas que contienen la lista desplegable y luego haga clic en Datos > Validación de datos > Validación de datos.
2. En el Validación de datos cuadro de diálogo, configure de la siguiente manera.
Después de crear la lista desplegable, aplique uno de los siguientes métodos para establecer el valor predeterminado para ellos.
Establecer valor predeterminado para la lista desplegable con fórmula
Puede aplicar la siguiente fórmula para establecer el valor predeterminado para la lista desplegable que creó como se muestran los pasos anteriores.
1. Seleccione la celda de la lista desplegable, ingrese la fórmula a continuación y luego presione el botón Participar para mostrar el valor predeterminado. Si las celdas de la lista desplegable son consecutivas, puede arrastrar el Llene la manija de la celda de resultado para aplicar la fórmula a otras celdas.
= SI (C2 = "", "--Elegir elemento de la lista--")
Notas:
Establezca el valor predeterminado para todas las listas desplegables en una hoja de trabajo a la vez con código VBA
Suponiendo que hay muchas listas desplegables ubicadas en diferentes rangos en su hoja de trabajo, para establecer el valor predeterminado para todas, debe aplicar la fórmula repetidamente. Eso lleva mucho tiempo. Esta sección proporciona un código VBA útil para que establezca un valor predeterminado para todas las listas desplegables en una hoja de trabajo a la vez.
1. Abra la hoja de trabajo que contiene las listas desplegables que desea establecer el valor predeterminado, presione el otro + F11 teclas para abrir el Microsoft Visual Basic para aplicaciones ventana.
2. En el Microsoft Visual Basic para aplicaciones ventana, haga clic recuadro > Móduloy luego pegue el siguiente código VBA en la ventana Código.
Código de VBA: establezca el valor predeterminado para todas las listas desplegables en una hoja de trabajo a la vez
Sub SetDropDownListToDefaultValue()
'Updated by Extendoffice 20201026
Dim xWs As Worksheet
Dim xRg, xFRg As Range
Dim xET: xET = Null
Dim xStr As String
xStr = "- Choose from the list -"
Set xWs = Application.ActiveSheet
Set xRg = xWs.UsedRange.Cells
On Error Resume Next
For Each xFRg In xRg
xET = Null
xET = xFRg.Validation.Type
If Not IsNull(xET) Then
If xFRg.Validation.Type = 3 Then
xFRg.Value = "'" & xStr
End If
End If
Next
End Sub
Notas: En el código anterior, - Escoge de la lista - es el valor predeterminado que se muestra en la celda de la lista desplegable. También puede cambiar el valor predeterminado según sus necesidades.
3. presione el F5 , luego aparece un cuadro de diálogo Macros, asegúrese de DropDownListToDefault está seleccionado en el Nombre de macro cuadro, y luego haga clic en el Ejecutar botón para ejecutar el código.
Luego, el valor predeterminado especificado se completa en las celdas de la lista desplegable inmediatamente.
Aumentar el tamaño de fuente de la lista desplegable
Normalmente, la lista desplegable tiene un tamaño de fuente fijo, si el tamaño de fuente es tan pequeño para leer, puede probar el siguiente método VBA para ampliarlo.
1. Abra la hoja de trabajo que contiene las listas desplegables cuyo tamaño de fuente desea ampliar, haga clic con el botón derecho en la pestaña de la hoja y luego seleccione Ver código desde el menú contextual.
2. En el Microsoft Visual Basic para aplicaciones ventana, copie el siguiente código VBA en el editor de código.
Código de VBA: aumente el tamaño de fuente de las listas desplegables en una hoja de trabajo
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice 20201027
On Error GoTo LZoom
Dim xZoom As Long
xZoom = 100
If Target.Validation.Type = xlValidateList Then xZoom = 130
LZoom:
ActiveWindow.Zoom = xZoom
End Sub
Note: aquí Zoom x = 130 en el código significa que ampliará el tamaño de fuente de todas las listas desplegables en la hoja de trabajo actual a 130. Puede cambiarlo cuando lo necesite.
3. presione el otro + Q llaves para cerrar el Microsoft Visual Basic para aplicaciones ventana.
De ahora en adelante, al hacer clic en la celda desplegable, se ampliará el nivel de zoom de la hoja de trabajo actual, haga clic en la flecha desplegable, puede ver que el tamaño de fuente de todos los elementos desplegables también se agranda.
Después de seleccionar el elemento de la lista desplegable, puede hacer clic en cualquier celda fuera de la celda desplegable para volver al nivel de zoom original.
Las mejores herramientas de productividad de oficina
Mejore sus habilidades de Excel con Kutools for Excel y experimente la eficiencia como nunca antes. Kutools for Excel ofrece más de 300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haga clic aquí para obtener la función que más necesita...
Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
- ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!