Data Validation in Excel: How to use it

Are you here to learn what is Data Validation? Well! Data Validation in Excel is an important feature in Spreadsheet Management. It is used to control or restrict the type of data users can enter a cell or worksheet. as it ensures accuracy and reliability. Read this blog further to learn more about Data Validation in Excel, its significance and how it can keep error-free data easily. Let’s dive in! 

Table of Content 

1) What is Data Validation in Excel? 

2) 7 Steps to Validate Data in Excel 

3) Excel Data Validation examples 

4) How to copy the Excel Data Validation rule to other cells 

5) How to edit Data Validation in Excel?

6) How to remove Data Validation in Excel?

7) How to find cells with Data Validation in Excel?

8) Conclusion 

What is Data Validation in Excel? 

As we mentioned above, Data Validation in Excel is a feature that controls or restricts the type of data users can enter a cell or worksheet. You can impose precise rules or guidelines using this feature. If users attempt to submit inaccurate data, it also allows them to display a customised message. Here are a few examples of Data Validation in Excel:

1) Enable only numeric or text values in a cell. 

2) Allow data entries of a particular length. 

3) Limit dates and times beyond a given range. 

4) Show an input message when the user chooses a cell. 

5) Show a warning message when users enter incorrect data. 

6) Identifies incorrect entries in validated cells.

Seven steps to validate Data in Excel 

Here are the seven key steps to Validate Data in Excel

1) Choose the cell to Validate 

Select the cell you want to validate. Then, go to the Data tab > Data tools, and click on the Data Validation button. After this step, a Data Validation dialogue box with three tabs will appear. They are Settings, Input Message, and Error Alerts.

2) Define validation criteria 

Specify your validation criteria on the settings tab.

3) Choose criteria under allow

Under Allow, select an option from Whole Number, Decimal, List, Date, Time, Text Length, and Custom.

4) Specify condition

Under Data, select a condition and set required values based on what you choose for Allow and Data. 

5) Provide input message 

You can enter the input message only if you want. This step is optional. 

6) Customise error message

You can also customise your custom error message. This step is optional. 

7) Confirm by clicking OK 

Click OK. Now, if you try entering a value outside the specified range (10, 50), it will result in an error. 

Discover vital Excel functions for Business Analytics with our Business Analytics With Excel Masterclass – sign up today! 

Excel Data Validation examples 

When you add a Data Validation rule in Excel, you can select one of the predefined settings or specify custom criteria according to your own validation formula. Let's discuss the various built-in options below.

Decimals and whole numbers 

Choose the corresponding item in the Allow box to restrict data entry to a whole number or decimal. Then, select one of the following criteria in the Data box: 

1) Greater than or less than the specified number 

2) Equal to or not equal to the specified number 

3) Between the two numbers or not between the two numbers to exclude that range of numbers

Excel date and time validation 

Choose Date in the Allow box, and then pick the right criteria in the Data box to validate dates. There are a lot of predefined options to select from: enable only dates between two dates, equal to, greater than or less than a particular date. 

On the other hand, select Time in the Allow box and then define the mandated criteria to validate times. 

For example, to allow only dates between the Start date in B1 and the End date in B2, apply this Excel Data Validation rule. At the same time, make your own Data Validation formulas to validate entries based on today's data and current time.

Text length 

Choose Text length in the Allow box, and select the validation criteria in accordance with your business logic to allow data entry of a specific length. 

Note. The Text length option restricts the number of characters but not the data type. That means the above rule will allow both text and numbers under ten characters or ten digits, respectively. 

Drop-down Excel Data Validation list 

Follow the steps given below to add a drop-down list to a cell or group of cells in Excel:

1) Choose the cells where you want the drop-down list.

2) Open the Data Validation dialogue box by going to the Data tab and selecting Data Validation.

3) On the Settings tab, choose 'List' from the 'Allow' box.

4) In the Source box, type the items for your drop-down list, separated by commas. For example, you might enter "Yes, No, N/A" for a list of three options.

5) Ensure the 'In-cell drop-down' option is checked. This makes the drop-down arrow appear next to the cell, indicating that there is a list to choose from.

6) Click OK. 

Be cautious with the "Ignore blank" option in Excel, which is enabled by default. If you're setting up a drop-down list based on a named range containing any blank cells, keeping this option checked allows any value to be entered in the validated cell. This applies to validation formulas as well: if any cell referenced by the formula is blank, then any input will be accepted in the validated cell. This flexibility can be useful or risky depending on your data integrity needs.

Enhance your professional skills with our Microsoft Office Training – register today! 

How to copy Excel Data Validation rule to other cells?

If you've set up Data Validation for one cell and want to apply the same rules to other cells, there's no need to recreate the rule from scratch. Here’s how to copy the validation rule in Excel:

1) Select the cell with the validation rule and press Ctrl + C to copy it.

2) Select the other cells you want to validate. To select non-adjacent cells, hold down the Ctrl key while clicking the cells.

3) Right-click the selected cells, choose 'Paste Special', and then select the 'Validation' option.

4) Alternatively, use the shortcut Ctrl + Alt + V, then press N, and click OK.

Tip: Instead of just copying Data Validation to other cells manually, consider converting your dataset into an Excel table. When you add more rows to the table, Excel automatically applies your validation rule to these new entries, streamlining the process.

How to edit Data Validation in Excel?

To modify an Excel validation rule, follow these steps:

1) Select any validated cell.

2) Open the Data Validation dialog box via the Data tab > Data Validation.

3) Make your changes.

4) To apply these changes across all cells with the same settings, check "Apply these changes to all other cells with the same settings."

5) Click OK to save.

For example, you can update your Excel Data Validation list by adjusting the Source box, affecting all cells with the same drop-down list.

How to remove Data Validation in Excel?

You have two main methods to remove Data Validation:

Standard Method:

1) Select the cells with Data Validation.

2) Go to the Data tab and click Data Validation.

3) In the Settings tab, click Clear All, then OK.

Tip: To remove all validated cells on a sheet, use Find & Select to highlight them all.

4) Keyboard-Only method (For the Keyboard Savvy):

5) Copy an empty, non-validated cell with Ctrl + C.

6) Select the cells to clear of validation.

7) Use Paste Special with Ctrl + Alt + V, then N for Data Validation.

8) Press Enter.

These methods offer a quick way to adjust or remove data rules, whether you prefer mouse clicks or keyboard shortcuts. 

How to find cells with Data Validation in Excel?

To quickly locate all validated cells in your current worksheet, navigate to the Home tab, find the Editing group, and click on "Find & Select," then choose "Data Validation."

Conclusion 

Data Validation in Excel is a useful feature that ensures inputs meet specific validation criteria. It verifies whether an entry is a number, date, time, or text of a certain length and can even display a drop-down menu with predefined options. This functionality ensures that only valid data is entered into cells. Additionally, you can set up custom messages to guide users before they enter data, enhancing the accuracy and reliability of data entry.

Learn to create and prepare Gantt charts by registering for our Excel Training With Gantt Charts course!