Cómo resaltar duplicados en Excel: Una guía completa paso a paso

Método 1: Cree una Lista Desplegable Simple desde una Lista Escrita

Lo mejor para: Listas cortas y fijas que raramente cambian.

Para crear una lista desplegable en Excel, seleccione la celda donde desea la lista, vaya a la pestaña de datos, haga clic en validación de datos, elija Lista en el cuadro permitir y especifique el rango de origen o ingrese manualmente los elementos separados por comas. Es posible escribir manualmente los elementos o hacer referencia a un rango de celdas para las listas desplegables en Excel.

Pasos:

1. Seleccione la celda o rango de celdas donde desea la lista desplegable.
2. Haga clic en la pestaña de datos en la cinta.
3. En el grupo de herramientas de datos, haga clic en validación de datos. Se abre el cuadro de diálogo de validación de datos.
4. En el cuadro permitir, seleccione Lista del menú desplegable.
5. Aparece la casilla de verificación de lista desplegable en celda. La opción de lista desplegable en celda debe estar marcada para habilitar un menú desplegable en una celda.
6. Haga clic en el cuadro de origen y escriba sus opciones como una lista separada por comas, por ejemplo: Electrónica, Muebles, Software, Servicios
7. Haz clic en Aceptar.

Ahora aparece una pequeña flecha en la celda. Al hacer clic en ella se abre el menú desplegable que muestra todas las opciones desplegables. Los usuarios pueden copiar una celda con una lista desplegable y pegarla en otro lugar, manteniendo la funcionalidad de la lista desplegable, lo cual es una forma rápida de aplicar la misma lista a otras celdas.

Evite espacios después de las comas en la lista separada por comas a menos que desee un espacio al inicio en la etiqueta. "Norteamérica, EMEA" mostrará " EMEA" con un espacio al inicio.

Sugerencia de captura de pantalla: Mostrar el cuadro de diálogo de validación de datos con la pestaña de configuraciones activa, Lista en el cuadro permitir y una lista separada por comas en el cuadro de origen. La celda de destino debe estar seleccionada en segundo plano usando el archivo de demostración de formulario de pedido.

Método 2: Cree una Lista Desplegable desde un Rango de Celdas

Lo mejor para: Listas con muchos elementos, o cuando los mismos datos de origen se reutilizan en múltiples celdas.

Hacer referencia a un rango de celdas como datos de origen es más flexible que escribir elementos. Cuando actualiza el rango de origen, las opciones de la lista desplegable en todas las celdas conectadas se actualizan también.

1. Ingrese sus elementos de lista en una columna, idealmente en una hoja separada (por ejemplo, una hoja llamada "Listas").
2. Seleccione la celda donde desea la lista desplegable.
3. Vaya a pestaña de datos > grupo de herramientas de datos > seleccione validación de datos.
4. En la ventana de validación de datos, en la pestaña de configuraciones, seleccione Lista en el cuadro permitir.
5. Haga clic en el cuadro de origen y seleccione el rango de celdas en su hoja de listas. Excel ingresa la referencia de rango automáticamente, por ejemplo =Listas!$A$3:$A$7.
6. Haga clic en OK.

Los criterios de validación ahora restringen las entradas solo a datos válidos de ese rango de origen. El cuadro de nombre en la parte superior izquierda muestra la dirección de la celda actual y le ayuda a verificar que la referencia correcta esté seleccionada.

Siempre use referencias absolutas con signos $ en el rango de origen. Usar =A3:A7 sin signos de dólar puede desplazarse si se insertan filas encima de él.

Sugerencia de captura de pantalla: Mostrar el cuadro de diálogo de validación de datos con una referencia de rango en el cuadro de origen y la pestaña de la hoja Listas visible en la parte inferior. Utilice el archivo de demostración de formulario de pedido.

Método 3: Use un Rango Nombrado como Fuente

Lo mejor para: Listas desplegables utilizadas en múltiples ubicaciones, o cuando los datos de origen están en una hoja diferente.

Un rango nombrado asigna un nombre memorable a un grupo de celdas, que luego puede usar en el cuadro de origen en lugar de una dirección de celda.

1. Seleccione sus elementos de lista, luego haga clic en el cuadro de nombre y escriba un nombre (por ejemplo CategoríasDeProducto), luego presione enter.
2. Seleccione la celda donde desea la lista desplegable, vaya a la pestaña de datos > validación de datos.
3. En la pestaña de configuraciones, seleccione Lista en el cuadro permitir, luego escriba =CategoríasDeProducto en el cuadro de origen.
4. Haga clic en OK.

