Categories
Device

How to restrict data input in excel with data validation

You can vet or validate data in Access desktop databases as you enter it by using validation rules. You can use the expression builder to help you format the rule correctly. Validation rules can be set in either table design or table datasheet view. There are three types of validation rules in Access:

1. Field Validation Rule You can use a field validation rule to specify a criterion that all valid field values must meet. You should not have to specify the current field as a part of the rule unless you are using the field in a function. Restrictions on types of characters to be entered in a field may be easier to do with an Input Mask. For example, a date field might have a validation rule that disallows values in the past.

Disallow date values in the past: >=Date()

Generally accepted email format: Is Null OR ((Like “*[email protected]?*.?*”) AND (Not Like “*[ ,;]*”))

Number less than or qual to five: =0

Restrict character length in string: Len([StringFieldName]) Record Validation Rule You can use a record validation rule to specify a condition that all valid records must satisfy. You can compare values across different fields using a record validation rule. For example, a record with two date fields might require that values of one field always precede values of the other field (e.g., StartDate is before EndDate).

Ensure the end date doesn’t come before the start date: [End Date]>=[Start Date]

Enter a required date that occurs no more than 30 days after the order date: [RequiredDate] Validation on a form You can use the Validation Rule property of a control on a form to specify a criterion that all values input to that control must meet. The Validation Rule control property works like a field validation rule. Typically, you use a form validation rule instead of a field validation rule if the rule was specific only to that form and not to the table no matter where it was used.

In this article

Overview

This article explains how to use validation rules and validation text in table fields and form controls. A validation rule is one way to restrict input in a table field or a control (such as a text box) on a form. Validation text lets you provide a message to help users who input data that is not valid.

When data is entered, Access checks to see whether the input breaks a validation rule – if so, the input is not accepted, and Access displays a message.

Access provides a number of ways to restrict input:

Data types Every table field has a data type that restricts what users can enter. For example, a Date/Time field accepts only dates and times, a Currency field accepts only monetary data, and so on.

Field properties Some field properties restrict data input. For example, the Field Size property of a field restricts input by limiting the amount of data.

You can also use the Validation Rule property to require specific values, and the Validation Text property to alert your users to any mistakes. For example, entering a rule such as >100 And Validation Rule property forces users to enter values between 100 and 1,000. A rule such as [EndDate]>=[StartDate] forces users to enter an ending date that occurs on or after a starting date. Entering text such as “Enter values between 100 and 1,000” or “Enter an ending date on or after the start date” in the Validation Text property tells users when they have made a mistake and how to fix the error.

Input masks You can use an input mask to validate data by forcing users to enter values in a specific way. For example, an input mask can force users to enter dates in a European format, such as 2007.04.14.

You can use these methods of validating data alone or in combination with each other. Data types are not optional, and provide the most basic type of data validation.

For more information about data types, field sizes, and input masks, see the article Introduction to data types and field properties.

Types of validation rules

You can create two basic types of validation rules:

Field validation rules Use a field validation rule to check the value that you enter in a field when you leave the field. For example, suppose you have a Date field, and you enter >=#01/01/2010# in the Validation Rule property of that field. Your rule now requires users to enter dates on or after January 1, 2010. If you enter a date earlier than 2010 and then try to place the focus on another field, Access prevents you from leaving the current field until you fix the problem.

Record validation rules Use a record validation rule to control when you can save a record (a row in a table). Unlike a field validation rule, a record validation rule refers to other fields in the same table. You create record validation rules when you need to check the values in one field against the values in another. For example, suppose your business requires you to ship products within 30 days and, if you don’t ship within that time, you must refund part of the purchase price to your customer. You can define a record validation rule such as [RequiredDate]

If the syntax for validation rules looks cryptic, see the section What you can put in a validation rule for an explanation of the syntax and some example validation rules.

Where you can use validation rules

You can define validation rules for table fields and for controls on forms. When you define rules for tables, those rules apply when you import data. To add validation rules to a table, you open the table and use commands on the Fields tab of the ribbon. To add validation rules to a form, you open the form in Layout view and add rules to the properties of individual controls.

The steps in the section Add a validation rule to a table explain how to add validation rules to table fields. The steps in the section Add a validation rule to a control on a form, later in this article, explain how to add rules to the properties in individual controls.

What you can put in a validation rule

Your validation rules can contain expressions — functions that return a single value. You can use an expression to perform calculations, manipulate characters, or test data. A validation rule expression tests data. For example, an expression can check for one of a series of values, such as “Tokyo” Or “Moscow” Or “Paris” Or “Helsinki”. Expressions can also perform mathematical operations. For example, the expression ([OrderDate] – [ShipDate]) calculates the number of days that elapsed between the time an order was placed and the time it shipped.

For more information about expressions, see the article Build an expression.

Add a validation rule to a table

You can add a field validation rule and/or a record validation rule. A field validation rule checks input to a field, and is applied when the focus leaves the field. A record validation rule checks input to one or more fields is applied when the focus leaves the record. Usually, a record validation rule compares the values of two or more fields.

Notes: The following field types do not support validation rules:

Michael Crider is a veteran technology journalist with a decade of experience. He spent five years writing for Android Police and his work has appeared on Digital Trends and Lifehacker. He’s covered industry events like the Consumer Electronics Show (CES) and Mobile World Congress in person. Read more.

How to restrict data input in excel with data validation

If you use Excel spreadsheets to collect data from other people, but find that they often fill your carefully-planned cells with the wrong kind of information, data validation can help.

This tool lets you restrict specific cells to only allow properly-formatted data. If someone enters anything that’s not supposed to be there—like “lunch at airport” instead of “$15.68” on an expense report—Excel rejects the input until they get it right. Think of it as a passive-aggressive way to make sure people don’t waste your time.

As an example, here’s the basic expense report worksheet for How-To Geek. Let’s say we want to make sure that people only enter numerical values that are formatted as currency (i.e., some digits, followed by a decimal point, followed by two more digits) into certain cells.

First, select all the cells you want to restrict.

How to restrict data input in excel with data validation

Switch over to the “Data” tab on the Ribbon, and then click the “Data Validation” button. If your window isn’t full size and you can’t see the labels, it’s the icon with two horizontal boxes, a green check mark, and a red crossed circle.

How to restrict data input in excel with data validation

In the Data Validation window, on the “Settings” tab, click the “Allow” dropdown menu. Here, you can set a specific type of input to allow for your selected cells. For our expense report, we’re going to insist that users put in a number with two decimal values, so we would select the “Decimal” option. You can also select other criteria, like making sure a cell contains text, a time or date, text of a specific length, or even your own custom validation.

How to restrict data input in excel with data validation

Whatever type of data you select on the “Allow” dropdown changes the options available to you on the rest of the “Settings” tab. Since we want a numerical value corresponding to currency, we’re setting the “Data” dropdown to the “between” setting. Then, we’re configuring a minimum value of 0.00 and a maximum value of 10000.00, which is way more than enough to cover our needs.

How to restrict data input in excel with data validation

To test it our, click “OK” to apply the validation settings, and then try putting in an improper value. For example, if we type “pancakes” for the Breakfast value instead of the cost of the meal, we’ll get an error message.

How to restrict data input in excel with data validation

While that does restrict people to entering only the correct type of data, it doesn’t give them any feedback on what type of data is required. So, let’s set that up, too.

Head back to the Data Validation window (Data > Data Validation on the Ribbon). You’ve got two options here (and you can use both of them if you want). You can use the “Input Message” tab to have a pop-up tool tip show people the type of data you want whenever they select a cell for which data validation is turned on. You can also use the “Error Alert” tab to customize the error they see when they enter the wrong type of data.

Let’s switch over to the “Input Message” tab first. Here, make sure the “Show input message when cell is selected” option is turned on. Then, give your input tooltip a title and some text. As you can see below, just clicking in one of the cells pops up the message letting people know what’s expected.

How to restrict data input in excel with data validation

On the “Error Alert” tab, you can customize the error message people see when they enter the wrong type of data. Make sure the “Show error alert after invalid data is entered” option is turned on. Pick a style for your error message from the “Style” dropdown. You can go with a Stop (the red circle with the X), Warning (yellow triangle with an exclamation point), or Information (blue circle with a lowercase “i”), depending on how strong you want the message to come across.

Type a title for your message, the text of the message itself, and then hit “OK” to finish up.

How to restrict data input in excel with data validation

Now, if someone tries to enter improper data, that error message is a little more helpful (or sarcastic, if you prefer).

It’s a bit of extra legwork setting up data validation, but it can save you a lot of time later on if you use spreadsheets for collecting data from other people. It’s even useful for preventing your own mistakes. And this is doubly true if you’ve set up formulas or any kind of automation tasks that rely on that data.

If you use Excel spreadsheets to collect data from other people, but find that they often fill your carefully planned cells with the wrong kind of information, data validation can help.

This tool allows you to restrict specific cells to allow only properly formatted data. If someone enters something that is not supposed to be there, such as “airport lunch” instead of “$ 15.68” on an expense report, Excel rejects the entry until they get it right. Think of it as a passive-aggressive way to make sure people don’t waste their time.

As an example, here is the basic expense report worksheet for How-To Geek. Let’s say we want to make sure that people only enter numeric values ​​formatted as currency (that is, a few digits, followed by a decimal point, followed by two more digits) into certain cells.

