Excel validation range limits
- by richardtallent
When Excel saves a file, it attempts to combine identical Validation settings into a single rule with multiple ranges.
This creates one of three issues, depending on the file type you choose to save:
When saving as a standard Excel file (Office 2000 BIFF), a maximum of 1024 non-contiguous ranges that can have the same validation setting.
When saving as a SpreadsheetML (Office 2002/2003 XML) file, you are limited to the number of non-contiguous ranges that can be represented, comma-delimited in R1C1 format, in 1024 characters.
When saving as an Open Office XML (Office 2007 *.xlsx), there is a maximum of 511 non-contiguous ranges that can have the same validation setting. (I don't have Office 2007, I'm using the file converter for Office 2003).
Once you bust any of these limits, the remaining ranges with the same Validation settings have their Validation settings wiped. For (1) and (3), Excel warns you that it can't save all of the formatting, but for (2) it does not.
