Data validation

Use data validation in Excel to make sure that users enter certain values into a cell. Data Validation is an effective tool in Microsoft Excel when you need to share a spreadsheet with a team and want to keep the content unified. In cells with validation enabled, creator of the spreadsheet can prompt users to choose data from a list, restrict cells to certain data types, or do a few other things that involve complicated custom settings. In Excel 2013, basic data validation is easy to setup. If you’re looking to learn how to use it, this article will show you. Data validation process in brief is –

  1. Create a drop down list of items in a cell
  2. Restrict entries, such as a date range or whole numbers only
  3. Create custom for what can be entered
In this article I show you the validation process step by step along with below topics:
⇒ When is data validation useful?
⇒ How to create data validation rules
⇒ How to handle a data validation alert
⇒ Add data validation to a cell or range
⇒ Adding other types of data validation
.

When is data validation useful?

Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. You can configure data validation to prevent users from entering data that is not valid. If you prefer, you can allow users to enter invalid data but warn them when they try to type it in the cell. You can also provide messages to define what input you expect for the cell, and instructions to help users correct any errors.For example, in a marketing workbook, you can set up a cell to allow only account numbers that are exactly three characters long. When users select the cell, you can show them a message such as this one:
alert1
If users ignore this message and type invalid data in the cell, such as a two-digit or five-digit number, you can show them an actual error message. In a slightly more advanced scenario, you might use data validation to calculate the maximum allowed value in a cell based on a value elsewhere in the workbook. In the following example, the user has typed 123434 in cell D5, which exceeds the maximum limit specified for commissions. If the payroll budget were to increase or decrease, the allowed maximum in D5 would automatically increase or decrease with it.alert2.

How to create data validation rules?

The data validation commands are located on the Data tab, in the Data Tools group.

Data Tools group on the Data tab

You configure data validation in the Data Validation dialog box.

Data Validation dialog box

Data validation is invaluable when you want to share a workbook with others in your organization, and you want the data entered in the workbook to be accurate and consistent.

 

Among other things, you can use data validation to do the following:

  • Restrict data to predefined items in a list: For example, you can limit types of departments to Sales, Finance, R&D, and IT. Similarly, you can create a list of values from a range of cells elsewhere in the worksheet.
  • Restrict numbers outside a specified range: For example, you can specify a minimum limit of deductions to two times the number of children in a particular cell.
  • Restrict dates outside a certain time frame: For example, you can specify a time frame between today’s date and 3 days from today’s date.
  • Restrict times outside a certain time frame: For example, you can specify a time frame for serving breakfast between the time when the restaurant opens and 5 hours after the restaurant opens.
  • Limit the number of text characters: For example, you can limit the allowed text in a cell to 10 or fewer characters. Similarly, you can set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1), plus 10 characters.
  • Validate data based on formulas or values in other cells: For example, you can use data validation to set a maximum limit for commissions and bonuses of 3,600, based on the overall projected payroll value. If users enter more than 3,600 in the cell, they see a validation message.

 

Example of simple list validation

I have twelve student in a batch and I want to find out there skilled wise exam result using data validation. My student list is as below:

validation list1

I want to validate rules in cell B5. So, first click on B5 and then click data validation option from data tab.

data tab

Now select the list from allow group and locate my source data as below:

selecting data list

Now a drop down menu is visible just in the right side of validate cell as below:

drop down menu

We are all set. Now see the magic. If I select a name from the drop down menu every data along with graph is changed accordingly. For an example, I select, Abdul Kader. The result is looks like below:

abdul kader

 

How to handle a data validation alert

What users see when they enter invalid data into a cell depends on how you have configured the data validation. You can choose to show an input message when the user selects the cell. Input messages are generally used to offer users guidance about the type of data that you want entered in the cell. This type of message appears near the cell. You can move this message, if you want to, and it remains until you move to another cell or press Esc.Data validation message
You can also choose to show an error alert that appears only after users enter invalid data.
Error alert with custom message
You can choose from three types of error alerts:

Icon Type Use to
Stop icon Stop Prevent users from entering invalid data in a cell. A Stop alert message has two options: Retry or Cancel.
Warning icon Warning Warn users that the data they entered is invalid, without preventing them from entering it. When a Warning alert message appears, users can click Yes to accept the invalid entry, No to edit the invalid entry, or Cancel to remove the invalid entry.
Information icon Information Inform users that the data they entered is invalid, without preventing them from entering it. This type of error alert is the most flexible. When an Information alert message appears, users can click OK to accept the invalid value or Cancel to reject it.

You can customize the text that users see in an error alert message. If you choose not to do so, users see a default message.

Input messages and error alerts appear only when data is typed directly into the cells. They do not appear under the following conditions:

  • A user enters data in the cell by copying or filling.
  • A formula in the cell calculates a result that is not valid.
  • A macro enters invalid data in the cell.

 

 Tips for working with data validation

