

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.
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).
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):
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.
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:
Hold on a second. Was this information helpful so far?
Why don't you subscribe to our monthly, free Excel newsletter?
Subscribe now! Your welcome gift: Our big 45 pages keyboard shortcuts package. In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Of course, I'm also on other networks:
Twitter: Follow @professorexcel
Facebook:
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:
Let’s take a look at an example with many different data validation rules.
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:
Please feel free to download the example workbook here.
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.
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.
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.
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.
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.
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.
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 more or get it on Amazon!
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Download test abc