data validation assignment in excel

The Ultimate Guide to Data Validation and Drop-down Lists in Excel

Data validation and drop-down lists are essential features in Excel that help ensure the accuracy, integrity, and efficiency of data entry and analysis. In this ultimate guide , we will explore the concept of data validation, its importance, and the various types of data validation in Excel. We will also delve into the creation of drop-down lists and how they can be enhanced with data validation settings. We will cover advanced techniques for data validation and address common troubleshooting issues related to data validation. By the end of this guide, you will have a comprehensive understanding of data validation and drop-down lists in Excel, empowering you to effectively manage and validate your data with confidence and precision.

What is Data Validation?

Data validation in Excel is a feature that allows users to ensure that the data entered in a cell meets specific criteria. By incorporating data validation, you can effectively prevent errors and maintain the accuracy of the data. It provides the ability to control the allowed data types, set input messages , and display error alerts when necessary.

A practical application of data validation is the creation of drop-down lists , which limits cell entries to predefined options. This feature promotes consistency in the entered data.

A useful tip for data validation is to utilize custom formulas to establish complex validation rules. These formulas enable you to perform data checks based on specific conditions and formulas, granting you greater flexibility and control over the validation process.

Why is Data Validation Important?

Data validation is crucial in Excel for ensuring data accuracy, consistency, and reliability. It helps prevent errors and inconsistencies in data entry, ensuring that the data meets specific criteria and adheres to established rules. By implementing data validation, you can enhance data quality, minimize mistakes, and save time in data analysis and reporting. It helps maintain data integrity and improves data validation issues, such as clearing invalid entries or handling errors effectively.

True story: A company experienced a major mishap when an employee accidentally entered incorrect data into an important financial spreadsheet, resulting in significant financial losses. This incident highlighted the importance of data validation in preventing such errors and ensuring the accuracy of critical data. The company implemented robust data validation procedures and training to prevent similar incidents in the future, emphasizing its significance in maintaining data integrity.

Why is Data Validation Important? Data validation is important because it safeguards data accuracy, consistency, and reliability. It plays a crucial role in preventing errors or inconsistencies during data entry, ensuring that the data meets specific criteria and follows established rules. With data validation, you can enhance the quality of your data, minimize mistakes, and save time in analyzing and reporting data. It also helps maintain data integrity and resolves data validation issues, such as clearing invalid entries and effectively handling errors. The significance of data validation was underscored by a real-life incident at a company, where incorrect data entry led to significant financial losses. Consequently, the company implemented stringent data validation procedures and training to avoid similar incidents in the future, highlighting the importance of maintaining data integrity through data validation.

Types of Data Validation in Excel

Photo Credits: Exceladept.Com by Joseph Perez

Discover the various types of data validation in Excel that can elevate your spreadsheet game to the next level. From input messages to error alerts, cell range validation, and even custom formulas, each sub-section offers invaluable tools to ensure the accuracy and efficiency of your data. Get ready to unlock the power of data validation and create error-free spreadsheets that will impress your colleagues and streamline your work process. Let’s dive in and explore the world of data validation in Excel!

Input Messages

Input messages in Excel data validation serve as valuable tools for providing instructions and guidance to users as they input data into a cell. These input messages can be personalized to effectively communicate specific requirements or restrictions associated with the data entered. By customizing these messages, they can either appear when a user selects a cell or hovers over it, thus ensuring that users receive clear instructions on how to accurately input data. This feature is particularly advantageous when dealing with intricate spreadsheets or collaborating with multiple users on the same document. It plays a crucial role in preventing data entry errors and guaranteeing consistent and precise data input.

Error Alerts

Error alerts are essential in data validation as they contribute significantly to maintaining the accuracy and integrity of data in Excel . Here are some important points to know about error alerts:

• Prompt for data correction: Error alerts display personalized messages when users enter invalid data, guiding them to correct their entries and preserve data integrity.
• Alert types: Excel offers various types of error alerts, including Stop , Warning , and Information . Users can select the appropriate alert type depending on the error’s severity.
• Customization options: Error alerts can be customized by specifying the error message, title, and style to provide clear instructions and enhance the user experience.
• Preventing data discrepancies: By enabling error alerts, users receive alerts when they input data that violates predefined rules, helping to prevent data discrepancies and inaccuracies.

Fun fact: Excel ‘s error alerts can save valuable time and effort by detecting and prompting for data correction, ensuring data accuracy to support improved decision-making.

Cell Range Validation

• Cell range validation is a crucial aspect of data validation in Excel . It allows users to set specific criteria for the values entered in a selected range of cells.
• Here are the steps to implement cell range validation :
• Select the range of cells where you want to apply cell range validation .
• Go to the “Data” tab and click on “Data Validation”.
• In the “Settings” tab, choose “Whole Number”, “Decimal”, “List” or other options based on your validation requirements.
• Set the validation criteria, such as minimum or maximum values, using the available options.
• Specify input messages and error alerts to guide users entering data in the validated range.
• Click “OK” to apply the cell range validation to the selected range of cells.
• By following these steps, you can ensure that the data entered in the specified cell range meets the defined criteria, enhancing the accuracy and reliability of your Excel spreadsheets.

Custom Formulas

When working with data validation in Excel , custom formulas provide advanced control over the validation process. By using custom formulas, you can create specific rules that data needs to follow before being accepted. These custom formulas, which can consist of logical operators , functions , and references to other cells, add flexibility to data validation. For example, you can incorporate custom formulas to ensure that a number entered in a cell is greater than a certain value or that a text entry meets specific criteria . They are a powerful tool in ensuring the accuracy and consistency of your data entries.

Pro-tip: When creating custom formulas for data validation, make sure to thoroughly test them to ensure they correctly validate your data.

Creating Drop-down Lists in Excel

Photo Credits: Exceladept.Com by Lawrence Wright

Ready to level up your Excel game? In this section, we’ll explore the art of creating drop-down lists in Excel. From basic designs to dynamic and dependent options, we’ll cover it all. Discover how data validation settings can enhance your drop-down lists and learn how to adjust options to fit your needs. Get ready to streamline your data entry with this ultimate guide to creating drop-down lists in Excel.

Creating a Basic Drop-down List

Creating a basic drop-down list in Excel is a straightforward process that can significantly improve data entry and organization. To create a basic drop-down list, you can follow these steps:

Start by selecting the cell or range where you want the drop-down list to appear.

Next, navigate to the “Data” tab in the Excel ribbon.

Click on the “Data Validation” button.

A Data Validation dialog box will open. From the “Allow” drop-down menu, choose “List” .

In the “Source” field, enter the desired values for the drop-down list, separating each value with a comma.

Once you have entered the values, click “OK” to close the dialog box.

Now, the selected cell or range will display a drop-down arrow. By clicking on the arrow, you can view the list of values.

By adhering to these steps, you can effortlessly create a basic drop-down list in Excel to streamline your data entry and selection processes.

Adding Dynamic Drop-down Lists

When you want to incorporate dynamic drop-down lists in Excel, it allows for flexibility and automated updating of options based on changing data. To accomplish this, here is a step-by-step guide:

• Select the cell where you want to add the dynamic drop-down list .

Go to the Data tab and click on Data Validation.

• In the Settings tab, choose List as the Validation Criteria .
• Enter the range of cells that contain the options for the drop-down list in the Source field.
• Click OK to apply the drop-down list to the selected cell.
• To make the drop-down list dynamic , utilize a defined range or table as the source of data instead of static cell references.
• Whenever the source data changes, the drop-down list will automatically update to show the new options .

Creating Dependent Drop-down Lists

• To create dependent drop-down lists in Excel , follow these steps :
• Start by creating a list of data for the first drop-down list.
• Next, select the cell where you want the first drop-down list to appear.
• Go to the Data tab and click on Data Validation .
• In the Settings tab, choose List as the validation criteria.
• Then, in the Source box, select the range of cells that contain the data for the first drop-down list .
• Create a new list of data for the second drop-down list . Make sure it is organized in a way that corresponds to the first list .
• Select the cell where the second drop-down list will appear .
• Repeat steps 3-5, but this time, in the Source box, enter the formula “ =INDIRECT ( cell reference of the cell with the first drop-down list)”.

By following these instructions, you can efficiently create dependent drop-down lists in Excel for data entry and analysis .

Enhancing Drop-down Lists with Data Validation Settings

By incorporating data validation settings, you can enhance drop-down lists in Excel and add more functionality and control to the data entry process. Follow the step-by-step guide below for improving drop-down lists:

• Create a drop-down list in Excel using the Data Validation feature, allowing you to customize the options available.
• Specify the range from which the drop-down list will draw its values. You can do this by either directly entering the values or selecting a range of cells.
• Add data validation settings to the drop-down list, giving you the ability to allow or disallow empty values, restrict input to specific values, or set input limits.
• Personalize error alerts to display helpful messages or prompts when users enter invalid data.
• Utilize conditional formatting to highlight or format certain cells based on the selected value from the drop-down list.
• Thoroughly test the drop-down list and data validation settings to ensure that they function correctly as intended.

Adjusting Drop-down List Options

When adjusting drop-down list options in Excel , it is crucial to ensure that the list reflects the desired choices. To do this, follow these steps:

1. Begin by selecting the cell or range where you have applied the drop-down list.

2. Navigate to the Data tab in the Ribbon and click on Data Validation.

3. Once in the Data Validation dialog box, access the Settings tab.

4. Under the “Allow” dropdown menu, choose “List” .

5. In the “Source” field, modify the list values by adding or removing options, keeping them separated by commas.

6. Finally, click on OK to save the changes you have made.

By following these straightforward steps, you can easily adjust the drop-down list options in Excel according to your specific requirements.

Advanced Techniques for Data Validation

Photo Credits: Exceladept.Com by Thomas Jones

Discover the secrets to taking your data validation skills in Excel to the next level! In this section, we’ll dive into advanced techniques that will revolutionize how you handle data validation. From leveraging data from different worksheets or workbooks to using named ranges, we’ll explore the power of harnessing external data sources. We’ll uncover methods for applying data validation to multiple cells and limiting input to specific numeric formats. Get ready to supercharge your Excel game!

Using Data from Another Worksheet or Workbook

