Quick Links
- Overview
- Validation Formulas
- Dependent Dropdown Lists
Introduction
Data validation is a feature in Excel used to control what a user can enter into a cell. For example, you could use data validation to make sure a value is a number between 1 and 6, make sure a date occurs in the next 30 days, or make sure a text entry is less than 25 characters.
Data validation can simply display a message to a user telling them what is allowed as shown below:
Data validation can also stop invalid user input. For example, if a product code fails validation, you can display a message like this:
In addition, data validation can be used to present the user with a predefined choicein a dropdown menu:
This can be a convenient way to give a user exactly the values that meet requirements.
Data validation controls
Data validation is implemented via rules defined in Excel's user interface on the Data tab of the ribbon.
Important limitation
It is important to understand that data validation can be easily defeated. If a user copies data from a cell without validation to a cell with data validation, the validation is destroyed (or replaced). Data validation is a good way to let users know what is allowed or expected, but it is nota foolproof way to guarantee input.
Defining data validation rules
Data validation is defined in a window with 3 tabs: Settings, Input Message, and Error Alert:
The settings tab is where you enter validation criteria. There are a number of built-in validation rules with various options, or you can select Custom, and use your own formula to validate input as seen below:
The Input Message tab defines a message to display when a cell with validation rules is selected. This Input Message is completely optional. If no input message is set, no message appears when a user selects a cell with data validation applied. The input message has no effect on what the user can enter — it simply displays a message to let the user know what is allowed or expected.
The Error Alert Tab controls how validation is enforced. For example, when style is set to "Stop", invalid data triggers a window with a message, and the input is not allowed.
The user sees a messagelike this:
When style is set to Information or Warning, a different icon is displayed with a custom message, but the user can ignore the message and enter values that don't pass validation. The table below summarizes behavior for each error alert option.
| Alert Style | Behavior |
|---|---|
| Stop | Stops users from entering invalid data in a cell. Users can retry, but must enter a value that passes data validation. The Stop alert window has two options: Retry and Cancel. |
| Warning | Warns users that data is invalid. The warning does nothing to stop invalid data. The Warning alert window has three options: Yes (to accept invalid data), No (to edit invalid data) and Cancel (to remove the invalid data). |
| Information | Informs users that data is invalid. This message does nothing to stop invalid data. The Information alert window has 2 options: OK to accept invalid data, and Cancel to remove it. |
Data validation options
When a data validation rule is created, there are eight options available to validate user input:
Any Value - no validation is performed. Note: if data validation was previously applied with a set Input Message, the message will still display when the cell is selected, even when Any Value is selected.
Whole Number - only whole numbers are allowed. Once the whole number option is selected, other options become available to further limit input. For example, you can require a whole number between 1 and 10.
Decimal - works like the whole number option, but allows decimal values. For example, with the Decimal option configured to allow values between 0 and 3, values like .5, 2.5, and 3.1 are all allowed.
List - only values from a predefined list are allowed. The values are presented to the user as a dropdown menu control. Allowed values can be hardcoded directly into the Settings tab, or specified as a range on the worksheet.
Date - only dates are allowed. For example, you can require a date between January 1, 2018 and December 31 2021, or a date after June 1, 2018.
Time - only times are allowed. For example, you can require a time between 9:00 AM and 5:00 PM, or only allow times after 12:00 PM.
Text length - validates input based on number of characters or digits. For example, you could require code that contains 5 digits.
Custom - validates user input using a custom formula. In other words, you can write your own formula to validate input. Custom formulas greatly extend the options for data validation. For example, you could use a formula to ensure a value is uppercase, a value contains "xyz", or a date is a weekday in the next 45 days.
The settings tab also includes two checkboxes:
Ignore blank - tells Excel to not validate cells that contain no value. In practice, this setting seems to affect only the command "circle invalid data". When enabled, blank cells are not circled even if they fail validation.
Apply these changes to other cells with the same settings - this setting will update validation applied to other cells when it matches the (original) validation of the cell(s) being edited.
Note: You can also manually select all cells with data validation applied using Go To + Special, as explained below.
Simple drop down menu
You can provide a dropdown menu of options by hardcoding values into the settings box, or selecting a range on the worksheet. For example, to restrict entries to the actions "BUY", "HOLD", or "SELL" you can enter these values separated with commas as seen below:
When applied to a cell in the worksheet, the dropdown menu works like this:
Another way to supply values to a dropdown menu is to use a worksheet reference. For example, with sizes (i.e. small, medium, etc.) in the range F3:F6, you can supply this range directly inside the data validation settings window:
Note the range is entered as an absolute address to prevent it from changingas the data validation is applied to other cells.
Tip: Click the small arrow icon at the far right of the source field to make a selection directly on the worksheet so you don't have to enter the range manually.
You can also use named ranges to specify values. For example, withthe named range called "sizes" for F3:F7, you can enter the name directly in the window, starting with an equal sign:
Named ranges are automatically absolute, so they won't change as the data validation is applied to different cells. If named ranges are new to you, this page has a good overview and a number of related tips.
You can also create dependent dropdown listswith a custom formula.
Tip - if you use an Excel Table for dropdown values, Excel will expand or contract the table automatically when dropdown values are added or removed. In other words, Excel will automatically keep the dropdown in sync with values in the table as values are changed, added, or removed. If you're new to Excel Tables, you can see a demo in this video on Table shortcuts.
Data validation with a custom formula
Data validation formulas must be logical formulas that return TRUE when input is valid and FALSE when input is invalid. For example, to allow any number as input in cell A1, you could use the ISNUMBER function in a formula like this:
=ISNUMBER(A1)If a user enters a value like 10 in A1, ISNUMBER returns TRUE and data validation succeeds. If they entera value like "apple" in A1, ISNUMBER returns FALSE and data validation fails.
To enable data validation with a formula, selected "Custom" in the settings tab, then enter a formula in the formula bar beginning with an equal sign (=) as usual.
Troubleshooting formulas
Excel ignores data validation formulas that return errors. If a formula isn't working, and you can't figure out why, set up dummy formulas to make sure the formula is performing as you expect. Dummy formulas are simply data validation formulas entered directly on the worksheet so that you can see what they return easily. The screen below shows an example:
Once you get the dummy formula working like you want, simply copy and paste it into the data validation formula area.
If this dummy formula idea is confusing to you, watch this video, which shows how to use dummy formulas to perfect conditional formatting formulas. The concept is exactly the same.
Data validation formula examples
The possibilities for data validation custom formulas are virtually unlimited. Here are a few examples to give you some inspiration:
To allow only 5 character values that begin with "z" you could use:
=AND(LEFT(A1)="z",LEN(A1)=5)This formula returns TRUE only when a code is 5 digits long and starts with "z". The two circled values return FALSE with this formula.
To allow only a date within 30 days of today:
=AND(A1>TODAY(),A1<=(TODAY()+30))To allow only unique values:
=COUNTIF(range,A1)<2To allow only an email address
=ISNUMBER(FIND("@",A1)Click for more formula examples and detailed explanations
Data validation to circle invalid entries
Once data validation is applied, you can ask Excel to circle previously entered invalid values. On the Data tab of the ribbon, click Data Validation and select "Circle Invalid Data":
For example, the screen below shows values circled that fail validation with this custom formula:
=AND(LEFT(A1)="z",LEN(A1)=5)
Find cells with data validation
To find cells with data validation applied, you can use the Go To > Special dialog. Type the keyboard shortcut Control + G, then click the Special button. When the Dialog appears, select "Data Validation":
Copy data validation from one cell to another
To copy validation from one cell to other cells. Copy the cell(s) normally that contain the data validation you want, then use Paste Special + Validation. Once the dialog appears, type "n" to select validation, or click validation with the mouse.
Note:you can use the keyboard shortcut Control + Alt + V to invoke Paste Special without the mouse.
Clear all data validation
To clear all data validation from a range of cells, make the selection, then click the Data Validation button on the Data tab of the ribbon. Then click the "Clear All" button:
To clear all data validation from a worksheet, select the entire worksheet, then, follow the same steps above.
FAQs
Is there a limit to Data Validation list 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.
Why is my Data Validation list not working? ›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.
How do I extend Data Validation to more 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.
- 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.
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.
How do I check data validation restrictions? ›- On the Edit menu, point to Find, and then click Go To.
- Click Special.
- Select Data Validation.
- To find all cells with data validation, select All, and then click OK. To find cells that have the same validation rules as another cell, select Same, and then click OK.
Data validation is the process of checking the accuracy and quality of data before it is entered into a database or a spreadsheet. Data validation can help you avoid errors, inconsistencies, and duplicates that can affect your analysis and reporting.
How do I stop 255 character limit in Excel? ›Text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function or the concatenation operator (&).
How do I increase cell limits in Excel? ›Open the 'Data' dropdown and select a limiting criteria. To limit it to a certain number, select 'Less than or equal to'. To set the maximum value, enter a value in the Maximum field. Click Ok.
Can you put multiple data validation in one cell? ›Re: Excel multiple data validation
You can create two lots of data validation in one column based on another column by using a custom formula. Here's how you can do it: Select the cells you want to apply data validation to. Go to the Data tab on the ribbon and click on Data Validation.
How do I fix Data Validation restrictions? ›
- Click the control whose data validation you want to modify.
- On the Format menu, click Data Validation.
- In the Data Validation dialog box, click the condition that you want to change, click Modify, and then make the changes that you want.
- Select one or more cells to validate.
- On the Data tab, in the Data Tools group, click Data Validation .
- On the Settings tab, in the Allow box, select List .
- In the Source box, type your list values, separated by commas. ...
- Make sure that the In-cell dropdown check box is selected.
Bypass Validation Rules
To set up a Validation Rule bypass, the process also starts by creating a new field on your User object. This field is a checkbox and can be named “Bypass VR”. This value must then be entered on all validation rules for all objects.
Go to Data > Data Validation. On the Settings tab, click in the Source box, and then change your list items as needed.
How do I extend a list of numbers in Excel? ›- Select the first cell in the range that you want to fill.
- Type the starting value for the series.
- Type a value in the next cell to establish a pattern. ...
- Select the cells that contain the starting values. ...
- Drag the fill handle.
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.
How do I allow multiple selections in a drop-down list? ›- Select one or more cells for your dropdown (D3:D7 in our case).
- On the Data tab, in the Data Tools group, click Data Validation.
- In the Allow drop-down box, select List.
- In the Source box, enter the formula that indirectly refers to Table1's column named Items.
Excel Drop Down Lists are intuitive to use and extremely useful in when you are creating an Excel Dashboard or a data entry form. You can create multiple drop-down lists in Excel using the same source data.
What are the data validation rules? ›What data validation rules are. Data validation rules control what constants can be entered into a cell, e.g. any whole number between 0 and 9, or one of several values from another part of the spreadsheet.
How to bypass validation rules in data loader? ›Simply create a Custom field in your object of the type Checkbox called as "Bypass Valdiation". Then set this value to TRUE when you update the records through the dataloader and tweak your valudation rule like this. This will ensure that the validation rule is not invoked when you update/insert through dataloader.
How do I access custom settings in validation rule? ›
- Configure Default Organization Level Value Setting. Click New and check all checkboxes. ...
- Configure settings for Profiles and Users. Click New in the section below the Default Organization Level Value.
If we're constantly seeking validation from others, we can start to believe that our worthiness is contingent on what others think of us. Low self-esteem can lead to unhealthy levels of self-consciousness and self-doubt.
What is the disadvantage of validation set? ›Drawbacks of the validation set approach:
- In validation approach, only a subset (e.g. half) of the data are used to train the model. - That is, the model won't be as well-estimated as if we had used all the data. - So the model is likely to predict worse on the validation set than if we had more data.
However, there is one (known) limitation; Excel only allows 255 string characters for defining the dropdown list.
How to increase character limit from 255 in data validation? ›To avoid this limitation, I can suggest using one of the following approaches: Add a hidden worksheet into your document, fill this worksheet with all required values for data validation and use a range of this worksheet as a data validation parameter.
What is the character limit for data validation input message? ›The data validation input message is limited to 255 characters, so the technique show above is subject to that limit too. If you need to show longer messages in the text box, you can download the Longer Message sample file, from the Download section, below.
What is the maximum cell limit in Excel? ›Cell Character Limits
Both . xlsx and . csv files have a limit of 32,767 characters per cell. Excel has a limit of 1,048,576 rows and 16,384 columns per sheet.
No way, you can't expand Excel sheet limits. On the other hand you may load your data into data model and process them using Power View returning compact result into Excel sheet.
Why can't I enter a 16 digit number in Excel? ›Excel has a surprising limitation that you may not discover until you try to enter more than 15 digits in a cell. The application can only store numbers with15 digits of precision. If you enter a number that has 16 digits or more, Excel changes those extra digits to zero.
How do I limit data validation based on another cell? ›Re: Data validation range based on another cell contents
On the Data tab of the ribbon, click Data Validation. Select Decimal from the Allow drop down, and between from the Data drop down. If desired, activate the Input Message and Error Alert tabs and enter appropriate messages. Finally, click OK.
How do I set up data validation multiple selections? ›
Go to Data –> Data Tools –> Data Validation. In the Data Validation dialogue box, within the settings tab, select 'List' as Validation Criteria. In Source field, select the cells which have the items that you want in the drop down. Click OK.
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.
Validation rule and validation text examples
Value must be zero or greater. You must enter a positive number. Value must be either 0 or greater than 100.
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.
Why do I keep getting a validation error? ›The below validation errors are the most common and will respond with some details, including a list of validation messages. Validation errors typically occur when a request is malformed -- usually because a field has not been given the correct value type, or the JSON is misformatted.
Why dropdown is not working in Excel? ›Drop down list in Excel fails to work if your worksheet is corrupted. Sometimes the files also get corrupted but they can be fixed by using the repair option. To repair you will need to locate your file.
Can Excel Data Validation allow multiple values? ›Excel Data Validation - Select Multiple Items. Instead of limiting an Excel data validation drop down list to a single selection, you can use this macro to allow multiple selections.
How do you do dynamic Data Validation in Excel with 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.
Can you use formulas in Data Validation? ›On the Settings tab of the Data Validation dialog window, select Custom in the Allow box, and enter your data validation formula in the Formula box. Click OK.
What is bypass validation? ›The "Bypass Validation" setting on Submit Buttons allows the user to move a record to a different status without triggering the field validation. This includes JavaScript Validation and mandatory settings on custom fields. The Submit Logic on the button itself will still be validated.
When can a validation rule be used to prevent invalid data 3 answers? ›
When records are edited by a user - If a user is updating any records user might know or might not know what kind of information needs to be entered into the fields.So validation rule can be used here to prevent invalid data.
How do I view all Data Validation rules? ›- On the Edit menu, point to Find, and then click Go To.
- Click Special.
- Select Data Validation.
- To find all cells with data validation, select All, and then click OK. To find cells that have the same validation rules as another cell, select Same, and then click OK.
"Text values in formulas are limited to 255 characters. To create longer text values in a formula, use the CONCATENATE function or the concatenation operator (&)."
Why is my dropdown list not showing in Excel? ›Click the DATA tab, and click Data Validation. In the Data Validation dialog, set Allow to List; this enables a list in the cell. Leave In-cell drop-down selected; this enables a drop-down list in the cell.
What is the data limit for Excel? ›| Feature | Maximum limit |
|---|---|
| Total number of rows and columns on a worksheet | 1,048,576 rows by 16,384 columns |
| Column width | 255 characters |
| Row height | 409 points |
| Page breaks | 1,026 horizontal and vertical |
You can use data validation in Excel to restrict data entry based on another column. You can create two lots of data validation in one column based on another column by using a custom formula.
How do you overcome the 255 character limit? ›Text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function or the concatenation operator (&).
How do I force a drop-down list in Excel? ›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 auto populate data from a drop-down list in Excel? ›- Select one or more cells you want to use as a basis for filling additional cells. For a series like 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. ...
- Drag the fill handle .
- If needed, click Auto Fill Options. and choose the option you want.
How Many Rows of Data Can Excel Handle? Excel spreadsheet can handle 1,048,576 rows and 16,384 columns at a time. If you are dealing with large datasets, it's inherent that you will have limitations using Excel Power Query.
What are the limits of Excel when working with big data? ›
Hands-on big data with Excel
In fact, Excel limits the number of rows in a spreadsheet to about one million; this may seem a lot, but rows of big data come in the millions, billions and even more. At this point Excel would appear to be of little help with big data analysis, but this is not true.
- Step 1: Setting up Source Dataset. In the first step, we prepared the source dataset. ...
- Step 2: Importing Source Dataset. ...
- Step 3: Adding to Data Model. ...
- Step 4: Inserting PivotTable from Data Model. ...
- Step 5: Employing Slicers. ...
- Step 6: Inserting Charts.
Whole Number - to restrict the cell to accept only whole numbers. Decimal - to restrict the cell to accept only decimal numbers. List - to pick data from the drop-down list. Date - to restrict the cell to accept only date.