Actually, only 1 tip is important.
Don’t kick in the Dunning – Kruger Val!
There is often a serious discrepancy between what people think to know and what they know effectively.
Beginners often make themselves overconfident, overestimating their own abilities and making stupid mistakes.
In short, a little knowledge is dangerous.
The real way to expertise often looks like this:
If we translate this into Excel, there is only one real tip you need to know.
Don’t assume you’re not making mistakes.
Or check your formulas!
Excel is very flexible you can drag and drop formulas, but unfortunately a mistake is made quickly and before you know it your crucial calculation is wrong.
What also helps is to make things visually.
I have added a very simple example what I use on my work to calculate how much steel you best trade in to do a water determination via KF. (Karl-Fisher is just about the most universal way to determine water, but this as a whole on the side)
- My head is green and says briefly what the intention is of the sheet.
- Blue is the part you need to fill.
- The output is red.
I usually protect the sheet, where only the cells in the Blue section remain open.
Often with a generic or even without a password.
The security in Excel is still too ridiculous for words. So why put a lot of time in there? The important thing is that no accidental changes are made.
I also often work with named ranges.
For example, in the example above, the formula is in cell B14.
So I gave the previous cells in the Blue Zone a named range, which corresponds to the formula that should be in it.
This makes control much easier.
In This connection I can highly recommend the tool of the Dutchman Jan Karel Pieterse.(Excel: Name Manager.)
For this sheet it might have been some overkill, but the experience shows me that you better exaggerate than that after X-number of time you have to determine that there was an error in the formula.
Ideally, you should also check your sheets thoroughly by someone else.
Unfortunately, there are still a lot of people who think they are immune to these kinds of mistakes.
However, research shows that an alarming large number (up to nearly 90%) Of the spreadsheets contain errors, where the damage can sometimes rise to the millions.
There is an annual conference that is dedicated to this underwork.On their website you can find an overview of the ZGN. Excel Horror Stories. EUROPEAN spreadsheet Risks Interest Group
Schedules of Lessons from Mt. STUPID-XONITEK
Below are some points that I think are ignored by many beginners (and many “advanced”):
Learning keyboard shortcuts;
Studying the different cell references;
Studying the structure of a function.What are the arguments, which are mandatory and which are allowed to omit. How do you recognize an unrequired argument and what is its default value;
If possible, make use of auxiliary columns containing short formulas instead of 1 cell with a complex formula that one day can no longer read or correct/optimize.
Working with formula evaluation.