Activate Data Validation Excel

[Slikk Huisenga]

Ifyou're creating a sheet that requires users to enter data, you might want to restrict entry to a certain range of dates or numbers, or make sure that only positive whole numbers are entered. Excel can restrict data entry to certain cells by using data validation , prompt users to enter valid data when a cell is selected, and display an error message when a user enters invalid data.

Test the data validation to make sure that it's 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.

Remove data validation - Select the cell or cells that contain the validation you want to delete, go to Data > Data Validation and in the data validation dialog box press Clear All, and then select OK.

You can use data validation to restrict the type of data or values that users enter into cells. For example, 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 abc , which is not an acceptable value in that cell.

Data validation is invaluable when you want to share a workbook with others, and you want the data entered to be accurate and consistent. Among other things, you can use data validation for the following:

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 visible until you move to another cell or press Esc.

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.

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. See Protect a worksheet.

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.

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.

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 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.

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, go to File > Options > Advanced > Editing options > clear the Enable fill handle and cell drag-and-drop check box, 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, go to the Formulas tab > Calculation group > Calculation Options > click Automatic.

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.

The tutorial explains how to do Data Validation in Excel: create a validation rule for numbers, dates or text values, make data validation lists, copy data validation to other cells, find invalid entries, fix and remove data validation.

When setting up a workbook for your users, you may often want to control information input into specific cells to make sure all data entries are accurate and consistent. Among other things, you may want to allow only particular data type such as numbers or dates in a cell, or limit numbers to a certain range and text to a given length. You may even want to provide a predefined list of acceptable entries to eliminate possible mistakes. Excel Data Validation allows you to do all these things in all versions of Microsoft Excel 365, 2021, 2019, 2016, 20013, 2010 and lower.

Excel Data Validation is a feature that restricts (validates) user input to a worksheet. Technically, you create a validation rule that controls what kind of data can be entered into a certain cell.

For instance, you can set up a rule that limits data entry to 4-digit numbers between 1000 and 9999. If the user types something different, Excel will show an error alert explaining what they have done wrong:

How to do data validation in ExcelTo add data validation in Excel, perform the following steps.

You can also open the Data Validation dialog box by pressing Alt > D > L, with each key pressed separately.

2. Create an Excel validation ruleOn the Settings tab, define the validation criteria according to your needs. In the criteria, you can supply any of the following:

With the validation rule configured, either click OK to close the Data Validation window or switch to another tab to add an input message or/and error alert.

3. Add an input message (optional)If you want to display a message that explains to the user what data is allowed in a given cell, open the Input Message tab and do the following:

As soon as the user selects the validated cell, the following message will show up:

4. Display an error alert (optional)In addition to the input message, you can show one of the following error alerts when invalid data is entered in a cell.

When adding a data validation rule in Excel, you can choose one of the predefined settings or specify custom criteria based on your own validation formula. Below we will discuss each of the built-in options, and next week we will have a closer look at Excel data validation with custom formulas in a separate tutorial.

For example, this is how you create an Excel validation rule that allows any whole number greater than 0:

Date and time validation in ExcelTo validate dates, select Date in the Allow box, and then pick an appropriate criteria in the Data box. There are quite a lot of predefined options to choose from: allow only dates between two dates, equal to, greater than or less than a specific date, and more.

Note. Please be careful with the Ignore blank option, which is selected by default. If you are creating a drop-down list based on a named range that has at least one blank cell, selecting this check box allows entering any value in the validated cell. In many situations, it is also true for validation formulas: if a cell referenced in the formula is blank, any value will be allowed in the validated cell.

Supplying comma-separated lists directly in the Source box is the fastest way that works well for small dropdowns that are unlikely to ever change. In other scenarios, you can proceed with one of the following ways:

For instance, you can edit your Excel data validation list by adding or removing items from the Source box, and have these changes applied to all other cells containing the same drop-down list:

How to copy Excel data validation rule to other cellsIf you've configured data validation for one cell and wish to validate other cells with the same criteria, you don't have to re-create the rule from scratch.

Tip. Instead of copying data validation to other cells, you can convert your dataset to an Excel table. As you add more rows to the table, Excel will apply your validation rule to new rows automatically.

This will select all cells that have any data validation rules applied to them:

How to remove data validation in ExcelOverall, there are two ways to remove validation in Excel: the standard approach designed by Microsoft and the mouse-free technique devised by Excel geeks who would never take their hands off the keyboard unless absolutely necessary (e.g. to take a cup of coffee :)

As you see, the standard method is pretty fast but does require a few mouse clicks, no big deal as far as I'm concerned. But if you prefer working with the keyboard over a mouse, you may find the following approach appealing.

Method 2: Paste Special to delete data validation rulesDe jure, Excel Paste Special is designed for pasting specific elements of copied cells. De facto, it can do many more useful things. Among others, it can quickly remove data validation rules in a worksheet. Here's how:

Instead of typing values directly in the criteria boxes, you can enter them in some cells, and then refer to those cells. If you decide to change the validation conditions later, you will simply type new numbers on the sheet, without having to edit the rule.

To enter a cell reference, either type it in the box preceded by an equal sign, or click the arrow next to the box, and then select the cell using the mouse. You can also click anywhere within the box, and then select the cell on the sheet.

3a8082e126