Data Validation in Excel: Restrict Cells to Allow Only Certain Values
Working with other people on the same Excel file can be challenging. Especially when you prepare an Excel workbook and another person has to fill in some data. In such case, you probably want to make sure that only your intended values are possible to enter. In this article we learn how to restrict cells to certain values with the data validation function. Furthermore we’ll take a look at the example of a cell in which the user has to fill in a complete form.
Basics of the data validation function in Excel
Excel provides the “Data Validation” function. With Data Validation, you can define, what values or what type of values are allowed for each cell (e.g. date, whole numbers).
Steps for adding restrictions to a cell
Follow these steps to restrict cells in Excel to values
In order to restrict cells to certain numbers or values, please follow these steps (the numbers are corresponding to the picture above):
- Select the cell(s) you want to limit.
- Click on “Data Validation” in the middle of the Data ribbon. Alternatively, press Alt –> A –> V –> V on the keyboard.
- Within the settings tab you can define the allowed values.
- Let’s say, you want people to enter their date of birth. So there are two limitations: It must be a date and it can’t be any day later than today. Select “Date” on the dropdown list.
- As we want to limit the maximum value, we have to select “less than” at “Data:”.
- The end date can either be a fixed date or a formula. In our case we go with the TODAY formula.
- If you want to give a hint about what data is allowed to fill in, you can set an input message (see below for more information)
- If someone enters a value which is not allowed (for example a date in the future or text), you can define an error message, as well as the message type.
Update all cells with the same settings
Update all cells with the same settings
Excel offers the option that you update all cells with the same settings. That’s very helpful – you don’t have to find and select all cells you want to change. Just set the tickmark like on the screenshot on the right side.
Input Message: Provide some help to the user
Create a small message for your restricted cell.
Your co-worker or client who has to fill out your form will thank you: Provide some help. When you select a cell, a small yellow field will appear with your help text (number 1 on the picture).
In order to set up an input message follow these steps:
- Open the data validation window again by clicking on “Data Validation” in the center of the Data ribbon.
- Go to the “Input Message” tab (number 2).
- Make sure the tick is set for “Show input message when cell is selected”.
- Type your text below for a title and the actual input message.
- Confirm with OK.
Hold on a second. Was this information helpful so far?
If yes: Why don't you subscribe to our monthly, free Excel newsletter? You get all this:
The best Excel tips, tricks and tutorials.
1x per month.
No spam. Promised.
Subscribe now!
In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Error message for wrong inputs
Provide your own error message for a wrong data entry.
You can also give your own error message, if someone enters a wrong value. You can even select the style:
- Select the cell you want to provide the error message for (number 1 on the screenshot).
- Open the data validation window again by clicking on “Data Validation” in the center of the Data ribbon.
- Go to the “Error Alert” tab (number 2).
- Select your style (number 3). You got 3 options:
- Stop:
- Warning:
- Information:
Important: If you choose warning, the user can choose to still use his “wrong” value. For stop or information, the user can’t.
- Stop:
- Type a message title as well as the error message.
- Confirm with OK.
Example: Prepare a form
Let’s take a look at an example with many different data validation rules.
The task
Our example: restrict the input cells according to the required input.
You want to restrict all the input cells (the beige cells). The user has to be restricted:
- The date of the event should be between May 2017 and December 2018.
- The number of visitors has to be a whole number and should be smaller than 300.
- The costs per visitor should be a decimal number less than 100.
- The location should be selected from the list on the right hand side.
- The time should be after 4 pm (16:00).
- The maximum length of the text (in this case the reason) reason should not restricted to 200 characters.
- Your ID number. This one is special as it should start with “ID-” and being followed by a 5-digit number. Therefore you have to choose the option “formula“.
The solution
Please feel free to download the example workbook here.
Only allow a certain date or date range
1) Select “Date” under “Allow:”. Under “Data” select “between” if you want to set a start and end date.
Select “Date” under “Allow:”. Under “Data” select “between” if you want to set a start and end date.
Click on the image to see it large or download the example workbook here.
Only allow whole numbers
2) If you want to allow whole numbers only, select “Whole number” under “Allow:”. Furthermore, you can set a minimum and maximum if you choose “between” under “Data:”.
If you want to allow whole numbers only, select “Whole number” under “Allow:”. Furthermore, you can set a minimum and maximum if you choose “between” under “Data:”.
Click on the image to see it large or download the example workbook here.
Only allow a number range between decimal numbers
3) Very similar to whole number is the allow option “Decimal”. In this example there is only a maximum defined.
3) Very similar to whole number is the allow option “Decimal”. In this example there is only a maximum defined.
Click on the image to see it large or download the example workbook here.
Only allow items from a list
4) This option is most popular: The user can select an item from a list. Therefore, you have to allow “List” and link to the list in Excel. Alternatively you can write the possible values directly into the “Source” field, separated by commas.
4) This option is most popular: The user can select an item from a list. Therefore, you have to allow “List” and link to the list in Excel. Alternatively you can write the possible values directly into the “Source” field, separated by commas.
Click on the image to see it large or download the example workbook here.
Only allow a certain time
5) If you want to restrict a time, you can do so by allowing “Time”. Set the Start time in the format “00:00”.
5) If you want to restrict a time, you can do so by allowing “Time”. Set the Start time in the format “00:00”.
Click on the image to see it large or download the example workbook here.
Only allow a maximum number of characters
6) Some people tend to write a lot. You can restrict that by allowing a maximum number of letters.
6) Some people tend to write a lot. You can restrict that by allowing a maximum number of letters.
Click on the image to see it large or download the example workbook here.
Only allow anything you want with a formula
7) The most complicated option: Use a formula to restrict input values. In this case the input value must start with “ID-” and must be followed by a 5-digit number. The complete formula is: =AND(LEFT(B10,3)=”ID-“,ISNUMBER(RIGHT(B10,5)*1))
7) The most complicated option: Use a formula to restrict input values. In this case the input value must start with “ID-” and must be followed by a 5-digit number. The complete formula is: =AND(LEFT(B10,3)="ID-",ISNUMBER(RIGHT(B10,5)*1))
Click on the image to see it large or download the example workbook here.
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
- Learn how Microsoft Excel performs calculations
- Use the simple and effective step-by-step guide to master each method
- Get to know the impact each method will have on performance
Learn more or get it on Amazon!
Final comments
- Please note that the data validation rules aren’t copied with the format painter. Instead, you can copy the cell, paste it with “Paste Special” and select “Validation” on the paste special window.
- Data validation can be quite troublesome when copying sheets from one workbook to another. When you link the condition to another place in the original workbook, you can’t use the “Break links” function (well, you can but it doesn’t work). Please refer to this article in such case.
