Data validation in excel: How to add, use or remove

 What is Data Validation?

Data validation is a powerful feature in Microsoft Excel that enables you to control the type and quality of data that is entered into a cell or range of cells. With data validation, you can create rules that restrict the types of data that can be entered, specify the allowable range of values, or create a drop-down list of acceptable entries. This feature helps ensure that the data entered into your worksheets is accurate and consistent.

In this article, we will provide an overview of data validation in Excel, including its benefits and how to apply it. We will also provide several examples of how to use data validation in various scenarios.

Benefits of Data Validation

Data validation offers several benefits, including:

• Improved data accuracy: Data validation helps to ensure that the data entered into your worksheets is accurate and consistent. This reduces the likelihood of errors, which can have negative consequences for your business.
• Increased efficiency: With data validation, you can speed up data entry by creating drop-down lists and restricting input to specific formats. This saves time and reduces the risk of errors.
• Better data analysis: By enforcing consistent data entry standards, you can ensure that your data is more accurate and reliable. This makes it easier to analyze and interpret the data, and to draw meaningful insights from it.

Types of Data Validation

Excel provides several types of data validation, including:

1. Whole number: This type of validation allows you to restrict entries to whole numbers only, such as integers or positive numbers.
2. Decimal: This type of validation allows you to restrict entries to decimal numbers only, such as those with a specific number of decimal places or those within a certain range.
3. Date: This type of validation allows you to restrict entries to dates only, such as those within a specific range or format.
4. Time: This type of validation allows you to restrict entries to times only, such as those within a specific range or format.
5. Text length: This type of validation allows you to restrict entries to a certain number of characters or a specific range of characters.
6. Custom: This type of validation allows you to create custom rules based on specific criteria, such as requiring a certain pattern of characters or a specific combination of values.

Applying Data Validation

To apply data validation in Excel, follow these steps:

• Select the cell or range of cells where you want to apply data validation.
• Click on the "Data" tab in the Excel ribbon.
• Click on the "Data Validation" button in the "Data Tools" group.
• In the "Data Validation" dialog box, select the type of validation you want to apply.
• Set the validation criteria, such as the minimum and maximum values for a number or the list of allowed values for a drop-down list.
• Customize the error message that users will see if they try to enter invalid data.
• Click "OK" to apply the data validation to the selected cells.

Examples of Data Validation

Whole Number Validation

Whole number validation in Excel is a type of data validation that restricts input to whole numbers only. This means that users can only enter integers, or positive and negative numbers with no decimal places.

Here's how to apply whole number validation in Excel for this example:

Suppose you have a worksheet that tracks the number of hours worked by employees in a given week. You want to ensure that users can only enter whole numbers for the hours worked, as fractional values don't make sense for this type of data.

To apply whole number validation to the "Hours Worked" column, follow these steps:

• Open a new Excel worksheet and enter the following column headings in Row 1: "Employee Name," "Hours Worked." 

  

  Work sheet

  
  
• Enter the employee names in Column A (starting from Row 2).
• Select the cells in the "Hours Worked" column (starting from Row 2). 

  

  Select "work hours" cells

  
  
• Click on the "Data" tab in the Excel ribbon.
• Click on the "Data Validation" button in the "Data Tools" group. 

  

  Data Tab and Click Data Validation

  
  
• In the "Data Validation" dialog box, select "Whole Number" from the "Allow" drop-down list.
• In the "Data" tab, set the minimum value to 0 and the maximum value to 24 (since a day only has 24 hours). 

  

  Select Whole No. (Min and Max)

  
  
• Customize the error message that users will see if they try to enter invalid data (such as "Please enter a whole number between 0 and 24"). 

  

  Error Message

  
  
• Click "OK" to apply the data validation to the selected cells.

Now, when users try to enter a fractional value (such as 3.5 hours), they will see an error message indicating that only whole numbers between 0 and 24 are allowed. Here's an example of what the worksheet might look like with whole number validation applied.

Decimal validation 

Decimal validation is a type of data validation in Excel that ensures that a user enters a decimal value within a specified range or with a certain number of decimal places. Decimal validation is commonly used in financial applications, where precision is important, such as when entering currency amounts or interest rates.

Here's an example of how to apply decimal validation in Excel:

Suppose you have a worksheet that tracks the monthly sales figures for a company. You want to ensure that users can only enter decimal values for the sales figures, as non-numeric values or whole numbers don't make sense for this type of data.

To apply decimal validation to the "Sales" column, follow these steps:

• Open a new Excel worksheet and enter the following column headings in Row 1: "Month," "Sales."
• Enter the months in Column A (starting from Row 2). 

  

  Worksheet

  
  
