Best practices
To maximize Excel’s effectiveness and ensure accurate and efficient results, it’s essential to follow best practices when using Excel. Adopting these practices can help avoid common pitfalls, streamline your workflow, and enhance the reliability of your data and calculations.
Principles for good Excel use
Excel modelling must be taken seriously, Excel is used too widely to be seen as just a flexible sheet. The models created are legitimate pieces of software and need to be simple, correct, and maintainable. Learn from our basic principles!
Good formatting in Excel
Microsoft has made it very easy to dress up your worksheets with all sorts of fill patterns, borders and other frills. The downside of this, however, is that many users format cells in an ad-hoc manner, creating messy spreadsheets.
Prefer INDEX and MATCH over VLOOKUP
The combination of INDEX and MATCH, as well as the VLOOKUP function are well known methods for search in Excel. To our great frustration, the latter is used a lot more often, while the combination of INDEX and MATCH is less error prone and a lot more efficient.
Calculate formulas only once
Sometimes, you need to use the exact same formula, based on the same numbers, in several other formulas. You might be tempted to calculate this formula twice, but resist the temptation. Make sure that you calculate formulas only once!
The dangers of hidden information
Do not hide anything except some sheets. Excel has too many attractive options that look good but may be risky in the long run. In this article we talk about the dangers of hidden information.
Create & maintain good spreadsheets
To create and maintain consistently good spreadsheets, there are a number of Excel best practices which we’ll share with you in this article.