Previously we finished with Pivot Charts, if you have any questions about that, do not hesitate to ask in the comments. This time we will be doing the Data Validation.
To practice Excel you need a data sample. Use this website to generate fake data of any fields and length you wish. https://mockaroo.com/
I have made a data of Last Names, First Names, Genders, etc., who work in ABC Corp.
Where to find it?
You can find Data Validation under “Data” Tab here:
What is Data Validation?
Contrary to what its name suggests, it is not only used for Validating the data. Use it for two purposes:
1. You want a user to input the data (by data I meant anything) from your described one and should not stray from what is required (I will explain this later).
2. Secondly, it is used to check that the exact data is inputting as is required.
Both of the above may seem quite similar if not the same, but wait for a moment and you will know the difference.
Let’s try to use it
For example, you have asked all of your office colleagues to fill-in a form for some insurance purpose as is required by the head office. So, you will make a form like this:
First Column of Serial Number:
You will want to put numbers, but only if someone starts filling the form. For this, you will use the “IF” function in a formula (don’t worry I will teach formulas in detail in later parts). Remember the rule for any formula, i.e. it should start with an equal sign. So, the formula here will be:
To explain the above formula, I have asked to check whether cell B2 is empty, if it is, leave the cell, in which the formula is, empty, otherwise, take 1 from the row number. Now put this formula in cell A2. When you enter the formula, nothing will happen, it is because cell B2 is still empty. Now just copy the formula to the 100 rows or to a number which you think your data will go. Just by pressing F2 on any of column A, where our formula happens to be, you can reveal it, or you can see it in the formula bar.
Now come to the last name column. Suppose you already have the list of all the employees in the company. Therefore, you just want them to pick their own last name.
So, you will use data validation. As advised before, always covert any data into a table and name it. We have done just the same with the already present list of our employees and named it as employees. Now put your cursor in the cell B2, i.e., on the first entry point under the Last Name column, click Data Validation, a dialogue box will open up:
You can see that the cursor is in B2. The Data Validation opens up with “Setting” selected by default. If you click on the arrow under the “Allow”, you will see all these options:
As you want to display a list of last names in our pre-formed table of employees, you will select List from these options. As you do so, you will see some changes have occurred:
Keep the “Ignore blank” and “In-Cell dropdown” selected (we will come to these later). In the source box, you have to enter the source of data you want your users to select, i.e., the last names. Now here, along with the Data Validation, we will also learn “Name Range” as we go.
What is Name Range?
The function of the name range is to name a range of data so that it is easy to use. For example, in the above example, we are using the Last Name of the Table Employees. So we will create a Name Range as Last names and use it for the data validation list in Column B.
Where to find Name Range?
Under the Formulas Tab, here it is:
What to do with it?
Take a deep breath and click on it, you will see the following dialogue box:
You can see here the red arrow pointing towards our table of Employee. You cannot make changes to it here, that is why it is disabled (greyed out). You can only change it on the sheet. Now to create a named range of the Last Name from our Table Employees, we will click on New, as we are making a new named range.
Now, give a name to this range. We wrote the Last Name, it is easy to remember, and it is logical. Always give a name that is logical to the list so that easy to remember, although you can use any name, the sky’s the limit.
Next, we let the scope be “workbook”, why because we want to use this named range on the whole workbook on all sheets. If you just select the sheet you are working on, then this name range will not be available for other sheets. Comments are optional, but it is wise to say something about this named range, as when the list of named ranges increase, it will become increasingly difficult as to why have you created this name in the first place. The last option is the most important, i.e., refers to. It will tell Excel which ranges this name encompasses. As you have seen we have used the Table Employees column last name. This is the way you will use your table. First, put an equal sign, the name of the table, i.e., “Employees”, then a bracket “[“, the name of the columns, in this case, “LastName”, then close bracket “]”. This is the format of referring to the table columns. You must get used to it. There is another better way to do it so that you don’t get errors. Click in the “Refers to” box, then click on the sheet tab where you have your Table, then click on the header of the column you want in the named range, the table and column name appears automatically.
We will do the “Named Range” separately in some other part of Lear Excel, so keep following.
You may ask a question that if we have to use a Name Range, then why we have to change our Data Range to a Table in the first place. As discussed in previous parts, converting all of your data ranges into Tables is advisable. Well, in this case, you have selected to put the LastName column of your Table Employees. Suppose, you have forgotten to input 2 or more employees in the list and at the eleventh hour, you remembered that. If it were to be just a simple Data Range, not only you have to put those employees’ data in the list but have to change all the Data input fields accordingly. But, as you are using a table, you just put the forgotten employees’ data and the Table will adjust itself to include those employees in the list automatically. Magical, haan?
After clicking OK, you will see that your named range now appeared here:
Go back to Data Validation
Now you go back to where we have left. We will put our cursor in the B2 cell, and click Data Validation and do the needful.
Now in the Source box, we have put “LastName”, this the name range we have just created above. Before Clicking OK, do not forget to click “Apply these changes to all other cells……..”, as all cells below B2 will get the same Data Validation scope, and you don’t have to do it again for them. Click Ok and you will see a small arrow appearing in B2 if your cursor is still there. Click on that small arrow and you will see it will display all the Last Names from your Table, i.e., last names of your employees only. Now just drag cell B2 downward to as long the list you want to be. Same settings will be applied to all the cells to where you have dragged it. When your cursor will be in any cell of column B, the small arrow will appear.
The Two options in Data Validation
We encountered the above two options in the data validation box, i.e., “Ignore blanks” and “in-cell dropdown”. The latter is easy, if you want a drop-down list to appear with an arrow select this otherwise deselect. It is the “Ignore blanks”, which needs further mentioning.
This checkbox decides if blank cells can be used to bypass the data validation. Meaning, if it encounters a blank cell, should it be checked for data validation, or should the blank cell be ignored and be allowed to be input. Selecting this option allows the data validation to be ignored when it encounters blank cells. This checkbox has nothing to do with removing blanks when you are using a drop-down list.
Coming back to two uses of Data Validation
You have used data validation above for the purpose so that your users should just select from a list and should not enter something else. The second use is that if a user is asked to enter his or her last name, he or she may make a spelling mistake or may not write the first-word capital and may use all capitals, etc. So, you have forbidden your users to enter anything, but a selection from the list. Try entering anything else other than from the list and you will get this message:
You can make this message a custom one, by doing this.
First, we have made our own “Input message” as a Tip, whenever a user is in any cell of column B, this message will appear.
Secondly, you will display a custom message whenever a user enters the wrong data. So, you do this:
When the user will enter something else, he or she will be shown your custom message:
You can use the same Data Validation for all other columns as well. Just another example I will put is the age range and you can well figure out how to do it for different data sources.
The Age range input data
Suppose you have made age range data so that users should select a range according to where they belong.
Put your cursor under Age Range Column and click on Data Validation and do this:
After clicking in the Source box, click the sheet where you have put your age range data, then just select that data with your mouse and the data range will be put in the Source in Data Validation.
Click OK and you will see the same small arrows now appearing under the age range.
What else you can do?
You can also give this age range a name as we have done for Last Names, and used that name range in the Data Validation source.
You can also ask Excel to put the date range automatically according to the date of birth a user will choose, or more interestingly, after the user chooses last and the first name, all other information should have been entered automatically in other fields, like age, date of birth, department, etc. Keep on watching this series and you will learn how easy it is to do this.
Any Questions will be welcomed.