Comments on: Data validation in Excel: how to add, use and remove

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. Continue reading →

Table of contents

Comments page 2. Total comments: 86

  1. 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.

  2. 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.

    1. FOUND IT!

  3. 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

  4. data validation doesn't work when i click another cell instead of hit enter key

  5. 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?

  6. 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

  7. 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.

  8. 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".

    1. 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.

  9. Thanks for the help

  10. Also how do you do a V-lookup

  11. "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?

    1. 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.

  12. 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

  13. 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

  14. 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

  15. 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.

  16. 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 ..

  17. IT WAS NICE AND TQQQQ

  18. 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"?

  19. 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.

  20. hi my name tayyib and me want learn the excel

  21. 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

  22. 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

  23. 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.

  24. 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?

  25. 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.

    1. Found the solution - use the "Tab" key instead of "Enter/Return" key

  26. 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

  27. How to make validation that first 4 digit is letters and next 4 numbers

  28. 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

  29. 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

  30. 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.

  31. Nice one

  32. 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?

    1. 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.

  33. Dear
    i want to data validation but some problem in cell between some cell merge how to data validation next cell.please help me

    1. 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.

  34. 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.

    1. 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!!

  35. 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

    1. 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.

      1. 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?

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)