Using data from another worksheet or workbook in Excel can greatly enhance the efficiency and accuracy of your data validation process. By incorporating the technique of using data from another worksheet , you can avoid duplicating data and ensure that any changes made in the source worksheet or workbook automatically reflect in the data validation.

Story: A financial analyst, Sarah , was working on a complex Excel model that required data from multiple worksheets. Instead of manually copying and pasting data into her main worksheet, she discovered the power of utilizing data from another worksheet . This technique saved her a significant amount of time and ensured that any updates made in the source worksheet were instantly reflected in her calculations. Thanks to this method, Sarah was able to streamline her workflow and deliver accurate financial reports to her clients on time.

Using Named Ranges in Data Validation

Data validation in Excel includes the use of named ranges , which allows for more efficient and organized validation processes. By using named ranges in data validation, users can simplify the process of assigning validation rules to multiple cells. It enables the user to specify the validation criteria for each named range , making it easier to manage and update the rules as needed. Additionally, using named ranges increases flexibility as users can easily apply the same validation rules to different parts of the worksheet without having to manually update each individual cell. This saves time and reduces the chances of error. Moreover, incorporating named ranges in data validation improves the readability and understandability of the spreadsheet. Instead of having complex formulas in each validation rule, the use of named ranges provides a clear label for the validation criteria, enhancing the overall usability of the worksheet. Furthermore, named ranges streamline maintenance tasks. If changes need to be made to the validation criteria, updating the named range automatically applies the modifications to all cells associated with that particular range. This simplifies the maintenance process and ensures consistency throughout the worksheet.

Applying Data Validation to Multiple Cells

When working with Excel, applying data validation to multiple cells is essential to ensure the accuracy and integrity of your data. Here are the steps to apply data validation to multiple cells :

Select the range of cells where you want to apply data validation .

Go to the “Data” tab and click on “Data Validation.”

In the Data Validation dialog box, set the criteria and validation settings for the cells.

Click on “OK” to apply the data validation to the selected range of cells.

By following these steps, you can efficiently apply data validation to multiple cells in Excel, allowing you to maintain consistency and prevent errors in your data entries.

Limiting Input to Whole Numbers or Decimal Numbers

• To restrict input to whole numbers or decimal numbers in Excel , you can utilize the data validation feature. Here is how you can do it:
• Select the specific cell or range of cells where you wish to implement the data validation.
• Head to the Data tab and click on Data Validation.
• In the Settings tab, select “ Whole Number ” or “ Decimal ” from the Allow dropdown menu, based on your requirements.
• If necessary, set additional criteria like minimum and maximum values.
• Click OK to apply the data validation.

By diligently following these steps, you can ensure that only whole numbers or decimal numbers are inputted in the designated cells or range. This process plays a crucial role in maintaining the accuracy and consistency of data in your Excel spreadsheets.

Troubleshooting Data Validation Issues

Photo Credits: Exceladept.Com by Willie Miller

Having trouble with data validation in Excel? Don’t worry, we’ve got you covered! In this section, we’ll delve into troubleshooting common data validation issues. From clearing data validation rules to handling errors and error alerts, we’ll equip you with the knowledge to overcome any hurdles. Get ready to master the art of dealing with invalid data entries and unlock the full potential of data validation and drop-down lists in Excel. Let’s dive in and find solutions that will streamline your data entry process.

Clearing Data Validation Rules

Clearing data validation rules in Excel is a straightforward process that can help you manage your data effectively. Here are the steps to clear data validation rules:

Select the cell or range of cells where you want to clear the data validation rules.

In the Data Validation dialog box, click on the “Clear All” button.

Click on OK to confirm deleting the data validation rules.

By following these steps, you can easily remove data validation rules from your selected cells or ranges. This allows you to modify and update your data without any restrictions.

In the early days of Excel, there was no built-in feature for clearing data validation rules. Users had to manually delete and adjust the validation settings, which was time-consuming and prone to errors. With advancements in technology and user feedback, Microsoft added the “Clear All” button, simplifying the process and improving the user experience. This enhancement has made working with data validation in Excel more efficient and convenient for users around the world.

Dealing with Invalid Data Entries

Dealing with invalid data entries in Excel is of utmost importance for maintaining precise and dependable data. To effectively manage these invalid entries, it is crucial to follow the steps outlined below:

• Apply data validation rules to restrict the type of data that can be entered in a cell, thus avoiding any invalid entries .
• Enhance user experience by using error alerts to promptly notify users about their invalid data entries. These alerts should include helpful messages and instructions.
• If necessary, you can clear data validation rules from a cell to eliminate any restrictions associated with it.
• For easy identification, make use of conditional formatting to visually highlight any invalid data .
• Implement advanced data cleansing techniques to correct or remove any invalid data from the dataset, ensuring that the data remains accurate and reliable .

By adhering to these steps, you can be confident that your Excel data will remain accurate, reliable, and devoid of any invalid entries.

Handling Errors and Error Alerts

When working with data validation in Excel, handling errors and error alerts is a crucial aspect to guarantee data accuracy. Here are the necessary steps to effectively handle errors and error alerts:

• Identify the type of error: It is essential to determine if the error is a data input error or a formula error.
• Read error message: It is important to understand the error message displayed by Excel in order to identify the issue.
• Correct errors: Make the necessary changes to fix the errors, such as correcting data entries or adjusting formulas.
• Apply error alert settings: Set up error alerts to promptly notify users when incorrect data is entered.
• Customize error messages: Tailor error messages to provide specific instructions or explanations to users.
• Test data entry: Validate data entries to ensure error alerts are triggered when appropriate.
• Update error handling: Continuously review and update error handling processes to enhance data accuracy.

Summary of the Benefits of Data Validation and Drop-down Lists in Excel

– By incorporating data validation and drop-down lists in Excel, you can experience enhanced data accuracy. This means that only valid and accurate data will be entered, minimizing the risk of errors and inconsistencies in your spreadsheets.

– Furthermore, the use of these features can lead to improved efficiency . With drop-down lists, users can choose from a pre-defined list of options, saving time and reducing the chances of making typos or entering incorrect information.

– The process of data entry can also be streamlined with data validation and drop-down lists. These features provide clear prompts and instructions, guiding users regarding the acceptable format and range of values for each cell.

– Additionally, data analysis can be enhanced through the enforcement of data validation rules . By ensuring consistency and validity in your data, you can conduct more accurate and reliable analysis.

– Lastly, data maintenance is simplified when utilizing data validation. Instead of manually updating each cell, you can make changes in a centralized location, resulting in easy update and maintenance of your spreadsheets.

Frequently Asked Questions

1. how do i create a drop-down list in excel.

To create a drop-down list in Excel, follow these steps:

• Select a cell where you want the drop-down list.
• Go to the Data tab and choose Data Validation.
• In the Data Validation dialog box, select the source range for the drop-down list.

2. Can I limit entries and reduce typing mistakes using drop-down lists?

Yes, drop-down lists help organize data and limit the amount of entries people can make to each cell. They significantly reduce input errors and typing mistakes.

3. How can I adjust the appearance of the drop-down list in Excel?

You can adjust the font size and zoom level magnification to make the items in the list appear bigger. This helps create a smooth-looking document and enhances the user experience.

4. Can I add additional items to an existing drop-down list in Excel?

Yes, you can add additional items to the drop-down list by modifying the source range in the Data Validation dialog box. This allows you to expand the options available to users.

5. How do I protect the source data for my drop-down list in Excel?

The worksheet containing the source data can be hidden or password protected to prevent accidental edits or removals. This ensures the integrity of the drop-down list and the order of information.

6. Is it possible to allow user input that is not in the drop-down list?

Yes, you can allow users to enter another item that is not in the drop-down list. In the Data Validation dialog box, uncheck the “Show error alert after invalid data is entered” option to enable this flexibility.

How to Use Data Validation in Excel: Full Tutorial (2024)

Sometimes we have to share our Microsoft Excel files with multiple users to fill in data.

The problem with sharing Excel files is that other people might input the wrong data. Which in turn screws up filtering, lookups, and other types of features and functions.

To prevent this, use data validation!

And I’ll walk you through how to do that here, step-by-step, in this table😊

If you want to tag along, download the practice workbook here .

Table of Contents

What is data validation in Excel?

How to add data validation, types of data validation rules, how to remove data validation.

Excel data validation helps to check input based on validation criteria. 🤔

That means data validation can be used:

• To check if a value is a number, a date, a time, a text with a specified length or
• To show a dropdown menu to the user with set options.

As a result, it can help users enter only valid data into a cell.🤗

You can also show a custom message to the user before entering data into a cell.

You can also display a warning message if the users enter invalid data.

This will simplify data entry and minimize input and typing errors. 😊

Before using Excel’s data validation feature on our table, we shall become familiar with it.👍

• To start, you have to select one or more cells in the Excel file for data validation.
• Then, go to the data tab.
• Click the data validation button, in the Data Tools Group, to open the data validation settings window.

This is how the data validation window will appear.

• The first tab in the data validation window is the settings tab.

You can create rules for data validation in this tab.

For example, we can specify that the date in the first column must be a future date.

In the following part, we will go through all of the data validation rules in depth🤗.

• Go to the input message tab to create an input message.

Put a tick mark for the “Show input message when cell is selected”.

Input messages can be a guide to user input data in the correct format and reduce entering invalid data.

Add a title for the input message.

Enter a custom message for the input message.

When you select a cell with data validation, you will see an input message similar to this.

• Finally, go to the error alert tab to create an error message for invalid data.

When a user enters an invalid entry or invalid value, you can show an error alert.

Put a tick mark next to the “show error alert after invalid data is entered” in the error alert tab.

You can select 3 different styles for the error message when a user inputs invalid data.

Next, give a title to your error alert.

Then, enter a custom error message.

This error message will show up if a student enters a date that has passed.

Let’s apply data validation rules to our Excel data table .

Dates and time validation rules

Date data validation rule.

Students can make an appointment date only for a future date.

Let’s learn how to avoid entering an invalid date in the date column.

• Select “date” from the allow box in the settings tab.

By selecting “date”, restrict data entry types to date in the validation cell.

• Select a condition under the data drop down list and specify data validation criteria in the start date box.

Students can now only enter future appointment dates in the first column of the Excel table.

Time data validation rule

Next, we want to make sure that students enter appointment times between 8 a.m. and 5 p.m.

• Select “Time” as the data validation criteria from the allow box.

By selecting “Time”, all other entries other than time will become invalid entries in data validation cells.

• Select a condition under the data drop down list in the data validation dialog box.

Then enter the data validation rule for the time column.

How to change data validation settings for many cells easily?

No need to copy data validation one by one.

You can check the below box to apply the changed data validation settings to all cells with the same settings.

Number validation rules

Whole number data validation rule.

We want to make sure that no more than three students attend the appointment.

In other words, we need to create an error message as invalid data if the data entry is not a whole number or is more than 3.

Let’s learn how to apply the whole number validation rule.

• Select the “Whole number” as the data validation criteria from the settings tab.
• Select a condition under data in the data validation dialog box.

I select “between” and give 1 & 3 as the minimum and maximum values.

Students can enter only 1, 2, or 3 in column C.

Decimal data validation rule

We would like to get the group results when they are scheduling the appointment.

The results should be between 0% to 100% (i.e., the results should be between 0 to 1).

Let’s learn how to do data validation in excel for decimal values.

• Select “Decimal” as the data validation criteria from the settings tab.
• Select a condition from the list under data and apply validation criteria.

Text validation rule

Do you think we can have data validation in Excel only for numeric values?🤔

No. We can apply for data validation in excel for text length as well.

When the user inputs a text that has a different length to the given data validation that data entry can be recognized as invalid data.

In this table, the group name should be less than 15 letters.

Let’s look at how we can validate the group name.

• Select the “Text length” as the data validation criteria from the settings tab.
• Select a condition for the data validation from the data box.

Drop-down lists

In the last column, students must enter either “Batch 1” or “Batch 2”.

We can give those 2 options in a cell dropdown box.

If the user selects “Batch 1”, it will show in that cell.

Let’s learn how to create a drop down list.

• Select the “List” as the data validation criteria.
• Enter the items for the drop down list in the source box or give a specified range for the drop down list in the source box.

To give a cell reference, click the upside arrow in the source box.

Is it difficult to update drop down list, if the number of options for drop down list are changing every time?😕

Don’t worry.

Give the cell reference of the source box to a Table in Excel .

The table automatically updates your list.😍

With the drop down list, users can easily select the batch.

Since drop down list is a very common data validation feature, it is good to learn this in a very detailed manner.

I highly recommend checking out my drop-down tutorial here to learn all about it!

Now users can enter only valid entries into our Excel table.

Now you know how to apply data validation in excel.👏

Do you want to know how to remove data validation?

It is very simple!🙂

• Select the cells that contain data validation you need to remove.
• Go to the data tab
• Click on the data validation icon.
• Click “Clear All”.

That’s it – Now what?

Now, you can easily add data validation to your cells, making it almost impossible for other users to mess up data input 😊

Pretty great, right?

But messed up data is only a problem if there’s something relying on that data.

Functions like IF, SUMIF, and VLOOKUP rely on data being neat and orderly. And those functions are the backbone of most important spreadsheets.

If you don’t master them yet, click here to learn IF, SUMIF, and VLOOKUP in my free 30-minute online course.

Other resources

If you want to use more advanced date and time functions as validation conditions, read our article about Excel’s Date and Time Functions .

After applying data validation, it is quite simple to narrow down your data to find what is relevant with Excel Filters. Read our How to Filter in Excel article to find out more information about the Excel filter tool.

And not just that! When you have complete data, you can easily rearrange it using Pivot Tables .

11 Awesome Examples of Data Validation

This is a guest post by Alan Murray from Computergaga .

Data Validation is a very useful Excel tool. It often goes unnoticed as Excel users are eager to learn the highs of PivotTables, charts and formulas.

It controls what can be input into a cell, to ensure its accuracy and consistency. A very important job when working with data.

In this blog post we will explore 11 useful examples of what Data validation can do.

To apply these Data Validation rules;

• First select the range of cells you want to apply the validation to.
• Click the Data tab and then the Data Validation button on the Ribbon.
• In the Settings tab, select the validation rule criteria.

Allow Uppercase Entries Only

You may need to ensure that data is entered in uppercase, such as this example of UK postcodes being entered.

The cells need to accept the entry of both text and numbers, but the text must be uppercase.

For this we can use a formula with the UPPER and the EXACT functions.

The UPPER function probably speaks for itself. It converts text into uppercase.

The EXACT function is used to compare the cell entry with the uppercase version to see if they are the same. If they are, then the entry is valid.

For this example, the validation was applied to range A2:A6. Select Custom from the Allow list and enter the following formula into the Formula box.

Prevent Future Dates

Entering dates is very common on a spreadsheet. Unfortunately users entering the wrong date is also commonplace.

By using validation rules, we can limit the mistake a user may make.

In this example, we prevent the entry of a future date. Maybe users are recording a transaction that has occurred. Therefore, it must be a date in the past or todays date.

Select Date from the Allow list and then Less than or equal to from Data.

In the End Date box, type the formula below.

The TODAY function returns the current date from the computer. Incredibly useful. Check out more great Excel date functions (https://www.howtoexcel.org/category/functions/date-and-time/).

Creating Drop Down Lists

Creating drop down lists is the reason most people become familiar with the Data Validation feature. Creating lists is a simple and effective way of controlling data entry.

Select List from the Allow list. You can then either type the list items directly into the Source box separated by a comma, or refer to a range of cells that contain the list items.

When you need a simple list such as Open and Closed, or Yes and No, then typing the entries in makes sense.

When you need a more dynamic list for items that change over time such as lists of products, places and people, then referring to a range makes sense.

For this list, click in the Source box and then go and select the cells that contain the items.

In this example, the items were in range A1:A5 of a sheet called Names.

Dependent Drop Down Lists

Let’s take our drop down lists further and create dependent lists. For these lists, the item selected in one list will affect what options appear in the next list.

In the example below, we have a list of cities in cell F2. The selection from this list affects what names appear in the next list in cell G2.

To achieve this, we first must name each list. For example, range A2:A4 is named city, range B2:B6 is named cardiff and so on.

Follow these steps to create a named range.

• Select the range to name e.g. A2:A4.
• Click in the Name Box to the left of the Formula bar.
• Type the name you would like to apply and press Enter.

The list in cell F2 is created just like in the previous example. In the Source box you can type =city to reference the named range.

For the dependent list in cell G2, the selection in cell F2 needs to be converted into a reference to the named ranges. This is done using the INDIRECT function (Learn more awesome examples of the INDIRECT function).

A common issue when creating dependent lists is the use of illegitimate characters in named ranges. You cannot begin named ranges with a number, or use spaces and some other symbols.

So, if items in your list use spaces, or start with numbers it presents an obstacle. Learn how you can overcome this issue in the video below.

Prevent Duplicate Values

Duplicate values are a very common problem in Excel. There are many techniques for identifying and removing duplicates, but it would be better if you could prevent them in the first place.

By using the COUNTIF function in a custom formula we can.

The formula below counts the occurrences of the inputted value in the range A2:A8. If the answer is 0 then the value is unique and allowed.

Allow only Numeric or Text Entries

The ISNUMBER function can be used to create a validation rule that only allows the entry of numeric values in a cell.

Select Custom from the Allow list and use the formula below. In this example, cell A2 is the upper left cell of the selected range of cells.

This will allow any numeric values only including dates and times.

To allow text values only we could use the ISTEXT function in the same way.

Validate an Entry Based on Another Cell

You can create Data Validation rules that are based on the value from another cell by writing a custom formula.

For example, maybe you only want a drop down list to appear if another cell is not empty.

The following IF function will test if cell A2 is not empty, and if so show the list from the location named range.

Allow the Entry of Weekdays Only

When entering dates, you may need to restrict a user to the entry of specific days of the week. We can achieve this with the WEEKDAY function.

For this example, we shall restrict entry of dates that are Monday to Friday only. The formula below can be used for this.

The WEEKDAY function returns a number that represents the day of the week. In the WEEKDAY function, the 2 specifies that a week starts on Monday as 1 through to the Sunday as 7.

In this validation rule, the returned value must be less than or equal to 5. This excludes Saturdays and Sundays.

Restrict the Text Length

Entries may need to be of a specific text length, or no more than a certain number of characters. Creating a validation rule can help us ensure this.

In this example, I want to confine the text length to exactly 9 characters.

Select Text length from the Allow list, equal to from the Data list and then type 9 for the length.

Entries Contain Specific Text

Using Formulas in validation rules enables us to test and validate for almost anything.

In this last example, we ensure entries contain specific text.

The FIND function is used to search for a specific string of text (“ENG” in this example) and return its position within the cell. If FIND does not locate the text, an error is returned.

The ISNUMBER function is used to check if the FIND function was successful (if it returned a number). If it did, then ISNUMBER returns True, otherwise it returns False. This completes our validation test.

Here is the formula to use.

Note: The FIND function is case sensitive. The SEARCH function can be used instead if you do not need to match the case of the text.

Create Meaningful Error Messages

We have looked at 10 Data Validation examples in this article. However, if a data entry mistake is made the same validation is shown, regardless of the validation criteria.

The great news is that you can create your own error messages to effectively communicate what may have gone wrong to the user.

Click the Error Alert tab of the Data validation window.

Select a Style from the list of Stop, Warning, or Information.

• Stop will prevent invalid data from being entered.
• Warning displays the error, but with the choice to allow the entry or prevent it.
• Information display the error, but does not prevent the data entry at all.

Type a Title and Error Message for the error alert.

Let’s take the example of allowing only the entry of weekdays into a cell. You could create an error message like this.

I believe Data Validation is an undervalued tool of Excel. Without clean data our PivotTables, charts and formulas will not function correctly.

This feature provides a method of limiting mistakes and collecting clean data on entry.

Data Validation cannot help though when receiving data from other databases, workbooks and websites.

If this is something that you do, I heavily encourage you to check out the Power Query feature of Excel . A powerful tool for establishing connections and automating the process of cleaning and transforming data from external sources.

About the Author

Alan is the founder of Computergaga and has a large YouTube following where he shares Excel tips and tutorials. He lives in the UK with his wife and 2 children. His passions for Excel and data keep him busy, but when he has free time he enjoys running and hiking.

Alan Murray

Subscribe for awesome Microsoft Excel videos 😃

John MacDougall

I’m John , and my goal is to help you Excel!

You’ll find a ton of awesome tips , tricks , tutorials , and templates here to help you save time and effort in your work.

• Pivot Table Tips and Tricks You Need to Know
• Everything You Need to Know About Excel Tables
• The Complete Guide to Power Query
• Introduction To Power Query M Code
• The Complete List of Keyboard Shortcuts in Microsoft Excel
• The Complete List of VBA Keyboard Shortcuts in Microsoft Excel

Related Posts

37 Awesome Excel Mouse Tips & Tricks You Should Know

Apr 15, 2019

While the keyboard is generally quicker, you shouldn’t completely ignore the mouse. There are also some great time saving mouse shortcuts as well. In this post we’ll take a look at some of the best Excel mouse time saving tips and tricks.

25 Amazing Power Query Tips and Tricks

Jun 11, 2018

Power query is amazing tool that allows you to import and transform data with ease and helps to create repeatable and robust procedures with your data. Here are some tips and tricks to help you get the most out Power Query.

Amazing Excel Tips and Tricks

May 12, 2018

A compilation of the best Excel tips and tricks ranging from beginner to pro that will save you time and make you more productive.

54 Comments

Nice Compilation Alan………..Thanks for sharing.

Wonderful Job Sir,

How do I enter Data Validation so that a new row iterates to the next row reference? For example so that a Data Validation setting in Cell C38 that references Cell C39 on line 39? For some cells its works and for others it doesn’t increment in the Data Validation Settings/Source field.

How do we disallow entry in dropdown cell and only allow the selection of list item in excel?

I don’t believe this is possible. Potentially with some VBA. But even if they type, it will get validated.

“How do we disallow entry in dropdown cell…?” On the Error Alert tab in the Data Validation window, select the “Stop” Style and add a message that explains what the user must do instead.

Oh, and choose List in the Validation criteria > Allow field.

Data validation is such a cool feature so im always intrin seeing the different ways people can use them.

Absolutely. And so often overlooked to learn sexier tools like PivotTables and charts. But without good data, its over.

Thank you so much for the information = Very Helpful!

How can you set up a Data Validation that will allow specific text but will not allow you to enter a space before or after that text? i.e. they can only enter EXE, PRO, SPEC in the cell – but not EXE , SPEC , PRO.

Thank you for your help! It is greatly appreciated!!!

You can use the list option for this. Either enter those words separated by a comma, or refer to a range of cells where they are written. If you don’t want the list you can uncheck the in-cell dropdown box.

Use this formula in custom option of data validation =ISERR(FIND(” “,E4))

use TRIM() in the validation formula:

=TRIM(cellref)=cellref

Thank you! But I don’t like to use drop down list because they show up so small. I do put the specific words that can be entered… But I’d someone accidently puts a space behind the letters then my formulas in other areas will not pick up the cell information that has a space in it. 😭😭😭😭

Yes, just uncheck the in-cell dropdown and you will not get a list. You can use a formula such as this – A1=TRIM(A1) to prevent spaces before and after. This assumes your selection starts at A1. We then just need it to restrict it to your three words also.

Your other formulas could include the TRIM() function so that even if spaces are wrongly added, those formulas would reas them correctly.

I’m not sure I’m explaining my self correctly. Can you have multiple Data Validations on the same cells? So let’s just say… I have selected cells A1..D20 I have done a Data Validations that only LTR, STR, EXE, PRO can be entered in these cells. Which works. So how can I do a data validation with a TRIM in these same cells A1..D20 so that if they enter a Space behind these letters it will not let them?

If you make a list in Data Validation by referring to a range of cells, and not typing them into the source box. This will also prevent the trailing space scenario. I just wrote your 4 items in cells A1:A4 on a sheet. Named the range items. And then used =items in the source of a Data Validation list. Remember you can turn of the in-cell drop down. You don’t have to have the list. Just this option for your four entrants.

Thank you! THANK YOU! THANK YOU!!!! That takes care of the trailing space… . Now can I am them be entered in with ALL CAPS?

The EXACT function can be used to ensure that text matches case, but I am not sure this can be used with the list functionality. I think this may be too much for Data Validation.

Did you ever get the answer you needed to this question?

HI, I need to use a list of products codes and in a specific cell to return the product code that’s matching that code once I start entering. What can I use? Data validation with a formula..to Search and Find the product code and show it once I start typing? Or something else? I need to limit the typing(entering the codes) to a list (range) and display the code once I start typing. Thanks

Displaying a list whilst typing is not something that Data Validation does as standard. There are tutorials on the web of people using VBA or complex formulas to get an imitation going. A lot of effort for little reward and will also increase Excel’s processing. Hopefully a future version will bring this.

Thanks for the reply.

Another project I am working on, requires me to use a workbook with 3 different columns that can be filtered(with regular filter) and then get final results in the 3 rd column. This is to reduce the drop down list to a narrow one.

In the actual workbook I need to show that final result in a drop down menu. I am thinking of a searchable table with results to be displayed. Is there any way I can do that using simple functions in Excel? An example would be very helpful. Thank you!

Hi Lyl, It sounds like you would like dependent drop down lists. I have a video on setting these up here – https://youtu.be/5nb84p2wX-c

Thank you so much for the information and for sharing your knowledge!

Let’s say I have to create two drop-down menus with one specifying the Start date and another the End date – my dates are listed on my active sheet (eg. 12/31/2014, 12/31/2015 etc.) How can I make sure the End date is always greater than or equal to Start date?

Thank you!!!

Thank you so much for the information and for sharing your knowledge!!

Question: Let’s say I had to create two drop-down menus: one specifying a Start date and the other an End date, with the dates listed on my active sheet (eg. 12/31/2015, 12/31/2016 etc.) How can I make sure the End date is always greater than or equal to the End date?

Many thanks!

A cell can only contain one validation rule if you are using it for a drop down. You could lose the drop down and use the Data Validation for the greater than rule instead. Or if you want to keep the dropdown, maybe use a different technique such as Conditional Formatting. Instead of stopping the user, you could get the cell to change colour if it is greater than or equal to the start date. In Conditional Formatting, if the start date is in A2 and the end date in B2 then the formula could be =B2<A2

I HAVE A PROBLEM THAT I HAVE TWO DIFFERENT TYPES OF DATA. I WANT THAT WHEN ONE TYPE OF DATA APPEARED BY VALIDATION THEN ITS TOTAL SHOULD BE PLACED IN A CELL AND WHEN I CHANGE THE DATA OF SAME CELL BY VALIDATION BY CHOOSING 2ND TYPE OF DATA THEN ITS TOTAL SHOULD BE PLACED IN ANOTHER CELL , NOT PREVIOUS.

Hi Yashraj, a cell can only contain one format type – number or text. Maybe you don’t want validation in the cell. Either way you can control what cell the total is shown in by using formulas in those cells. Depending on your scenario a simple IF may suffice to test the validation cell and run the formula or not.

I found “Validate An Entry Based On Another Cell” particularly useful, Alan, and immediately applied it to a current project using Table names in this formula: =IF(ISNUMBER(E6),INDIRECT(“Table_Title_OBJECT_TYPE[System ID]”),INDIRECT(“Table_Title_OBJECT_TYPE[Hierarchy]”)).

This allows users to pick from a list (Hierarchy) or to enter the Hierarchy’s ID number if they know it already.

Here’s a variation using Named Ranges: =IF(G2=”HH”,HH,IF(G2=”NS”,NS,IF(G2=”Bot”,Bot)))

Thanks for the tip!

You’re welcome Philip. Thank you.

Lets say I hard code 1,0 while creating a drop down, and change the format to display the result as YES or NO. How do I apply this format to the drop down list as well, and not just the result?

The drop down list content cannot be formatted, just the result of that content by formatting the cell that the drop down is in.

Its Awesome – Very well explained – I managed to clear lot of doubts – Thank you

You’re very welcome Asoka. Happy to help.

Hello, In your “Prevent Duplicates” section, for a value to be unique, the COUNTIF formula must return 1, not zero. That’s how it works in my Excel. If I put zero, any entry is refused.

Quite right Patrick. I’ll get it changed.

is there a way I can enter only specific 2 to 3 words and dates in a cell, eg if an activity is completed then date of completion, if activity is pending than “Pending” if issue “Issue” apart from these any entry should not be possible

Sure Varaprasad. The three options would be your classic drop down list. For the date of completion, you could make this cell dependent upon completion being entered in another cell if you wanted. An example of a rule based on another cells value is shown in the tutorial.

Hi Alan, Is there a way to combine a drop down list together with another validation such as Prevent duplicate values?

No sorry, not within the Data Validation rules. You would have to look at using a macro for this validation check on top of the drop down list one.

Thanks Alan.

I have a little complex problem.

I have two tables T1 and T2, and both have common columns say “Name” and “City”. Now, how to add a data validation list to T2.Name that is filtered with T1.Name where T2.City = T1.City.

Basically I want to filter the Names drop down in the T2.Name only with the T1.Names from the same City.

Thanks in advance.

My pleasure. This sounds like a lookup formula instead of a Data Validation list. If it is filtered by T1 for one city, you wouldn’t need a list. VLOOKUP will do the job.

Good to see the reply Alan.

Certainly I need a data validation drop down list, just that it has to be filtered based on the current table’s City (common column between 2 tables) value in the lookup table.

So for T2 Name column dropdown to be filtered and show only those Names who live in that city.

To up the complexity a little bit😀 this filtered, relevant list should be ‘distinct’ Names

Oh ok. Are you using 365 because if you are, with the new UNIQUE and FILTER functions this task is easy. If not, PivotTables could be used to create the unique city lists and dependent drop downs can be created from them.

how to put the validation for Mandatory columns does anyone know..

You are probably better off doing this in VBA. Data Validation will only work when someone attempts entry to a cell. VBA could be used to check cell values (ensure they have been used) prior to submitting, saving, printing or whatever the action may be.

Hi Alan. Thanks for this nice post. I have specific query. I have used data validation input message option to add comments to some specific cell in excel. However, there is no indication that the particular cell has some message. Its only when I click on the cell, I get to see the message. In the case when we add a comment for a cell, there is a small red arrow in the cell corner which indicates that a comment is entered for the cell. Is there some similar kind of arrangement that we can do for data validation message.

Thanks –Ashutosh

Hi Ashutosh, there is not a setting for this in Data Validation. Sorry.

Hi Alan, Is there a way to create a drop down list that contains a data name and other text values? For instance, want a drop down list of Year(Today()) – 1, Year(Today()), None, Both. Thank you!

Not sure exactly what you mean Cal. But looking at your example, I would put those formula into a cell somewhere along with the text values. And select them for the DV list.

Hi can someone help with a formula in excel that ensures that cell can only be 9 characters long. And the following must show if the entry does not meet the requirements: a stop symbol and an error message that says: the text lenght must be 9 characters

Hi Elizna, this is shown in the tutorial. There is an example for “Restrict the Text Length” and then one for “Create Meaninging Error Messages”.

Get the Latest Microsoft Excel Tips

Follow us to stay up to date with the latest in Microsoft Excel!

How-To Geek

How to restrict data input in excel with data validation.

If you use Excel spreadsheets to collect data from other people, but find that they often fill your carefully-planned cells with the wrong kind of information, data validation can help.

This tool lets you restrict specific cells to only allow properly-formatted data. If someone enters anything that’s not supposed to be there---like “lunch at airport” instead of “$15.68” on an expense report---Excel rejects the input until they get it right. Think of it as a passive-aggressive way to make sure people don’t waste your time.

As an example, here’s the basic expense report worksheet for How-To Geek. Let's say we want to make sure that people only enter numerical values that are formatted as currency (i.e., some digits, followed by a decimal point, followed by two more digits) into certain cells.

First, select all the cells you want to restrict.

Switch over to the "Data" tab on the Ribbon, and then click the “Data Validation” button. If your window isn't full size and you can’t see the labels, it’s the icon with two horizontal boxes, a green check mark, and a red crossed circle.

In the Data Validation window, on the "Settings" tab, click the “Allow” dropdown menu. Here, you can set a specific type of input to allow for your selected cells. For our expense report, we’re going to insist that users put in a number with two decimal values, so we would select the “Decimal” option. You can also select other criteria, like making sure a cell contains text, a time or date, text of a specific length, or even your own custom validation.

Whatever type of data you select on the “Allow” dropdown changes the options available to you on the rest of the "Settings" tab. Since we want a numerical value corresponding to currency, we're setting the "Data" dropdown to the “between” setting. Then, we're configuring a minimum value of 0.00 and a maximum value of 10000.00, which is way more than enough to cover our needs.

To test it our, click “OK” to apply the validation settings, and then try putting in an improper value. For example, if we type “pancakes” for the Breakfast value instead of the cost of the meal, we'll get an error message.

While that does restrict people to entering only the correct type of data, it doesn't give them any feedback on what type of data is required. So, let's set that up, too.

Head back to the Data Validation window (Data > Data Validation on the Ribbon). You've got two options here (and you can use both of them if you want). You can use the "Input Message" tab to have a pop-up tool tip show people the type of data you want whenever they select a cell for which data validation is turned on. You can also use the "Error Alert" tab to customize the error they see when they enter the wrong type of data.

Let's switch over to the "Input Message" tab first. Here, make sure the "Show input message when cell is selected" option is turned on. Then, give your input tooltip a title and some text. As you can see below, just clicking in one of the cells pops up the message letting people know what's expected.

On the "Error Alert" tab, you can customize the error message people see when they enter the wrong type of data. Make sure the "Show error alert after invalid data is entered" option is turned on. Pick a style for your error message from the "Style" dropdown. You can go with a Stop (the red circle with the X), Warning (yellow triangle with an exclamation point), or Information (blue circle with a lowercase "i"), depending on how strong you want the message to come across.

Type a title for your message, the text of the message itself, and then hit "OK" to finish up.

Now, if someone tries to enter improper data, that error message is a little more helpful (or sarcastic, if you prefer).

It's a bit of extra legwork setting up data validation, but it can save you a lot of time later on if you use spreadsheets for collecting data from other people. It's even useful for preventing your own mistakes. And this is doubly true if you’ve set up formulas or any kind of automation tasks that rely on that data.

Data validation with conditional list

Related functions .

To allow a user to switch between two or more lists, you can use the IF function to test for a value and conditionally return a list of values based on the result. In the example shown, the data validation applied to C4 is:

This allows a user to select a city from a short list of options by default, but also provides an easy way to view and select a city from a longer list of cities.

Note: I ran into this formula and approach on the excellent Chandoo site.

Generic formula

Explanation .

Data validation rules are triggered when a user adds or changes a cell value. This formula takes advantage of this behavior to provide a clever way for the user to switch between a short list of cities and a longer list of cities. In the worksheet shown, the data validation applied to C4 looks like this:

The IF function is configured to test the value in cell C4. When C4 contains the text "See full list", IF returns the named range long_list. When C4 is empty or contains any other value IF returns the named range short_list.

The user starts with the values in E6:E13 as seen below:

When the user selects "See full list", they can select from the longer list of cities in G6:G35:

The named ranges used in the formula are not required, but they make the formula easier to read. If you are new to named ranges, this page provides a good overview .

Dependent dropdown lists

Expanding on the example above, you can create multiple dependent dropdown lists. For example, a user selects an item type of "fruit", so they next see a list of fruits to select. If they first select "vegetable" they then see a list of vegetables. Click the image below for instructions and examples:

Related functions

• IF Function

The Excel IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical...

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related Information

• Excel Data Validation Guide

Get Training

Quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

Add, change, or remove data validation

Data validation is the ability to automatically check for errors while the user fills out a form. By adding data validation to controls in your form template, you can ensure that the data that you collect is accurate and consistent, and that it conforms to any standards that are already in use by your company. For example, you can use data validation to let users know when the amount that they enter for an expense item exceeds the approved amount, or when they mistakenly enter their name in a box that is used for collecting phone numbers.

If a form contains data validation errors, and it is connected to a database or Web service, users won't be able to submit the form until they fix these errors. Users can save a local copy of the form, and then correct and submit the data later.

In this article

Ways that users are notified about validation errors, compatibility considerations, add data validation, change a data validation condition, remove data validation.

For a form that is designed to be filled out by using InfoPath, you can set a data validation message to be displayed as a ScreenTip. After viewing the ScreenTip, your users can optionally display a detailed alert that you provide. Alternatively, you can set the detailed alert to be displayed automatically. For example, if you specify an error condition that you want to occur if a user types their name into a box that is used for collecting phone numbers, you can enable a ScreenTip to display the message "Type a telephone number in this field." If you enable an alert to appear automatically, you can show a detailed message by default, such as "This field requires a telephone number in the format (555) 555-0100." If you choose to display a ScreenTip by default, your users can manually display the alert that you provide by right-clicking the field in InfoPath, and then clicking the option to show the alert.

1. The pointer changes to an I-beam when you rest it over the field, and the ScreenTip is displayed.

2. When you right-click the field, a shortcut menu appears — and when you point to Full error description , the pointer changes to an arrow.

3. When you click Full error description , the alert appears.

For a form that is filled out in a Web browser, alerts cannot be displayed automatically. However, users can display the alert by clicking in the field on the Web page that contains the ScreenTip, and then clicking the link that appears.

2. The pointer changes to a hand when you move it to the ScreenTip.

3. When you click the link, the alert appears.

Top of Page

When you design a form template in InfoPath, you can choose a specific compatibility mode to design a browser-compatible form template. When a browser-compatible form template is published to a server running InfoPath Forms Services, and then browser-enabled, forms based on the form template can be viewed in a Web browser. When you design a browser-compatible form template, some controls are unavailable in the Controls task pane because they cannot be displayed in a Web browser.

Some data validation features work differently in a Web browser than they do in InfoPath. For example, when you add data validation to a control, you create explanatory text to be displayed when a user enters invalid data into that control. You can have this explanatory text appear in a ScreenTip, and optionally allow users to display an alert that contains additional information, or you can have the alert appear automatically when a user enters invalid data. Alerts cannot be displayed automatically for forms that are viewed in a Web browser, but users can still view the ScreenTip, and optionally display an alert containing additional information.

Note:  When users fill out forms by using a browser, they can quickly display a data validation alert by pressing CTRL+SHIFT+I.

List of controls that support data validation

The following table lists the Microsoft Office InfoPath 2007 controls that support data validation and whether they are available for browser-compatible form templates.

Click the control that you want to add data validation to.

On the Format menu, click Data Validation .

In the Data Validation dialog box, click Add .

Under If this condition is true , add a condition.

The following example shows how to create a data validation condition to ensure that data typed into a text box control matches a specific value — in this case, the word Hello.

In the first box, click Select a field or group , and then select the field or group that the control is bound to.

In the second box, click is not equal to .

In the third box, click Type text , and then type Hello .

Note:  To require users to enter data into the control, in the Control Properties dialog box, select the Cannot be blank check box.

To automatically show a dialog box message when a user leaves a control blank, select the Show dialog box messages immediately when users enter invalid data check box.

Note:  Because dialog box messages cannot be displayed automatically in a Web browser, a user who fills out your form by using a Web browser will see only the ScreenTip.

In the ScreenTip box, type the text that you want to display when a user points to the control or right-clicks the control.

In the Message box, type the text that you want to display in the message dialog box either immediately or when the user requests more details.

Note:  The ScreenTip for a data validation error will not display for controls that also have a default ScreenTip set on the Advanced tab of the Control Properties dialog box. This behavior helps to ensure that the name of the control is accurately conveyed to users who are using screen readers.

Click the control whose data validation you want to modify.

In the Data Validation dialog box, click the condition that you want to change, click Modify , and then make the changes that you want.

Click the control whose data validation you want to remove.

In the Data Validation dialog box, click the condition that you want to remove, and then click Remove .

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

How to use Excel Table within a data validation list (4 easy ways)

Excel Tables expand automatically whenever new data is added. This feature alone makes Tables one of the most powerful tools within the Excel user’s toolkit. A Table can be used as the source data for a chart and within a named range, both of which benefit from the auto-expand feature. Data validation lists would also benefit from the auto-expand feature, but they just don’t work correctly—an oversight on Microsoft’s part (in my opinion).

In this post, we examine the issues and solutions for creating an Excel data validation list from a Table.

Example data

The problem, normal cell references over a table, problems with this approach, indirect function, warning about this method, named range of the table column, create the named range, add the named range to the data validation list, key points about this method, dynamic array spill range, create a spill range, use the spill range in the data validation list, which method to choose.

Download the example file:  Join the free Insiders Program and gain access to the example file used for this post.

File name: 0076 Data Validation with Tables.zip

The examples in this post use the following data:

The Table name is myList (I know, it’s not a great name, but it will work for the examples).

The following data validation settings look like they should work. It should use the column called Animals from a Table called myList .

Instead of giving us an Excel data validation list from the Table, Excel shows an error message.

What can you use to solve this problem? I’ve got four solutions for you:

Let’s look at each of these and you can decide which is the best option for your situation.

Normally, if we use standard cell references, the data validation list is static. Each time a new item is added to the list either the data validation source will need to be updated, or the new items will need to be added within the existing list range.

However, if we create the data validation list using normal cell references, which also happen to be all the rows from a Table column, it will expand.

Look at the screenshot below. The cells $A$2:$A$7 are exactly the same cells as the reference myList[Animals] would be for a Table.

It looks like it would behave like a standard static list. But somehow Excel knows.

If we add data to the bottom of the Table, the data validation list expands when values are added to the Table. Try it for yourself.

Now, don’t get too excited. There is a big problem with this approach:

• If the data validation list is on a separate sheet to the Table, it does not expand.
• This method only works if the cells selected include the entire Table column (i.e., in our example, if you reference $A$4:$A$7 it will not expand when a new item is added as it is not the whole Table column).

Due to the issues noted above, it is probably best to avoid this method.

The INDIRECT function converts text into a Range that Excel recognizes and can use in calculations. For example, if Cell A2 contains the text “ A1 ” you can write =INDIRECT(A2) and Excel converts that to the cell reference =A1 .

We can use the INDIRECT function with the Table and column reference as text and Excel will understand this.

The formula used in the screenshot above is.

If the list only has one column, it is possible to refer to just the Table.

The range for the data validation list now expands whenever new items are added to the Table.

With this method, there is one key thing to be aware of; the Table and column names are hardcoded into the INDIRECT formula. If either of these change, the data validation list will cease to work as the formula cannot find the source data.

INDIRECT is normally considered a volatile function, which, in some circumstances, can cause slow calculations in Excel workbooks.

However, data validation lists are only recalculated when we interact with the cell. Therefore, INDIRECT does not perform a volatile function in this context.

Named ranges accept a Table column as the source, and data validation lists accept a named range. Therefore, we can take a two-step approach to achieve the desired result.

To create the named range, click Formulas > Define Name

The New Name dialog box opens.

Give the named range a name ( myDVList in the example below) and set the Refers to box to the name of the Table and column.

Click OK . The named range has been created.

As with the example above, if the Table only has one column, we can refer to just the Table without the column name.

The screenshot below shows the named range added to the data validation list box.

The data validation list now expands whenever new items are added to the Table.

In relation to this method, there are a few things to be aware of:

• This is a two-stage process, which takes a little longer to set up.
• If the Table or Column names change the named range automatically updates to reflect the changes.

While it takes longer to set up, this method is harder to break.

We are about to get technical for a moment, but don’t worry applying this method is very easy, so stick with me.

Excel 2021 and Excel 365 have a feature known as dynamic arrays. These allow a formula to return multiple results and to spill the results across multiple cells; this is known as a spill range .

If the number of rows returned by the formula changes, the rows and columns in the spill range also change.

We use the # sign (also known as the spill operator) to refer to the cells in the spill range.

Here is the key part… data validation lists accept the spill range as a valid range reference. So this means we can create a two-step process:

• Use the spill range in a data validation list

To create the spill range on a sheet, we just need to refer to the Table and column.

The formula in cell F2 is:

Since the Table column has multiple cells, those values spill down into cells F2:F7 . This can be seen by the blue box which highlights the spill range.

If the number of items in the Table changes, the spill range of the Table changes.

Just as for previous methods, if the Table only contains a single column, we could refer to just the Table name of myList, instead of the myList[Animals].

To get the spill range into the data validation list, we refer to the first cell in the range, followed by # .

In the screenshot above, we are using cell F2 and its spill range as the source for the data validation list.

If the data validation list refers to the range on another worksheet, the sheet name must also be included in the source, such as:

• If the Table or Column names change, the formulas update automatically to reflect the changes.

So, with four options to choose from, the question is which is the best?

I don’t advise using the standard cell references over a table as it won’t work across worksheets. And I avoid the INDIRECT method as it can easily break if renaming Tables or columns.

I like the named range and spill range options. While they take a few extra seconds, they are easy to set up and less likely to break if changes occur.

We cannot create an Excel data validation list directly from a Table. However, in this post, we have seen four methods that we can use to achieve the same result.

Related Posts:

• Don’t trust data validation in Excel!
• How to loop through each item in Data Validation list with VBA
• How to use Table slicers for advanced interactivity in Excel

Discover how you can automate your work with our Excel courses and tools.

Excel Academy The complete program for saving time by automating Excel.

Excel Automation Secrets Discover the 7-step framework for automating Excel.

Office Scripts: Automate Excel Everywhere Start using Office Scripts and Power Automate to automate Excel in new ways.

Leave a Comment Cancel reply

• Ablebits blog

How to make a dynamic dependent dropdown list in Excel an easy way

The tutorial shows how to create an Excel drop down list depending on another cell by using new dynamic array functions.

Creating a simple drop down list in Excel is easy. Making a multi-level cascading drop-down has always been a challenge. The above linked tutorial describes four different approaches, each including a crazy number of steps, a bunch of different formulas, and a handful of limitations relating to multi-word entries, blank cells, etc.

That was the bad news. The good news is that those methods were designed for pre-dynamic versions of Excel. The introduction of dynamic arrays in Excel 365 has changed everything! With new dynamic array functions, creating a multiple dependent drop-down list is a matter of minutes, if not seconds. No tricks, no caveats, no nonsense. Only fast, straightforward and easy-to-follow solutions.

• This new dynamic array way of making dropdown lists only works in Excel 365 and Excel 2021. In pre-dynamic Excel, you will have to do it the long old-fashioned way as described in Creating a dependent drop down in Excel 2019 - 2007 .
• This solution is for a single row. If you want to copy your picklists down multiple rows , then follow the instructions in Dependent drop-down list for multiple rows .

How to make dynamic drop down list in Excel

This example demonstrates the general approach to creating a cascading drop down list in Excel by using the new dynamic array functions .

To create a dynamic dependent drop down list in Excel, carry out these steps:

1. Get items for the main drop down list

For starters, we shall extract all different fruit names from column A. This can be done by using the UNIQUE function in its simplest form - supply the fruit list for the first argument ( array ) and omit the remaining optional arguments as their defaults work just fine for us:

=UNIQUE(A3:A15)

2. Create the main drop down

To make your primary drop-down list, configure an Excel Data Validation rule in this way:

• Select a cell in which you want the dropdown to appear (D3 in our case).
• On the Data tab, in the Data Tools group, click Data Validation .
• Under Allow , select List .
• Click OK to close the dialog.

3. Get items for the dependent drop down list

To get entries for the secondary dropdown menu, we'll filter the values in column B based on the value selected in the first dropdown. This can be done with the help of another dynamic array function called FILTER :

=FILTER(B3:B15, A3:A15=D3)

Where B3:B15 are the source data for your dependent drop down, A3:A15 are the source data for your main dropdown, and D3 is the main dropdown cell.

4. Make the dependent drop down

Tips and notes:

• To have the new entries included in the drop-down list automatically , format your source data as an Excel table. Or you can include a few blank cells in your formulas as demonstrated in this example .
• If your original data contains any gaps, you can filter out blanks by using this solution .
• To alphabetically sort a dropdown's items, wrap your formulas in the SORT function as explained in this example .

How to create multiple dependent drop down list in Excel

In the previous example, we made a drop down list depending on another cell. But what if you need a multi-level hierarchy, i.e. a 3 rd dropdown depending in the 2 nd list, or even a 4 th dropdown depending on the 3 rd list. Is that possible? Yes, you can set up any number of dependent lists (a reasonable number, of course :).

To make a multiple dependent drop down list in Excel, this is what you need to do:

1. Set up the first drop down

The main dropdown list is created with exact the same steps as in the previous example (please see steps 1 and 2 above). The only difference is the spill range reference you enter in the Source box.

2. Configure the second drop down

As you may have noticed, now column B contains multiple occurrences of the same exporters. But you want only unique names in your dropdown list, right? To leave out all duplicate occurrences, wrap the UNIQUE function around your FILTER formula, and enter this updated formula in F8:

=UNIQUE(FILTER(B3:B15, A3:A15=E3))

Where B3:B15 are the source data for the second drop down, A3:A15 are the source data for the first dropdown, and E3 is the first dropdown cell.

3. Set up the third drop down

To gather the items for the 3 rd drop down list, make use of the FILTER formula with multiple criteria . The first criterion checks the entire fruit list against the value selected in the 1 st dropdown (A3:A15=E3) while the second criterion tests the list of exporters against the selection in the 2 nd dropdown (B3:B15=F3). The complete formula goes to G8:

=FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3))

If you are going to add more dependent dropdowns (4 th , 5 th , etc.), then most likely column C will contain multiple occurrences of the same item. To prevent duplicates from getting into the preparation table, and consequently in the 3rd dropdown, nest the FILTER formula in the UNIQUE function like we did in the previous step:

=UNIQUE(FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3)))

Tip. In a similar manner, you can get items for subsequent drop-downs . Assuming column D contains the source data for your 4 th dropdown list, you can enter the following formula in H8 to retrieve the corresponding items:

How to make an expandable drop down list in Excel

After creating a dropdown, your first concern may be as to what happens when you add new items to the source data. Will the dropdown list update automatically? If your original data is formatted as Excel table , then yes, a dynamic drop down list discussed in the previous examples will expand automatically without any effort on your side because Excel tables are expandable by their nature.

If for some reason using an Excel table is not an option, you can make your dropdown list expandable in this way:

• To include new data automatically as it is added to the source list, add a few extra cells to the arrays referenced in your formulas.
• To exclude blank cells , configure the formulas to ignore empty cells until they get filled.

Keeping these two points in mind, let's fine-tune the formulas in our data preparation table. The Data Validation rules do not require any adjustments at all.

Formula for the main dropdown

With the fruit names in A3:A15, we add 5 extra cells to the array to cater for possible new entries. Additionally, we embed the FILTER function into UNIQUE to extract unique values without blanks .

Given the above, the formula in G3 takes this shape:

Formula for the dependent dropdown

The formula in G3 does not need much tweaking - just extend the arrays with a few more cells:

=FILTER(B3:B20, A3:A20=D3)

How to sort drop down list alphabetically

Want to arrange your dropdown list alphabetically without resorting the source data? The new dynamic Excel has a special function for this too! In your data preparation table, simply wrap the SORT function around your existing formulas.

The data validation rules are configured exactly as described in the previous examples.

To sort from A to Z

Since the ascending sort order is the default option, you can just nest your existing formulas in the array argument of SORT, omitting all other arguments which are optional.

For the main dropdown (the formula in G3):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))

For the dependent dropdown (the formula in H3):

=SORT(FILTER(B3:B20, A3:A20=D3))

To sort from Z to A

To sort in descending order, you need to set the 3 rd argument ( sort_order ) of the SORT function to -1.

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)

=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)

Tip. Another fast and easy way to enter information in Excel spreadsheets is a data entry form .

That's how to create dynamic drop down list in Excel with the help of the new dynamic array functions. Unlike the traditional methods, this approach works perfectly for single and multi-word entries and takes care of any blank cells. Thank you for reading and hope to see you on our blog next week!

Practice workbook for download

You may also be interested in.

• Data validation in Excel with custom rules and formulas
• How to create dropdown list in Excel
• Create a multiple dependent drop down list in Excel 365 - 2010
• How to make multi-select drop down list in Excel
• How to make a drop down with images in Excel 365
• How to edit, copy and delete drop down list in Excel

Table of contents

143 comments

Hi, What happens if you have multiple dynamic dropdowns, Many rows where you choose different fruits and then need the exporters available in column E D3 has Apricot and the choices are available as expected in E3 - data comes from $F$8# D4 has Orange but the choices available in E4, are still for Apricot, as the Exporter list is still referring to the Fruit in D3. $F$8#

Hi! Try carefully using the instructions in the third section of this article: How to make an expandable dropdown list in Excel.

To use the Filter function but it links to another worksheet

Hi! You can use an external reference to another worksheet in the FILTER function . Suppose you have data in Sheet2 (A1:B10) and want to filter rows where the value in column A is greater than 50. You can use the following formula in a cell on your current sheet:

=FILTER(Sheet2!A1:B10, Sheet2!A1:A10>50)

I recommend reading this guide: Excel reference to another sheet or workbook (external reference) .

Thank you, but my excel is in new version and it does not have filter and unique formula, can you please help?

Hi! The FILTER function is available in Excel for Microsoft 365, Excel 2021, Excel 2019. The UNIQUE function is available in Excel for Microsoft 365, Excel 2021.

I have question about first section : How to make dynamic drop down list in Excel. D3 is your main dropdown cell. What if i want to have dropdown cell in another rows (D4, D5.... ) with same filter options and sorce data?

Hi! You can find the answer to your question in this article: How to copy drop down list in Excel .

Hi! The quoted article explains how to extend the main dropdown to further rows. Is there a way to do the same with the dependent dropdown?

That is, with the example of this article, make every cell in the E column have a dropdown that depends on the value of the cell in the D column of the corresponding row.

Hi! You can make a dynamic dependent dropdown list yourself by using these instructions: Set up dependent drop-down list for multiple rows in Excel .

Hello, I have a list of 5 criteria questions that will be selected and based on those answer I want to feedback to be the top 3 choices based on the questions the selected. How would I write the formula, I am thinking we will need to assign a value on the answer they selected before selecting the top 3 options to select from

Hi! Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.

Thank you for this! This was very helpful and worked in my first three drop down. We could finally automate our reports. However, I have 4 drop down that needs to be made. What would be the formula if I need 4 drop down? Thank you.

Hi! The code works for all drop-down lists on your worksheet. Unless you specify a range of cells in which it can work. This is explained in the article above.

I followed these steps almost excatly and was so excited I finally found a way to get my drop dowans to work I got the first 2 drop downs to work but then I keep getting a calc error on the third -.-.

Source Data Table: Column 1 = A1000 - A1329 Column 2 = B1000 - B1329 Column 3 = C1000 - C1329

Prep Table: First drop down in cell F1000 reads - =UNIQUE(A1000:A1329) Second drop down in cell G1000 reads =UNIQUE(FILTER(B1000:B1329,A1000:A1329=L1000) Third drop down, dysplaying #CALC!, in cell H000 reads =UNIQUE(FILTER(C1000:C1329,(A1000:A1329=L1000)*(B1000:B1329=M1000)))

Dynamic Table: Cell L1000 drop down works (data validation reads =$F$1000#) Cell M1000 drop down works (data validation reads =$G$1000#) Cell N1000 drop down does not work and displays the #CALC! error (data validation reads =$H$1000#)

Im not sure if you can help provide all these details but im not sure what im doing wrong.

Hopefully someone can help, thank you in advance this guide is great!

Hi! Sorry, I can't recommend a formula to you as I can't see your data.

I am trying to create a drop down of product names, but each name has three or four specific fields of dependent data to that product name that I want to come over as it is selected from the drop down. Once an item is selected from the drop down, a calculator will reference that data to come up with a specific number. Is that possible?

Hi! A drop-down list creates a standard text string. No references or data fields are possible. With the selected value, you can then use a formula to retrieve the data associated with it. For example, using VLOOKUP or INDEX+MATCH . I hope I've understood what you're asking.

Is there a way to be able to use our dynamic lists for each row?

Basically what I am wanting to do is the Value of cells in Column D depend on value selected in Column C - for each row.

Hi! All the necessary information is in the article above.

Say I am tracking equipment on a large construction project that includes 4 different construction sites where equipment will be either stored or utilized. Each construction site will have its own area on an excel sheet (all 4 on the same sheet for better visualization when determining equipment needs). Using source data, I create 4 identical lists of available equipment for the project, one for each site. Is it possible to have parameters in each list such that when I select a piece of equipment that is currently in site #1, that same piece of equipment would not be show up in the same list in site #2 (or site #3 and #4, for that matter) for selection, until that piece of equipment is de-selected? I hope that makes sense! My main concern is eliminating the potential of showing the same piece of equipment in two different sites. Thank you!

Hi! You're asking beyond the advice this blog can provide. Solving this complex problem requires more than a single formula. If you have a specific question about how a function or a formula works, I'll do my best to help you.

I have three dependent values similar to Fruit, Exporters and State/Province. In the above example, there are 3 sections like the Source data, preparation table and the Dynamic drop-down area. in the Dynamic drop-down area, currently only one row and their drop-down results are shown. I have a scenario where the Dynamic drop-down is there in a separate sheet and I have to do it for multiple rows. i.e., In Row 1, User would select Orange, USA and the State list will be shown. in the Row 2 and subsequent rows, select the Fruit and Exporter, based on it show the third drop-down list for multiple rows.

Hi! If I understand the question correctly, you can create dependent dropdown lists as described in this guide. But without seeing your data, it is impossible to offer you formulas for this.

Is it possible to make the pulled data editable? When I change this data it disappears. Thank you!

Hi! If you select a new value from the drop-down list, the old value disappears and is replaced by the new value. If you want to select multiple values from the drop-down list, visit: How to make Excel drop down list with multiple selections .

Thanks, learned something new today :)

How do you make an indirect dynamic drop down for excel 2010? I managed to get the dynamic lists to work, the primary dropdown to work, but its dependent drop down doesn't work. Help most appreciated.

Hi! The following tutorial should help: Create a multiple dependent drop down list in Excel 365 - 2010 .

Thanks for the insightful explainer.

I noticed that if a cell is already populated, changing the adjacent cell where the data would now not match does not depopulate the cell, meaning the cell now has erroneous info. How would one automate the cell to exclude non conforming input?

Hi! If I understand correctly, you can automatically remove data from a cell with a second dropdown list when the first dropdown list is modified using a VBA macro.

How can I achieve the same thing but with multiple rows in column D:E? Like: I have several rows where the user selects a value from the first drop-down list and the cell right to it needs to be dynamically populated with corresponding values.

Your example works only if I have one row, because you will need one temporary table. How does it work with multiple rows of lists?

Hi! A drop-down list can only insert a value into one cell. You must create a drop-down list for every other cell. You can use dependent drop-down lists or use function VLOOKUP or INDEX MATCH to search for a matching value.

This worked perfectly. I had my spreadsheet with a list of businesses in different categories and those having their own sub-categories. Do the exercise on the page, don't try to immediately interpret it into your application. Just - fresh spreadsheet, apricots, oranges and Brazils. It makes complete sense when you do the exercise, and then easy to interpret into my application. Thanks, Alexander and Ablebits Team!

I have the same question as Dennis, does anyone have the answer? Meaning: I want to perform the same cascading dynamic dropdown menu's multiple times, but this means that for each input row, I would have to make a unique temporary table. Can this be done easier? Can I put the UNIQUE and FILTER formula in the data validation list criteria?

Hi! If I understand your task correctly, this article above may be helpful. This article shows you how to use the Dynamic Array functions to create a cascading drop-down list in Excel.

Thanks Alexander! Unfortunately, this is not what I am looking for. I have a multiple dependent drop-down list as in your example, but I want to perform this exercise multiple times based on the same input table. Using the example your sent, this means that I want to be able to select fruit, then exporter, then state/province in cells E3:G3 (using the info from the preparation table), en then I want to perform this exercise again in cells E4:G4 and again and again. But this means taht I would have to build a new preparation table each time I want to perform this exercise right? Or is there a simpler way, eg by using the UNIQUE and FILTER formulas in the data validation list criteria?

Have you read the article you are commenting on? The article talks about how to use UNIQUE and FILTER formulas in the data validation list criteria. Or explain why these recommendations do not work for you.

Hi, yes I have read the article above. Probably I am not understanding it correctly, but as I read it, it explains how to use the UNIQUE and FILTER formulas to create the correct list in the preparation table. Then this list can be called in the data validation list. I have succeeded in doing so. However, my case requires something different. Let me explain:

I have a table with headers called 'Supplier', 'Product Group', 'Product Name', 'Cost Price' and 'Discount'. I want users to be able to select their top 10-20 products in terms of procurement volume and see in one glance how much discount they can get on the total of these top products. This means that I want all of their top 10-20 products in an overview. Since the list of products is enormous, I want them to be able to first filter by supplier, then by productgroup, and then select their top product. Then automatically cost price and discount will be shown. Afterwards, the user has to be able to select their second top product in the row below. Selecting the supplier is no problem, but then selecting the product group is, because the drop-down list still refers to the selected supplier form the first product. Do I need to create a preparation table for the total number of products I want my users to be able to select? Or can I include the combi UNIQUE and FILTER formula in the data validation criteria (so not in the preparation table)?

Hi! I can't see your data and can't give you a formula. I think it is better to create a separate dynamic table using the FILTER formula and get the data for the dropdown list from it.

Thank you for training

thanks, it as a great help

Sorry, new to validation, for some reason when I asked my last question, did not realize that Copy would work fine.

Post a comment

How to Create Data Validation with Checkbox Control in Excel

In this article, we will discuss five simple steps to create Data Validation with Checkbox control in Excel.

5 Easy Steps to Create Data Validation with Checkbox Control in Excel

In this section of the article, we will discuss five simple steps to create Data Validation with Checkbox control in Excel. Let’s say, we have a blank dataset of Sales Data of ABC Company. Our goal is to fill up this dataset using Data Validation with Checkbox Control.

Let’s give you a quick overview of how we will fill this blank dataset. In the Name column, we will only accept text input, whereas, in the Sales Amount column, we will allow only numerical inputs. In the Region column, we will use a drop-down list to fill up the cells. For all the columns, we will use Checkboxes that need to be activated. Otherwise, we will not be able to insert data into these cells. Now, let’s follow the steps mentioned below to do this.

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.

Step 01: Insert Checkboxes from Developer Tab

In the first step, we will learn how we can insert Checkboxes in our Excel worksheet. Let’s follow the instructions outlined below.

• Firstly, create the following table as shown in the following image. This will come in handy later on.

• After that, Go to the Developer tab from Ribbon .
• Now, click on the Insert option from the Controls group.
• Then, choose the Check Box (Form Control) option from the drop-down.

• Following that, draw a Checkbox in the Data Validation Control column, as shown in the image below.

• Afterward, right-click on anywhere on the Checkbox .
• Now, select the Edit Text option.

• Subsequently, rename the Checkbox according to your convenience. In this case, we have renamed it Enable Data Entry .

Step 02: Link Cells with Checkboxes

In this step, we will link specific cells to the Checkboxes. Linking the Checkbox to a cell returns a TRUE or FALSE value in the linked cell, depending on whether the Checkbox is ticked or unticked. We will use these TRUE and FALSE values in our Data Validation formula in the upcoming steps.

• Firstly, right-click on any portion of the Checkbox .
• After that, select the Format Control option.

As a result, the Format Control dialogue box will appear on your worksheet.

• Now, in the Format Control dialogue box, go to the Control tab.
• Following that, select cell C16 in the Cell link field.
• Subsequently, click OK .

After doing this, if you check the Enable Data Entry Checkbox , TRUE will be returned in cell C16 , as shown in the image below.

• Now, follow the same procedure to create two more Checkboxes and link them with cells C17 , and C18 respectively.

Step 03: Formulate Data Validation for First Column

In this section of the article, we will formulate the Data Validation for the Name column. In the Name column, we will only accept text input as mentioned earlier. Now, let’s use the instructions given in the following section to do this.

• Firstly, select all the cells of the Name column.
• Then, go to the Data tab from Ribbon .
• After that, choose the Data Validation option from the Data Tools group.

• Now, in the Data Validation dialogue box, select the Custom option in the Allow field.
• Following that, use the formula given below in the Formula field.

Here, cell C16 indicates the first cell of the Linked Cell column, and cell B5 represents the first cell of the Name column.

Formula Breakdown 

• Here, cell B5 is the value argument.
• Output → TRUE .
• $C$15 → This is the logical_test argument.
• ISTEXT(B6) → This refers to the value_if_true argument.
• FALSE → It indicates the value_if_false argument.
• Then, click OK .

• Now, insert a numerical input in cell B5 .

You can see that Excel is giving an error. Because this cell is not allowed to have numerical data in it.

• Now, let’s insert a text value in cell B5 . But this time, we have unchecked the Checkbox of Enable Data Entry .

Just like the previous one, Excel is returning an error again. That means the Enable Data Entry Checkbox should be checked and at the same time the entered data should be a text value. This indicates that our inserted Checkbox with Data Validation is working perfectly.

• Now, let’s enter the Name of the salespersons in the Name column while keeping the Enable Data Entry Checkbox checked.

Step 04: Develop Data Validation for Second Column

Now, we will develop Data Validation for the Sales Amount column to create Checkbox with Data Validation in Excel. In the Sales Amount column, we will allow only the numerical values. Let’s use the instructions outlined below to do this.

• Firstly, select all the cells of the Sales Amount column.
• Afterward, go to the Data tab from Ribbon .
• Following that, select the Data Validation option from the Data Tools group.

• Now, in the Data Validation dialogue box, choose the Custom option in the Allow field.
• Following that, enter the following formula in the Formula field.

Here, cell C17 refers to the second cell of the Linked Cell column, and cell C5 indicates the first cell of the Sales Amount column.

• Here, cell C5 is the value argument.
• $C$16 → It is the logical1 argument.
• $C$17 → This refers to the [logical2] argument.

• Subsequently, make sure that both the Enable Data Entry and the Validate Sales Checkboxes are checked.
• Then, enter the Sales Amount for each salesperson as demonstrated in the following picture.

Step 05: Construct Drop-Down List for Third Column

In the final steps, we will create a drop-down list for the Region column. Now, let’s follow the procedure discussed below to do this.

• Firstly, in the Region List column, insert the available Regions as shown in the image given below.

• After that, select all the cells of the Region column.
• Now, select the Data Validation option from the Data Tools group.

• Following that, in the Data Validation dialogue box, select the List option in the Allow field.
• Then, use the following formula in the Source field.

Here, cell C18 indicates the third cell of the Linked Cell column, the range of cells D16:D18 represents the cells of the Region List column, and cell D19 represents a blank cell .

• $C$18 → This refers to the [logical2] argument.
• Here, TRUE → This is the logical_test argument.
• $D$16:$D$18 → It refers to the value_if_true argument.
• $D$19 → This indicates the value_if_false argument.
• Output → {“East”;”West”;”South”} .
• After that, click OK .

As a result, drop-down icons will be available in each cell of the Region column.

• Now, make sure that both the Enable Data Entry and the Activate Region List Checkboxes are checked.
• Following that, click on the drop-down icon beside cell D5 , and the drop-down list will be available as shown in the image below.

If you uncheck any of the previously mentioned Checkboxes, the drop-down list will disappear.

• Now, insert the Regions for respective salespersons in the Region column, as demonstrated in the image below.

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

Download Practice Workbook

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to create Data Validation with Checkbox control in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below.

Related Articles

• How to Perform Data Validation for Alphanumeric Only in Excel
• Excel Data Validation for Date Format
• How to Use Data Validation in Excel with Color
• How to Circle Invalid Data in Excel
• [Fixed] Data Validation Not Working for Copy Paste in Excel
• Excel Data Validation Greyed Out

<< Go Back to Data Validation in Excel | Learn Excel

What is ExcelDemy?

Tags: Excel Data Validation

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF

R news and tutorials contributed by hundreds of R bloggers

R solution for excel puzzles.

Posted on May 13, 2024 by Numbers around us in R bloggers | 0 Comments

Puzzles no. 449–453

Author: ExcelBI

All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github . Enjoy.

Puzzle #449

We are starting this week with rotating words. Wait what? No, we are not gonna swirl them and twist them. We need to find out if one word is second one’s rotated version, which means that it has to be shifted some places and letters from the end are coming to the beginning. But it is little bit tricky. Rotated version should be rotated by X spaces, where X is not equal to lenght of word. They have to be equal in lenghts as well, because it should come to word back after shifting it with rest of characters. Let’s rotate them.

Loading libraries and data

Transformation approach 1, transformation approach 2.

Puzzle #450

Today we need to rank sales person in different companies without sorting structure of table itself. Fortunatelly in case of R it is pretty easy.

Transformation

Puzzle #451

We have long sequence of numbers, and we need to find which of them negative and positive separately, will form longer consecutive chains. Let’s try doing it.

Puzzle #452

Why there is mosquito in ilustration? Because we have parasitic numbers today? What are they? Those are numbers that if multiplied by single digit integer number, will form number with almost the same shape but rotated (last digit comes to beginning). And today we need to find numbers that are parasitic, are lower than 1M and we need their multipliers as well. Get to work.

Puzzle #453

We need to find out from how many fruits from two lists we need to make exact pairs, so if there is 3 apples in one list, but 2 in second, there are only 2 pairs. Lets find the rest.

Feel free to comment, share and contact me with advices, questions and your ideas how to improve anything. Contact me on Linkedin if you wish as well. PS. Couple weeks ago, I started uploading on Github not only R, but also in Python. Come and check it .

R Solution for Excel Puzzles was originally published in Numbers around us on Medium, where people are continuing the conversation by highlighting and responding to this story.

Copyright © 2024 | MH Corporate basic by MH Themes

Never miss an update! Subscribe to R-bloggers to receive e-mails with the latest R posts. (You will not see this message again.)