[throwaway485]

I have done excellent work in O365 Excel that is later muddied by concurrent/other editors.

Half the time I'm correcting cells where a user navigated to the wrong one and replaced the formula. What's more frustrating is when I develop a BETTER formula to use for the entire column, but there's no easy way to replace it all and I actually want to preserve previously calculated values. (I preserve values for historical accuracy, even if the formula then was not-great.) Makes me think Excel needs git-like version control for rows/records, and the ability to query things like last-modified of a cell, etc.

I have copied everything in the column out to a temporary spreadsheet then paste-by-value to put it back, once the correct formula is set throughout. That's tedious and error-prone and sometimes I lose the dumb historic coloring that previous editors wanted (I preserve what I can) and log why this changed as the maintainer changed. Am I doing this wrong?

There's no easy way to spot holes/changes in formulas as you scan down the column: You can use the Review tab option to show Formulas, but if the formulas are largely the same (start the same but are very long), you're unlikely to spot the difference. This could be thousands of rows to scroll through, or the last hundred you're concerned with. Seems like there should be a better way.

I want an easy table protection option to require that 'this formula will be the only formula in this column'. Table protection is so lacking. You can't protect a column to say "only computed values exist here". You protect the column, and it prevents users from entering a new row/record, making a mistake, and deleting the row to try again. We train folks: If you mess up the next row, just delete the entire row and attempt to add it back. The computed columns/values will be there for you. Protecting a column makes this impossible.

Online Excel is advancing.. but I want too much. I feel like there's been low-hanging fruit for years and it's no wonder all these alternatives are good enough to replace Office.

[elmolino89]

IMHO instead of ramming a square peg into a round hole one may think of switching from Excel to i.e. Jupyter notebook(s). Keep the raw data separate from the calculations/visualizations and get a more trackable environment.

You may also pinch out bunch of functions as separate scripts in a git repo and share these between various notebooks/people.

Calculate md5 checksums of raw data files. If values in rows can be modified, get the hash sums (xxhash?) per row. That way even with the lowest common denominator (text CSVs) you know if the inputs changed and where.

For larger data consider storing it in parquet format and using duckdb.

Assuming that you must have Excel output calculate what's needed using raw data, notebooks/scripts then output Excel using openpyxl