• Select the cells in the "Sales" column (starting from Row 2).
• Click on the "Data" tab in the Excel ribbon. 
• Click on the "Data Validation" button in the "Data Tools" group.
• In the "Data Validation" dialog box, select "Decimal" from the "Allow" drop-down list.

  

  Decimal Validation

  
  
• In the "Data" tab, set the minimum value to 0 and the maximum value to 1000000 (you can adjust this based on the range of sales figures you expect to see).
• Customize the error message that users will see if they try to enter invalid data (such as "Please enter a decimal between 0 and 1000000"). 

  

  Error message

  
  
• Click "OK" to apply the data validation to the selected cells.

Now, when users try to enter a non-numeric value or a whole number (such as "N/A" or "500"), they will see an error message indicating that only decimal values between 0 and 1000000 are allowed. Here's an example of what the worksheet might look like with decimal validation applied.

Note that decimal validation is useful when you want to allow users to enter fractional values, but you want to restrict the range of values they can enter. In this example, we set the minimum value to 0 to ensure that negative values are not entered, and we set the maximum value to 1000000 to ensure that overly large values are not entered.

Date Validation

Date validation in Excel is a feature that allows you to restrict data entry to a specific date format, or to a range of dates. This helps to ensure that users enter valid and consistent dates in your worksheet, preventing errors or inconsistencies in your data.

Here's an example of date validation in Excel:

Suppose you have a worksheet that tracks the due dates of various tasks. You want to ensure that users can only enter valid dates for the due dates, as non-date values or invalid dates don't make sense for this type of data.

To apply date validation to the "Due Date" column, follow these steps:

• Open a new Excel worksheet and enter the following column headings in Row 1: "Task Name," "Due Date."
• Enter the task names in Column A (starting from Row 2). 

  

  Worksheet

  
  
• Select the cells in the "Due Date" column (starting from Row 2).
• Click on the "Data" tab in the Excel ribbon.
• Click on the "Data Validation" button in the "Data Tools" group.
• In the "Data Validation" dialog box, select "Date" from the "Allow" drop-down list. 

  

  Date Validation

  
  
• In the "Data" tab, set the minimum date to today's date and the maximum date to a reasonable future date (such as one year from today).
• Customize the error message that users will see if they try to enter invalid data (such as "Please enter a valid date between today and one year from today"). 

  

  Error message

  
  
• Click "OK" to apply the data validation to the selected cells.

Now, when users try to enter a non-date value or an invalid date (such as "N/A" or "February 30th"), they will see an error message indicating that only valid dates between today and one year from today are allowed. Here's an example of what the worksheet might look like with date validation applied.

Note that date validation is useful when you want to ensure that users enter valid dates, but you want to restrict the range of dates they can enter. In this example, we set the minimum date to today's date to ensure that past due dates are not entered, and we set the maximum date to one year from today to ensure that overly distant future dates are not entered.

Time Validation

Time validation in Excel is a feature that allows you to restrict data entry to a specific time format, or to a range of times. This helps to ensure that users enter valid and consistent times in your worksheet, preventing errors or inconsistencies in your data.

Here's an example of time validation in Excel:

Suppose you have a worksheet that tracks the start and end times of employee shifts. You want to ensure that users can only enter valid time values for the shift start and end times, as invalid or non-numeric values don't make sense for this type of data.

To apply time validation to the "Shift Start" and "Shift End" columns, follow these steps:

• Open a new Excel worksheet and enter the following column headings in Row 1: "Employee Name," "Shift Start," "Shift End."
• Enter the employee names in Column A (starting from Row 2). 

  

  Worksheet

  
  
• Select the cells in the "Shift Start" and "Shift End" columns (starting from Row 2).
• Click on the "Data" tab in the Excel ribbon.
• Click on the "Data Validation" button in the "Data Tools" group. 
• In the "Data Validation" dialog box, select "Time" from the "Allow" drop-down list. 

  

  Time Validation

  
  
• In the "Data" tab, set the minimum value to 0 (12:00:00 AM) and the maximum value to 0.999988426 (11:59:59 PM).
• Customize the error message that users will see if they try to enter invalid data (such as "Please enter a valid time between 12:00:00 AM and 11:59:59 PM"). 

  

  Error Message

  
  
• Click "OK" to apply the data validation to the selected cells.

Now, when users try to enter an invalid time value (such as "13:00:00" or "abc"), they will see an error message indicating that only valid time values between 12:00:00 AM and 11:59:59 PM are allowed. Here's an example of what the worksheet might look like with time validation applied.

Note that time validation is useful when you want to allow users to enter time values, but you want to restrict the range of values they can enter. In this example, we set the minimum value to 12:00:00 AM and the maximum value to 11:59:59 PM to ensure that only valid time values are entered.