Use these tips and tricks for working with data validation in Excel.

  • If you plan to protect the worksheet or workbook, protect it after you have finished specifying any validation settings. Make sure that you unlock any validated cells before you protect the worksheet. Otherwise, users will not be able to type any data in the cells.
  • If you plan to share the workbook, share it only after you have finished specifying data validation and protection settings. After you share a workbook, you won’t be able to change the validation settings unless you stop sharing. However, Excel will continue to validate the cells that you have designated while the workbook is being shared.
  • You can apply data validation to cells that already have data entered in them. However, Excel does not automatically notify you that the existing cells contain invalid data. In this scenario, you can highlight invalid data by instructing Excel to circle it on the worksheet. Once you have identified the invalid data, you can hide the circles again. If you correct an invalid entry, the circle disappears automatically.
  • To quickly remove data validation for a cell, select it, and then open the Data Validation dialog box (Data tab, Data Tools group). On the Settings tab, click Clear All.
  • To find the cells on the worksheet that have data validation, on the Home tab, in the Editing group, click Find & Select, and then click Data Validation. After you have found the cells that have data validation, you can change, copy, or remove validation settings.
  • When creating a drop-down list, you can use the Define Name command (Formulas tab, Defined Names group) to define a name for the range that contains the list. After you create the list on another worksheet, you can hide the worksheet that contains the list and then protect the workbook so that users won’t have access to the list.

If data validation isn’t working, make sure that:

  • Users are not copying or filling data     Data validation is designed to show messages and prevent invalid entries only when users type data directly in a cell. When data is copied or filled, the messages do not appear. To prevent users from copying and filling data by dragging and dropping cells, clear the Enable fill handle and cell drag-and-drop check box in the Advanced category of the Excel Options dialog box (File tab, Options command), and then protect the worksheet.
  • Manual recalculation is turned off     If manual recalculation is turned on, uncalculated cells can prevent data from being validated correctly. To turn off manual recalculation, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic.
  • Formulas are error free     Make sure that formulas in validated cells do not cause errors, such as #REF! or #DIV/0!. Excel ignores the data validation until you correct the error.
  • Cells referenced in formulas are correct     If a referenced cell changes so that a formula in a validated cell calculates an invalid result, the validation message for the cell won’t appear.

 

Add data validation to a cell or range

So let’s add some data validation. The steps in this section explain how to apply one type of validation—restricting data entry by providing a drop down list—and the table that follows explains how to add some of the other types of validation that Excel provides.

Follow the first three steps in this section to add any type of data validation.

  1. Select one or more cells to validate.
  2. On the Data tab, in the Data Tools group, click Data Validation.
    Data Tools group on the Data tab
  3. If it isn’t already selected, in the Data Validation dialog box, click the Settings tab.
  4. In the Allow box, select List.
  5. Click the Source box and then type the list values, separated by your Microsoft Windows list separator character (commas by default). For example:
    • To limit an answer to two choices (“Do you have children?” for example), type Yes, No.
    • To limit a vendor’s quality reputation to three ratings, type Low, Average, High.
    • You can also create the list entries by referring to a range of cells elsewhere in the workbook. Note   The width of the drop-down list is determined by the width of the cell that has the data validation. You might need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.
  6. Make sure that the In-cell dropdown check box is selected. Otherwise, you won’t be able to see the drop-down arrow next to the cell.
  7. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. Note   If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell. Tip    If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, on the Settings tab, select the Apply these changes to all other cells with the same settings check box.
  8. Test the data validation to make sure that it is working correctly. Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

 

Help! The Data validation command is unavailable.

  • An Excel table might be linked to a SharePoint site     You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.
  • You might currently be entering data     The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering data, press Enter or ESC.
  • The worksheet might be protected or shared     You cannot change data validation settings if your workbook is shared or protected.Display an optional input message
  • Click the Input Message tab (Data tab > Data Tools > Data Validation).
  • Make sure the Show input message when cell is selected check box is selected.
  • Fill in the title and text for the message.Specify an optional alert or error message when invalid data is entered.
  • Click the Error Alert tab (Data tab > Data Tools > Data Validation), and make sure that the Show error alert after invalid data is entered check box is selected. If you want to allow users to type entries that are not in the list, clear the Show error alert after invalid data is entered check box instead.
  • Select one of the following options for the Style box:
  • To display an information message that does not prevent entry of invalid data, select Information.
  • To display a warning message that does not prevent entry of invalid data, select Warning.
  • To prevent entry of invalid data, select Stop.
  • Fill in the title and text for the message (up to 225 characters). If you don’t, Excel displays a generic alert message.

 

Adding other types of data validation

The following table lists other types of data validation and shows you ways to add it to your worksheets.

To do this: Follow these steps:
Restrict data entry to whole numbers within limits
  1. Follow steps 1-3 in Add data validation to a cell or range above.
  2. From the Allow list, select Whole number.
  3. In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.
  4. Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value.For example, say you’re validating data in cell F1. To set a minimum limit of deductions to two times the number of children in that cell, select greater than or equal to in the Data box and enter the formula, =2*F1, in the Minimum box.
