How to do Data Validation in Excel?

Data Validation is a function of Excel used to monitor what can be inserted into a cell by a user. Data science experts say data validation, for example, may be used to ensure that a value is a number between 1 and 6 or to ensure that the date happens in the next 30 days or to ensure that a text entry is less than 25 characters long.

The validation of data will simply show a message to a user telling them what is permitted. In addition, data validation can be used to provide the user with a predefined option in a drop-down menu. Data validation can also avoid invalid user input. This can be a beneficial way to give a user precisely the values that match the requirements.

Blog Contents

1. Accessing Data Validation in Excel

2. Add Data Validation in Excel

3. Excel Data Validation List (drop-down)

4. Edit Data Validation in Excel

5. Copy the Validation Rule of Excel Data to Other Cells

6. Remove Data Validation in Excel

7. Conclusion

Accessing Data Validation in Excel

You can access Data Validation in Excel via the Data tab in the Ribbon. It can also be opened by pressing Alt> D> L, pressing it separately for each key.

Add Data Validation in Excel

Step 1: Open the Data Validation dialogue box

Go to the Data tab> Data Tools group, pick one or more cells to validate, and then click the Data Validation button. Or by pressing Alt> D> L, open the Data Validation dialogue box with each key pressed separately.

Step 2: Build a validation rule for Excel.

Define the validation requirements on the Settings tab according to needs. This may include any of the following under the criteria:

• Values - type the numbers, as shown in the screenshot below, in the requirements boxes.

• Cell references - in another cell, create a rule based on a value or formula.

• Formulas - allow, as in this example, to convey more complex conditions.

Step 3: Add an input message.

To show a message that explains to the user what information in a given cell is allowed, open the Input Message tab, and do the following:

• Make sure the Show input message box is checked when the cell is selected.

• Enter the message title and text into the corresponding fields.

• In order to close the dialogue window, press OK.

Excel Data Validation List (drop-down)

Select the target cells to add a drop-down list of things to a cell or a group of cells, and do the following:

Step 1: Open the dialogue box for Data Validation (Data tab> Data Validation).

Step 2: In the Settings tab, in the Allow tab, pick List.

Step 3: Type the items in the Excel validation list, separated by commas, in the Source box. For example, type Yes, No, N/A to restrict the user's input to three choices.

Step 4: Make sure to pick the In-cell drop-down box so that the drop-down arrow appears next to the cell.

Step 5: Click, OK.

Edit Data Validation in Excel

To change the validation rule for Excel, perform these steps:

Step 1: Select any of the cells that are validated.

Step 2: Open the dialogue box for Data Validation (Data tab> Data Validation).

Step 3: Make the changes as required.

Step 4: To copy the changes made to all other cells with the original validation requirements, select the Add these changes to all other cells with the same setting check box.

Step 5: To store the changes, click OK.

Copy the Validation Rule of Excel Data to Other Cells

There is no need to rewrite the rule from scratch once a validation rule has been established for one cell and want to validate other cells with the same requirements at a later stage.

To copy an Excel validation rule, follow these four simple steps:

Step 1: Select the cell that the validation rule applies to, and to copy it, press Ctrl + C.

Step 2: Select other cells that you would like to validate. Press and hold the Ctrl key when choosing the cells in order to pick non-adjacent cells.

Step 3: Right-click a list, click Paste Special, and from the context menu, pick Validation. Alternatively, press the shortcut for Paste Special> Validation: Ctrl + Alt + V, then N.

Step 4: Then click, OK.

Remove Data Validation in Excel

Overall, in Excel, there are two ways to eliminate validation:

• the traditional Microsoft method and

• the mouse-free technique created by Excel geeks who, unless absolutely necessary, will never take their fingers off the keyboard (e.g., to take a cup of coffee :)

Method 1: Standard method of deleting data validation

Normally, you take these steps to delete data validation from Excel worksheets:

• Select the cell(s) with the validation of the data.

• On the Data tab, press the button for Data Validation.

• Click the Clear All button on the Settings tab and click OK.

Method 2: Paste Special Data Validation Rules to Delete

• De jure, Excel Paste Special is intended for the pasting of copied cell-specific elements. It can, de facto, do many more useful things. It can easily delete data validation rules from a worksheet, among other items. This is how:

• Without data validation, pick an empty cell and press Ctrl + C to copy it.

• Press Ctrl + Alt + V, then N, which is the Paste Special> Data Validation shortcut.

• Click Enter.

Conclusion

So, here's a few things that can be done with data validation in Excel. Data Validation has become an important tool in Excel as it helps to customize the responses of the users and hence to make the answers specific. Future technology is advancing day by day. Enroll in a Big Data Certification/data science certification.

Result-oriented Technology expert with 10 years experience in education & technology roles. Passionate about getting the best ROI for the brand, associate with Global Tech Council.