Note:This is an advanced topic on data validation. For an introduction to data validation, and how to validate a cell or a range, see Add data validation to a cell or a range.
You can use data validation to restrict the type of data or values that users enter into cells. For example, you might use data validation to calculate the maximum allowed value in a cell based on a value elsewhere in the workbook. In the following example, the user has typed abc , which is not an acceptable value in that cell.
When is data validation useful?
Data validation is invaluable when you want to share a workbook with others, and you want the data entered to be accurate and consistent. Among other things, you can use data validation for the following:
-
Restrict entries to predefined items in a list— For example, you can limit a user’s department selections to Accounting, Payroll, HR, to name a few.
-
Restrict numbers outside a specified range— For example, you can specify a maximum percentage input for an employee’s annual merit increase, let’s say 3%, or only allow a whole number between 1 and 100.
-
Restrict dates outside a certain time frame— For example, in an employee time off request, you can prevent someone from selecting a date before today’s date.
-
Restrict times outside a certain time frame— For example, you can specify meeting scheduling between 8:00 AM and 5:00 PM.
-
Limit the number of text characters— For example, you can limit the allowed text in a cell to 10 or fewer characters.
-
Validate data based on formulas or values in other cells— For example, you can use data validation to set a maximum limit for commissions and bonuses based on the overall projected payroll value. If users enter more than the limit amount, they see an error message.
Data Validation Input and Error Messages
You can choose to show an Input Message when the user selects the cell. Input messages are generally used to offer users guidance about the type of data that you want entered in the cell. This type of message appears near the cell. You can move this message if you want to, and it remains visible until you move to another cell or press Esc.
You set up your Input Message in the second data validation tab.
Once your users get used to your Input Message, you can uncheck the Show input message when cell is selected option.
You can also show an Error Alert that appears only after users enter invalid data.
You can choose from three types of error alerts:
| Icon | Type | Use to |
 | Stop | Prevent users from entering invalid data in a cell. A Stop alert message has two options: Retry or Cancel. |
 | Warning | Warn users that the data they entered is invalid, without preventing them from entering it. When a Warning alert message appears, users can click Yes to accept the invalid entry, No to edit the invalid entry, or Cancel to remove the invalid entry. |
 | Information | Inform users that the data they entered is invalid, without preventing them from entering it. This type of error alert is the most flexible. When an Information alert message appears, users can click OK to accept the invalid value or Cancel to reject it. |
