Excel Data Validation Function: How to Use It

Whatever the type of work we carry out in a program like Excel, in most cases it is more than possible that it has some importance. Whether at the home or more business level, these spreadsheets based on numerical data are treated with care. For this, for example, we can use the data validation function that the program presents us.

The first thing to keep in mind in this regard is that here we are talking about a powerful application that is used in all kinds of environments. In this way, it can be of enormous use to us both for domestic accounting and for global accounts of a large company. Everything will depend on how much we want to delve into the program as such.

Excel Data Validation Function

You already know that Excel is part of Microsoft‘s popular office suite, Office , so it is not free software. But with everything and with it, its power and functionality is beyond doubt. In fact, what Excel can offer us depends largely on how much we want to delve into all its functions, which are many. But of course, for all this, the first thing we must do is enter the data as such and with which we are going to work here. For this task, which is not always pleasant, the program presents us with a cell-based interface that facilitates the task.

What is the Excel data validation function

As we enter data in the form of numbers in the program, we place them in such a way so that we can then operate with them. For this, as we have commented on many occasions, the thousands of cells that the program itself offers us in its interface help us . Then in turn we also make use of the elements offered here.

Interfaz de Excel

Among these we can add additional elements such as formulas , images, variables , graphs, texts, etc. But above all this, the most advisable thing is that we have some control. It is also recommended that we carry out this control of the data as the spreadsheet grows. If we save everything for last, the project can turn into chaos.

Therefore, when multiple data variables are hosted in a project, the temporal and spatial grids of those variables must be identical, for example. It is for all this that the user must control the data that other people enter in their own personal project. And it is that in a spreadsheet, especially at the business level, on many occasions there are several people who participate. That’s when you need to dip into the data validation feature.

This allows us to be able to control in some way the changes that others make to our sheet or project. How could it be otherwise, Microsoft Excel itself makes this job easier, since it makes things much easier for us.

How to use data validation for cells

To give us a more concrete idea of what we are talking about, say that the data validation function in Excel actually allows the user to control what can be entered in a cell. At the same time, this functionality is responsible for providing a predefined list of acceptable entries to eliminate possible errors or mistakes by others who also enter data. All of this is applied by rules that we ourselves have to define before others participate in the project . It can be said at the same time that when customizing this data validation function, we must carry out several phases. This is precisely what we will talk about next.

Enter the details of the data validation

At this point, it should be mentioned that one of the most common uses for using the data validation function here is creating a drop-down list . Therefore, the first thing we must do in this regard is to enter the details of the data validation. For all this that we tell you, the first thing we will do is open the program as such, and start the spreadsheet on which we are going to work.

Then with the mouse we will have to select the cells that you want to limit, that is, those in which others are going to enter their own data. It is then when we have to click on the Data section located in the menu at the top of the interface.

menu datos

Once located here, we have to locate the section called Data tools , where the direct access that interests us in this case is located. This is the button called Data Validation, on which we click.

Botón validación datos

At that moment the data validation window will open in which we find several controls. For example, from the Allow section, we can make sure that only numbers are added, or a certain length of text. In this case, we are going to opt for the Time option so that other users enter the hours they have set to work and when they have finished. So here we just have to specify the start time and the end time.

hora Excel

Customize the input message and alert

Once we have indicated the type of data that will be able to be entered in the selected cells, we now go to the Input Message tab. Here, as you can imagine, we have the ability to customize the input message that we want to appear when something other than what is specified is entered.

We can indicate both the title of the warning message and its body. Once we have customized this section, we go to the Error Message tab where we carry out the same steps as before. We also have the possibility to keep the default values here.

mensaje entrada

To finish all this, click on the Accept button. From now on, when one of the cells marked before is selected, users will see the entry reminder message where they can enter the arrival and departure times in our case. But if the entered time is out of range, the specified error message will be displayed.