How to Create Data Validation Rules in Excel 2016

Edited 4 months ago by ExtremeHow Editorial Team

Microsoft OfficeExcelData ValidationRulesSpreadsheetDataInputConsistencyWindowsMacProductivitySecurity

In the world of data management, it is important to ensure that information is entered correctly and consistently. Excel 2016 offers a great feature called Data Validation that helps you maintain control over the type and quality of data you input into your spreadsheet. Data validation is not only beneficial for maintaining data integrity, but it also enhances the user experience when entering data.

This guide will teach you how to create data validation rules in Excel 2016, allowing you to place restrictions on the data that can be entered into a cell. These restrictions can range from simple limitations, such as restricting a cell to only numeric entries, to more complex custom formulas that enforce specific business rules.

What is data validation in Excel?

Data validation in Excel is a feature that allows you to control the type of data or values that users can enter into a cell. This tool gives you the power to set rules that help prevent incorrect data from being entered, reducing the chance of errors. For example, you can limit entries to a specific list, ensure that only numbers within a certain range are entered, or only allow dates before a certain day to be input.

Excel's data validation can apply a variety of conditions, such as:

• Number Restriction - Specify that the data entered must be numbers within a certain range.
• Text Length - Make sure the inputted data is a specific number of characters.
• Date and Time - Verify that inputs are date/time and optionally within a certain range.
• Lists - Allow only predefined items to be entered from a list.
• Custom formulas - Use formulas to define validation criteria.

Creating data validation rules

Now, let's move on to the steps to create a data validation rule in Excel 2016:

Step 1: Select the cell(s)

First select the cell or range of cells where you want to apply data validation. You can select cells by clicking and dragging the mouse or by holding down the “Ctrl” key to select multiple non-adjacent cells. Selecting the right cell range is important because it determines where the rules will be applied.

Step 2: Open the Data Validation dialog box

Once your required cells are selected, go to the 'Data' tab on the ribbon. You will find the 'Data Validation' option in the 'Data Tools' group. Click on the 'Data Validation' option, and a dialog box will appear.

Step 3: Set your verification criteria

The Data Validation dialog box is divided into three tabs: Settings, Input Message, and Error Alert. You will initially work with the 'Settings' tab to define your validation criteria. Let's look at how you can set up rules using different criteria:

• Whole number

  If you want to allow only whole numbers, select 'Whole numbers' from the 'Allow' drop-down menu. You can further define the minimum and maximum numbers allowed. For example, to restrict ages from 18 to 65, you would set the minimum to 18 and the maximum to 65.

• Decimal

  For decimal numbers, select 'Decimal' and set the limits. For example, if dealing with monetary values such as prices, you may want to limit entries to between 0.01 and 1000.00. These limits ensure that only realistic and sensible entries are allowed.

• List

  To restrict entries to specific items, such as department names, use the 'List' option. You can type a list of acceptable entries, separated by commas, or reference a list range in the workbook. If you have a separate sheet containing the list data, you can define a name for the range (using the 'Name Manager') and reference it in the dialog box.

• Date

  For date entries, select 'Date'. You can set start and end dates to ensure all entries fall within a valid timeframe, such as a project deadline. This limits entries to only dates within your specified range, avoiding erroneous entries such as distant past or future dates.

• Time

  Similarly, you can validate time entries by selecting 'Time' and specifying a range, which is useful for ensuring that appointments are scheduled within business hours.

• Length of the text

  This criterion limits the number of characters that can be entered in a cell. For example, this is important in data entry forms where part numbers or product IDs have a constant length.

• Custom

  For advanced users, the 'Custom' option allows the entry of specific formulas for validation. For example, to limit entries to even numbers, you can use a formula such as =MOD(A1,2)=0. Such formulas provide a lot of flexibility for unique validation scenarios.

Step 4: Set the input message

The 'Input Message' tab allows you to display a message when the user selects a cell. Use this feature to let users know what data is valid for entry, which will reduce errors. For example, a message could be, "Please enter a product code between 1000 and 9999." Selecting a cell will pop up the input message, providing helpful instructions to the user.

Step 5: Set up error alerts

The 'Error Alert' tab allows you to specify what happens when an invalid entry is made. The most common types are:

• To stop

  This is the most restrictive alert type. If someone tries to enter data that doesn't follow your rules, a 'Stop' alert stops the entry completely, forcing the user to correct their mistake.

• Alert

  The 'Warning' alert informs the user of a potential problem with the entry, but allows them to proceed if they wish to do so. This creates a middle ground between enforcement and user discretion.

• Information

  The 'Notification' alert is the least intrusive. It simply notifies the user with a message without enforcing any rules, providing useful feedback and allowing the entry to be acknowledged.

Customize the error message that appears with a relevant explanation, such as, "Input exceeds the maximum limit of 65," explaining why the entry is invalid.

Advanced data validation – formulas and dynamic lists

Excel's data validation becomes even more powerful with the use and inclusion of formulas. A common scenario is to perform validation using dynamic lists, especially when list options update or change regularly. You can link a list with cell references that fill dependent lists based on previous selections.

Example for dynamic lists

Suppose you want validations that change based on the value of another cell, such as dynamically changing a city list based on the country selected in another cell. This can be accomplished by using Excel's INDIRECT function in conjunction with named ranges.

Let's say:
- Cell A1: Selected country (USA, Canada)
- Category named for USA cities: USACities
- Category named for Canadian cities: CanadaCities

1. Define the name for the city list under each country.
2. In B1 (where City is selected) set the validation: Type: List, Source: =INDIRECT(A1 & "Cities")

Here, if 'USA' is selected in A1, the city drop-down in B1 will source from 'USACities', otherwise it will source from 'CanadaCities'. The use of named ranges and the INDIRECT function supports flexible, dynamic list management.

Test your data validation

Once you've set up your data validation rules, it's important to test them to make sure they work as expected. Try entering different data into the validation cells to see how the rules respond to valid and invalid data. Seeing how the validation rules perform with different inputs gives you confidence that data entry into these fields will adhere to your required standards.

Editing and deleting data validation

Sometimes, you may need to update data validation rules or remove them altogether. To do this:

• Select the cell that contains the validation rule you want to modify.
• Open the Data Validation dialog box via the 'Data' tab.
• Make the necessary edits in the 'Settings', 'Input Message', or 'Error Alert' tabs.
• To remove a validation rule, click "Clear All" in the Data Validation dialog box. This will effectively remove all validation settings from the selected cells.

Conclusion

Creating effective data validation rules in Excel 2016 can significantly impact your data management by increasing accuracy and consistency. Whether enforcing simple numerical constraints or employing complex formulas for validation, these features streamline the data input process and prevent errors. With practice, customizing and expanding your validation criteria will become an invaluable tool when you work with Excel spreadsheets.

  Share This Article

If you find anything wrong with the article content, you can request an update