Welcome back. Hope you are doing
well in learning Excel. As part of our series on Data Validation, we saw how to
use Data Validation with examples in an earlier
post. Today, we will see few of such practical examples to sharpen our
understanding of this concept even further.
From now onwards, I assume we are
aware of how to access Data Validation from the ribbon or menu so I will not mention it. If you are in
doubt, please refer to any of the previous posts on Data Validation.
As we move forward, remember the
below points:
- Unless otherwise mentioned, I try to apply validation in cell A1 in all the below examples.
- All the formulas are to be entered in Formula field in Data Validation window > Settings tab > Allow: > Under Custom option.
- Once you enter the formula, check if the validation is working or not
Let’s start.
Allow Only Text Entry
We have an option in the Data
Validation to allow only whole numbers or fractions. But there is no option if
you want to allow only text entries. For this we have to use custom validation. Enter the below formula in the validation window.
=ISTEXT(A1)
This formula evaluates entry in
cell A1, if the result is true (means text is entered) then entry is accepted.
Otherwise error message is displayed. You may also provide Input Message &
Error Alert that you want Excel to show.
Tip: Try using functions ISEVEN
(to restrict to enter only even numbers), ISODD (odd numbers only), ISLOGICAL
(True or False only), ISNONTEXT (non-text values only), ISNUMBER (only numbers).
Prevent Total Exceeding a Limit
When you have defined budget amount
for example, you would want users to limit the purchases with in the
budget. We already know how to restrict number between a minimum and maximum
limit, but in only one cell. So, how to limit entries that are exceeding the
budget over multiple cells?
We will take an example to
understand this easily. I have a budget of 1,000 for my department. I can buy
multiple items within this budget. I record my first purchase value in cell A2
and whenever I buy again, I continue to enter the next purchase values in cell
A3 and below. I want an error alert when I try to enter the purchase after I
exhausted my budget. Enter the following formula.
=SUM($A$2:$A$100)<=1000
In above formula, I assumed that my purchases won’t go beyond cell A100. I am sure you will change this formula to suit your needs.
Tip: Mess with COUNT, AVERAGE etc functions too.
Allow Only Weekdays Entry
No one wants to work on weekends, unless forced to by their retarded manager. Let’s see how you can restrict entry of weekend dates. Type below formula!
=AND(WEEKDAY(A1)<>1, WEEKDAY(A1)<>7)
This formula uses the WEEKDAY function to see the day of the date entered. WEEKDAY returns 1 for Sunday and 7 for Saturday. We are using these values further to accept or restrict user entry.
Note: You have to make sure that users are not using copy & paste in the cells where validation is applied, if they do, your validation will not work. We also know that values resulted by formulas will override the validation.
Prevent Duplicates
You can use COUNTIF function to prevent duplicates in a given range. Let’s go by an example. I need to apply validation in cells A1:A10 so that no value is entered more than once in these cells. Select A1:A10 cells and in validation, enter below formula to achieve this.
=COUNTIF($A$1:$A$10,A1)<=1
Tip: Get a formula to allow any one value multiple times and prevent others to be duplicated. Use IF function.
Prevent Entries with a Particular Word
We use Dropdown list if you want to enter user to select a particular word. What if I want user to restrict entering a particular word but allow all others? Let’s assume that word is “Abcd”. You will enter below formula in the cell to restrict this from being entered.
=A1<>"Abcd"
This works when only “Abcd” entered in the cell. But this won’t work if user enters this word as part of a sentence like “I am entering Abcd here”. If you want to restrict this way of entries too, then enter below formula.
=ISERROR(FIND("Abcd",A1))=TRUE
But FIND is case sensitive. Meaning, “Abcd” is not same as “ABcd” or “ABCD”. Taking our example further, if you want to restrict entry of “Abcd” in any case then enter the below formula.
=ISERROR(FIND("ABCD",UPPER(A1)))=TRUE
Tip: Try using SEARCH, SUBSTITUTE functions to achieve the same results.
Prevent Space & Special Characters
Sometimes you may want to restrict users from entering spaces or any other special characters like “!”, “@”, “#” etc. Use the below formula in that case.
=A1=SUBSTITUTE(A1," ","")
There is a space in highlighted quote marks. Replace that space with any character you want to prevent entry. SUBSTITUTE function replaces space character with “nothing” (denoted by two quotes with nothing in-between). Then this formula checks that entry in A1 is equal to the entry without spaces.
But this formula won’t work if entry is a number. For this we will use another function of Excel i.e. LEN. LEN calculates the length of a cell means number of characters entered in a cell. This number includes spaces. Enter the below formula if the validation to be performed on number.
=LEN(A1)=LEN(SUBSTITUTE(A1," ",""))
Tip: Try to achieve the same result by using ISERROR & FIND function as in one of the example above.
Above example can be applied for space at any position in the cell entry.
No Space as First or Last Character
You might sometimes don’t want a space at the start or at the end of an entry. But a space anywhere else would be fine. In this case, you can use below formula.
=A1=TRIM(A1)
Multiple Criteria
What if I have multiple criteria to be applied for an entry in a cell? For example: I want unique numbers with exactly 3 Digits in cells A1:A10. Look at the below formula to get this validation.
=AND(COUNTIF($A$1:$A$10,A1)<=1, ISNUMBER(A1),LEN(A1)=3)
COUNTIF function makes sure that each entry is unique. ISNUMBER prevent text and LEN ensures number of characters is 3.
We can build validation formulas depending on the situations. These practical situations are literally unlimited and it is humanly not possible to guess all and provide ready-made examples for them. But if you follow the basic principles of formulas in validation, I am sure you would be able to come up with a solution. If you are not able to get a solution for your problem, just let me know and I will try to help. Happy Excelling!!
