One of our users sent me an Excel file of 6 MiB.
It has 7 worksheets. Most of them have 30 seconds to calculate the sheet, however there are no real
calculations, just a few concatenated string.
My first impression is that this is yet another example of Excel
(ab)used as a database.
When the user tries to save (or save as), he gets the following
Excel could not save all the data and formatting you recently added to
* To return to your workbook without saving click Cancel.
* If you want to continue saving changes without repairing the
problem, click OK.
[OK] [Cancel] [Help]
Help gives a blank help page.
It appears the last added (or copypasted) rows in
the sheet have lost
their validation. I can add validation again by copy - pastespecial -
validation, but Excel won't save that change.
I have found a KB with the same error message:
This talks about more than 2050 rows of conditional formatting. It is
possible that there are more than 2050 rows with conditional
formatting, but this does not explain why the data validation is not
I found another KB:
It talks about more than 4000 different combinations of formatting.
I'm not sure, but I don't think there are more than 4000 different
combinations. Anyway, data validation is not the same as formatting,
so I don't see this as relevant either.
I have removed all conditional formatting from the sheet, and I have
set most of the formatting (font, border, background,...) to the
default values. I still get the error.
What is going on and how can I solve this?