First, select all the cells that you want to restrict.

Switch to the “Data” tab on the ribbon and then click the “Data Validation” button. If your window isn’t full-size and you can’t see the labels, it’s the icon with two horizontal boxes, a green check mark, and a crossed red circle.

How to restrict data input in excel with data validation

In the Data Validation window, on the “Settings” tab, click the “Allow” drop-down menu. Here, you can set a specific type of input to allow selected cells. For our expense report, we are going to insist that users enter a number with two decimal values, so we would select the “Decimal” option. You can also select other criteria, such as making sure a cell contains text, a time or date, text of a specific length, or even your own custom validation.

How to restrict data input in excel with data validation

Regardless of the type of data you select in the “Allow” drop-down menu, it will change the options available in the rest of the “Settings” tab. Since we want a numerical value corresponding to the currency, we are configuring the “Data” drop-down menu in the “between” configuration. Then, we set a minimum value of 0.00 and a maximum value of 10000.00, which is much more than enough to cover our needs.

How to restrict data input in excel with data validation

To test it, click “OK” to apply the validation settings, and then try setting the wrong value. For example, if we write “pancakes” for the value of Breakfast instead of the cost of the meal, we will get an error message.

How to restrict data input in excel with data validation

While that restricts people to entering only the correct type of data, it doesn’t give them any feedback on what kind of data is required. So, let’s configure that as well.

Return to the Data Validation window (Data> Data Validation on the ribbon). You have two options here (and you can use both if you want). You can use the “Input Message” tab to have a tooltip pop-up show people the type of data you want each time they select a cell for which data validation is turned on. You can also use the “Error Alert” tab to customize the error they see when they enter the wrong data type.

Let’s first switch to the “Input Message” tab. Here, make sure the “Show input message when cell is selected” option is checked. Then give your input tooltip a title and some text. As you can see below, just clicking one of the cells brings up the message so people know what to expect.

How to restrict data input in excel with data validation

In the “Error Alert” tab, you can customize the error message that people see when they enter the wrong data type. Make sure the option “Show error alert after entering invalid data” is checked. Choose a style for your error message from the “Style” drop-down menu. It can go with a stop (the red circle with the X), a warning (a yellow triangle with an exclamation point), or an information (a blue circle with a lowercase “i”), depending on how hard you want it to be. get the message across.

Enter a title for your message, the text of the message itself, and then press “OK” to finish.

How to restrict data input in excel with data validation

Now if someone tries to enter the wrong data, that error message is a bit more useful (or sarcastic, if you prefer).

It’s a bit more field work to set up data validation, but it can save you a lot of time later if you use spreadsheets to collect data from other people. It is even useful to prevent your own mistakes. And this is doubly true if you have set up formulas or any kind of automation tasks that are based on that data.

If you just want the users type characters in a limited number in Excel cell, for example, only can type 7-10 number of characters in cells, how can you do? In this article, I am going to talk about limiting the number of digits by using Data Validation function in Excel.

If you just want users to type 7-digit or 10-digit string only in cells, do as these:

1. Select the cells you want to limit digits, click Data > Data Validations > Data Validation.

2. Under Settings tab, select Custom from Allow drop-down list, then type this formula =OR(LEN(A1)=7,LEN(A1)=10) into Formula textbox.

3. Click OK. Now the selected cells only can type 7-digit string or 10-digit string. Or it pops out a dialog to warn you.

Note: If you just want users to type number strings, use this formula =OR(AND(LEN(A1)=7,LEN(A1)=10),ISNUMBER(A1)).

If you only allow users typing 7-10 digits of strings in cells, you can do as these:

1. Select the cells you want to limit digits, click Data > Data Validations > Data Validation.

2. Under the Settings tab, select Text length from Allow drop-down list, choose between in Data drop-down list, in Minimum and Maximum textbox, type the number you use.

3. Click OK. Now the selected cells only can type strings in digits between 7 and 10.

Tip: if you want to pop a dialog out with specific warning to remind users, you can do as these:

In the Data Validation dialog, under Error Alert tab, type the reminder in the Error message textbox.

Then click OK, while users enter the invalidation data, the message pops out to remind users type right data.

Tabbed browsing & editing multiple Word documents/Excel workbooks as Firefox, Chrome, Internet Explore 10!

You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Word documents or Excel workbooks in one Word window or Excel window, and easily switch between them by clicking their tabs.
Click for free trial of Office Tab!

The more I work with customers, the more I realize that data quality is still a very big problem. Data Validation in Excel is an effective solution to this problem. Here is a short guide for you. Reading time 12 min.

How to restrict data input in excel with data validation

The Need

Data must be entered accurately. That is a universal need. Correcting mistakes at the time of typing the data is the easiest and fastest method. Remember that there are three types of data: Numbers, text and dates. Within text, you may want to restrict typing and provide a dropdown.

How to apply Data Validation in Excel

Select the range where data is to be entered. Go to Data tab – Data Validation and choose the desired validation. The most powerful is the last one – Custom.

How to restrict data input in excel with data validation

Default is any value. That is why Excel accepts any type of data in any cell. We have to make Excel behave more strictly by applying these validations. Whole number or decimal is for numbers. Date and time is also set the same way by giving the upper and/or lower limits.

Only the text length can be controlled. If you want to specify a predefined set of items to choose, use the List option. The list of valid options must be already created somewhere. Usually such sheets are hidden to prevent users from changing the base values.

Here is a detailed explanation on how to use each option.

Use Excel Table for list data

The list itself must be a formal Excel Table (Insert – Table). This way, any additions or deletions from the list are automatically reflected into the validation drop-downs.

How to apply Data Validation in Excel to multiple areas?

Simple Copy – Paste Format is enough. The formatting includes Data Validation settings as well. If you just want to paste validations, choose the appropriate option from the Paste Special dialog.

Remember to provide an input message

This is the commonest mistake I have observed. Why did we put the validation in the first place? To capture data accurately- right? Now, look at it from the point of view of the person entering the data. That poor thing has no clue what validation you have applied. Wrong entry will give an error message. Most often the error message is also the default one – which does not say what is the right value! What is the point? It only leads to more frustration.

That is why, when you add validation, there is an Input Message tab. Which allows you to type the correct values for the data. So here is the best practice. As soon as you add validation settings, change the input message immediately.

How to restrict data input in excel with data validation How to restrict data input in excel with data validation

Unfortunately, you have to manually type the message.

You cannot make data entry mandatory

This is another common question. No. Without programming, there is no way to make data entry into specific cells mandatory in Excel. Data Validation does not help. You can clear the Ignore Blank checkbox to make data entry more strict. It works only if you go to a cell and press ENTER. At that point, it will force you to enter correct data. However, if you never go to a cell and press Enter the validation is simply not triggered.

Applying validation AFTER data is entered

Data Validation in Excel can be applied post-facto. This is required when you already have the data but you want to check it quickly based upon various validations.

What happens if the data fails validation? Excel will not delete it, rather, it cannot delete it.

Then how do you know which data is valid and which one is invalid? Two methods available. First one – the older method – is to apply Validation Circles – something which your finance team would fall in love with!

NO need to select the data. These circles will highlight any data which is failing the applied validation across the sheet. These circles do not get printed. When you change the data as per the expected validation rule, the circle goes away.

How to restrict data input in excel with data validation How to restrict data input in excel with data validationHow to restrict data input in excel with data validation

Of course, this option needs to be selected manually. At this stage, you know what is invalid. But you don’t know what was the actual validation. If the sheet is protected, you cannot see the validation itself. If someone else has added these validations, you will not be able to see the validation condition.

But there is another simpler method. Just create a table around the data. Now you will see the famous Green error mark. This shows the expected validation range, without the need to go to the Data Validation dialog.

How to restrict data input in excel with data validationHow to restrict data input in excel with data validation

Best Practices for Data Validation in Excel

First of all, your data as well as lists for validation should be in tables.

Add useful and accurate input messages for all validations.

Remember to keep enough rows to accept data.

Select and unprotect the cells – Cell Format – Protection – Uncheck Locked

Protect the rest of the sheet

Do not hard code dates as far as possible. Use the Today() function to create validation rules. That way, the template will be valid for a longer period of time.

Disadvantages and the Solution

If multiple people are going to use the data entry template, you will need to receive their inputs and manually copy paste the entered data into a master sheet. That is a very cumbersome process.

Using a SharePoint list, this process can be very easily streamlined. We simply do not use Excel for data entry. We use SharePoint. See this video to understand how it is done.

philippospan

  • Home
  • YouTube Channel
  • Tips 4 MS Word
  • SystemPlus

Data Validation is a feature in Microsoft Excel that is used to control what a user can or cannot enter in a Cell of the Spreadsheet . It means that we can check the accuracy and quality of the Source Data before using it, importing it or otherwise processing the Data . We can use different types of Validation depending on what we want to accomplish. In this post, I have created something simple enough for everyone to understand what Data Validation is about.

How to restrict data input in excel with data validation

In the image below, I have created a Worksheet , that Column A contains the Soccer Clubs , Column B the Country of the Soccer Club . In Column J, I have created an If Function , that if the answer in Column L is correct the Message Correct will be displayed in the Cells J3, J4 and on. If the answer is Wrong , then the Message Wrong will be displayed in the Cell J3, J4 and on. Now in the Column L and specifically the cell L2 I want to enter my Validation Criteria . I want to create a Drop-Down List where someone can select from the List the Number of Total European Trophies , they think that each Club has one. The List with the numbers for my Criteria , I have already created and are in a different Column .

