Share via

Data validation not saving in a .xls file, under Excel 2010.

Anonymous
2013-06-05T00:17:35+00:00

I'm working on a .xls file, that was created with a version older than Excel 2010. I had created a bunch of data validation options to 10 columns or so. I made sure that all the changes were saved. I'm currently working under Excel 2010. When I reopened the file, all the arrows and data validation was gone. Is there a way to fix this bug?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2013-08-09T18:26:45+00:00

Hello,

I too recently have been experiencing issues opening Excel Spreadsheets with Data Validations I created.  

After testing I realized it is a compatibility issue between Excel 2003 and Excel 2010.  Therefore, if a spreadsheet was created in Microsoft Excel 97-2003 Worksheet (.xls) and you have Excel 2010, when you save your spreadsheet it will not save the validations as it's saving to the Microsoft Excel 97-2003 version.  However, if it is saved as, Microsoft Excel Worksheet (.xlsx) or Microsoft Excel 2010 Worksheet (.xlsx) it will.  Just remember to delete/remove the older file version.

Good luck,

VLGKJP

Was this answer helpful?

60+ people found this answer helpful.
0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-02-20T16:36:44+00:00

    I've had this happen to me and I think i've come to understand why.

    In the .xls file i'm working on - using Excel 2010 - I have Data Validations on a number of pages. They are all list types (drop-downs), based on a range of values held somewhere else in the spreadsheet. Most of the ranges are in the same sheet as where the Data Validation is, and these remain in place if I save the spreadsheet as .xls and re-open later.

    However, I also have a Data Validation where the range looks at a different sheet to the one where the Data Validation will be held. This one disappears after I save and re-open. I'll use my example with sheet names because I haven't done a good job of explaining it above(!):

    Example 1

    Sheet Name with Data Validation: 'DI - EstSheet1'

    Allow: List

    Source: ='DI - EstSheet1'!$B$176:$B$190

    This example remained in the spreadsheet after it was saved and re-opened.

    Example 2

    Sheet Name with Data Validation: 'DI - Estimating'

    Allow: List

    Source: ='DI - CBS'!$AG$5:$AG$16

    This example disappeared after it was saved and re-opened.

    So I think the problem occurs when you refer to a different sheet in the source of the Validation to the sheet where the Validation lives. Hope that makes sense!

    Was this answer helpful?

    7 people found this answer helpful.
    0 comments
  2. Anonymous
    2015-02-20T23:05:49+00:00

    Yes, that is entirely consistent with expectations.  The ability to have validation lists on a different sheet, using an address like the one you mention was not supported until Excel 2010.  So the xls file format which is compatible with Excel 2003 does not support it. 

    Indeed when I try to save a workbook containing such a validation list as an xls file I get a compatibility warning telling me that the validation will be lost.

    If you name the range on the other sheet and use the range name as the source of the list it will be saved successfully (and will work in 2003 and 2007 as well).

    Was this answer helpful?

    4 people found this answer helpful.
    0 comments
  3. Anonymous
    2015-12-30T05:23:46+00:00

    This answer also helped me as I was facing the same problem where the original Excel file was created in the older version. Therefore Validation Rules were not getting saved.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  4. Anonymous
    2015-01-16T19:45:12+00:00

    Thank you for this enlightenment.

    I knew there was a minor enhancement to validation in 2010 (to allow the validation list to be cells on a different worksheet) but I did not realise that if you saved validations created in 2010 in a 97-2003 format workbook that they would not be saved.

    It is, of course, always sensible to do development of a spreadsheet in the earliest version of Excel that will ever be used to read it, and to use the most recent file format that is compatible with that earliest version.

    I have version Office 365 and it is happening to me with Excel in that version.

    I am loosing data almost every time we open it, and getting very frustrated with loosing listing of members in our association, and unable to know it is happening, nor who is gone and when.

    Problem is still happening in latest updated version.

    Also information is put in and you hit save button, and when open up file next time a lot of the updates are NOT saved, so loose a whole days of input, as find it the next day when open files.

    This is getting very frustrating and time costly. Microsoft should get this fixed and soon.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments