In Excel Data Validation will allow you to add an element of control to your spreadsheets by restricting when the user can enter into a cell or cells. It can be classed as a spreadsheet security feature; however, it is not 100% fool proof. By setting up Data validation, you can ensure users only enter text of a certain length to cells, or ensure they only enter a number between a specific range. A common use of data validation is to create a drop down containing a list that users can select between.
Now you know data validation is used for, Let’s have a look at the content of this article. It’s going to be a long one, so do grab a coffee before you start. By the time you complete this article you will be an expert at using Data validation in Excel.
Contents
How to set up Basic Data Validation
Example 1 – Data Validation – Only allow a date to be entered into a cell How to set up Input messages and edit existing data validation How to set up Data Validation Error Alerts and Restriction Style How to remove Data Validation in Excel How to find cells with Data Validation Data Validation List and Excel tables Example 2- Data validation with a Drop-Down List Example 3: Dynamic Data validation drop down Example 4 - How to reference Dynamic Arrays in Excels Data Validation Custom Data Validation Example 5 – How to apply multiple data validations into one cellHow to set up Basic Data Validation
Data validation can be found On the Data tab, in the Data Tools group.
Example 1 – Data Validation – Only allow a date to be entered into a cell
We wish to ensure users only enter today's date into cell C3
Start by selecting the cell, or cells that you wish to validate. The from the Data time and the data tools group, select Data Validation.
This will open the data validation set up box on the settings tab. It is from the settings tab that you set criteria for the validation. The default setting is to allow any value. This really means there is no validation in place.
We can select different criteria by using the Allow drop down. In this example we need to select Date.
Once we select Date, or any other allow criteria, further options will become available.
Numerical and date data allows you select between the following:
- Between
- Not Between
- Equal to
- Not Equal to
- Greater than
- Less than
- Greater than or equal to
- Less than or equal to
=TODAY ()
This will ensure only today's date can be entered into the selected cell. The final step is to select OK and the data validation rules will be applied.
Now if the user tried to enter anything but today's date, they will not be able to move forward and will get an error.
How to set up Input messages and edit existing data validation
An input message allows you give directions to the spreadsheet user. When the user clicks on a cell with a data validation input message set, they will see the message that you have set.
Sticking with our example, let’s say we wanted to tell the user they must enter today's date. To do this we must edit our existing data validation.
Start by selecting the cell, the select data validation from the data ribbon. Once the set-up box opens, select the input message tab. We can give our message a title, in this case we have called it Date. Then you enter your Input message and select Ok to apply the setting to the selected cell or cells.
Now when the user selects the cell with the data validation, they will see the input message that you set.
How to set up Data Validation Error Alerts and Restriction Style
An Error Alter will warn the users if they enter the wrong type of data to a cell with data validation. The restriction style will define if the error will allow the user to continue with the error or make them change their entry.
To set or change an Error Alert or Restriction Style, go to the Error Alert tab on the Data Validation set up box. We can add in a Title and the error message. This will be shown if the user enters something that does not match the validation criteria.
You can also change the Style from the Error Alter tab. The default setting is Stop. The Stop style will not accept data that is not valid and the user much either change the data or cancel the entry.
If the style is set on Warning, the user will receive a notification with the Error message, but they will have the option to proceed with the incorrect data.
When the style is set to information, the users will receive a notification with the error message, but they can continue by pressing ok on the error message.
How to remove Data Validation in Excel
To remove data validation from cells in Excel, first select the cells. Then select Data Validation from the data ribbon to open up the Data validation set up box. On the bottom left of the setup box you will find Clear All. To remove the data validation, select Clear All from the setting tab. Clear all in the Input message and Error Alert tabs will not clear all the validation, only the message from the chosen tab. To remove the data validation in its entirety, you must select Clear all from the settings tab.
How to find cells with Data Validation
You can easily find all cells on a worksheet that have data validation using Go to Special. Go to Special can be accessed via the Home Ribbon. Under the Editing set of commands is Find & Select. The drop down will show Go to Special.
By Selecting ALL, any cells that contain data validation will be selected. If you are looking to find cells with a specific data validation, select one cell containing the validation you are looking for and in Go to Special, select Data validation and select Same.
Data Validation List and Excel tables
Data validation can be applied to tables and columns of tables. As tables have dynamic properties, once you set up the data validation then it will copy as you add new rows to the table.
Tables are also a way to dynamically feed a data validation list, so when the table updates the data validation will also update.
Example 2- Data validation with a Drop-Down List
A common use of data validation is to provide a drop-down list with predefined values that a user can choose from. Setting this up is relatively simple. In this example we are going to create a drop-down list so users can choose between customers when completing a table containing invoice details. This table has been named Invtb.
Start by selecting the column you wish to add the validation to. As we are using tables, if we hover the mouse over the column, we will get a black arrow, click once and you will select the column excluding the header.
Access the data validation set up box and select List in the Allow criteria. Insure In-cell dropdown is selected. Then in the source, you can type in the list items you wish the user to select between. Each item must be separated with a comma.
Alternatively, instead of typing in the list, you can reference cells that contain the values you wish to include in the list.
Now when the user adds a new column to the table, and gets to the customer field, the user will be able to select between the predefined list of customers.
Example 3: Dynamic Data validation drop down
In the above example we created a drop down using a list, or by typing in the values. However, this type of list is not dynamic and will not update automatically if you want to include new values. By using a table as a source we can ensure our drop-down updates when new values are added.
Start by creating a list of values and converting the list to a table. CTRL+T will convert to a table.
Tables by their nature use structured naming for the columns within the table. However, for some reason, the data validation set up box does not recognize these. The trick now is that we must give the list column a predefined name.
Then select the table column you wish to use as the data source. From the Formulas ribbon, select Define Name.
This will open the Define name set up box. First give the name. You can then update the scope if needed and add a comment. As we had already selected the data, we can see the Refers to field is complete. And it uses the structured naming from the table. If you can see cell references here instead of the structured naming, delete the cell references and re-select the data. Cell references will not be dynamic. The structured referencing shows that we have taken the entire column, which will include any updates.
Now select the cells in which you want the data validation to be applied and open the data validation set up box.
Select List as the allow criteria option and in source we can paste in the name. F3 is the keyboard shortcut to paste name.
Now if we update our table containing our customers, our data validation drop down list will also update.
Example 4 - How to reference Dynamic Array List in Excels Data Validation
Introduced in Excel 365 Dynamic Arrays offer a new way to solve problems in Excel. By using Dynamic arrays, you can create a Dynamic unique and sorted list and use this as a reference in the data validation source. If you are not familiar with Dynamic arrays, I would suggest you read this article Excel Dynamic Arrays – A new way to model your Excel Spreadsheets
Suppose we have created a unique sorted list using Excel Dynamic arrays, as we can see in the image. As this is a dynamic array the formula only resides in cell H2, however the spill range is currently H2:H6. To reference an entire spill range in Excel you select the cell that contain the formula, in this case H2 and follow it by #. So H2#
It is this same referencing you use in Data validation to use a Dynamic Array spill range as a data validation list.
Custom Data Validation
Custom Data validation will allow you to set your own rules using Excel formulas to validate the data being entered. You can be highly creative when you are using Custom data validation and knowing Excel formulas, and being strong with conditional statements comes in very handy.
When you are writing a formula for custom data validation, the formula must return a True or a False statement. Data will be valid when the true conditions of the formula are met. The data will be rejected if a false value is returned.
Example 5 – How to apply multiple data validations into one cell
Suppose we wanted to ensure that the user can only enter data that beings with either WWW or HTTPS and only 30 character are allowed.
To achieve this, we must write a conditional statement to test the following logic
The total length of the data entered must be no longer than 30 characters and the first 3 letters of the cell must be “www” or the first 5 letters must be “https”
I would suggest writing and testing the formula in an adjacent cell before you apply it to the data validation. This saves a lot of time debugging if the formula goes wrong.
Assuming our data validation cell is B2, the conditional statement we would use in this case is
=AND(LEN(B2)<30,OR(LEFT(B2,3)="www",LEFT(B2,5)="https"))
Once we have written and testing our formula, we can then take a copy of the formula and use this as the source in our custom data validation.
Select the cell in which you want to apply the validation and open the data validation set up box. Select Custom as the allow criteria. Then simply paste in the formula you created to the Formula box. (alternatively, you can link to the cell that contains the formula. However, I would not recommend this. The reality is you do not need this formula in your spreadsheet and once it has been pasted into data validation, you can then delete the formula from the spreadsheet)
Conclusion
Data validation in Excel is a powerful tool that will allow you to control and restrict the user inputs. This will ensure your data is more accurate and your spreadsheet more robust. However, it does have its limitations. A user can copy and paste over a cell with data validation, removing the validation in the process.
SIGN UP
Cross posted from my blog with SteemPress : https://theexcelclub.com/excel-data-validation-control-and-restrict-user-inputs/