Excel Data Validation: Learn About Excel’s Data Validation Tool
Without question, Microsoft Excel is the world’s most popular spreadsheet application. It features calculation, VBA, graphing tools, and pivot tables to manage mountains of data. You can also use it to organize, format, and calculate formulas within your spreadsheets’ cells. Excel also provides powerful visuals that summarize and illustrate data insights using charts and graphs.
As expansive as those features may seem, Excel is a pretty straightforward software to learn. Once mastered, you’ll appreciate how much its tools simplify your workload. Take data validation, for instance. If you’re entering data within a spreadsheet and find most of the entries to be repetitive, you can use data validation to create a drop-down list instead of typing out the value each time. Or perhaps you’re catching a lot of invalid responses within the shared workbook and want to prevent certain entries.
If either of those scenarios sounds familiar, keep reading to see how you can use data validation to improve your workflow.
What is data validation?
In simplistic terms, data validation is a tool within Excel that allows users to control what data is permissible in their worksheets. Users can create a drop-down list in a single cell, designate custom rules for what they can enter, and set criteria on restricted entries. For example, one can use data validation to ensure a value contained within a cell is between X and Y number, ensure a text entry is less than 30 characters, or ensure a date occurs within the next 15 days.
Data validation criteria is also useful when you want to display error messages when you or another user inputs invalid data, or if you are looking to present predefined choices within a drop-down menu.
One thing that data validation does an exceptional job of is creating foolproof sheets. By telling Excel what values a user can select, the spreadsheet can be kept clean, precise, and to the point.
There are many use cases for drop-down lists in Excel, such as getting people to complete a form or managing large sets of data.
Where is the data validation option?
Because it’s one of the most commonly used tools within Excel, the data validation prompt is conveniently available on the Excel ribbon. Simply navigate to the Data tab, and click “Data Validation.”
If you’re having trouble finding the data validation tool within Excel’s interface, the image below will give you a better idea of where to find it. If your Excel ribbon is hidden, you won’t be able to see it. All you have to do is click on the tab that says data, and it will expand to give you a full view of the data options, including data validation.
How do I create a drop-down list?
Now that we know where to locate the data validation tool, we can get started with learning how to use it.
First on our list is the drop-down list. As we hinted at earlier in the blog, Excel’s drop-down lists are an excellent way to present a menu of choices within a single cell in a spreadsheet. When you’re dealing with a large amount of data, these drop-down lists will be your key to keeping your spreadsheet clean and organized.
Creating a drop-down list is very simple. Here are the complete steps:
- The first thing you want to do is create a new blank sheet. This is where you’ll enter all the options that you want on your list. You could create this list on the same spreadsheet where the drop-down menu will appear, but having a designated sheet for list values will make for a cleaner, more organized workbook.
- In the blank sheet, add all of the items that you would like to appear in your drop-down list. These should all be entered in a single row or column, and be sure to list them in the order that you expect them to appear in the drop-down menu.
- Once you’ve entered in the complete list, you should select the entire range of cells that contain your entries. So if you entered True and False in cells A2 and A3, select cells A2 and A3. After that, right-click on the selection and select the option to “Define Name.”
- In the “Name” field, enter a name for your list. Most people use a name that is closely related to the entries. For instance, a list of produce might be named Produce. Whatever you decide to label it, be careful not to include any spaces between the characters in the name.
- You should also see a field for ‘Scope.” Select “Workbook” here so that your drop-down list is available to use within all of the sheets in your workbook.
- Now that you have named your list and made it accessible, you can click “OK” to save your settings. Next, you’ll want to navigate to the cell where you want the list to appear.
- Once you have selected a cell, you’ll want to go back to the “Data” ribbon that we talked about earlier and click on “Data Validation.” You should see a prompt that looks similar to the image above.
- Stay on the “Settings” tab and go to the “Allow” menu. Choose the list option so that Excel knows that you want to add a drop-down menu to the selected cell.
- After clicking on the “list” option, a “Source” field should appear. This is where you will enter the name of the list that you created in the fourth step. Enter an “=” sign followed by the list name.
- Finally, click “OK,” highlight any one of the cells. Now when you select the cell that you chose to insert the drop-down list, a drop-down arrow will appear. This is how you pull up your menu to see all of the available options. Simply select a choice from the drop-down list to add it to the cell.
You can use worksheet references to supply values to a drop-down menu. For example, all items within a specific range can be defined as drop-down list options within the data validation settings window. Keep in mind that you will always have to enter ranges as absolute addresses with dollar signs. These characters prevent the range from changing if the data validation runs across other cells in the worksheet.
Consider this to be one of the most important advanced Excel skills for professionals.
What are the different types of validation options?
There are eight different data validation rules.
- Any Value: No validation is needed. This is the default option.
- Whole Number: Only whole numbers are allowed. For example, users can accept any number between 1 and 10 as input.
- Decimal: Only allows decimal values.
- List: Only allows values from a predefined list. This is one of the most common data validation options. You can enter the allowed values using the “Settings” tab under drop-down menu controls.
- Dates: Only dates within a specified range are allowed.
- Time: Only times within a specified range are allowed.
- Text length: Only accepts input based on a certain text length.
- Custom: Allows validation using a custom formula, which can create additional criteria for data validation. For example, custom data validation can require users to input data in all uppercase characters, containing a set character string or a date within the next 30 days.
Depending on the validation criteria, Settings will allow you to set multiple parameters. For example, allowing a Whole Number will prompt you to choose from the following options, followed by a “Maximum” option.
- Between: Will only apply to whole numbers within a set range
- Not between: Will exclude all whole numbers within the specified range
- Equal to: Will only apply to the value listed
- Not equal to: Will exclude the value listed
- Greater than: Will only apply to whole numbers greater than the value listed
- Less than: Will only apply to whole numbers lower than the value listed
- Greater than or equal to: Will apply to the number listed, along with every number with a greater value
- Less than or equal to: Will apply to the number listed, along with every number with a lesser value
What other functions are available in the data validation window?
In addition to the “Settings” tab as an option to enter validation criteria for data in a cell, there are two other tabs for “Input Message” and “Error Alert.”
The “Input Message” tab allows users to display a message when a user selects a cell with data validation applied. It can be as detailed or as vague as you like, informing the user what is allowed or not allowed. Using the “Input Message” tab is completely optional.
The “Error Alert” tab controls what happens when validation does not occur. For example, setting the style to “Stop” prevents the input from being entered in the first place. Once the “Stop” alert window appears, users have the option to Retry or Cancel. Error alerts comprise a style, a title, and an error message.
Other styles like “Information” or “Warning” inform users to enter non-validation values. Warning windows have “Yes,” “No,” and “Cancel” to prompt accepting, rejecting (edit), and canceling the invalid data. Information windows have two options: “OK” (accept) and “Cancel” (remove). It is very easy to set an error message.
Can I use a list on a different sheet to create a drop-down?
If you want to avoid using a named Excel table, create a named range and apply that as a drop-down list source. The source list can be on the same sheet or a different sheet than the drop-down list.
Can I copy and paste drop-down lists in Excel?
Yes, you may copy and paste all data validation cells to other cells. Doing this will carry all of the data validation rules over to the new cell.
To apply a drop-down list from one cell to a range, copy the cell and paste it across the range. This will make the drop-down list available and copy the formatting as well.
All about delimited lists
In addition to referring to an items list on a worksheet, you can type a list directly in the “Source” box. You should separate each item with a comma.
Here is an example.
Excellent, Good, Fair, Poor
There are two caveats with delimited lists.
1) All words are case-sensitive. Typing a word in all CAPS will display an error message.
2) Additional spaces before or after items are accepted. No error message will appear.
Users are also able to type items that do not appear in the drop-down list. Simply go to the Error Alert tab and remove the checkmark to turn it off.
Can I perform data validation using Google Sheets?
Yes, data validation is compatible with Google Sheets. Google Sheets utilizes a more straightforward process than Excel, eliminating the need to name a cell range.
Here are steps you can take to create a data validation drop-down list using Google Sheets.
- Visit a blank or existing worksheet. As with Excel, creating a new sheet helps keep the existing spreadsheet clean and organized.
- Right-click on the cell where you like the drop-down list to appear.
- Select “Data Validation” towards the bottom of the pop-up menu
- Within the dialog box, navigate to the Criteria: field and select Select Data Range
- Go to the worksheet and highlight all of the data that you want to appear in the drop-down list.
- Click OK and Save to close the dialog box.
- The preselected cell will display a drop-down arrow. Click the arrow to view all items in the drop-down list. Users also have the option to type the input, but the input must be identical to how it appears with no misspellings or spacing issues.
- The selected value will populate in the cell.
Data validation is one of the easiest things to learn for most first-time Excel users. Even better, the tool becomes progressively easier over time with practice.
What are some popular data validation rules?
Here are some of the most common data validation rules.
Preventing future dates
Entering the wrong dates on a spreadsheet occurs more often than you think. Data validation allows users to set a date in the past or apply today’s date.
- Open the Data Validation window
- From the ‘Allow’ List, select ‘Date.’
- From the ‘Data’ dropdown, select “Less than or equal to from”
- In the ‘End Date’ box, type in the formula =TODAY()
The ‘TODAY () formula automatically returns today’s date.
Preventing duplicate values
To prevent users from entering duplicate values, perform the following steps.
- Select the range.
- Open the Data Validation window.
- From the ”’Allow” List, select “Custom.”
- In the “Formula” box, enter the following formula – =COUNTIF($A$2:$A$20,A3)=1.
- Optional: visit the Input Message and Error Alert tab if you wish to display an input or error alert message.
This counts the number of cells in the range that are equal to the value in cell A3. Note that an absolute reference ($A$2:$A$20) was created to carry over the formula to other cells.
Set a text length
You may also create validation rules to restrict the number of characters in a cell. Let’s use the steps below to confine text length in a cell to seven characters.
- Open the Data Validation window from the Data tab.
- From the ‘Data’ dropdown, select “equal to.”
- Under the ‘Length’ box, type in the number seven as ‘7.’ This tells Excel that it should not allow more than seven characters within the given cell.
Require specific text for entries
- Open the Data Validation window.
- From the “Allow” list, select “Custom.”
- Enter the following formula: ISNUMBER(FIND(“TEXT”.A2)).
In this formula, the FIND function is what allows you to search for the word “TEXT.” If Excel does not find “TEXT” anywhere in the spreadsheet, then an error appears. The ISNUMBER portion lets users know if the FIND function was successful, returning the value “True” if found and “False” if not found.
All in all, the data validation tool is an excellent way to make your spreadsheets error-free. This makes it easier to pass between different collaborators without the risk of worksheet formulas or cell contents being severely compromised.
Top courses in Excel
Excel students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.