How to restrict data input in excel with data validation

We must select the Data tab and then from the right area of the ribbon we locate the area named Data Tools as shown in the image below.

Once we have located the Data Tools area of the ribbon, we select the drop-down menu of the command Data Validation. Here we can Pick From A List Of Rules To Limit The Type Of Data That Can Be Entered In A Cell. For Example, We Can Provide A List Of Values, Like, 1, 2, And 3, Or Only Allow Numbers Greater Than 1000 As Valid Entries.

How to restrict data input in excel with data validation

Once we select the Data Validation command, the Data Validation dialog box appears as shown below. The Data Validation dialog box, is separated in three tabs and which are the following:

How to restrict data input in excel with data validation

For my example I have selected from the drop-down menu the Validation Criteria as List. The Data: area is not available, and we also have the choice to activate or deactivate the check boxes next to the commands if we want to Ignore Blank , or to have or not the List to be displayed as In-Cell Drop-Down .

How to restrict data input in excel with data validation

Finally, we must select the Source from where we want the data in our List to be displayed.

In the image above, we notice the area in text pane of the Source Data is =$P$2:$P$80 . In order to select the area, we must press the up arrow at the right of the text pane. Once selected, our Spreadsheet will be activated and then we just select with our mouse the area with our Data . As you can see in the image below, Column P contains the Data needed for my Validation Rule .

How to restrict data input in excel with data validation

Keep in mind that if we do not activate the check box at the left of the command Show Input Message When Cell Is Selected , none of the rest of the options will be available.

How to restrict data input in excel with data validation

Keep in mind, that if we don’t activate the check box at the left of the Show Error Alert After Invalid Data is Entered , none of the rest none of the rest of the options will be available.

How to restrict data input in excel with data validation

Whatever adjustments we make, for them to take place we must press the Ok button located at the bottom right corner of the Data Validation dialog box. We can also use the Clear All button, which is located at the bottom left corner of the Data Validation dialog box, to Clear All the changes that we have made no matter at which Tab we are at the moment.

In the next image, the List is displayed, where we must select one the Numbers in the List (which is the Source for our Validation Criteria ). In the next image I have selected the Number 22 from the List , which is the correct answer of How Many European Trophies has Real Madrid from Spain won. Once I have selected the correct answer, in the Cell J2 I have created an If Function displaying that I have selected the right answer.

How to restrict data input in excel with data validation

In the next image, I have entered a number that was not in the Source List that I had created of How Many European Trophies Milan from Italy has won. Straight away the Error Alert dialog box appeared with the icon Style that I had chose and the Error Message that I had typed in.

How to restrict data input in excel with data validation

In the image below, we can see the rest of the Teams with the correct or wrong answers on How Many European Trophies they have won. The data in the Column P , is my Source List for my Validation Criteria .

How to restrict data input in excel with data validation

Below you can check out the video describing How To Use The Data Validation Feature in Microsoft Excel.

This tutorial will demonstrate how to apply data validation so that data entry is only allowed based on the value of another cell in Excel and Google Sheets.

When entering values in Excel, you can restrict the data that is entered into any cells in a range based on text or a value held in another cell.

Custom Formula for Data Validation

Since the data validation rule is based on another cell, you need to use Data Validation with a custom formula.

  1. First, highlight the cells to include in the data validation range.

How to restrict data input in excel with data validation

  1. In the Ribbon, select Data > Data Tools > Data Validation.

How to restrict data input in excel with data validation

  1. In the Allow drop-down box, select Custom, then remove the check from the Ignore blank check box. In the Formula box type:

How to restrict data input in excel with data validation

  1. Click OK.
    To test the validation, remove the word Allow from cell B2 and then try to type in one of the cells in the data validation range. An error will occur.

How to restrict data input in excel with data validation

  1. However, if you type the word Allow in B2, and then type in any of the cells included in the data validation range, can enter any value or text into that range.

How to restrict data input in excel with data validation

Data Validation Based on Another Cell in Google Sheets

Data Validation based on another cell in Google Sheets works much the same as it does in Excel.

  1. In Google Sheets, highlight the cells to included in the restricted range and then in the Menu, select Data > Data validation.

How to restrict data input in excel with data validation

The Cell range is already selected.

  1. For the Criteria, select Custom formula is from the drop-down list, and then type the formula:

How to restrict data input in excel with data validation

  1. Click the Reject Input button (so invalid data cannot be entered), and ensure that the Show validation help text check box is ticked. Click Save to save the data validation.
  2. Select any cell within the data validation range to type text into and test the validation. If you do not have the word Allow in cell B2 then and error will occur.

How to restrict data input in excel with data validation

  1. Type the word Allow into B2 to allow entry into the data validation range of cells.

by melih · March 2, 2014

You can setup a data validation list to limit entry to certain cells. But you will realize at some point that your data validation can be overridden by simple copy/paste. If you want your data validation to persist without coding macros, here is how to block data validation override.

There is a known solution for this issue, which is blocking copy/paste commands on workbook by use of VBA coding. But you need to be more than a bit Excel savvy to apply this solution. Also you might want copy/paste available or you just might not want a macro enabled workbook for a number of reasons.

My solution doesn’t actually blocks data validation override but renders values unusable in case of a copy/paste override attempt. So copy/paste is not an option for data validation override. Here is how to set it up:

We have a number of employees which we can assign tasks. Each employee has a certain capacity to spare so we can assign limited number of tasks to each one. Here is our employee capacity table:

Following is the picture of the list we are going to use to assign tasks to employees.

If you apply data validation list to column I using range B3:B9 as source, you can limit entries to our employee names. But this doesn’t cover employee capacity. So we need a more complex approach.

Setup another range (B16:B22) with following formulation:

This formula checks assigned tasks versus capacity and shows employee name if there is available capacity, results blank if capacity is filled. Now if you use this new range as source for your data validation list, you can restrict data entry with employee capacity.

But your data validation setup can still be overridden by simple copy/paste. If anybody wants to, they can copy “John” and paste it into your list 10 times. So all this setup can easily go to trash. To prevent this, we need to make an even more complicated setup (but nothing to be afraid of).

We are going to separate name input field from our task list. Then fetch inputted data into our task list with a conditional formula. And then we will lock our list’s name field for editing to ensure nothing can go wrong.

Above is a picture of our final setting. Column G has the same data validation setting above. It reads range B16:B22 for name values. Column I has following formula and is protected for editing. This If-And Combination Formula might seem a little scary to some but it is really not that complicated. I broke it down anyway. Formula of cell I3:

This formula simultaneously checks entered name against name list and whether it inside of available capacity. If either one of the conditions returns negative, it displays a custom error message. In this example, if you input John two times you will see John in the list (see the picture above). If you input John for a 3 rd time (you can do so only by copy/paste) you will get an error message on the list (see the picture below).

You can download and inspect Override Data Validation example workbook for further understanding of the concept.

Note: Sheet1 is protected. You can unprotect it from REVIEW Ribbon. It is not password protected.

