bring back performance for calculations, filters and conditionnal formatting
Overall performance of calculations, conditionnal cell formatting and auto filters have suffered a lot in the last two versions of Excel.
We use to put formulas everywhere. Now I must recommend users to use them with caution because it might freeze their Excel and changes will be lost if it crashes.
Now I must make sure those formulas are taken care of by SQL queries so Excel does not have to bother about it. But that puts a lot of pressure on my team which we had not when formulas were running like lightning.
Also, display of formatting with an "on-demand" kind of approach (that's what it looks like) does not seem to work because whenever a user highlights a whole row, it seems like all cells have been assigned a style code individually, (I looked at the sheet1.xml file within the xlsx)
Ending up with a user complaining that his Excel spreadsheet kept freezing, and when I extracted the contents, I could see sheet1.xml was 90 Mb!! Even though the xlsx file was only 6Mb. So when I tried to see what was the garbage in this sheet1.xml, I could see the whole line 1 had been assigned a style code for each cell in there (didn't check if they were all in there but it looked like it), up to XFD column!!
And then I just found out that the reason might be because there was some style applied to some cells in XEW546:XFB546.
From my estimation, only 90 000 cells are actually used in this spreadsheet. And many have no value and same formatting. To me it seems there could be a little bit of optimization to avoid processing cells that are identical. It should rather treat them as a range.
Otherwise, it would be like saying that a JPEG has a better way of compressing the information than this new flavor of Excel.
I could probably submit the file if it is through a secure channel since it contains private information.