Tips for working with data validation
Use these tips and tricks for working with data validation in Excel.
Note:If you want to use data validation with workbooks in Excel Services or the Excel Web App you will need to create the data validation in the Excel desktop version first.
-
The width of the drop-down list is determined by the width of the cell that has the data validation. You might need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.
-
If you plan to protect the worksheet or workbook, protect it after you have finished specifying any validation settings. Make sure that you unlock any validated cells before you protect the worksheet. Otherwise, users will not be able to type any data in the cells. See Protect a worksheet.
See AlsoSimple Data Validation in SQL -
If you plan to share the workbook, share it only after you have finished specifying data validation and protection settings. After you share a workbook, you won't be able to change the validation settings unless you stop sharing.
-
You can apply data validation to cells that already have data entered in them. However, Excel does not automatically notify you that the existing cells contain invalid data. In this scenario, you can highlight invalid data by instructing Excel to circle it on the worksheet. Once you have identified the invalid data, you can hide the circles again. If you correct an invalid entry, the circle disappears automatically.
To apply the circles, select the cells you want to evaluate and go to Data > Data Tools > Data Validation > Circle Invalid Data.
-
To quickly remove data validation for a cell, select it, and then go to Data > Data Tools > Data Validation > Settings > Clear All.
-
To find the cells on the worksheet that have data validation, on the Home tab, in the Editing group, click Find & Select, and then click Data Validation. After you have found the cells that have data validation, you can change, copy, or remove validation settings.
-
When creating a drop-down list, you can use the Define Name command (Formulas tab, Defined Names group) to define a name for the range that contains the list. After you create the list on another worksheet, you can hide the worksheet that contains the list and then protect the workbook so that users won't have access to the list.
-
If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, on the Settings tab, select the Apply these changes to all other cells with the same settings check box.
-
If data validation isn't working, make sure that:
-
Users are not copying or filling data - Data validation is designed to show messages and prevent invalid entries only when users type data directly in a cell. When data is copied or filled, the messages do not appear. To prevent users from copying and filling data by dragging and dropping cells, go to File > Options > Advanced > Editing options > clear the Enable fill handle and cell drag-and-drop check box, and then protect the worksheet.
-
Manual recalculation is turned off - If manual recalculation is turned on, uncalculated cells can prevent data from being validated correctly. To turn off manual recalculation, go to the Formulas tab > Calculation group > Calculation Options > click Automatic.
-
Formulas are error free - Make sure that formulas in validated cells do not cause errors, such as #REF! or #DIV/0!. Excel ignores the data validation until you correct the error.
-
Cells referenced in formulas are correct - If a referenced cell changes so that a formula in a validated cell calculates an invalid result, the validation message for the cell won't appear.
-
An Excel table might be linked to a SharePoint site - You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.
-
You might currently be entering data - The Data Validation command is not available while you are entering data in a cell. To finish entering data, press Enter or ESC to quit.
-
The worksheet might be protected or shared - You cannot change data validation settings if your workbook is shared or protected. You’ll need to unshare or unprotect your workbook first.
-
How to update or remove data validation in an inherited workbook
If you inherit a workbook with data validation, you can modify or remove it unless the worksheet is protected. If it’s protected with a password that you do not know you should try to contact the previous owner to help you unprotect the worksheet, as Excel has no way to recover unknown or lost passwords. You can also copy the data to another worksheet, and then remove the data validation.
If you see a data validation alert when you try to enter or change data in a cell, and you're not clear about what you can enter, contact the owner of the workbook.
Need more help?
You can always ask an expert in the Excel Tech Communityor get support in the Answers community.
FAQs
How do I add more items to Data Validation? ›
On the worksheet where you applied the drop-down list, select a cell that has the drop-down list. Go to Data > Data Validation. On the Settings tab, click in the Source box, and then change your list items as needed. Each item should be separated by a comma, with no spaces in between like this: Yes,No,Maybe.
Is there a limit on Data Validation in Excel? ›There are limits to the number of items that will show in a data validation drop down list: The list can show up to show 32,767 items from a list on the worksheet. If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators.
What are the 3 types of Data Validation in Excel? ›Time - The user should enter a time. Text Length - It validates input based on the length of the data. Custom - It validates the user input using a custom formula.
How to display more than 8 in Data Validation list in Excel? ›By default, a data validation drop-down list only shows 8 items in the list when clicking the drop-down arrow. So you need to drag the scroll bar down for viewing more values in the list.
What are the 4 step processes of data validation? ›- Detail Plan. It is the most critical step, to create the proper roadmap for it. ...
- Validate the Database. This is responsible for ensuring that all the applicable data is present from source to sink. ...
- Validate Data Formatting. ...
- Sampling.
- A) Prospective validation (or premarket validation)
- B) Retrospective validation.
- C) Concurrent validation.
- D) Revalidation.
The only way to increase the size of a Data Validation drop-down list is to increase the zoom percentage at which you view the sheet.
What are data validation rules in Microsoft Access? ›A validation rule is one way to restrict input in a table field or a control (such as a text box) on a form. Validation text lets you provide a message to help users who input data that is not valid.
What are three 3 critical aspects of validation? ›Validators must look at the evidence in the sample, and determine if it is valid, reliable, sufficient, current and authentic.
What are the 4 basic data types in Excel? ›| Data type in Excel | Data type in DAX |
|---|---|
| Whole Number | A 64 bit (eight-bytes) integer value 1, 2 |
| Decimal Number | A 64 bit (eight-bytes) real number 1, 2 |
| TRUE/FALSE | Boolean |
| Text | String |
How do I show more than 10000 items in Excel? ›
10,000 is just filtering drop-down list limit and not filtering limit. You can use "Number Filters" or "Text Filters" to use logic to filter those columns with more than 10,000 unique values.
How do I apply Data Validation list to multiple cells? ›- Select the cell to which the validation rule applies and press Ctrl + C to copy it.
- Select other cells you want to validate. ...
- Right-click the selection, click Paste Special, and then select the Validation option. ...
- Click OK.
- Select a cell below or to the right of the numbers for which you want to find the smallest number.
- On the Home tab, in the Editing group, click the arrow next to AutoSum. , click Min (calculates the smallest) or Max (calculates the largest), and then press ENTER.
There are three types of data validation checks: (1) field checks, (2) record checks, and (3) file checks. Common field check controls include alphanumeric field tests, missing data (completeness) tests, range tests, limit tests, existence (validity) tests, and check-digit verification tests.
How do I create a custom validation attribute? ›- Choose a "web application" project and give an appropriate name to your project.
- Select the "empty" template, check on the MVC, and click OK.
- Right-click on Models folder, choose “Add”, then choose the class. ...
- Right-click on the Controllers folder and add a new controller.
Data Type Validation: This technique checks if the data entered into the system is of the correct data type, such as a string, integer, or date. Range Validation: This technique checks if the data entered into the system falls within a specific range of values, such as a customer's age between 18 and 65 years old.
What is the best practice of data validation? ›The best way to ensure the high data quality of your datasets is to perform up-front data validation. Check the accuracy and completeness of collected data before you add it to your data warehouse. This will increase the time you need to integrate new data sources into your data warehouse.
What is data validation in Excel with example? ›Data validation in Excel is a feature that allows you to control the type of data entered into your worksheet. For example, Excel data validation allows you to limit data entries to a selection from a dropdown list and to restrict certain data entries, such as dates or numbers outside of a predetermined range.
What are the 6 levels of validation? ›- SIX LEVELS of VALIDATION.
- Level One: Stay Awake and Pay Attention.
- Level Two: Accurate Reflection.
- Level Three: Stating What Hasn't Been Said Out Loud (“the unarticulated”)
- Level Four: Validating Using Past History or Biology.
- Level Five: Normalizing.
- Level Six: Radical Genuineness.
- Click Tables on the Model menu. ...
- Select the table in the Navigation Grid for which you want to define validation rule usage. ...
- Click the Validation tab.
- Select the validation usage item in the grid that you want to define and work with the following options: ...
- Click Close.
What is data validation in ETL? ›
What Is Data Validation? In simple terms, Data Validation is the act of validating the fact that the data that are moved as part of ETL or data migration jobs are consistent, accurate, and complete in the target production live systems to serve the business requirements.
What are the 3 stages of process validation? ›The 3 stages of process validation are 1) Process Design, 2) Process Qualification, and 3) Continued Process Verification. Current Good Manufacturing Practices (cGMP) come strongly into play when participating in pharmaceutical process validation activities. A number of them are legally enforceable requirements.
What are the five steps in validation process? ›- Set up a team and assign a leader to carry out the design of the validation. ...
- Determine the scope of the study. ...
- Design a sampling plan. ...
- Select a method of analysis. ...
- Establish acceptance criteria.
Method validation is the process used to confirm that the analytical procedure employed for a specific test is suitable for its intended use. Results from method validation can be used to judge the quality, reliability and consistency of analytical results; it is an integral part of any good analytical practice.
What is a dynamic drop-down list? ›It's a list that we can select from in order to filter attendance data by department. The source of this dynamic data validation list has been created using the UNIQUE function. UNIQUE is a Dynamic Array Formula that returns all the unique values from a data range. In other words, it removes duplicates from a data set.
What is data verification in database? ›Data verification is a process in which different types of data are checked for accuracy and inconsistencies after data migration is done. In some domains it is referred to Source Data Verification (SDV), such as in clinical trials.
How do I enlarge a drop-down list? ›The length of the drop-down box is not big enough to display the entire text. The simple solution is to widen the column that the drop-down box is in. You can adjust the column manually by dragging the border of the column header.
Why is my dropdown in Excel so small? ›The length of the drop-down box is not big enough to display the entire text. What is this? The simple solution is to widen the column that the drop-down box is in. You can adjust the column manually by dragging the border of the column header.
Why is my data validation list so wide? ›In most cases: the worksheet had other cells with data validation in the same row. all dropdowns became the width of the widest dropdown. one of the data validation lists is in a merged cell.
What are data validation rules in Excel? ›Data validation rules allow you to constrain the values that can be entered into a worksheet cell. You can define one or more data validation rules for your worksheet. Typically, you define a separate data validation rule for each column in your worksheet where you need to constrain user entered values.
What are validation rules? ›
Validation rules verify that the data a user enters in a record meets the standards you specify before the user can save the record. A validation rule can contain a formula or expression that evaluates the data in one or more fields and returns a value of “True” or “False”.
What is the maximum length of validation rule in Access? ›The maximum length for the ValidationRule property setting is 2048 characters. The maximum length for the ValidationText property setting is 255 characters.
How to add more options in Data Validation list Google Sheets? ›Tap Data Validation. Under "Criteria," choose an option: List of items: To add an item, tap +Add and enter an item. To save an item, tap Done.
How do I add a list to Data Validation in Excel? ›Select the cells that you want to contain the lists. On the ribbon, click DATA > Data Validation. In the dialog, set Allow to List. Click in Source, type the text or numbers (separated by commas, for a comma-delimited list) that you want in your drop-down list, and click OK.
How do I create a longer Data Validation list in Excel? ›- Highlight where you want to extend it to.
- Make sure you have included cells with the data validation.
- Click on Data – Data Validation.
- You will receive a message that says “ The selection contains some cells without Data Validation settings. ...
- Click on Yes.
Select a cell that you want to expand its drop-down list. Press Alt + down arrow button, then the drop-down list is expanded. Press down arrow to choose the value in drop-down list that you want to fill in cell, press Enter key to fill it in cell.
How do I continue a drop-down list in Excel? ›Select the cell where you want a drop-down list. Click Data Validation, select List, click in Source, press F3, select the name, click OK, and click OK again. Verify the cell contains the drop-down list with the entries provided by the named range, and copy the list to the other cells.
Why is my drop-down list so small in Excel? ›The length of the drop-down box is not big enough to display the entire text. What is this? The simple solution is to widen the column that the drop-down box is in. You can adjust the column manually by dragging the border of the column header.
Can you select more than one option Data Validation? ›On an Excel worksheet, you can only select one item from a data validation drop down list of items. Later, if you choose a different item from the drop down, the new item replaces the previous selection in the cell. However, with a bit of Excel programming, you select multiple items, and keep all of them in the cell.
How to apply multiple Data Validation in one cell in Google sheet? ›Click right button on the column and click on data validation. Then you can add some rule for data validation.
How do you add Data Validation to a sheet? ›
In Google Sheets, open a spreadsheet. Select the cell or cells you want to change, then select an option: Click Data. Data validation.
How do I create a Data Validation list from a table? ›Select the cell in the worksheet where you want the drop-down list. Go to the Data tab on the Ribbon, then click Data Validation . On the Settings tab, in the Allow box, click List . If it's OK for people to leave the cell empty, check the Ignore blank box.
How do I set Data Validation list to table? ›Select the column in your data entry table that you wish to add data validation to. Go to Data/Data Validation or Alt + D + L to open the Data Validation window. Select “List” from the “Allow” dropdown menu. In the “Source” box, hit the F3 key and select your defined name from the “Paste Name” box.
What is the shortcut to expand Data Validation list in Excel? ›To activate the Data Validation dialog box, select the Data Validation command (Data tab, Data Tools group, Data Validation menu) or use the keyboard shortcut Alt+A+V+V.