LinkBack
  • LinkBack URL
  • About LinkBacks
  • Thread Tools
    • Show Printable Version
    • Subscribe to this Thread…
  • Display
    • Linear Mode
    • Switch to Hybrid Mode
    • Switch to Threaded Mode
  • Restrict cell input based on another cell value

    Hi everyone, this is my first post here. I consider myself a more advanced Excel user and can navigate my way around fairly well. I’ve written a spreadsheet and am wondering if there is a way to permit data entry in a cell, contingent upon another cell being populated.

    In simple terms, you can’t enter data in cell X, until cell “Y” has been populated with something.

    Can anyone help with this, because I can’t figure it out.

    Last edited by Falk781; 07-30-2009 at 04:15 PM .

    Re: Here’s a question.

    Yes, this can be done with a Data Validation trick.

    Let’s say it’s B2:B10 and you want to make sure the value in the adjacent cell in column A is there before entry is allowed.

    Select B2:B10
    Data > Data Validation
    Allow: Custom
    Formula: =AND(A2<>0,A2<>“”)
    OK

    _________________
    Microsoft MVP 2010 – Excel
    Visit: Jerry Beaucaire’s Excel Files & Macros

    If you’ve been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” – Ray Kroc
    “Actually, I *am* a rocket scientist.” – JB (little ones count!)

    Re: Here’s a question.

    Welcome to the forum, Falk.

    Please take few minutes to read the forum rules, and then amend your thread title accordingly.

    Re: Here’s a question.

    My first day on the job and I’m in trouble already. I’m sorry for that, I was just in a hurry for some help. I’ll be sure and read the requirements before posting again.

    The suggestion that was made has worked. I really appreciate you’re help with this.

    Re: Here’s a question.

    By chance, can you put more than one data validation in there? More specifically, we force them to enter data into another cell, but can we also restrict it to just numbers and not letters?

    Re: Restrict cell input based on another cell value

    Change the initial DV formula to:

    Re: Restrict cell input based on another cell value

    This is helpful. One more problem that I have is that you have two cells. One is dependant on the other being populated. Having said that, you populate one cell, then the second and everything is fine. If you go back to the one you needed to populate and delete the number, the second number you put in still, stays put. It won’t let you rewrite over it because of the DV which is good, but it still stays there.

    This negates the whole point of putting the number in the 1st cell. Is there something that can be written in there for this?+

    Re: Restrict cell input based on another cell value

    Perhaps a macro that deletes the contents in B2 when A2 is deleted?

    I’m trying to make data validation for phone numbers in EXCEL. Pattern should contain only digits and dashed. And at least one dash should be present. How can I acheve this without VBA?

    3 Answers 3

    You can do this with Data Validation.

    How To

      Go to Data Validation and set the Allow drop-down to “Custom”.

    Use the following in the Formula field (assuming the first cell in your range is A1 ):

    =ISNUMBER( SUBSTITUTE(A1,”-“,””) * 1)

    Explanation

    The SUBSTITUTE function looks at the value in cell A1 with every dash replaced with nothing (i.e. removed).

    The characters left are multiplied by 1. If the characters contain only digits (0-9) then this action returns a number. If there are letters or symbols other than dashes this returns an error.

    Finally, the ISNUMBER function returns TRUE if the result above is a number. It returns FALSE if the result is anything other than a number including an error.

    Allow Additional Characters

    To allow additional characters, include a series of nested SUBSTITUTE functions, one for each character.

    Allow Digits (numbers), Dashes & Spaces

    ex. 123-456-7890 or 123 456 7890

    Allow Digits (numbers), Dashes, Spaces, Parenthesis

    How to restrict data input in excel with data validation

    Would a Custom Number Format suffice? ###-###-####

    Limit the input to whole numbers.

    How to restrict data input in excel with data validation

    How to restrict data input in excel with data validation

    What you are asking is a bit more complex than what you need, and likely more complex than what you think.

    Instead of requiring users to enter phone numbers in such a specific format (What if they add parenthesis before their area code? What about a space between digit blocks? What about someone who enters 6 numbers with a dash? What about 20 numbers and 20 dashes?), consider just applying specific formatting rules against an entry of solely numbers. If you do that, your data validation would simply be “Must enter whole numbers” and the formatting will automatically show how it looks.

    There’s already a special “Phone Numbers” format, so you don’t even need to apply any special rules to make that work.

    We can control the type of data or the values that users can enter into a particular cell or range using Data Validation in Excel.

    In This Section:

    What is data validation and Its Use:

    Data Validation is a feature available in Excel to define restrictions and what data can enter in a cell or a range.
    For example,
    1. We can restrict data entry to a certain range of values
    2. User can select a choice form predefined list
    3. We can display a message to provide the instruction to the user
    4. We can display a message when user enter an incorrect value

    Creating a simple list to enter gender of a person – Practical Learning

    Step 1: Select a Range or Cells which you want to restrict or add data validation

    How to restrict data input in excel with data validation

    Step 2: Click on the Data Validation tool from the Data Tab

    How to restrict data input in excel with data validation

    Step 3: Set the Validation Criteria: Select List from the Allow drop-down list in the Setting Tabs

    How to restrict data input in excel with data validation

    Step 5: Enter the values to show in the Drop-down list

    How to restrict data input in excel with data validation

    You are done! Now you can check the range, your list is available to choose

    How to restrict data input in excel with data validation

    How to choose list items from a Worksheet

    It is a good practice to have your list of values in the worksheet and choose those values for drop-down list. It is particularly very useful when your data list is having more number of items or your list is changing frequently.

    Follow the below Steps to choose the list items from worksheet:

    1. Select the Range / Cells to restrict or add data validation.
    2. Click Data Validation Tool from Data menu
    3. Click List in the Allow drop-down list from the Settings tab
    4. Click Source button to select the list Items
    5. Select the Range to fill the drop-down

    How to restrict data input in excel with data validation

    How to set user instructions message

    You can provide the instructions to the user while entering the data. In the following example, we will see how to restrict the enter values between a range and provide the user instructions.

    Follow the below Steps to choose the list items from worksheet:

    1. Select the Range / Cells to restrict or add data validation.
    2. Click Data Validation Tool from Data menu
    3. Select Whole number from the Allow drop-down list in the Settings tab
    4. Select between number from the Data drop-down list in the Settings tab
    5. Enter Minimum and Maximum Values (example: 1 and 100)

    How to restrict data input in excel with data validation

    Goto Input Message Tab and Enter Required Title and Instructions in the Input Message Box (example: ‘Note:’ and ‘Please enter any value between 1 and 100’), then Click on OK

    How to restrict data input in excel with data validation

    You are done! Now you can select the cell, you can see the instructions

    How to restrict data input in excel with data validation

    How to Set user alert message

    You can provide the error message to the user while entering the incorrect data. In the following example, we will see how to provide an alert message to the user.

    Follow the below Steps to choose the list items from worksheet:

      The below first 5 steps are same as above

  • Select the Range / Cells to restrict or add data validation.
  • Click Data Validation Tool from Data menu
  • Select Whole number from the Allow drop-down list in the Settings tab
  • Select between number from the Data drop-down list in the Settings tab
  • Enter Minimum and Maximum Values (example: 1 and 100)
  • Goto Error Alert Tab and Enter Required Title and Instructions in the Error Message Box (example: ‘Note:’ and ‘You can only enter the values between 1 and 100’)

    How to restrict data input in excel with data validation

    You are done! Now you can select the cell, and try to enter a value which is not between 1 and 100

    How to restrict data input in excel with data validation

    Example File

    Download this example file and see different ways of using data validation features of Excel.

    How to restrict data input in excel with data validation

    Note: Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula if you need more control or flexibility.

    To allow a user to enter only dates that are weekdays (i.e. Monday, Tuesday, Wednesday, etc.) you can use data validation with a custom formula based on the WEEKDAY function.

    In the example shown, the data validation applied to C5:C7 is:

    Data validation rules are triggered when a user adds or changes a cell value.

    This custom validation formula uses the WEEKDAY function to get a numeric value, 1-7, corresponding to to a week beginning Monday (1) and ending Sunday (7). To get a number for a Monday-based week, the return_type argument for WEEKDAY is provided as 2.

    The WEEKDAY result is then compared to 6. Any value less than 6 is a weekday, so the expression returns TRUE and validation succeeds. If the weekday number is not less than 6, validation fails because the date is a Saturday or Sunday.

    Date is weekend

    To allow only dates that occur on a weekend (Saturday or Sunday), you can use a similar formula:

    Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case C5.

    by melih · Published May 7, 2013 · Updated April 9, 2017

    There is a way to restrict user input in excel. This can be achieved by Data Validation.

    Have you ever got frustrated by users inputting random stuff as variables and mess up your calculations ? Or do you want to give users a more convenient way of entering data, that doesn’t include typing? If so, data validation is the tool for you.

    With data validation, you can restrict data input for the cells you choose, you can display custom messages when certain cells are selected or you can display custom error messages in case of wrong data input into certain cells.

    Data validation button is located on Data Ribbon.

    Restricting Data Input:

    How to restrict data input in excel with data validation

    When you first open data validation, you will see Settings screen. From this tab you can restrict data input for selected cells. From this tab, you can use one of several options located in Allow list:

    Whole Number and Decimal : When you select either one of these options, you will be presented with a selection of conditions (between, grater than, etc.) for restricting data input. Depending on the condition you will need to input additional data (min/max values, etc.).

    For example if you set it as: Allow: Whole number, Data: greater than, Minimum: 50, users can input only whole numbers greater than 50.

    List: When you select this option, you are going to be asked to specify a source (range of cells) for your list. When you specify your source for the list, users will only be able to select values from that list as input. This is by far the most used option for data validation.

    For example you want to allow users to select fruits only are in your inventory. For that you need to set a data validation for fruit selection input cell like this:

    Allow:List, Source: =Inventory!A2:A11, in-cell drop down (checked).

    Now, when a user clicks on the fruit input cell, they will see an expansion arrow on the right side of that cell. When clicked, a drop down list that shows fruits located in A2:A11 range of Inventory sheet. They can not type anything else, can only select from list.

    Date and Time : Date and time options are very similar to whole number and decimal options. You set date or time instead of numbers. That is all the difference.

    Text Length: This option is also same as above. You need to specify numbers for text length condition you choose.

    Custom: When you select this option, you will be asked to input a formula as condition. Depending on the formula you entered, user input into selected cell will be restricted.

    displaying Input Message:

    You can also display messages when user select certain cells. For this, open Data Validation window and navigate to second tab. In this screen you can specify a titile and a message for displaying. When finished, click ok. Now users will be greeted with an input message when they select that cell. Message will look like a comment.

    Displaying Error Message:

    You can also display messages when user inputs erroneous data into certain cells. For this, open Data Validation window and navigate to third tab. In this screen you can specify a title and a message for displaying, plus an icon for your error message. When finished, click ok. Now if a user inputs a data that doesn’t match with your specifications, he/she will get your error message.

    Circle Invalid Data:

    You can also use data validation to point out invalid data in your cells. While you can restrict data entry to your cells, sometimes a macro can input invalid data or a formula in that cell can result an invalid value. To check your worksheet against such issues, you can use Circle Invalid Data tool which is located under Data Validation button (in drop down menu).

    When you click this button, excel will check your worksheet for invalid data and put a red circle around them if finds any. Circles will disappear either when you correct that data and make it valid or if you click Clear Validation Circles button just bellow Circle Invalid Data button.

    How to restrict data input in excel with data validation

    In Excel, you can use data validation to control (to some extent!) what users can enter in a cell. One option is to create a drop down list, so users can only select from a list of valid options.

    How to restrict data input in excel with data validation

    Data Validation Custom Criteria

    In some cases, a drop down list isn’t practical, but you still want some control over what goes into the cell. For example, in the screen shot below, we want to prevent users from typing a space character in the cell.

    To do this, you can use data validation with custom criteria. Thanks to Jerry Latham for sending this example, and the custom validation formula.
    Follow these steps to set up the data validation.

    • Select cell B3, where the item code, with no spaces, will be entered.
    • On the Excel Ribbon, click the Data tab, and click Data Validation
    • In the Data Validation dialog box, on the Settings tab, select Custom
    • In the formula box, type this formula, which refers to the active cell (B3), then click OK
      • =B3=SUBSTITUTE(B3,” “,””)

    How to restrict data input in excel with data validation

    How It Works

    The SUBSTITUTE function replaces each space character – ” ” – with an empty string – “”

    The value entered in cell B3 must be equal to the result of that SUBSTITUTE function. If there is a space in B3, the results won’t be equal, so the data validation test will fail.

    For more examples of Custom data validation, see the Data Validation Custom Criteria Examples page on the Contextures website.
    ________________

    11 thoughts on “Prevent Spaces in Excel Data Entry Cell”

    Would it be better to use =ISERR(FIND(” “,B3)) instead? The SUBSTITUTE method works fine for text, but it does not allow numbers. If someone were entering SKUs, for example, and she wanted to ensure no spaces in the SKU, she would need to have a more flexible data validation formula.

    I would just set the data entry cell as text if someone were to enter SKUs. Then the formula would work fine if someone were to enter numbers or letters.

    Is there any way that some can enter only numeric + characters both but not any special character.
    For Example:
    TEDSERVER01 –> Valid
    TEDSERVER$&01 –> Invalid

    Hello,
    This is very much help me .
    Thanks being helper.
    Subhankar Ganai

    how to prevent a user from entering space in the beginning in a cell.

    I want a user to enter a value from a list which includes January,February,March…
    The user has the habit of entering a space after a word.
    How can I ensure that they enter a value from the list and not include a space?
    In a macro I’ve had to use a workaround such as If Test =Trim(input) however it would be more convenient if the data validation rule worked.

    Jim, can you use a drop down list in the cell instead, so they can only select or type items that are in that list?
    http://www.contextures.com/xlDataVal01.html

    Debra, I was searching for a way to prevent users from entering spaces before and after entries and found this one of yours: http://www.contextures.com/xlDataVal07.html
    I tried several of the options (TRIM, SUBSTITUTE) but it only works with text, not numbers. So if I enter a number preceded or followed by a space, the data validation rules still allows it. However, if I format the range as text, it then will prohibit it. Why is that? Thanks,

    Michael, when you press Enter, Excel automatically strips out any leading or trailing spaces, if it sees the entry as a number.
    So, when the data validation rule checks the number, there aren’t any spaces, and the entry is valid.
    If you format the cell as Text, those spaces are NOT stripped out automatically, and the DV rule shows its error message.

    The quotes for the formula are confusing. It would be helpful to put the formulas in a code block or to use a different font that didn’t automatically add opening and closing quotes.

    Thanks Paul, and the curly quotes are turned off now.

    Leave a Reply Cancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sometimes when you are creating worksheets with data inputs, you may want to restrict the data input format (i.e. numbers, decimals, date, text etc.) and their range. This is very user friendly because it doesn’t allow you to input invalid data.

    For this select the cell and go to Data tab > Data Validation

    How to restrict data input in excel with data validation

    In the dialog box, there are three tabs Settings, Input Message and Error Alert change the settings as you per your requirement.

    Settings:

    I have entered Date as the valid data format. By enabling Ignore blank, the cell can be left blank. The date range can also be set, which I have set the valid date to be greater than 1/1/1930.

    How to restrict data input in excel with data validation

    Input Message:

    This is used to show an input message when the cell is selected. You have to first enable this option by marking the tick at the top of the dialog box. Then you can enter a Title and Input message. A sample is shown in the snap shot below.

    How to restrict data input in excel with data validation

    The preview will be displayed as follows:

    How to restrict data input in excel with data validation

    Error Alert:

    This option is used to show error alert after invalid data is entered. You can type the message title and error message in the given spaces.

    How to restrict data input in excel with data validation

    If you enter invalid data you will get an error message and the error message is shown below.

    How to restrict data input in excel with data validation

    Note:

    To remove data validation from a cell, select the cell > go to Data tab, click Data Validation, and then click Clear All.

    How to restrict data input in excel with data validation

    • You also can use Go To Special tool to quickly select all cells with data validation.

    This Excel tutorial explains how to set up data validation to only allow positive numbers to be entered in the cell in Excel 2010 (with screenshots and step-by-step instructions).

    See solution in other versions of Excel :

    Question: In Microsoft Excel 2010, I want to set up a cell to only allow positive numbers. How can I do this?

    Answer: Select the cells that you wish to restrict to a positive numbers. In this example, we’ve selected cells A2 to A6.

    Select the Data tab in the toolbar at the top of the screen. Then in the Data Tools group, click on the Data Validation drop-down and select Data Validation.

    How to restrict data input in excel with data validation

    When the Data Validation window appears, set up your criteria. In this example, we’ve setup the cells to allow a positive number.

    Next, click on the Error Alert tab.

    How to restrict data input in excel with data validation

    Enter an Error message to appear when data entered does not conform to the validation rules.

    How to restrict data input in excel with data validation

    Now if a value is entered in one of those cells is not a positive number, the following error message will appear:

    In this tutorial we are going to look at five different ways to populate a Data Validation Dropdown List in Excel.

    You can download the file here and follow along. When you get a preview, look for Download in the upper right hand corner.

    Option 1 – Manual Entry:

    You can physically type in the values you want in the list. Just insert a comma between them, no need for spaces. Just go to the Data tab of the Ribbon, click on Data Validation, in the “Allow” drop down, choose “List”. Then in the “Source” box, type your entries and click OK. This is best for a short list like Yes,No, or True,False, where you don’t have to type too many options.

    How to restrict data input in excel with data validation

    Option 2 – Select Range

    If you have the list of values already typed into a worksheet in your workbook, go to the Data tab of the Ribbon, click on Data Validation, in the “Allow” drop down, choose “List”. Then in the “Source” box, click on the up arrow at the right. It will minimize the Data Validation box, then you can highlight the range (in this case D2:D13). Now, click on the down arrow at the right, click OK, and your list is created.

    How to restrict data input in excel with data validation

    Option 3 – Named Range:

    As with Option 2, if you have the list of values already typed into a worksheet in your workbook, just name that range (no spaces or special characters allowed). In the example below, I highlighted F2:F13 and in the Name Box in the upper left corner, typed in “Months”. Now that range is named “Months”. Then in the Source box in Data Validation, just type =Months and your list is complete.

    How to restrict data input in excel with data validation

    (Options 4 and 5 assume that the list is part of or has been converted to a table. In these examples, the table name is “AllMonths” and the column header for the list I want to use for my Data Validation dropdown list is “Months”.)

    Option 4 – Use a Table:

    If your list is in a table, in the “Source” box of Data Validation, use the INDIRECT function to name the table and column header. They must be in double quotes, and the column header in square brackets, like this:

    =INDIRECT(“AllMonths[Months]”)

    How to restrict data input in excel with data validation

    Option 5 – Named Formula:

    Again, assuming the same table name, go to the Formulas tab of the Ribbon and click on “Name Manager”. Click “New…” and fill in the box as follows:

    How to restrict data input in excel with data validation

    Here I called the named formula “MonthTable”, and in “Refers to” typed =AllMonths[Months]. This is similar to Option 4, but I didn’t need to include the INDIRECT function.

    Now in the “Source” box of Data Validation, just type =MonthTable

    How to restrict data input in excel with data validation

    What can you do next?
    Share this post with others that can benefit from it!
    Leave a comment or reply below – let me know what you think!
    Subscribe to this blog for more great tips in the future, or…
    Check out my YouTube channel!

    Schedule a Demo with Trifacta

    Learn how to use Trifacta as an alternative to Excel data validation.

    What Is Excel Data Validation?

    Excel data validation is a feature that allows you to control the type of data entered into your worksheet. For example, Excel data validation allows you to limit data entries to a selection from a dropdown list and to restrict certain data entries, such as dates or numbers outside of a predetermined range. Data validation can also help you control formulas and the input from those formulas. You can even craft custom Excel data validation messages that help guide users toward the right data entry when they hit a limit. As a result, Excel data validation helps reduce the amount of unstandardized data, errors, or irrelevant information in your worksheet.

    It’s a helpful feature, especially when widely sharing an Excel worksheet with others for completion. Many data analysts find data validation in Excel to be beneficial when they are working with many users or with strict guidelines in data entry. In addition, data validation in Excel can help save analysts valuable resources that are spent when the data isn’t input correctly. Overall, data validation in Excel is a beneficial feature, but even beneficial features have limitations that can impede its ability to help users.

    How to restrict data input in excel with data validation

    The Limitations of Excel Data Validation

    Using Excel data validation to set certain restrictions in your worksheet can help guide users toward more accurate data entries, but ultimately, these restrictions can easily be bypassed. If a user copies data from a cell without Excel data validation to a cell with Excel data validation, the validation is destroyed (or replaced). So despite using data validation in Excel to control input, analysts can still be left with messy data sets because of this way to bypass any restrictions. In addition, if the validation is destroyed or replaced, it could affect how future data entries are input and generate future analysis complications.

    What’s more, having the ability to control data collection is a rare occurrence for most business users; more often than not, data is collected from various systems or third-party sources without being able to control how that data is entered and managed. Instead, you’re simply given messy spreadsheets and must make do with them as best you can. This situation is common for many modern businesses, and it means that the analysts don’t have any control over how data is input. When analysts are handed a data set, they aren’t able to use data validation in Excel at all. But they still want to be able to clean and control the data in some form in order to use analysis tools properly.

    In both cases, when Excel data validation breaks down or when there’s not an opportunity to use it in the first place, validating data within any given Excel spreadsheet becomes much more difficult. Identifying unstandardized data requires tedious searching and replacing. Spotting errors in rows and rows of data can feel like finding a needle in a haystack. And overall, the process adds precious time to the data preparation process , which is already widely understood as the most time-consuming part of any analytics project.

    In instances where users can’t rely on Excel data validation and need to quickly prepare faulty data, many are turning toward intelligent data preparation tools . Intelligent data preparation platforms allow users to more quickly validate their data and prepare it for use in data modeling or visualization projects with great assurance of its accuracy. Data preparation tools are necessary for data sets that use data validation in Excel and can also be a better alternative as well.

    How to restrict data input in excel with data validation

    The Need for Data Preparation

    Certainly, there will continue to be many instances where Excel data validation is a reliable and helpful feature. However, as data grows beyond the realm of our control, and Excel data validation becomes less feasible, having other resources in mind to prepare messy data is critical. The key is to use data preparation tools. Data preparation can reduce the amount of time spent analyzing and mitigate the complications that could come from using data validation in Excel . Data validation Excel tools can be extremely valuable, but analysts are better able to benefit during the analysis process from these preparation tools over data validation in Excel . Even when data validation in Excel is possible and beneficial, it can still be necessary to prepare the data properly to eliminate anything that went wrong during the validation phase. For example, data preparation is necessary if a user was able to override the data validation in Excel .

    Data preparation tools focus on taking the raw data and making it usable for data analytics. This process can involve removing null values, standardizing data entries, or eliminating duplicates. While data validation in Excel can help with some of these issues while the data is being entered, the tools can be overridden. That’s why data preparation tools are crucial with large and growing data sets. Preparing the data will save analysts time and make tools like data validation in Excel more beneficial.

    How to restrict data input in excel with data validation

    Excel Data Validation Meets Trifacta

    When it comes to data preparation tools, Trifacta is widely recognized as the leader in data preparation. Its visually-driven platform not only allows users to easily spot data quality issues, but also allows anyone in the organization to speak the same language, which improves transparency about how particular data sets have been transformed. The Trifacta Wrangler is a data preparation tool that can save analysts time and extra effort. The Wrangler works well with data validation in Excel to bring these two time saving processes together. In addition, the Wrangler can be used in place of data validation in Excel for those data sets that are overwhelming and detailed.

    We’d love to chat with you about how you could use Trifacta as an alternative to Excel data validation when applicable or how to incorporate our data preparation tool with data validation in Excel . Schedule a demo of Trifacta today.

    irfananeeza

    Active Member
    • Sep 18, 2014
  • #1
  • How can I use data validation in excel 2010 to restrict user to input number with 2 decimal places only?

    Excel Facts

    par60056

    Well-known Member
    • Sep 18, 2014
  • #2
  • How are they inputing the value? In a cell, an inputbox in a macro?

    Without more information, all I can suggest is in a general way, test to see it value=floor(value,.01)

    TerryHogarth21

    Board Regular
    • Sep 18, 2014
  • #3
  • How can I use data validation in excel 2010 to restrict user to input number with 2 decimal places only?

    How to restrict data input in excel with data validation

    Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

    Forum statistics

    Share this page

    • Forums
    • Question Forums
    • Excel Questions

    We’ve detected that you are using an adblocker.

    Which adblocker are you using?

    Disable AdBlock

    Follow these easy steps to disable AdBlock

    1)Click on the icon in the browser’s toolbar.
    2)Click on the icon in the browser’s toolbar.
    2)Click on the “Pause on this site” option.

    How to restrict data input in excel with data validation

    Disable AdBlock Plus

    Follow these easy steps to disable AdBlock Plus

    1)Click on the icon in the browser’s toolbar.
    2)Click on the toggle to disable it for “mrexcel.com”.

    How to restrict data input in excel with data validation

    Disable uBlock Origin

    Follow these easy steps to disable uBlock Origin

    1)Click on the icon in the browser’s toolbar.
    2)Click on the “Power” button.
    3)Click on the “Refresh” button.

    How to restrict data input in excel with data validation

    Disable uBlock

    Follow these easy steps to disable uBlock

    1)Click on the icon in the browser’s toolbar.
    2)Click on the “Power” button.
    3)Click on the “Refresh” button.

    Using excel, you can restrict the type of data that a user can enter. This can be useful when you want a user to enter only whole numbers, decimal, date, time, or a list of exceptable values etc. This is achievable by using excel built in data validation.

    1. In excel, select a cell and go to Data -> Validation.

    2. In the settings tab of the data validation window, you will see that the default validation criteria is to allow any value.

    3. Choose the data type that you would want to restrict a user to play nice with.

    4. Take for example you would allow users to insert only whole numbers within the range 1 to 100.
    microsoft-excel-data-validation

    Allow only whole numbers from 1 to 100

    ” data-medium-file=”https://www.davidtan.org/wp-content/uploads/2008/04/microsoft-excel-data-validation-300×217.jpg” data-large-file=”https://www.davidtan.org/wp-content/uploads/2008/04/microsoft-excel-data-validation.jpg” src=”https://www.davidtan.org/wp-content/uploads/2008/04/microsoft-excel-data-validation-300×217.jpg” alt=”Allow only whole numbers from 1 to 100″ title=”microsoft-excel-data-validation” width=”300″ height=”217″ srcset=”https://www.davidtan.org/wp-content/uploads/2008/04/microsoft-excel-data-validation-300×217.jpg 300w, https://www.davidtan.org/wp-content/uploads/2008/04/microsoft-excel-data-validation-150×108.jpg 150w, https://www.davidtan.org/wp-content/uploads/2008/04/microsoft-excel-data-validation.jpg 431w” sizes=”(max-width: 300px) 100vw, 300px” />

    5. Press OK. The next time a user enter something stupid into the cell, excel will pop up a warning message.
    microsoft-excel-data-validation-2

    How to restrict data input in excel with data validation

    The drop-down list feature in Microsoft Excel makes data entry easy, consistent and standardized. Let’s review how it works and how to enable it.

    Data Validation allows you to do things that involve complicated custom settings like prompting users to choose an option from a list or restricting cells to certain data types. It’s beneficial when you need to keep your Microsoft Excel data consistent and standardized.

    Today I’ll show you how to create a drop-down list in a cell using Data Validation. Although I’m taking the screenshots using Excel 2016, the steps are the same if you’re using older versions. The steps are also the same in modern versions of Excel in the Microsoft 365 (formerly O365) suite.

    Create a Data Table and the List of Options

    First, set up a basic data table. You can either type your data in manually or copy and paste it from another source.

    Next, we’re going to enter a list of options for the drop-down list. You can do that when you define the data validation or define a list in another location on the same worksheet or another worksheet.

    For this example, we’re going to list the options for the drop-down list on another worksheet so click one of the worksheet tabs at the bottom of the Excel window.

    How to restrict data input in excel with data validation

    Enter each option in a column (or row), one option to a cell. Then, go back to the worksheet with your data.

    How to restrict data input in excel with data validation

    Turn on Data Validation for Selected Cells

    For this example, we want to add drop-down lists to the Rating column or column B. Select the cells you want to add the drop-down lists to. In our case, we selected B2 through B10.

    Then, click the Data tab.

    How to restrict data input in excel with data validation

    In the Data Tools section, click the Data Validation button.

    How to restrict data input in excel with data validation

    Add a Drop-Down List to the Selected Cells

    The Data Validation dialog box displays. On the Settings tab, you can have Excel restrict entries in the selected cells to dates, numbers, decimals, times, or a certain length.

    For our example, select List from the Allow drop-down list to create a drop-down list in each of the selected cells.

    How to restrict data input in excel with data validation

    Select the Source for the Drop-Down List Options

    Now, we need to specify the source for the options in each drop-down list. There are two ways you can do this. The first method involves manually typing the options in the Source box separated by commas. This can be time-consuming if you have a long list of items.

    Earlier in this article, we created a list of items on a separate worksheet. We’re now going to use that list to populate the drop-down list in each of the selected cells. This second method is easy to manage. You can also hide the worksheet containing the options (right-click on the worksheet tab and select Hide) when you distribute the workbook to your users.

    To add the list from the separate worksheet to your drop-down list, click the up arrow on the right side of the Source box.

    The Data Validation dialog box shrinks down to just the Source box, and you can access your workbook under the dialog box. Click the tab for the worksheet containing the drop-down list options.

    How to restrict data input in excel with data validation

    Next, select the cells containing the options. The worksheet name and the cell range with the options are added to the Source box on the Data Validation dialog box.

    Click the down arrow on the right side of the Source box to accept the input and expand the dialog box.

    How to restrict data input in excel with data validation

    Add an Input Message

    You can add an optional message to the drop-down list. Maybe you want to display a help message or tip. It’s a good idea to keep the message short.

    To add a message that displays when a cell containing the drop-down list is selected, click the Input Message tab. Next, enter a Title and the Input message in the boxes.

    How to restrict data input in excel with data validation

    Add an Error Alert

    Another option item on the drop-down list is an error message, which would display when a user tried to enter data that doesn’t match the validation settings. In our example, the error message displays when someone types an option into the cell that doesn’t match any of the preset options.

    To add an error message, click the Error Alert tab. The default option for the Style of the error alert is Stop. You can also select Warning or Information. For this example, accept the default option of Stop in the Style drop-down list.

    Enter the Title and Error message for the Error Alert. It’s best to keep the error message short and informational. Click OK.

    How to restrict data input in excel with data validation

    Use Your New Drop-Down List

    Now, when you click on a cell to which you added a data validation list, a drop-down list will display, and you can select an option.

    How to restrict data input in excel with data validation

    If you added an Input Message to the drop-down list, it displays when you select a cell containing the drop-down list.

    How to restrict data input in excel with data validation

    If you try to enter an option that doesn’t match any of the preset options, the Error Alert you set up displays on a dialog box.

    How to restrict data input in excel with data validation

    How have you made use of drop-down lists in Excel? Let us know in the comments. Also, check out our other Microsoft Office tips and our tutorial for creating drop-downs in Google Sheets.

    Suppose we have some data for an employee list that will continually be updated and we want to make sure that any user adding to the list doesn’t add a duplicate entry. In this post we’ll learn how to use data validation to prevent a user from entering a value if it’s already been entered in the table.

    How to restrict data input in excel with data validation

    How to restrict data input in excel with data validation

    In our example we have a simple set of data that contains a field for employee ID, name and address and we will prevent any duplicated employee ID’s being entered. Our data has also been turned into a table and the table has been named EmployeeData. If you want to read how to create a table in Excel, please read this Introduction To Excel Tables .

    How to restrict data input in excel with data validation

    Add a custom validation criteria. This will allow us to write a formula to test whether the data is valid or not. In this case we will enter a formula to count how many times the value appears in the list and limit it to 1.

    1. Select the full column of data which we’re going to limit to unique values.
    2. Go to the Data tab.
    3. Press the Data Validation button.
    4. Select Data Validation from the drop down menu.
    5. Select the Settings tab.
    6. In the Allow drop down select Custom.
    7. In the Formula area input this formula.

    How to restrict data input in excel with data validation

    How to restrict data input in excel with data validation

    How to restrict data input in excel with data validation

    We can also add a warning message to users that will display when the active cell cursor is on a cell with this data validation.

    1. Go to the Input Message tab.
    2. Enter a Title for the input message.
    3. Enter a descriptive message that will warn the user about the data validation for unique values.
    4. When the active cell is in the range with this unique value data validation, the input message will display.

    How to restrict data input in excel with data validation

    We can also add a error message that will display if the user does try to input a duplicate value.

    1. Go to the Error Alert tab.
    2. Select the style of pop up message, either Stop, Warning or Information.
    3. Enter a Title for the pop up message.
    4. Enter a descriptive message that will warn the user about their error.
    5. Press the OK button.
    6. Now if you try to enter a duplicate value, a pop up message box will alert you about your error of entering a duplicate value.

    Writing content on the text editors requires some level of validity. You need to ensure your content or what you are writing is correct and of good or desired quality. In excel sheets, we record data from different fields of study, we also need to get the validity of that data.

    To validate is to prove validity which means correctness, genuine, and authentic. We do data validation in excel sheets depending on some conditions. In the case where the condition is met, then the data record is considered valid.

    We have several conditions that are used to validate data, these conditions may include; numbers only, texts only, specific characters only, and many more conditions. Not all conditions are used to validate data in an excel sheet, only one or two conditions can be used.

    To correctly validate data using given conditions, we have several steps to adhere to from the first to the last. These steps include the ones that are discussed below in detail.

    Step 1

    Table of Contents

    Open a blank excel sheet from your laptop or computer and insert some data as in the case below.

    How to restrict data input in excel with data validation

    Step 2

    From the above excel sheet, you can validate the data based on all conditions you would like. The procedure is the same only that you will need to input a different formula. The formula for validation under the conditions numbers only is; ISNUMBER (the cell you wish to validate).

    Also, the formula to use when validating texts is; IS TEXT (the cell you wish to validate.) To do this, select or highlight the cells, columns, or rows you want to validate. From the menu bar, click on the data button. Under the data, button search and click on the data validation command.

    How to restrict data input in excel with data validation

    The following window of options will appear.

    How to restrict data input in excel with data validation

    In the text box under the settings tab below allow, choose custom and in the textbox below formula type the formula; ISNUMBER (C2). Click the ok button to make the changes.

    How to restrict data input in excel with data validation

    Cell C2 has been validated on condition number only so if you try to write anything apart from a number, it will display an error box.

    What is Data Validation?

    Data validation refers to the process of ensuring the accuracy and quality of data. It is implemented by building several checks into a system or report to ensure the logical consistency of input and stored data.

    How to restrict data input in excel with data validation

    In automated systems, data is entered with minimal or no human supervision. Therefore, it is necessary to ensure that the data that enters the system is correct and meets the desired quality standards. The data will be of little use if it is not entered properly and can create bigger downstream reporting issues. Unstructured data, even if entered correctly, will incur related costs for cleaning, transforming, and storage.

    Types of Data Validation

    There are many types of data validation. Most data validation procedures will perform one or more of these checks to ensure that the data is correct before storing it in the database. Common types of data validation checks include:

    1. Data Type Check

    A data type check confirms that the data entered has the correct data type. For example, a field might only accept numeric data. If this is the case, then any data containing other characters such as letters or special symbols should be rejected by the system.

    2. Code Check

    A code check ensures that a field is selected from a valid list of values or follows certain formatting rules. For example, it is easier to verify that a postal code is valid by checking it against a list of valid codes. The same concept can be applied to other items such as country codes and NAICS industry codes North American Industry Classification System (NAICS) The North American Industry Classification System (NAICS) is a hierarchical coding system that groups businesses into industries. .

    3. Range Check

    A range check will verify whether input data falls within a predefined range. For example, latitude and longitude are commonly used in geographic data. A latitude value should be between -90 and 90, while a longitude value must be between -180 and 180. Any values out of this range are invalid.

    4. Format Check

    Many data types follow a certain predefined format. A common use case is date columns that are stored in a fixed format like “YYYY-MM-DD” or “DD-MM-YYYY.” A data validation procedure that ensures dates are in the proper format helps maintain consistency across data and through time.

    5. Consistency Check

    A consistency check is a type of logical check that confirms the data’s been entered in a logically consistent way. An example is checking if the delivery date is after the shipping date for a parcel.

    6. Uniqueness Check

    Some data like IDs or e-mail addresses are unique by nature. A database should likely have unique entries on these fields. A uniqueness check ensures that an item is not entered multiple times into a database.

    Practical Example

    Consider the example of a retailer that collects data on its stores but fails to create a proper check on the postal code. The oversight could make it difficult to leverage the data for information and business intelligence Business Intelligence (BI) Business Intelligence (BI) is the practice of turning data into actionable insights. The actionable insights enable business leaders to take specific action . Several problems can occur if the postal code is not entered or entered improperly.

    It can be difficult to define the location of the store in some mapping software. A store postal code will also help generate insights about the neighborhood where the store is located. Without a data check on the postal code, it is more likely to lose the value of data. It will result in further costs if the data needs to be recollected or the postal code needs to be manually entered.

    A simple solution to the problem would be to put a check in place that ensures a valid postal code is entered. The solution could be a dropdown menu or an auto-complete form that allows the user to choose the postal code from a list of valid codes. Such a type of data validation is called a code validation or code check.

    Data Validation in Excel

    The following example is an introduction to data validation in Excel. The data validation button under the data tab provides the user with different types of data validation checks based on the data type in the cell. It also allows the user to define custom validation checks using Excel formulas Basic Excel Formulas Mastering basic Excel formulas is critical for beginners to become proficient in financial analysis. Microsoft Excel is considered the industry standard piece of software in data analysis. Microsoft’s spreadsheet program also happens to be one of the most preferred software by investment bankers . The data validation can be found in the Data Tools section of the Data tab in the ribbon of Excel:

    How to restrict data input in excel with data validationFig. 1: Data validation tool in Excel

    Data Entry Task

    The example below illustrates a case of data entry, where the province must be entered for every store location. Since stores are only located in certain provinces, any incorrect entry should be caught.

    It is accomplished in Excel using a two-fold data validation. First, the relevant provinces are incorporated into a drop-down menu that allows the user to select from a list of valid provinces.

    How to restrict data input in excel with data validationFig. 2: First level of data validation

    Second, if the user inputs a wrong province by mistake, such as “NY” instead of “NS,” the system warns the user of the incorrect input.

    How to restrict data input in excel with data validationFig. 3: Second level of data validation

    Further, if the user ignores the warning, an analysis can be conducted using the data validation feature in Excel that identifies incorrect inputs.

    How to restrict data input in excel with data validationFig. 4: Final level of data validation

    Additional Resources

    Thank you for reading CFI’s guide to Data Validation. In order to help you become a world-class \analyst and advance your career to your fullest potential, these additional resources will be very helpful:

    • Free Data Science Course Data Science Fundamentals Enroll in this Data Science Fundamentals course by CFI and advance your career. Students get an overview of how data science is used in business and finance.
    • Big Data in Finance Big Data in Finance Big data in finance refers to large, diverse (structured and unstructured) and complex sets of data that help provide solutions to long-standing business challenges
    • Data Tables Data Tables Data tables are used in Excel to display a range of outputs given a range of different inputs. They are commonly used in financial modeling and analysis to assess a range of different possibilities for a company, given uncertainty about what will happen in the future.
    • Business Intelligence vs. Data Science Business Intelligence vs. Data Science Business Intelligence (BI) and data science are both data-focused processes, but there are some key differences between the two.
    • Data Warehouse Data Warehouse A data warehouse (often abbreviated as DW or DWH) is a central data repository used for reporting and data analysis.

    This post describes the data validation feature in Microsoft Excel and how to imply various data validations into your worksheet.

    Data Validation in Microsoft Excel

    The Data Validation in Excel allows us to restrict the type of data entered into a cell in the worksheet. It prompts the user to enter valid data and displays errors for invalid data. Depending on the requirements, different data validations can be performed.

    Select the cells to which the validation is to be applied.

    Select Data tab -> Data Tools-> Data validation.

    A dialog box appears, under Allow, select an option based on your need.

    How to restrict data input in excel with data validation

    • Whole Number – allows only whole numbers.
    • Decimal – allows only decimal numbers.
    • List – pick data from the drop down list.
    • Date – allows only date.
    • Time – allows only time.
    • Text Length – restricts the length of the text.
    • Custom – custom formula.

    Under Data, select a condition:

    • between
    • not between
    • equal to
    • not equal to
    • greater than
    • less than
    • greater than or equal to
    • less than or equal to

    Click on Ignore blank checkbox if you want to ignore blank spaces.

    Based on what you choose for options Allow and Data. Set the other required values. For example, if you select decimal in allow and between in data, then give the minimum and maximum values for the cells.

    Then move to Input message, and set a title and message that is to be displayed when the user hovers over the selected cells. This tells the user how the input must be.

    How to restrict data input in excel with data validation

    How to restrict data input in excel with data validation

    Now when a user enters invalid data into the selected cell then an error message displays.

    Data Validation allows you to do things that involve complicated custom settings like prompting users to choose an option from a list or restricting cells to certain data types. It’s extremely useful when you need to keep your Microsoft Excel data consistent and standardized.

    These instructions work in Excel 2016, as well as older versions.

    Create a Data Table and the List of Options

    First set up a basic data table. You can either type your data in manually or copy and paste it from another source.

    Next enter a list of options for the drop-down list. You can do this when you define the data validation, or you can define a list in another location (either on the same worksheet or on another worksheet).

    If you want to list the options for the drop-down list on another worksheet, click one of the worksheet tabs at the bottom of the Excel window.

    Enter each option in a column (or row), one option to a cell. Then go back to the worksheet that contains your data.

    Turn on Data Validation for Selected Cells

    Now you will add drop-down lists to the Rating column, or column B. Select the cells to which you want to add the drop-down lists. In this case select B2 through B10.

    Click the Data tab.

    In the Data Tools section, click the Data Validation button.

    Add a Drop-Down List to the Selected Cells

    Within the Data Validation dialog box, open the Settings tab. You can force Excel to restrict entries in the selected cells to dates, numbers, decimals, times, or a certain length.

    You might select List from the Allow field to create a drop-down list in each of the selected cells.

    Select the Source for the Drop-Down List Options

    You need to specify the source for the options in each drop-down list. There are two ways you can do this.

    The first method involves manually typing the options in the Source box separated by commas. This can be time consuming if you have a long list of items.

    The second method is faster because you’ve already created a list of items on a separate worksheet. You’re going to use this list to populate the drop-down in each of the selected cells. You can also hide the worksheet containing the options (right-click on the worksheet tab and select Hide).

    To add the options to your drop-down list from a separate worksheet, click the up arrow on the right side of the Source box.

    The Data Validation dialog will shrink down to just the Source box. You can access your workbook underneath the dialog box. Click the tab for the worksheet containing the drop-down list options.

    Select the cells that contain your options. The worksheet name and the cell range will be added to the Source field in the Data Validation dialog box.

    Click the down arrow on the right side of the Source box to accept the input and expand the dialog box.

    Add an Input Message

    You can add an optional message to the drop-down list. Maybe you want to display a tip or help message. It’s a good idea to keep the message short.

    To add a message that displays when a cell containing the drop-down list is selected, click the Input Message tab. Then enter a Title and Input Message.

    Add an Error Alert

    You could also add an error message, which would display when a user tries to enter data that doesn’t match the validation settings. The error message would appear if someone manually typed an option (that doesn’t match any of the presets) into the cell.

    To add an error message click the Error Alert tab. The default option for the Style of the alert is Stop. You can also select Warning or Information.

    Enter the Title and Error Message for the Error Alert. It’s best to keep the error message short and informational. Click OK.

    Use Your New Drop-Down List

    Now when you click on a cell that contains your data validation list, a drop-down will display and you can select an option.

    If you added an Input Message, it will display when you select a cell containing the drop-down list.

    If you try to enter an option that doesn’t match any of the presets, you will see an Error Alert.

    This Access tutorial explains how to validate data type and data value in Access Table using Data Validation Property.

    Access Table Data Validation

    When you create a Table, you can control the data type of the Field in Design View of the Table.

    Assume that you have created a table of the following Fields.

    Field Name Data Type
    Department Long Text
    Employee ID Number
    Salary Number
    Employment Date Date/Time

    When you type a Text in Salary, you will be prompted an error, and you cannot save the record. You will not be able to save the value even if you are using Query to input the data.

    How to restrict data input in excel with data validation

    This kind of validation is for Data Type only, so what about the value of the data?

    Switch to Design View of the Table.

    Select Salary, and change the Validation Rule to >0 (meaning user must input a value in Salary >0)

    How to restrict data input in excel with data validation

    Switch to DataSheet View of the Table. Now change a salary value to -1, the following error message will pop up.

    Instead of showing the above message, you can customize an error message in the Validation Text right under the Validation Rule.

    Other examples – Access Table Data Validation

    I refer to Microsoft support and find some examples below.

    Validation rule Description
    Number BETWEEN 0 AND 1 Enter a value with a percent sign. (For use with a field that stores number values as percentages).
    Date >=#01/01/2007# AND =[StartDate] Enter an ending date on or after the start date.
    Text M Or F Enter M for male or F for female.
    Text LIKE “[A-Z]*@[A-Z].com” OR “[A-Z]*@[A-Z].net” OR “[A-Z]*@[A-Z].org” Enter a valid .com, .net, or .org e-mail address.

    Operators for Access Table Data Validation

    I refer to Microsoft support and find a list of Operators that can be used in Data Validation.

    Written by co-founder Kasper Langmann, Microsoft Office Specialist.

    Data validation is an Excel feature that lets you control what users enter into a cell.

    Most users know about data validation as a way to create dropdowns .

    However, it can do more than that. With data validation, you can literally make users enter data only in your specified format .

    In this article, we’ll show you what you can do with data validation and how to do them.

    Let’s get started! 😊

    Table of Content

    Get your FREE exercise file

    Before you start:

    Throughout this guide, you need a data set to practice.

    I’ve included one for you (for free).

    Download it right below!

    Download the FREE Exercise File

    How to restrict data input in excel with data validation

    What is data validation?

    Data validation is an Excel feature that allows you to restrict the type of data or values users can enter in a cell.

    To be precise, you’re able to set up “validation” rules on cells and decide what kind of data the cell would accept.

    In addition, you can set up errors whenever the data entered is against the validation rules you set. There’s even an option where you can circle invalid data on the worksheet.

    We’ll show you below the basic options and settings in using the feature.

    Data Validation Dialogue Box

    Using this feature is simple.

    To create and apply a validation rule , simply select the cell(s) and click ‘Data’ from the tab list.

    How to restrict data input in excel with data validation

    Then, click ‘Data Validation’ from the ‘Data Tools’ group in the ‘Data’ tab .

    How to restrict data input in excel with data validation

    You’ll immediately see the data validation dialogue box.

    How to restrict data input in excel with data validation

    Basically, there are seven (7) types of data you can set:

    • Whole number
    • Decimal
    • List
    • Date
    • Time
    • Text length
    • Custom

    Depending on the chosen data, there are various criteria from which you can select from:

    • between
    • not between
    • equal to
    • not equal to
    • greater than
    • less than
    • greater than or equal to
    • less than or equal to

    Once you choose a criterion, you’ll see additional options you have to fill out:

    • between
    • not between
    • equal to
    • not equal to
    • greater than
    • less than
    • greater than or equal to
    • less than or equal to

    Once the entry violates the validation rule, the user will receive a warning similar to the one below:

    The sections below will show you how to create a validation rule depending on the type of data as well as data validation examples.