Esto hace que la lista de validación sea más fácil de gestionar. Los rangos nombrados también permiten listas desplegables dependientes cuando se combinan con la función INDIRECT (cubierta en el Método 5).

Sugerencia de captura de pantalla: Mostrar el cuadro de nombre con un nombre de rango ingresado y el cuadro de origen de validación de datos mostrando =CategoríasDeProducto.

Método 4: Agregar Mensajes de Entrada y Alertas de Error

Lo mejor para: Libros de trabajo compartidos o formularios llenados por otras personas.

Los usuarios pueden agregar mensajes de entrada para que se muestren cuando un usuario haga clic en la celda con una lista desplegable para guiarlos sobre qué seleccionar. Excel permite la configuración de mensajes de error personalizados dentro del cuadro de diálogo de Validación de Datos.

Mensaje de entrada:

1. Abra pestaña de datos > validación de datos.
2. Haga clic en la pestaña de mensaje de entrada en el cuadro de diálogo de validación de datos.
3. Marque Mostrar mensaje de entrada cuando la celda está seleccionada y escriba un título más un mensaje en los campos de abajo.
4. Haga clic en OK.

Cuando alguien hace clic en esa celda, el mensaje de entrada aparece como una ayuda contextual guiando su selección.

Alerta de error:

1. Haga clic en la pestaña de alerta de error en el cuadro de diálogo de validación de datos.
2. Marque la casilla mostrar alerta de error (también etiquetada como alerta de error después de ingresar datos inválidos o alerta después de datos inválidos).
3. Elija un estilo: Detener bloquea datos no válidos, Advertencia los permite con un mensaje, Información solo muestra una nota.
4. Ingrese un título y un mensaje de error.
5. Haz clic en OK.

Cuando se ingresan datos no válidos fuera de la lista permitida, se dispara la alerta de error. Para formularios estrictos, use Detener de manera que solo se pueda aceptar datos válidos. La configuración de mostrar alerta de error y la pestaña de mensaje de entrada se controlan de manera independiente, para que pueda habilitar una sin la otra.

Si desea que se permitan celdas en blanco, marque la casilla Ignorar en blanco en la pestaña de configuraciones. Esto evita que se dispare la alerta de error en celdas vacías.

Sugerencia de captura de pantalla: Mostrar la pestaña de alerta de error del cuadro de diálogo de validación de datos con Detener seleccionado y un mensaje de error personalizado escrito. Use el archivo de demostración del formulario de recursos humanos.

Método 5: Construir Listas Desplegables Dependientes

Lo mejor para: Selección multinivel donde la segunda lista depende de la primera.

Las listas desplegables dependientes en Excel enlazan dos o más listas desplegables juntas, donde la selección en la primera lista controla las opciones disponibles en la segunda lista. Por ejemplo, si un usuario selecciona Pizza de la primera lista desplegable, la segunda lista desplegable puede ser poblada con elementos específicos de pizza, mientras que al seleccionar Comida China se mostrarían platos chinos en la segunda lista. Crear listas desplegables dependientes puede mejorar la eficiencia y precisión de la entrada de datos asegurando que los usuarios solo vean opciones relevantes según sus selecciones previas.

Configuración:

1. Cree sus datos de origen en columnas, una columna por categoría. La fila de encabezado de cada columna debe coincidir exactamente con lo que aparece en la primera lista desplegable.
2. Nombres cada columna de elementos usando el cuadro de nombre, coincidiendo exactamente con la primera opción de la lista (por ejemplo, nombre la columna de Pizza "Pizza", la columna de Comida China "Comida China").
3. Cree la primera lista desplegable en la columna A usando el Método 1 o el Método 2.
4. Seleccione la celda para la segunda lista desplegable (columna B). Vaya a pestaña de datos > lista de validación de datos seleccionada en la pestaña de configuraciones, elija Lista en el cuadro permitir, y en el cuadro de origen ingrese =INDIRECT(A4).
5. Haz clic en Aceptar.

Cuando se elige un valor en la primera lista desplegable, INDIRECT busca el rango nombrado con el mismo nombre y lo usa como la lista de selección para la segunda lista desplegable. Los criterios de validación para la segunda celda se actualizan dinámicamente según la primera selección.

