Which tool you will use in Excel to see what must be the value of a cell to get required result?

This is a guest post by Alan Murray from Computergaga.

Data Validation is a very useful Excel tool. It often goes unnoticed as Excel users are eager to learn the highs of PivotTables, charts and formulas.

It controls what can be input into a cell, to ensure its accuracy and consistency. A very important job when working with data.

In this blog post we will explore 11 useful examples of what Data validation can do.

To apply these Data Validation rules;

1. First select the range of cells you want to apply the validation to.
2. Click the Data tab and then the Data Validation button on the Ribbon.
3. In the Settings tab, select the validation rule criteria.

Allow Uppercase Entries Only

You may need to ensure that data is entered in uppercase, such as this example of UK postcodes being entered.

The cells need to accept the entry of both text and numbers, but the text must be uppercase.

For this we can use a formula with the UPPER and the EXACT functions.

The UPPER function probably speaks for itself. It converts text into uppercase.

The EXACT function is used to compare the cell entry with the uppercase version to see if they are the same. If they are, then the entry is valid.

For this example, the validation was applied to range A2:A6. Select Custom from the Allow list and enter the following formula into the Formula box.

=EXACT(A2,UPPER(A2))

Prevent Future Dates

Entering dates is very common on a spreadsheet. Unfortunately users entering the wrong date is also commonplace.

By using validation rules, we can limit the mistake a user may make.

In this example, we prevent the entry of a future date. Maybe users are recording a transaction that has occurred. Therefore, it must be a date in the past or todays date.

Select Date from the Allow list and then Less than or equal to from Data.

In the End Date box, type the formula below.

=TODAY()

The TODAY function returns the current date from the computer. Incredibly useful. Check out more great Excel date functions (https://www.howtoexcel.org/category/functions/date-and-time/).

Creating Drop Down Lists

Creating drop down lists is the reason most people become familiar with the Data Validation feature. Creating lists is a simple and effective way of controlling data entry.

Select List from the Allow list. You can then either type the list items directly into the Source box separated by a comma, or refer to a range of cells that contain the list items.

When you need a simple list such as Open and Closed, or Yes and No, then typing the entries in makes sense.

When you need a more dynamic list for items that change over time such as lists of products, places and people, then referring to a range makes sense.

For this list, click in the Source box and then go and select the cells that contain the items.

In this example, the items were in range A1:A5 of a sheet called Names.

Dependent Drop Down Lists

Let’s take our drop down lists further and create dependent lists. For these lists, the item selected in one list will affect what options appear in the next list.

In the example below, we have a list of cities in cell F2. The selection from this list affects what names appear in the next list in cell G2.

To achieve this, we first must name each list. For example, range A2:A4 is named city, range B2:B6 is named cardiff and so on.

Follow these steps to create a named range.

1. Select the range to name e.g. A2:A4.
2. Click in the Name Box to the left of the Formula bar.
3. Type the name you would like to apply and press Enter.

The list in cell F2 is created just like in the previous example. In the Source box you can type =city to reference the named range.

For the dependent list in cell G2, the selection in cell F2 needs to be converted into a reference to the named ranges. This is done using the INDIRECT function (Learn more awesome examples of the INDIRECT function).

=INDIRECT(F2)

A common issue when creating dependent lists is the use of illegitimate characters in named ranges. You cannot begin named ranges with a number, or use spaces and some other symbols.

So, if items in your list use spaces, or start with numbers it presents an obstacle. Learn how you can overcome this issue in the video below.

Prevent Duplicate Values

Duplicate values are a very common problem in Excel. There are many techniques for identifying and removing duplicates, but it would be better if you could prevent them in the first place.

By using the COUNTIF function in a custom formula we can.

The formula below counts the occurrences of the inputted value in the range A2:A8. If the answer is 0 then the value is unique and allowed.

=COUNTIF($A$2:$A$8,A2)=0

Allow only Numeric or Text Entries

The ISNUMBER function can be used to create a validation rule that only allows the entry of numeric values in a cell.

Select Custom from the Allow list and use the formula below. In this example, cell A2 is the upper left cell of the selected range of cells.

=ISNUMBER(A2)

This will allow any numeric values only including dates and times.

To allow text values only we could use the ISTEXT function in the same way.

=ISTEXT(A2)

Validate an Entry Based on Another Cell

You can create Data Validation rules that are based on the value from another cell by writing a custom formula.

For example, maybe you only want a drop down list to appear if another cell is not empty.

The following IF function will test if cell A2 is not empty, and if so show the list from the location named range.

=IF(A2<>””,location)

Allow the Entry of Weekdays Only

When entering dates, you may need to restrict a user to the entry of specific days of the week. We can achieve this with the WEEKDAY function.

For this example, we shall restrict entry of dates that are Monday to Friday only. The formula below can be used for this.

=WEEKDAY(A2,2)<=5

The WEEKDAY function returns a number that represents the day of the week. In the WEEKDAY function, the 2 specifies that a week starts on Monday as 1 through to the Sunday as 7.

In this validation rule, the returned value must be less than or equal to 5. This excludes Saturdays and Sundays.

Restrict the Text Length

Entries may need to be of a specific text length, or no more than a certain number of characters. Creating a validation rule can help us ensure this.

In this example, I want to confine the text length to exactly 9 characters.

Select Text length from the Allow list, equal to from the Data list and then type 9 for the length.

Entries Contain Specific Text

Using Formulas in validation rules enables us to test and validate for almost anything.

In this last example, we ensure entries contain specific text.

The FIND function is used to search for a specific string of text (“ENG” in this example) and return its position within the cell. If FIND does not locate the text, an error is returned.

The ISNUMBER function is used to check if the FIND function was successful (if it returned a number). If it did, then ISNUMBER returns True, otherwise it returns False. This completes our validation test.

Here is the formula to use.

=ISNUMBER(FIND(“ENG”,A2))

Note: The FIND function is case sensitive. The SEARCH function can be used instead if you do not need to match the case of the text.

Create Meaningful Error Messages

We have looked at 10 Data Validation examples in this article. However, if a data entry mistake is made the same validation is shown, regardless of the validation criteria.

The great news is that you can create your own error messages to effectively communicate what may have gone wrong to the user.

Click the Error Alert tab of the Data validation window.

Select a Style from the list of Stop, Warning, or Information.

• Stop will prevent invalid data from being entered.
• Warning displays the error, but with the choice to allow the entry or prevent it.
• Information display the error, but does not prevent the data entry at all.

Type a Title and Error Message for the error alert.

Let’s take the example of allowing only the entry of weekdays into a cell. You could create an error message like this.

Conclusion

I believe Data Validation is an undervalued tool of Excel. Without clean data our PivotTables, charts and formulas will not function correctly.

This feature provides a method of limiting mistakes and collecting clean data on entry.

Data Validation cannot help though when receiving data from other databases, workbooks and websites.

If this is something that you do, I heavily encourage you to check out the Power Query feature of Excel. A powerful tool for establishing connections and automating the process of cleaning and transforming data from external sources.

About the Author

Alan is the founder of Computergaga and has a large YouTube following where he shares Excel tips and tutorials. He lives in the UK with his wife and 2 children. His passions for Excel and data keep him busy, but when he has free time he enjoys running and hiking.