Site icon Professor Excel

Data Validation in Excel: Restrict Cells to Allow Only Certain Values

data, validation, cell, restrict

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, including adding drop-down lists. Furthermore we’ll take a look at the example of a cell in which the user has to fill in a complete form.

In a hurry? Click here to scroll down to the drop-down list guidance.

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):

  1. Select the cell(s) you want to limit.
  2. Click on “Data Validation” in the middle of the Data ribbon. Alternatively, press Alt –> A –> V –> V on the keyboard.
  3. Within the settings tab you can define the allowed values.
  4. 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.
  5. As we want to limit the maximum value, we have to select “less than” at “Data:”.
  6. The end date can either be a fixed date or a formula. In our case we go with the TODAY formula.
  7. 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)
  8. 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:


Hold on a second. Was this information helpful so far?
Connect with me on LinkedIn...

Boost your Excel skills: Learn the best Excel tricks and tutorials!


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:

 

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:

  1. The date of the event should be between May 2017 and December 2018.
  2. The number of visitors has to be a whole number and should be smaller than 300.
  3. The costs per visitor should be a decimal number less than 100.
  4. The location should be selected from the list on the right hand side.
  5. The time should be after 4 pm (16:00).
  6. The maximum length of the text (in this case the reason) reason should not restricted to 200 characters.
  7. 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 (including drop-down lists)

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.

 

4) This option is most popular: The user can select an item from a drop-down 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 drop-down 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.

Confirm with “OK” and your cell now has a drop-down lists.

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.


var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 );

script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Final comments

Exit mobile version