Sugerencia de captura de pantalla: Mostrar el archivo de demostración de listas desplegables dependientes con Pizza seleccionada en la columna A y los elementos del menú de pizza visibles en el desplegable de la columna B. La hoja de Listas de Menú debe ser visible en segundo plano.

Método 6: Crear una Lista Desplegable Dinámica

Lo mejor para: Listas que crecen con el tiempo y necesitan actualizarse automáticamente cuando se añaden nuevos elementos.

Puede crear una lista desplegable dinámica en Excel usando la función OFFSET, lo cual permite que la lista se actualice automáticamente a medida que se añaden nuevos elementos al rango de origen. Usar una tabla de excel como la fuente para una lista desplegable permite que ésta se expanda automáticamente cuando se agregan nuevas filas, convirtiéndola en una gran opción para listas dinámicas.

Opción A: Tabla de Excel

1. Seleccione sus elementos de origen y presione Ctrl + T (desde la pestaña insertar) para crear una tabla de Excel. Elija un estilo de tabla y confirme la opción de fila de encabezado.
2. Haga referencia a la columna de la tabla en el cuadro de origen de validación de datos: =Tabla1[Categoría].

Cuando se añade una nueva fila a la tabla de Excel, las opciones desplegables se actualizan automáticamente sin necesitar cambios en la regla de validación.

Opción B: Fórmula OFFSET

Para crear una lista desplegable dinámica, puede usar la fórmula =OFFSET(Hoja2!$A$1,0,0,COUNTA(Hoja2!$A:$A),1), la cual ajusta el rango basado en el número de celdas no vacías en la columna de origen.

Ingrese esto en el cuadro de origen de la ventana de validación de datos. A medida que se añaden elementos a la columna de origen, el rango de origen se expande y las opciones desplegables se actualizan automáticamente.

Sugerencia de captura de pantalla: Mostrar el cuadro de origen de validación de datos con la fórmula OFFSET ingresada y la hoja de Listas visible. Utilice el archivo de demostración de formulario de pedido.

Método 7: Agregar, Editar y Eliminar Elementos de la Lista Desplegable

Gestión de elementos en el rango de origen:

Para agregar un elemento a una lista desplegable existente, vaya a la hoja original que contiene la lista, haga clic derecho en la celda debajo de donde desea insertar, seleccione Insertar, elija mover celdas hacia abajo e ingrese el nuevo elemento. Las opciones de la lista desplegable se actualizan automáticamente. En la misma hoja de trabajo o entre hojas, los mismos pasos se aplican siempre que el rango de origen cubra la nueva celda.

Eliminación de la lista desplegable:

Para eliminar una lista desplegable en Excel, seleccione la celda con la lista desplegable, vaya a la pestaña de datos, haga clic en validación de datos, y luego haga clic en eliminar (Borrar todo) para eliminar la lista. Para borrar solo el valor de la celda sin eliminar la regla de validación, seleccione la celda y presione eliminar. Para eliminar la regla completamente, use Borrar todo en la ventana de validación de datos.

Tenga en cuenta que si desea presionar eliminar solo sobre el contenido de la celda y mantener la validación, puede hacerlo de manera segura. La lista de validación permanece en la celda incluso después de que el contenido es borrado.

Proteger con contraseña las celdas validadas:

Para evitar que otros editen su configuración de lista desplegable, vaya a Revisar > Proteger hoja e ingrese una contraseña (proteger con contraseña la hoja de trabajo). Los usuarios aún pueden usar las listas desplegables para ingresar datos pero no pueden modificar los criterios de validación ni eliminar las reglas.

Sugerencia de captura de pantalla: Mostrar el cuadro de diálogo de validación de datos con el botón Borrar todo visible, listo para eliminar la regla. También mostrar el cuadro de diálogo de Proteger hoja con el campo de contraseña activo.

Problemas Comunes y Solución de Problemas

La flecha de lista desplegable no aparece

Confirme que la casilla lista desplegable en celda esté marcada en la pestaña de configuraciones. Si está desmarcada, la validación aún funciona pero no aparece ninguna flecha en la celda. Esta es la configuración más omitida cuando la lista desplegable parece haber desaparecido.

La alerta de error se activa en datos válidos

Esto suele ser una discrepancia de formato. Compruebe el cuadro de origen para espacios dispersos alrededor de las comas en la lista separada por comas, o inspeccione los datos de origen para celdas en blanco o capitalización inconsistente. Los criterios de validación comparan los valores exactamente.