Restrict data entry to a decimal number within limits
  1. Follow steps 1-3 in Add data validation to a cell or range above.
  2. In the Allow box, select Decimal.
  3. In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.
  4. Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value.For example, to set a maximum limit for commissions and bonuses of 6% of a salesperson’s salary in cell E1, select less than or equal to in the Data box and enter the formula, =E1*6%, in the Maximum box. Note    To let a user enter percentages, for example 20%, select Decimal in the Allow box, select the type of restriction that you want in the Data box, enter the minimum, maximum, or specific value as a decimal, for example .2, and then display the data validation cell as a percentage by selecting the cell and clicking Percent Style Button image in the Number group on the Home tab.
Restrict data entry to a date within a time frame
  1. Follow steps 1-3 in Add data validation to a cell or range above.
  2. In the Allow box, select Date.
  3. In the Data box, select the type of restriction that you want. For example, to allow dates after a certain day, select greater than.
  4. Enter the start, end, or specific date to allow. You can also enter a formula that returns a date.For example, to set a time frame between today’s date and 3 days from today’s date, select between in the Data box, enter =TODAY() in the Start date box, and enter =TODAY()+3 in the End date box.
Restrict data entry to a time within a time frame
  1. Follow steps 1-3 in Add data validation to a cell or range above.
  2. In the Allow box, select Time.
  3. In the Data box, select the type of restriction that you want. For example, to allow times before a certain time of day, select less than.
  4. Enter the start, end, or specific time to allow. If you want to enter specific times, use the hh:mm time format.For example, say you have a time value for serving breakfast entered in cell G1. If you want to restrict time entries to the period starting when the restaurant opens (the value in cell G1) and five hours after that, select between in the Data box, enter =G1 in the Start time box, and then enter =G1+”5:00″ in the End time box.
Restrict data entry to text of a specified length
  1. Follow steps 1-3 in Add data validation to a cell or range above.
  2. In the Allow box, select Text Length.
  3. In the Data box, select the type of restriction that you want. For example, to allow up to a certain number of characters, select less than or equal to.
  4. Enter the minimum, maximum, or specific length for the text. You can also enter a formula that returns a number value.For example, to set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1) plus 10, select less than or equal to in the Data box and enter =SUM(LEN(A1),LEN(B1),10) in the Maximum box.
Calculate what is allowed based on the content of another cell
  1. Follow steps 1-3 in Add data validation to a cell or range above. In the Allow box, select the type of data that you want.
  2. In the Data box, select the type of restriction that you want.
  3. In the box or boxes below the Data box, click the cell that you want to use to specify what is allowed.For example, to allow entries for an account only if the result won’t go over the budget in cell E4, select Decimal for Allow, select less than or equal to for Data, and in the Maximum box, enter =E4.
Use a formula to calculate what is allowed
  1. Follow steps 1-3 in Add data validation to a cell or range above.
  2. In the Allow box, select Custom.
  3. In the Formula box, enter a formula that calculates a logical value (TRUE for valid or FALSE for invalid entries).

The following table provides examples.

.
Examples of formulas in data validation

To make sure that Enter this formula
The cell for the picnic account (B1) can only be updated if nothing is budgeted for the discretionary account (D1) and the total budget (D2) is less than the $40,000 allocated. =AND(D1=0,D2<40000)
The cell that contains a product description (B2) only contains text. =ISTEXT(B2)
For the cell that contains a projected advertising budget (B3), the subtotal for subcontractors and services (E1) must be less than or equal to $800, and the total budget amount (E2) must also be less than or equal to $97,000. =AND(E1<=800,E2<=97000)
The cell that contains an employee age (B4) is always greater than the number of full years of employment (F1) plus 18 (the minimum age of employment). =IF(B4>F1+18,TRUE,FALSE)
All the data in the cell range A1:A20 contains unique values. =COUNTIF($A$1:$A$20,A1)=1 You must enter the formula in the data validation for cell A1, and then fill the cells A2 though A20 so that the data validation for each cell in the range has a similar formula, but the second argument to the COUNTIF will match the current cell.
The cell that contains a product code name (B5) always begins with the standard prefix of ID- and is at least 10 characters long. =AND(LEFT(B5, 3) =”ID-“,LEN(B5) > 9)
Create the Dynamic Named Ranges

After you type the lists of items for the data validation drop down lists, create a dynamic named range for each list.

The formulas that were used for the two dynamic named ranges in the sample file were:

  • ClientList: =OFFSET(Lists!$D$1,0,0,COUNTA(Lists!$D:$D),1)
  • FruitList: =OFFSET(Lists!$B$1,0,0,COUNTA(Lists!$B:$B),1)

pivot table top 10

 

Set Up the Data Entry Sheet

In the DataEntry sheet, follow these instructions to add data validation drop down lists in the columns for Client and Fruit:

1. Select the cells where you want the Client drop down lists
2. On the Ribbon, click the Data tab, then click Data Validation (In Excel 2003, click the Data menu, then click Validation.)
3. From the Allow drop-down list, choose List
4. In the Source box, type an equal sign and the list name, for example: =ClientList
5. On the Error Alert tab, remove the check mark to allow invalid entries.

data validation source

6. Click OK

Repeat the steps for the Fruit data entry cells, using =FruitList as the source

Download Excel File

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment