3 ms·
It's interesting how often people's response to the manifest problems caused by people using Excel in unfortunate ways is to either suggest tools with much stee
by Mvandenbergh 7y ago
It's interesting how often people's response to the manifest problems caused by people using Excel in unfortunate ways is to either suggest tools with much steeper learning curves or to suggest additional Excel features. 99% of these problems can be solved by proper training and standards appropriate to the problem and context at hand.
For instance:
Always separate and label inputs, calculations, and outputs.
Document where source data has come from, where one cell has had an ad-hoc adjustment made, what formulas do.
Use some type of version control and don't keep loads of concurrent versions around floating on email and local hard drives.
If the spreadsheet loads data from external sources, try and make that load automatic and live to prevent staleness.
Consistent formatting rules.
If data is tabular, put it in an Excel table. If data is tabular and we are always doing the same queries on it, and it is large then we move it to a database but that rarely happens.
Make it clear who owns spreadsheets and is responsible for keeping either/or data & functions/formulas to work.
Do all of this first, only then start thinking of replacing Excel with something else.
- mulmen 7y agoDon't try to fix the user. If we lived in a world where your suggestions were followed Excel would indeed be an OK tool. Here in the real world however Excel has just the right combination of power and usability to shoot off every left foot in a five cube radius, and frequently does.
- TeMPOraL 7y agoNote that the reason why Excel is popular is precisely because people "don't try to fix the user", but instead develop software solutions tailored to address (author's misunderstanding of) a specific use case users have. The problem is, the specifics of that use case changes much faster than the dedicated software can be updated. Processes that are thoroughly understood are easy to automate. Those that aren't, require competent people with flexible tools. Excel is a flexible tool. Competence can be gained through training.
- Mvandenbergh 7y agoSure, in general, don't try to fix the user. In our context, we use Excel as a team in a work context where our rules make our specific tasks easier. It is much easier for people joining who are already proficient with Excel to follow some formatting guidelines than to invent a custom tool and learn to use that.
- ozim 7y agoHardest part of everything I do in my life is making other people follow rules, or any kind of guide lines. If you succeed with making only 1 rule of those you described consistently followed by organization of 10 people for 6 months ... please send me how you did that, then I am going to be a millionaire. I am team lead of small dev team, if you push people by force to follow rules they will start maliciously follow them up to the point where no work is done and your spreadsheets are perfect.