¿Cómo crear la validación de datos en Microsoft Excel?

Microsoft Excel es una de las herramientas más utilizadas en las organizaciones. No importa si es ingeniero o científico de datos, al final necesita tener números en la hoja de Excel para presentar su trabajo. Entre muchas otras características de Excel, la validación de datos es una de las características comunes para usar en un entorno de equipo o grupo. Por ejemplo, puedes enviar un Excel a todos tus compañeros para que lo rellenen a partir de los valores restringidos. Esto lo ayudará a evitar ingresar texto libre y hacer que los datos estén dentro de un alcance específico. En este artículo, exploremos cómo crear una validación de datos en Microsoft Excel con varias opciones.

Relacionado: ¿Cómo corregir la lentitud de Excel y acelerar su trabajo?

3. Mensaje de entrada de la regla de validación

Excel permite establecer reglas de validación para celdas individuales o rangos de celdas. Puede hacer uso de esto para monitorear la aceptación de valores específicos solo en celdas particulares.

Tomemos un ejemplo para la validación de una hoja de calificaciones de un estudiante.

• No puede ser un texto, menor que cero o mayor que 100.
• Excel debería rechazar todas las demás entradas automáticamente.
• Para establecer estas validaciones, primero seleccione el rango de celdas que desea restringir los valores. En nuestro ejemplo, seleccionamos las celdas D4 a D7.
• Vaya a la pestaña «Datos» y seleccione la opción «Validación de datos» en el grupo «Herramientas de datos».

Puede definir las reglas de validación de datos en Excel siguiendo los pasos a continuación.

• Esto abrirá el cuadro de diálogo «Validación de datos». Puede configurar las entradas permitidas para las celdas seleccionadas en la pestaña «Configuración» del cuadro de diálogo.
• Haga clic en el menú desplegable debajo de «Permitir:» y seleccione su valor preferido. En nuestro ejemplo, seleccionaremos el número entero para las marcas.

Creating Data Validation Rule in Excel

• En el cuadro de lista «Datos» puede definir las condiciones de validez. Esto limita las posibilidades de entrada y seleccionamos la opción «entre» ya que solo debemos permitir los valores de 0 a 100. Recuerde, las opciones cambiarán según su selección. Por ejemplo, si selecciona «mayor que», solo verá un cuadro de texto para ingresar un valor «Mínimo».
• Para la condición “entre” se puede determinar el lapso posible en las casillas “Mínimo” y “Máximo”. Ingresaremos el mínimo como 0 y el máximo como 100.

Excel Data Validation Settings

• Seleccione la celda o rango de celdas y vaya a “Datos > Validación de datos” como se explicó anteriormente. Le recomendamos que realice este paso juntos para que pueda configurar la validación y el mensaje de error en un solo paso.
• Cuando esté en el cuadro de diálogo «Validación de datos», vaya a la pestaña «Alerta de error».

Haga clic en el botón «Aceptar» y ahora que ha configurado una validación de datos para las celdas seleccionadas con éxito.

4. Reglas de validación de datos existentes

Usando una regla de validación, puede restringir los valores permitidos en una celda. Cuando intente ingresar un valor 110 en la celda D4, Excel le mostrará un mensaje de error y lo restringirá.

Data Not Allowed Due to Invalid Entries