La lista dinámica no se actualiza automáticamente

Si el origen es un rango de celdas estático, los nuevos elementos añadidos fuera de ese rango no aparecerán. Cambie a una tabla de Excel o la fórmula OFFSET para que el rango de origen se expanda automáticamente. La lista de validación de datos debe hacer referencia a un rango que incluya todos los elementos actuales y futuros.

Lista dependiente muestra un error de referencia

Esto significa que el rango nombrado no coincide exactamente con la selección de la primera lista desplegable. Abra Fórmulas > Administrador de nombres y confirme que los nombres coinciden exactamente con las opciones en la primera lista desplegable, incluyendo la capitalización. Cualquier discrepancia hace que INDIRECT devuelva un blanco o error en todas las celdas usando la regla dependiente.

Referencia Rápida: Métodos de Lista Desplegable

Para Desarrolladores: Agregar Validación de Datos con IronXL

Si su aplicación .NET genera libros de trabajo Excel con formularios o plantillas de entrada de datos, IronXL le permite aplicar listas desplegables de validación de datos programáticamente en C# sin Microsoft Office en el servidor. El enfoque recomendado es AddFormulaListRule(), que referencia un rango de celdas como fuente y evita el límite de 255 caracteres que aplica al pasar elementos de lista como una cadena directamente.

using IronXL;

WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet formSheet = workBook.DefaultWorkSheet;
formSheet.Name = "Order Form";

// Headers
formSheet["A1"].Value = "Order ID";
formSheet["B1"].Value = "Product Category";
formSheet["C1"].Value = "Region";

// Write source list values to a Lists sheet
WorkSheet listSheet = workBook.CreateWorkSheet("Lists");
string[] categories = { "Electronics", "Furniture", "Software", "Office Supplies", "Services" };
string[] regions = { "North America", "EMEA", "APAC", "Latin America", "Middle East" };

for (int i = 0; i < categories.Length; i++)
{
    listSheet[$"A{i + 1}"].Value = categories[i];
    listSheet[$"B{i + 1}"].Value = regions[i];
}

// Apply drop-down validation referencing the Lists sheet source range
var categoryRule = formSheet.DataValidations.AddFormulaListRule(
    "B2:B20",           // cell range to validate
    "Lists!$A$1:$A$5"  // source range on Lists sheet
);
categoryRule.ShowErrorBox = true;
categoryRule.ErrorBoxTitle = "Invalid Category";
categoryRule.ErrorBoxText = "Please select a valid category from the drop-down list.";
categoryRule.ShowPromptBox = true;
categoryRule.PromptBoxTitle = "Product Category";
categoryRule.PromptBoxText = "Choose a product category from the drop-down.";

var regionRule = formSheet.DataValidations.AddFormulaListRule(
    "C2:C20",
    "Lists!$B$1:$B$5"
);
regionRule.ShowErrorBox = true;
regionRule.ErrorBoxTitle = "Invalid Region";
regionRule.ErrorBoxText = "Select a valid region from the drop-down list.";

workBook.SaveAs("order-form-validated.xlsx");

using IronXL;

WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet formSheet = workBook.DefaultWorkSheet;
formSheet.Name = "Order Form";

// Headers
formSheet["A1"].Value = "Order ID";
formSheet["B1"].Value = "Product Category";
formSheet["C1"].Value = "Region";

// Write source list values to a Lists sheet
WorkSheet listSheet = workBook.CreateWorkSheet("Lists");
string[] categories = { "Electronics", "Furniture", "Software", "Office Supplies", "Services" };
string[] regions = { "North America", "EMEA", "APAC", "Latin America", "Middle East" };

for (int i = 0; i < categories.Length; i++)
{
    listSheet[$"A{i + 1}"].Value = categories[i];
    listSheet[$"B{i + 1}"].Value = regions[i];
}

// Apply drop-down validation referencing the Lists sheet source range
var categoryRule = formSheet.DataValidations.AddFormulaListRule(
    "B2:B20",           // cell range to validate
    "Lists!$A$1:$A$5"  // source range on Lists sheet
);
categoryRule.ShowErrorBox = true;
categoryRule.ErrorBoxTitle = "Invalid Category";
categoryRule.ErrorBoxText = "Please select a valid category from the drop-down list.";
categoryRule.ShowPromptBox = true;
categoryRule.PromptBoxTitle = "Product Category";
categoryRule.PromptBoxText = "Choose a product category from the drop-down.";

