Forum Discussion
Why do worksheet XML files store shared formulas 2+ times instead of once?
Currently running Excel 365 (16.0.13801). I've been diving into the XML files within some .xlsx files and the way that XML stores shared formulas seems inefficient and slightly confounding. For my ex...
What happens if you don't enter in one cell and copy, but first select the region, write the formula and then press control+enter?
- Looks like that does work, and results in the same XML that I created in the second box of code above. Very interesting. The unfortunate bit is that it's not the most intuitive way to go about entering formulas so it's not really something you could recommend as a best practice. It seems like it's just a lazy algorithm to me. You've entered something in C3, and you want to fill the box, but instead of just going back and editing C3 to unify the whole range, it creates two new ranges to fill only the empty space in the box and leaves C3 alone.
- Does it help to save as the file to the xml strict format? I expect not, just curious and too tied up to try it myself.
- The workbook I was testing was already .xlsx, but I gave this a try anyway. Unfortunately it still used the original, 'inefficient', XML structure.
