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.
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 cell
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:
=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.
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.
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.
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.
Tables are also a way to dynamically feed a data validation list, so when the table updates the data validation will also update.
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.
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.
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.
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.
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)
SIGN UP FOR OUR NEWSLETTER TODAY – GET EXCEL TIPS TRICKS AND LEARN AND EARN ACTIVITIES TO YOUR INBOX