var regionRule = formSheet.DataValidations.AddFormulaListRule(
    "C2:C20",
    "Lists!$B$1:$B$5"
);
regionRule.ShowErrorBox = true;
regionRule.ErrorBoxTitle = "Invalid Region";
regionRule.ErrorBoxText = "Select a valid region from the drop-down list.";

workBook.SaveAs("order-form-validated.xlsx");

Imports IronXL

Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim formSheet As WorkSheet = workBook.DefaultWorkSheet
formSheet.Name = "Order Form"

' Headers
formSheet("A1").Value = "Order ID"
formSheet("B1").Value = "Product Category"
formSheet("C1").Value = "Region"

' Write source list values to a Lists sheet
Dim listSheet As WorkSheet = workBook.CreateWorkSheet("Lists")
Dim categories As String() = {"Electronics", "Furniture", "Software", "Office Supplies", "Services"}
Dim regions As String() = {"North America", "EMEA", "APAC", "Latin America", "Middle East"}

For i As Integer = 0 To categories.Length - 1
    listSheet($"A{i + 1}").Value = categories(i)
    listSheet($"B{i + 1}").Value = regions(i)
Next

' Apply drop-down validation referencing the Lists sheet source range
Dim categoryRule = formSheet.DataValidations.AddFormulaListRule(
    "B2:B20",           ' cell range to validate
    "Lists!$A$1:$A$5"   ' source range on Lists sheet
)
categoryRule.ShowErrorBox = True
categoryRule.ErrorBoxTitle = "Invalid Category"
categoryRule.ErrorBoxText = "Please select a valid category from the drop-down list."
categoryRule.ShowPromptBox = True
categoryRule.PromptBoxTitle = "Product Category"
categoryRule.PromptBoxText = "Choose a product category from the drop-down."

Dim regionRule = formSheet.DataValidations.AddFormulaListRule(
    "C2:C20",
    "Lists!$B$1:$B$5"
)
regionRule.ShowErrorBox = True
regionRule.ErrorBoxTitle = "Invalid Region"
regionRule.ErrorBoxText = "Select a valid region from the drop-down list."

workBook.SaveAs("order-form-validated.xlsx")

IronXL se ejecuta en .NET 6 y posteriores, compatible con Windows, Linux, macOS, Docker y Azure. Para más detalles, consulte la referencia del API de DataValidation.

Para comenzar: Instala a través de NuGet con Install-Package IronXL.Excel. Un prueba gratuita está disponible con 30 días de funcionalidad completa y sin necesidad de tarjeta de crédito.

Lecturas adicionales:

• IronXL DataValidation API reference
• Escribir archivos Excel en .NET con IronXL
• Guía de inicio de IronXL

Resumen

Agregar una lista desplegable a las celdas de Excel toma menos de un minuto una vez que sabe dónde ir. Para una lista desplegable rápida en celda con un puñado de opciones fijas, escribir una lista separada por comas directamente en el cuadro de origen en el cuadro de diálogo de validación de datos es la ruta más rápida. Para cualquier cosa que necesite escalar o actualizarse con el tiempo, hacer referencia a un rango nombrado o una tabla de excel da al desplegable espacio para crecer. La lista de validación de datos permanece conectada a sus datos de origen, por lo que al agregar un nuevo elemento al rango de origen se hace automáticamente disponible en cada celda conectada.

Cuando múltiples personas llenan la misma hoja de cálculo, el mensaje de entrada en la pestaña de mensaje de entrada los guía antes de que hagan una selección, y una alerta de error de nivel Detener en la pestaña de alerta de error asegura que los datos no válidos nunca aterrizan en una celda validada. Listas desplegables dependientes construidas con INDIRECT y rangos nombrados permiten que la primera lista desplegable controle la segunda automáticamente, manteniendo el conjunto de opciones relevante sin trabajo extra del usuario.

Para los desarrolladores que necesitan generar archivos Excel validados desde aplicaciones .NET, IronXL cubre todo el flujo de trabajo de validación de datos: rangos de origen, alertas de error, mensajes de entrada y reglas de lista de fórmulas, todo sin Office instalado en el servidor. Comience con una prueba gratuita para probar la validación de datos en su propio proyecto.

¿Tiene un escenario de lista desplegable que esta guía no cubrió? Deje un comentario abajo, o visite el blog de Iron Software para más guías sobre entrada de datos en Excel, validación y automatización de hojas de cálculo.