How to Use Data Validation Feature in Excel

Updated on: January 4, 2023

Maybe you’ve heard about Data Validation as an advanced feature that Microsoft Excel can offer you to sort your information more efficiently. Perhaps the name can give an image of how it works. But do you really know what you can do with this tool? Don't let this question eat away at your mind, keep reading because, in this article, you will learn how to use Data Validation in Excel.

First and foremost, the “Data validation feature” is a special technique that will restrict and limit a user's entry, preventing the worksheet from being edited in a certain way. This process is usually related to protecting a worksheet or locking a cell as well.

You can find an everyday use of Data Validation in a dropdown list. This is because if you share the spreadsheet with someone, you specify the criteria you want them to pick. If you don’t use this feature, you’re basically saying that anyone can add or delete the requirements you made.

Insert Data Validation Feature

Okay, the first thing you need is to locate this aid. To do that, you ought to switch to Data” tab in the principal ribbon panel and look for the “Data Tools” group, which is almost at the end of the panel tools. Once there, you will find a button called “Text to columns.” Next to it, you will find three icons. The third one, represented by a list with a checkmark and an error mark, is the Data Validation feature.


As you can see in the picture, after clicking, it will appear a pop-up dialog box with three options. Each one will perform a different operation. But the first one, also called “Data Validation,” will bring up another dialog window in which you can set what you need.



Once you open the dialog box, you will find two dropdown lists. In the “Allow” one, you can pick any of the following options:

  • Whole Number: restrict the cell to accept only whole numbers (like 1 or 2).
  • Decimal: Limit the cell to accept only decimal numbers (like 1.5).
  • List: Pick data from the determined drop-down list. 
  • Date: Define cell to accept only date. 
  • Time: Specify the cell to accept only time. 
  • Text Length: Restrict the length of the text. 
  • Custom: for the custom formula.

And under the “Data” dropdown, you just need to select a condition according to your needs. The rest of the setting is very intuitive, and you can change or determine what fits best to sort your data.

Input Message 

Notice that when the dialog window is open, you can access other tabs; the second one is the “Input message” one and is basically text you can leave so the readers or users will see it when entering the data.

Note: Of course, you must enable this option by clicking on the “Show input messages when a cell is selected” checkbox. 

Error Alert

The last tab is similar to the second one, but you can create an error alert message in this case. First, of course, you must enable the option marking the checkbox, and then you can choose which “Style” of alert you want to establish; you can pick from “Stop,” “Warning,” or “information” (each one has its own icons). Then you can create a title and write the text.

There you have it. This aid can make your life much easier and help you give your spreadsheet a refined look. So don’t wait any longer. Just try it yourself and customize your features according to whatever you like!

Lorena M. Rodas leverages her experience across film, writing, and production to make complex tech concepts accessible through storytelling. With a background spanning sci-fi, AI, and emerging tech, Lorena translates her depth of knowledge into engaging, educational content. She is an expert at decoding high-level topics to reach broad audiences.
Access 4,242+ video lessons. Instantly.
Start Learning Now →
crossmenu linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram