by The tutorial explains how to do Data Validation in Excel: create a validation rule for numbers, dates or text values, make data validation lists, copy data validation to other cells, find invalid entries, fix and remove data validation.
When setting up a workbook for your users, you may often want to control information input into specific cells to make sure all data entries are accurate and consistent. Among other things, you may want to allow only particular data type such as numbers or dates in a cell, or limit numbers to a certain range and text to a given length. You may even want to provide a predefined list of acceptable entries to eliminate possible mistakes. Excel Data Validation allows you to do all these things in all versions of Microsoft Excel 365, 2021, 2019, 2016, 20013, 2010 and lower.
What is data validation in Excel?
Excel Data Validation is a feature that restricts (validates) user input to a worksheet. Technically, you create a validation rule that controls what kind of data can be entered into a certain cell.
Here are just a few examples of what Excel's data validation can do:
- Allow only numeric or text values in a cell.
- Allow only numbers within a specified range.
- Allow data entries of a specific length.
- Restrict dates and times outside a given range.
- Restrict entries to a selection from a drop-down list.
- Validate an entry based on another cell.
- Show an input message when the user selects a cell.
- Show a warning message when incorrect data has been entered.
- Find incorrect entries in validated cells.
For instance, you can set up a rule that limits data entry to 4-digit numbers between 1000 and 9999. If the user types something different, Excel will show an error alert explaining what they have done wrong:
How to do data validation in Excel
To add data validation in Excel, perform the following steps.
1. Open the Data Validation dialog box
Select one or more cells to validate, go to the Data tab > Data Tools group, and click the Data Validation button.
You can also open the Data Validation dialog box by pressing Alt > D > L, with each key pressed separately.
2. Create an Excel validation rule
On the Settings tab, define the validation criteria according to your needs. In the criteria, you can supply any of the following:
- Values - type numbers in the criteria boxes like shown in the screenshot below.
- Cell references - make a rule based on a value or formula in another cell.
- Formulas - allow to express more complex conditions like in this example.
As an example, let's make a rule that restricts users to entering a whole number between 1000 and 9999:
With the validation rule configured, either click OK to close the Data Validation window or switch to another tab to add an input message or/and error alert.
3. Add an input message (optional)
If you want to display a message that explains to the user what data is allowed in a given cell, open the Input Message tab and do the following:
- Make sure the Show input message when cell is selected box is checked.
- Enter the title and text of your message into the corresponding fields.
- Click OK to close the dialog window.
As soon as the user selects the validated cell, the following message will show up:
4. Display an error alert (optional)
In addition to the input message, you can show one of the following error alerts when invalid data is entered in a cell.
| Alert type | Description |
| Stop (default) |  The strictest alert type that prevents users from entering invalid data. You click Retry to type a different value or Cancel to remove the entry. |
| Warning |  Warns users that the data is invalid, but does not prevent entering it. You click Yes to input the invalid entry, No to edit it, or Cancel to remove the entry. |
| Information |  The most permissive alert type that only informs users about an invalid data entry. You click OK to enter the invalid value or Cancel to remove it from the cell. |
To configure a custom error message, go to the Error Alert tab and define the following parameters:
- Check the Show error alert after invalid data is entered box (usually selected by default).
- In the Style box, select the desired alert type.
- Enter the title and text of the error message into the corresponding boxes.
- Click OK.
And now, if the user enters invalid data, Excel will display a special alert explaining the error (like shown in the beginning of this tutorial).
Note. If you do not type your own message, the default Stop alert with the following text will show up: This value does not match the data validation restrictions defined for this cell.
Excel data validation examples
When adding a data validation rule in Excel, you can choose one of the predefined settings or specify custom criteria based on your own validation formula. Below we will discuss each of the built-in options, and next week we will have a closer look at Excel data validation with custom formulas in a separate tutorial.
As you already know, the validation criteria are defined on the Settings tab of the Data Validation dialog box (Data tab > Data Validation).
Whole numbers and decimals
To restrict data entry to a whole number or decimal, select the corresponding item in the Allow box. And then, choose one of the following criteria in the Data box:
- Equal to or not equal to the specified number
- Greater than or less than the specified number
- Between the two numbers or not between to exclude that range of numbers
For example, this is how you create an Excel validation rule that allows any whole number greater than 0:
Date and time validation in Excel
To validate dates, select Date in the Allow box, and then pick an appropriate criteria in the Data box. There are quite a lot of predefined options to choose from: allow only dates between two dates, equal to, greater than or less than a specific date, and more.
Similarly, to validate times, select Time in the Allow box, and then define the required criteria.
For example, to allow only dates between Start date in B1 and End date in B2, apply this Excel date validation rule:
To validate entries based on today's data and current time, make your own data validation formulas as shown in these examples:
Text length
To allow data entry of a specific length, select Text length in the Allow box, and choose the validation criteria in accordance with your business logic.
For example, to limit the input to 10 characters, create this rule:
Note. The Text length option limits the number of characters but not the data type, meaning the above rule will allow both text and numbers under 10 characters or 10 digits, respectively.
Excel data validation list (drop-down)
To add a drop-down list of items to a cell or a group of cells, select the target cells and do the following:
- Open the Data Validation dialog box (Data tab > Data Validation).
- On the Settings tab, select List in the Allow box.
- In the Source box, type the items of your Excel validation list, separated by commas. For example, to limit the user input to three choices, type Yes, No, N/A.
- Make sure the In-cell dropdown box is selected in order for the drop-down arrow to appear next to the cell.
- Click OK.
The resulting Excel data validation list will look similar to this:
Note. Please be careful with the Ignore blank option, which is selected by default. If you are creating a drop-down list based on a named range that has at least one blank cell, selecting this check box allows entering any value in the validated cell. In many situations, it is also true for validation formulas: if a cell referenced in the formula is blank, any value will be allowed in the validated cell.
Other ways to create a data validation list in Excel
Supplying comma-separated lists directly in the Source box is the fastest way that works well for small dropdowns that are unlikely to ever change. In other scenarios, you can proceed with one of the following ways:
Custom data validation rules
In addition to built-in Excel data validation rules discussed in this tutorial, you can create custom rules with your own data validation formulas. Here are just a few examples:
- Allow numbers only
- Allow text only
- Allow text beginning with specific characters
- Allow only unique entries and disallow duplicates
For more examples, please see Custom data validation rules and formulas.
How to edit data validation in Excel
To change an Excel validation rule, perform these steps:
- Select any of the validated cells.
- Open the Data Validation dialog box (Data tab > Data Validation).
- Make the required changes.
- Select the Apply these changes to all other cells with the same settings check box to copy the changes you've made to all other cells with the original validation criteria.
- Click OK to save the changes.
For instance, you can edit your Excel data validation list by adding or removing items from the Source box, and have these changes applied to all other cells containing the same drop-down list:
How to copy Excel data validation rule to other cells
If you've configured data validation for one cell and wish to validate other cells with the same criteria, you don't have to re-create the rule from scratch.
To copy the validation rule in Excel, perform these 4 quick steps:
- Select the cell to which the validation rule applies and press Ctrl + C to copy it.
- Select other cells you want to validate. To select non-adjacent cells, press and hold the Ctrl key while selecting the cells.
- Right-click the selection, click Paste Special, and then select the Validation option.
Alternatively, press the Paste Special > Validation shortcut: Ctrl + Alt + V, then N.
- Click OK.
Tip. Instead of copying data validation to other cells, you can convert your dataset to an Excel table. As you add more rows to the table, Excel will apply your validation rule to new rows automatically.
How to find cells with data validation in Excel
To quickly locate all validated cells in the current worksheet, go to the Home tab > Editing group, and click Find & Select > Data Validation:
This will select all cells that have any data validation rules applied to them:
How to remove data validation in Excel
Overall, there are two ways to remove validation in Excel: the standard approach designed by Microsoft and the mouse-free technique devised by Excel geeks who would never take their hands off the keyboard unless absolutely necessary (e.g. to take a cup of coffee :)
Method 1: Regular way to remove data validation
Normally, to remove data validation in Excel worksheets, you proceed with these steps:
- Select the cell(s) with data validation.
- On the Data tab, click the Data Validation button.
- On the Settings tab, click the Clear All button, and then click OK.
Tips:
- To remove data validation from all cells on the current sheet, use the Find & Select feature to select all of the validated cells.
- To remove a certain data validation rule, select any cell with that rule, open the Data Validation dialog window, check the Apply these changes to all other cells with the same settings box, and then click the Clear All button.
As you see, the standard method is pretty fast but does require a few mouse clicks, no big deal as far as I'm concerned. But if you prefer working with the keyboard over a mouse, you may find the following approach appealing.
Method 2: Paste Special to delete data validation rules
De jure, Excel Paste Special is designed for pasting specific elements of copied cells. De facto, it can do many more useful things. Among others, it can quickly remove data validation rules in a worksheet. Here's how:
- Select an empty cell without data validation, and press Ctrl + C to copy it.
- Select the cells(s) from which you want to remove data validation.
- Press Ctrl + Alt + V, then N, which is the shortcut for Paste Special > Data Validation.
- Press Enter. Done!
Excel data validation tips
Now that you know the basics of data validation in Excel, let me share a few tips that can make your rules a whole lot more effective.
Excel data validation based on another cell
Instead of typing values directly in the criteria boxes, you can enter them in some cells, and then refer to those cells. If you decide to change the validation conditions later, you will simply type new numbers on the sheet, without having to edit the rule.
To enter a cell reference, either type it in the box preceded by an equal sign, or click the arrow next to the box, and then select the cell using the mouse. You can also click anywhere within the box, and then select the cell on the sheet.
For example, to allow any whole number other than the number in A1, pick the not equal to criteria in the Data box and type =$A$1 in the Value box:
To take a step further, you can enter a formula in the referenced cell, and have Excel validate the input based on that formula.
For example, to restrict users to entering dates after today's date, enter the =TODAY() formula in some cell, say B1, and then set up a Date validation rule based on that cell:
Or, you can enter the =TODAY() formula directly in the Start date box, which will have the same effect.
Formula-based validation rules
In situations when it's not possible to define a desired validation criteria based on a value or cell reference, you can express it using a formula.
For example, to limit the entry to the minimum and maximum values in the existing list of numbers, say A1:A10, use the following formulas:
=MIN($A$1:$A$10)
=MAX($A$1:$A$10)
Please pay attention that we lock the range by using the $ sign (absolute cell references) so that our Excel validation rule works correctly for all selected cells.
How to find invalid data on the sheet
Although Microsoft Excel allows applying data validation to cells that already have data in them, it won't notify you if some of the existing values do not meet the validation criteria.
To find invalid data that had made its way into your worksheets before you added data validation, go to the Data tab, and click Data Validation > Circle Invalid Data.
This will highlight all cells that don't meet the validation criteria:
As soon as you correct an invalid entry, the circle will be gone automatically. To remove all circles, go to the Data tab, and click Data Validation > Clear Validation Circles.
How to protect a worksheet with data validation
In case you'd like to protect worksheet or workbook with password, configure the desired data validation settings first, and then protect the sheet. It is important that you unlock validated cells prior to protecting the worksheet, otherwise your users won't be able to enter any data in those cells. For the detailed guidelines, please see How to unlock certain cells on a protected sheet.
How to share a workbook with data validation
To allow multiple users to collaborate on the workbook, be sure to share the workbook after you have done data validation. After sharing the workbook your data validation rules will keep working, but you won't be able to change them, nor to add new rules.
Excel Data Validation not working
If data validation isn't working properly in your worksheets, it's most likely because of one of the following reasons.
Data validation does not work for copied data
Data validation in Excel is designed to prohibit typing invalid data directly in a cell, but it cannot stop users from copying invalid data. Though there is no way to disable copy/paste shortcuts (other than by using VBA), you can at least prevent copying data by dragging and dropping cells. To do this, go to File > Options > Advanced > Editing options, and clear the Enable fill handle and cell drag-and-drop check box.
Excel data validation is unavailable when in cell edit mode
The Data Validation command is unavailable (greyed out) if you are entering or changing data in a cell. After you've finished editing the cell, press Enter or Esc to quit the edit mode, and then do data validation.
Data validation cannot be applied to a protected or shared workbook
Although the existing validation rules keep working in protected and shared workbooks, it's not possible to change data validation settings or set up new rules. To do this, unshare and/or unprotect your workbook first.
Incorrect data validation formulas
When doing formula-based data validation in Excel, there are three important things to check:
- A validation formula does not return errors.
- A formula does not reference empty cells.
- Appropriate cell references are used.
For more information, please see Custom data validation rule not working.
Manual recalculation is turned on
If the Manual Calculation mode is turned on in your Excel, uncalculated formulas can prevent data from being validated correctly. To change the Excel calculation option back to automatic, go to the Formulas tab > Calculation group, click the Calculation Options button, and then click Automatic.
For more information, please see Automatic calculation vs. Manual calculation.
That's how you add and use data validation in Excel. Another way to correctly enter data in a worksheet is to use Excel Autocorrect. I thank you for reading and hope to see you on our blog next week!
84 comments
My friend has share an excel workbook, some cells are validated cell some of them are woking while some of the validated cells are not working or not showing the validated list.
See comment below.
Hey Svetlana Cheusheva,
Your article on Excel data validation was exactly right. Your clear methods and relatable examples made it simple to understand. I'm looking forward to putting these spreadsheet suggestions to work. Thank you for the fantastic information!
When I add a drop down list to my spreadsheet with 2 simple commands, yes,No and one is selected. The name in the cell disappears and the Yes or No replaces it. How can I have it just aadd the word yes or no to the cell and keep the name that is already there?
Thank you,
Rick Peterson
An Excel cell cannot have two values. You can combine the values of two cells in a third cell as described in this guide: CONCATENATE in Excel: combine text strings, cells and columns.
Getting error message that "This value doesn't match the data validation restrictions defined for this cell"...
I have tried and watch several videos, but none could solve the issue. Please help
Hi!
Check which data validation rule is set for this cell. See the article above for all the necessary information: how to find, edit and delete data validation.
I have a range of cells: A1:A6. Users can enter any numbers in these cells, but I need to restrict the sum of all numbers within this cell range to no more than 1000. I have applied data validation to the whole cell range A1:A6 with the custom formula =SUM(A1:A6)<=1000, however this does not work; only a number exceeding 1000 entered in one of the cells in the range A1:A6 triggers the prompt. If several numbers totalling over 1000, e.g. cell A1 = 500, cell A2 = 400, cell A3 = 200 are entered, the prompt does not appear.
Please could you advise how I should resolve this?
Thanks in advance.
Hello!
Use absolute cell references in your data validation formula.
=SUM($A$1:$A$9)<=1000
Works perfectly now, many thanks Alexander!
How do you analyse or collate the circled data from the data validation process? Can you put it into a table? I have around 2400 rows of data and 200 circled as invalid.
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.
Data validation rules are preserved when copy/pasting by simply using "paste values".
Hi Chris,
Yes, they are. But regular copy/pasting also copies the contents of the source cell. To copy only the data validation rule, use Paste Special.
A shared workbook (my friemd has share with me a workbook excel) cells are validated some are working and some others are not working. So how can i fix this problem
Hi! Regrettably, I can't provide you with any valuable recommendations without seeing your data.
Hi I used the dependent drop down list validation in Excel. For example, C2 is dependent to B2. And when I change the B2, I am seeing that C2 don't refresh (to appropiate list that dependent to B2) and still remaing as previous selection. At lease C2 should refresh to blank. Any tip??
Hi!
Automatic updating of values in a dependent drop-down list is only possible using a VBA macro.
This isn't exactly true. you can use formulas that leverage dynamic ranges:
=INDEX('SEARCH LIST'!$A:$A,ROW())# to find dynamically matching lookup content from a list of values in column A on sheet SEARCH LIST.
Try this in Cell C2 (if it helps)
=Indirect(B2)
1: Data Validation List is located in a Table ("DVTable")
2: Data for the DVTable grows with the addition of new customers
Is there a way when a cell value errors as the match can't be found in the DVTable, that the user could be prompted (Info not found, Add Information Y/N) to the DV Table.
Hello!
The information you provided is not enough to understand your case and give you any advice. Explain in detail how you do Data Validation, what formulas you use.
If you're using a range of values to validate data, then this guide might be helpful: Excel dynamic named range: how to create and use.
Hi!
How to print all information if you have used data validation. I created students academic report cards and only one is displayed at a time. I want to print all of them at once.
How to allows number between 1and 60 and tow characters x and / in a sheet
Hi Svetlana Cheusheva
The article is very useful and informative. I have one question in my mind that can we apply two conditions in one cell in Data Validation as suppose we have a cell named Mobile number and I want to apply two conditions 1. The number should be 10 digits, if number is less than 10 digits then it gives alert message and if number is more than 10 digits it again gives alert message and the 2 condition will be the cell only allowed numbers not alphabets. Please explain in details. Thanks
Hello Mam to receive your blogs daily, what should I do. Please guide me.
Hi,
If I understood the problem correctly, this article will be useful to you: How to use Data Validation in Excel - custom validation rules and formulas.
Use the formula:
=AND(C2>0,C2<100)
This should solve your task.
Hi
Could you tell me your project have data validation
Hi,
I have a dropdown list with multiple selections. My question is that is there anyway to unselect the already selected value from the list.
Thank you for this. Very helpful.
I do have a question. I was sent an excel inventory sheet where I need to add a category. Now data validation was applied to this column. I went to the click on data validation and under source I see "='Data validation'!$B$2:$B$8" I do not see any other sheets created containing the category list nor is it on the existing sheet.
Please guide me on how I can find and edit the category list.
FOUND IT!
Hoe kan je een % valideren.
Stel je wil max 3% in een cel.
en geen 4%
ik krijg het in orde voor gehele en decimale getallen maar niet als ik een percentage wil gebruiken
Hello!
If I understand your task correctly, instead of 3%, you can use 0.03
data validation doesn't work when i click another cell instead of hit enter key
Hi,
There is a number in cell A1. Is it possible to prevent clearing that cell and force to keep any number in that cell?
Hi,
I want use validation on numeric values that contains serial Wise Like, If i put 1 then after only come 2 not greater then 2 or less then 2
I am trying to remove data validation, under Method 1: Regular way to remove data validation
Where is the settings option? Not able to find it.
How to add multiple alert message on same column?
Ex - A numeric filed with valid entry range is 1000 to 9999. If i try to enter 'dd343' Error message should be - Enter only numeric value.
If i enter '99999' Then error message should be -"Number must be in range of 1000 to 9999".
Hello Deependra,
You can set just one alert for a particular range using standard Data Validation in Excel. If there are several notifications you want to get, you need a VBA macro. I am really sorry but we can’t help you with this.
Thanks for the help
Also how do you do a V-lookup
Hi Elias,
Please see this tutorial for the detailed explanation of the VLOOKUP function: Excel VLOOKUP tutorial for beginners
"Excel data validation based on another cell"
when the stop message prompt out, the user still can choose "cancel" button to ignore the restrict input.
May i know how to remove the "cancel" button on the message, to force user to must follow the rule that i set?
Hi San,
It’s not possible to remove the Cancel button from the alert. However, in a stop alert, Cancel just closes the error message, but it does not let the user enter the restrict input. On the next try, if the users enters a restricted value, they will see the stop alert again.
I am soms from Papua New Guinea, I am designing a simple program. I need your help. I want log off users not to input anything into the worksheet after the set due date, How will I do it?. need help please
Dear, thanks, very informative.
I am having a problem in displaying a message when 2 cells are of same Name.
Please Help me to find out the way.
Doc. Received By Doc. Audited By
John Mathew John Mathew
They should not be same. How should I display a message so the one should not Audit who Received the Document.
Appreciate for Help.
Regards,
Ali
Hi,
i have thousands of drop down validation cells in a spreadsheet. I need to add drop down info to my list criteria and apply them to each cell without removing/erasing what is currently noted in the cells. I tried copying them, but that erases what my current cell data is. Is there a way to have the cells be updated without copying&pasting? i would like to add to my criteria and have the cells automatically update without altering/erasing current cell data.
thanks
Thanks for the information! However, found it very hard to use with the moving banner at the bottom of the screen, had to screenshot the text because it was so uncomfortable on the eyes.
Hii,
I am priyanka. i want interested to know about how can i set filter validation. Like:- I WANT down Down- drops too-
****i want to make this drop down****
lost to competition
lost to co-dealer
Purchase postponed
if i want to set *lost to competition -drop down -
Compulsion from relatives/friends
Exterior looks are not good
Features not appealing
High Maintenance cost
Information not revealed
Low Brand Image
Low Exchange Value
Poor After Service
Poor mileage
Poor/No Response from Show room
Scheme not attractive
Stock not available
Other
****lost to co-dealer*** - drop down
Better discount Offer
Compulsion from relatives/friends
Dealership was close to home
Information not revealed
Poor After Service
Poor/No Response from Show room
Stock not available
**** Purchase postponed*****
Customer out of station
Exterior looks not good
Finance problem
Scheme not attractive
Stock not available
Waiting for new model
**** so how can make this typ of validation ****
plz tell me ..
Hello!
You need to make a dependent drop-downs for this. The detailed instructions can be found here:
How to create a cascading (dependent) drop-down list
IT WAS NICE AND TQQQQ
In cells that I have data validation applied to, sometimes, when I start typing an autofill function gives me the best solution and other times noting happens, even when I know there is only one selection that would meet the typed criteria. Is there a way to always have the autofill option "On"?
How can I trigger Excel to "recalculate" or update dependent cells when a validation entry is changed via VBA?
The validation cell is used in other functions (i.e. VLOOKUP, CUBEMEMBER) and changing the cell manually triggers the calculation but doing so via VBA and nothing happens.
hi my name tayyib and me want learn the excel
Hi. I have an excel sheet in which I have a cell which has a drop-down. This drop-down lists a group of names. Can I copy this cell to another sheet which would then display the same names when the drop-down is pressed?
Thanks,
-S
hello,
when i write many formula (if) in source box, it seems limited, please enlighten me about this, it's true that the source box has limited character (word/number) that can be written there? thank you
hi! i am just wondering that can we use data validation in a way as we utilize the options in the other softwares like if i select "option A" in coloumn "X" so it will provide me a list of option which is nested under the "option A" in the very next column or where i want to utilize this function. and if i selected "Option B" in the column X so it'll show me the options which is nested under the option B. so in this way, we can effectively use the head of accounts like if i use Head of Account of Salary in the column X so it shows me only the employees list which i nested under Salary Head of Account. I don't have any idea that this option is available in excel or not thats why i m posting it here so that i can get some answers from excel eXperts.
Hi, Hasan,
What you described looks like a dependent drop-down list.
You will find the instructions on how to create one in this article of ours.
Is there a way to prevent a user from editing a drop down list selection after they pick from the list? In my case, the user selected one of the items in the list and then changed it. Can this be prevented?
Hello,
we have a spreadsheet where the first 2 columns use data validation via 2 separate drop down lists. On the first row (under the Headers) the data validation works as expected - ie a user clicks in the first row cell and can select the from the list, ditto for the second cell in the same row.
The problem we have: after entering free form text / data into the remaining row cells and pressing enter / return the new row does not show the data validation in the first two columns when the user clicks in either cell unless some free form text / data is entered in one of the remaining row cells.
Is there a way to have the Data Validation selection working for the first 2 columns on a new row without having to enter text / data in one of the other row cells?
We are using Excel 2013 with "Include new rows and columns in table automatically as you work" selected under AutoCorrect options. We do not VBA.
Thanks for any help / advice.
Found the solution - use the "Tab" key instead of "Enter/Return" key
Hi, i need to check whether particular range of cells are empty.Am checking checking mandatory fields.If its empty it should display alert msg..
thanks in advance
How to make validation that first 4 digit is letters and next 4 numbers
hi,
I have an ex file with date validation in it.
My boss told me not to add rows in between because it destroys the data validation. I've added 2 rows and on the right site where is data val. I have blanks. How can I adjust my data validation without deleting my added rows?
Thanks
hello; I Have one problem to resolve when i Creat date in the tab to make atransfer it says invalid date format therefore, please help me how to set it to be valid date.
in the tab in my cell
hello; I Have one problem to resolve when i Creat date in the tab to make atransfer it says invalid date format therefore, please help me how to set it to be valid date.
Nice one
Hi
I copied and pasted data containing data validation into one workbook from another workbook. My normal drop downs are working, but my indirect drop down list stopped working.
1) I changed all the named lists to use the current workbook as a source.
2) I removed the indirect data validation and tried to redo it.
The indirect is still not working and keeps telling me the formula evaluates to an error.
What could be causing the error and how do I fix it?
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
Dear
i want to data validation but some problem in cell between some cell merge how to data validation next cell.please help me
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
Hi,
Is there a way to only allow 0, 50 or multiples of 50 to be input into a cell without doing it from a list.
Yes, possible.
Select custom in validation criteria.
Enter this formula : =mod(A1,50)=0
OK
Ignore the pop up message (if any) that says "this formula evaluates to an error".
Done!!
could you please help out on how to find automatic formula like for example if I enter the data then the answer will automatically popping on
Hi Monica,
I am afraid I cannot suggest any solution based on such a generic description. Anyway, Excel Data Validation only checks the data entry in selected cells and it can only show an input message or error alert when invalid data is entered.
Hi Svetlana,
Sorry I appear to be replying to an old post. I am actually asking a question. I am trying to copy an entire column (in sheet 1) whose data values in each cell are generated from a drop-down menu, to new column in sheet two. I am using the special paste, validation option. Although the values are getting copied, only the drop-down menu in the first cell is active. How can I solve this problem?