Sin embargo, el mensaje de error no muestra que el rango permitido es de 0 a 100. Para que el mensaje de error sea más significativo, puede crear un mensaje de alerta de error personalizado que le brinde instrucciones claras cuando ocurra el error.[: 🙂 La parte importante es establecer el estilo del mensaje de error. Haga clic en el menú desplegable «Estilo» y podrá ver tres opciones: Detener, Advertencia e Información. Dependiendo de su necesidad, puede restringir completamente el valor de la celda o permitir a los usuarios un mensaje de advertencia o información. Por ejemplo, cuando el usuario ingrese 110 en nuestro caso obtendrá los siguientes errores para diferentes estilos de alerta.

• En el En el cuadro «Título», ingrese el encabezado de su error.
• Ingrese los detalles de la regla en el panel «Mensaje de error».
• Seleccione la celda o el rango de celdas y vaya a «Datos > Validación de datos» como se explicó anteriormente.
• Cuando esté en el cuadro de diálogo «Validación de datos», vaya a la pestaña «Mensaje de entrada».

Data Validation Error Alert

Relacionado:

Consejos de Excel para mejorar su productividad.Esta es también la alerta predeterminada que aparece cuando no configura ningún mensaje de alerta de error personalizado. Excel no le permitirá ingresar las entradas no válidas y debe escribir los valores permitidos.

2.3. Alerta de error de advertencia:

Esta es una alerta de información simple y Excel le permitirá continuar con el valor no válido cuando haga clic en el botón «Aceptar».

Stop Error Alert

4.1. Ampliación de la validación de datos

Esta es una advertencia y Excel le ofrecerá opciones para continuar o cancelar.

Information Error Alert

4.2. Etiquetado de valores no válidos existentes

Al igual que una alerta de error, también puede crear un mensaje de visualización al hacer clic en la celda. Esto ayudará a escribir los datos correctos un paso antes de obtener el error.

Warning Error Alert

5. Lista de reglas de validación

Haga clic en la celda validada para ver el mensaje de entrada que aparece a continuación como una instrucción.

• En el cuadro de entrada «Título», ingrese el encabezado del mensaje para su error.
• Ingrese los detalles de la regla en el panel «Mensaje de entrada».
• Seleccione la entrada «Lista» en el cuadro desplegable «Permitir».
• Seleccione la opción «Desplegable en celda» para mostrar los valores en el menú desplegable.

Data Validation Input Message

Esto será útil cuando tenga una gran Excel con diferentes tipos de validación de datos en diferentes celdas.

Validation Rule Input Message

Relacionado:

¿Cómo corregir errores de fórmula en Excel?Cuando crea una regla de validación, es poco probable que su tabla de Excel todavía esté vacía . Por lo tanto, debe tener cuidado con algunas configuraciones al usar reglas de validación en datos existentes.

2.1. Alerta de error de parada:

Cuando selecciona celdas fuera de las reglas de validación y hace clic en la opción «Validación de datos», Excel le mostrará un mensaje.[: :: Puede hacer clic en «Sí» para extender la validación a las celdas seleccionadas. Sin embargo, el problema es que Excel NO validará ninguno de los datos existentes con sus reglas de validación. Solo puede usar una regla de validación para las entradas que siguen una regla establecida previamente. Excel ignorará los datos existentes sin ninguna validación. La única opción disponible en Excel es etiquetar los datos no válidos para que pueda corregirlos manualmente.

4.3. Eliminación de etiquetas en datos no válidos

Para encontrar las celdas de datos existentes no válidas, vaya al menú «Datos» y haga clic en el botón desplegable «Validación de datos». en el grupo «Herramientas de datos». Ahora, seleccione la entrada «Circular datos no válidos» para resaltar las celdas no válidas con círculos rojos como a continuación:

Extending Data Validation

Con los círculos de validación, puede crear una descripción general clara de los datos no válidos en su tabla. Si ha corregido los valores o ya no necesita los círculos, puede eliminarlos fácilmente.

Puede volver a usar la entrada «Borrar círculos de validación» en el menú desplegable «Validación de datos» para eliminar la etiqueta del círculo rojo.

Circling Invalid Data

Como alternativa a las reglas de validación, puede utilizar una lista que contiene todos los valores válidos. Por lo tanto, cada usuario puede simplemente elegir entre los valores disponibles e insertar los datos correctos. En otras palabras, puede restringir al usuario para que seleccione dentro de los valores especificados. En nuestro caso de ejemplo, solo puede permitir marcas como 50, 60, 70, 80 y 90 y restringir cualquier otra entrada del usuario.

Cuando esté en la ventana emergente «Validación de datos», vaya a «Configuración» tab:

2.2. Alerta de error de información:

La celda formateada de esta manera recibe una flecha de lista al momento de la activación. Ahora puede ingresar uno de los valores posibles manualmente o abrir la lista con el botón de flecha y luego seleccionar la entrada deseada.

También puede configurar el mensaje de entrada y la alerta de error como se explicó anteriormente.

• Marque o desmarque la opción «Ignorar en blanco» según sus necesidades.
• Ingrese los valores permitidos en el cuadro de texto «Fuente» separados por comas. Si los valores requeridos ya existen en una tabla o en otras hojas, después de hacer clic en el cuadro «Fuente», seleccione el área correspondiente para adoptar los valores. Asegúrese de que la fuente de los valores esté disponible en el mismo libro de Excel para que funcione cuando comparta Excel con otros.
• Haga clic en el botón «Aceptar» para guardar los cambios.
• 1. Definición de reglas de validación
• 2. Alerta de error de regla de validación

Data Validation with List Values

Use el » Borrar todo” para eliminar toda la validación de datos, mensajes de entrada y alertas de error con un solo clic.

Select Values from the List

Las celdas debajo de la columna «Asunto 1» deben ser un número.

El valor permitido es entre 0 y 100.