5 ms·
Disagree on the "users can verify" part. Excel is very hard to verify, because of corner cases like date conversion. Formulas and macros, maybe spread out all o
by corty 6y ago
Disagree on the "users can verify" part. Excel is very hard to verify, because of corner cases like date conversion. Formulas and macros, maybe spread out all over multiple sheets in hidden away white-on-white cells, make things very opaque and impossible to verify for most users. After a long gone colleague has passed the usage of some excel file, it is treated as an arcane and unchangeable wisdom from the past, never to be really understood or changed. Just maybe used as a copy-paste value import to the next generation.
Also, recognized good practices for software development (which any excel sheet that does more than just a sum() will be) like commenting and versioning are quite hard to impossible in excel. So even if you endavour to do it "right" but with excel, it is just the wrong tool for the job.
- theshrike79 6y agoI vaguely remember a discussion about this and the problem boils down to Excel sheets being impossible to unit-test. There might be, for example, a formula going through 20k lines in column F. But the one on line 1138 has a typo and the formula references an incorrect cell. No human will ever go through all the lines to check. Excel itself doesn't check stuff like that. And there are no tools for it either.
- reportgunner 6y agoExcel actually checks that and gives you an 'Inconsistent formula' warning with a little green corner similar to when you comment a cell. All you need to do is scroll through the worksheet you have just made.
- iso1631 6y agoAll you need to do is scroll through 20,000 lines looking at each one
- reportgunner 6y ago20,000 is not a lot of lines though, I used to scroll 300k sometimes. Anything below 500k rows is a 'small table' still.
- iso1631 6y agoThe point is that's a manual, tedious, process that will be performed with error or completely skipped. We have computers to do that sort of work.
- reportgunner 6y agoIt is a manual and tedious process if you make it manual and tedious for yourself. After all, it's people that make computers do this work in the first place.
- Isthatablackgsd 6y agoExcel have an ability to highlight the cell that have invalid data with a red circle to make it pop out on the table. Just look for something red.
- baobabKoodaa 6y agoYou can easily create tests for cases like that in excel. For example, if you know that all cells in a column must conform to specific conditions, you can create a new cell with a formula that validates the other cells. I have a bookkeeping excel where the "validation cell" becomes red when it detects errors.