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.
Keep it simple
Simplicity improved readability, reduces mistakes, and increases productivity. Keeping your models simple is essential!
Keep it safe
Build reliable spreadsheets without blunders in formulas, privacy concerns, or lack of protection. Keep your models safe!
Keep it going
Having a spreadsheet that is fast, easy to maintain and easy to adapt without it slowing down is important. Build for the future!
1. Keep your formatting consistent
Consistent formatting makes a spreadsheet easier to follow. The importance of uniformity is proportional to the complexity of the model. A well-formatted model is good for maintainability, especially for other users. Being consistent means that color, fonts and row- and column formats are used predictably throughout your model.
2. Choose a uniform architecture
Spreadsheets can last for many years. That’s why it’s critical to choose an architecture that suits all the functional requirements. Architecture concerns the choices made with regards to the modelling of your Excel file. For example, an architectural decision is to always have a separate input worksheet for database imports, or to consistently have an ID-column to the left of your data.
3. Don’t be afraid to remodel
Form follows function. If the function of a model changes, so must its form. Don’t turn your spreadsheet into a ‘hack’: if new requirements call for a change in the calculation model, don’t hesitate to apply those changes. Only by keeping your model up to date do you keep it from turning into a convoluted mess.
4. Consider the worksheet order
Large models often come with many worksheets. Take note of the order in which you place them in Excel. Do you work from left to right? From result to source or from input to conclusion? Every spreadsheet calls for a different approach, but in every case a consistent, predictable worksheet order makes a model nicer to work with.
5. Don’t be afraid to add another worksheet
A common misconception is that more worksheets make a spreadsheet more complicated. In fact, usually the opposite is true. Worksheets that try to do too many things, like include multiple tables, tend to be the most error-prone. Excel used to be limited in the number of worksheets it accepts, but that was in the distant past. Although additional worksheets can make a spreadsheet look more complicated from a schematic perspective, in practical terms it actually makes the model easier to use.
6. Keep your timeline consistent
By way of exception, in Excel you are allowed to compare apples and oranges. After all, both are fruit. What you should not do is to include quantities of a different unit. So a list with apples and oranges should not contain an item labelled ‘fruit’ (except as a sum perhaps), and a list of days should not be mixed with week or month records. Ideally, introduce a separate table for every unique unit.
7. Separate input, output, and calculations
Excel doesn’t make it immediately obvious which cells contain formula results and which are entered values. It’s important to keep them well separated; ideally in separate worksheets, or else through clear formatting. This way you avoid accidentally overwriting calculations with new input data. When mixing in- and output is inevitable, take extra care to give them a distinctive look.
8. Use consistent labels
Whether used to provide clarity to the next user, to remind yourself, or for the creation of tables and charts, clear labels are incredibly important. Therefore, use good labels: Titles, row & column headers, named ranges, and clear worksheet names.
9. Keep formulas readable
It sometimes seems like a competition of who can write the longest formula. However, much more challenging is to design a calculation model that is easy to understand for other people. It’s a case of finding a balance between compactness and readability. Break up a formula too much and it becomes hard to follow. Keep it too long and people lose track trying to decipher it. Tip: use clear labels for intermediate columns. Also place repeating calculations in their own column and refer back to those where needed.
10. Avoid duplication
Don’t calculate the same thing twice. It’s unnecessary. Much better to perform the calculation once and refer back to it in subsequent cells. It doesn’t just help improve performance, it also saves you from forgetting to update all cases when a change is required. Using a formula result a lot? Turn it into a defined name!
11. Avoid hard coded numbers in formulas
With fixed, or hard coded, numbers in formulas, you will lose the plot in no time. These numbers are pretty much impossible to check because you have no references in your formulas. When a number is used often (like VAT) you will almost definitely change it in some formulas and forget to change it in others when it’s hard coded. It’s much better to group and name values in one place and then refer to them. Frequently used constants should be given a defined name.
12. Do not merge cells
It’s tempting, for the formatting, to merge a couple of cells into one big one, but merged cells generally cause more problems than they solve. When you try to reference a merged cell, and you don’t reference the top left space, it simply doesn’t work. Merged cells also make sorting, filtering, copying, and pasting a nightmare. If you really want to line out a label with several columns or rows, use the “center along selection” option. Then the merging is only done visually.
13. Protect, don’t hide
Don’t worry! It’s ok to hide worksheets which don’t need to be looked at by the user, but never hide cells, rows, and columns! Even the sneaky way (by making the text color white). Hiding information is just asking for problems, information gets forgotten, and things can be changed unintentionally. It’s much better to just protect formulas.
14. Build in automated checks
Excel is the king of numbers, use that to your advantage! For example, add-up subtotals and compare that result to the total, makes sure those two numbers are equal. With bigger models we even suggest a separate worksheet called a “control sheet.” Use smart formulas there that check if a model is, for example, balancing properly. This one you can see in one glance if something is going wrong.
15. Save styling for the end
Make sure you’re not wasting time on formatting that may need to change when the model is done. Format just once by only adjusting the formatting of tables, lines between groups of cells, and even titles only after you have all the data you need.
16. Follow company rules
If you discuss standards with your colleagues on how you will deal with formulas, formatting, and structure, then your models become much easier to understand, maintain, and transfer. Especially in larger companies it’s important to set standards for what an Excel model should look like, and have these recorded and shared with Excel users.
17. Keep conditional formatting simple
Conditional formats are meant to make a spreadsheet easier to read, but they can quickly become a mess. If you have too many rules you start to get conflicting ones, you get ones that were copied accidentally, and it can slow down your spreadsheet when every cell is trying to calculate which formatting it should take. So, use conditional formats, but use them sparingly.
18. Don’t ignore new possibilities
Let’s face it, people are creatures of habit. We are quick to do what we know, and slow to learn. Sadly many Excel users use outdated methods and formulas when newer, faster, better solutions exist. Excel is constantly developing, our habits must do the same.
19. Indicate negative numbers clearly
Take the revenue, subtract the costs, what could possibly go wrong? Well if, as in many cases, the costs are saved as negative, then by subtracting them you will actually be performing an addition. When a number is under 0 you’re asking for problems. That’s why we advise you to make negative numbers very clear, for example, you can make them red.
20. Use Power Query
If you are gathering data external to your model, like from another workbook or csv file, then use Power Query. With this feature you can clean up the data before you start working with it, and in many cases Power Query will simply work faster and better than the older features many still use in Excel.
21. Label units clearly
You were taught to do this at school, clearly label units! Sadly it still goes wrong pretty often. Kg’s that are understood as lb’s, hours calculated as minutes. Use number formats, label your columns, or use comments/helper cells to make it clear what a number represents.
22. Document your work
On average, each spreadsheet is used by 13 people. It’s important to verbally discuss your work with your colleagues, but nothing goes above clear documentation. This is not just about how your model works, but should also discuss things like version management. Do you have a log sheet in the model where you track the changes that each user has made? Think about not just finding an old version, but actually understanding what others are doing and why certain things have been changed in the model.
23. Avoid VBA
VBA is found at the heart of many Excel issues. Excel is easy to use, while VBA is much less intuitive. Even in the hands of the best Excel users VBA can be hit or miss. Data gets thrown away accidentally when saving, formulas suddenly stop working, and needed references have vanished, and the error messages are hard to decipher. Recorded macros are easily the worst. These are very difficult to edit and are often bound to their original context, therefore inflexible. A lot of the new Excel functions replace what was once done in VBA, avoid VBA whenever possible!
24. Use simple ranges
You would be surprised at some of the possibilities in Excel. For example, if you separate two ranges with a space, the overlapping cell range will be taken. Or think of a multi-sheet reference, in which you use 1 reference to get data from several different worksheets. It sounds really nice, but it’s very easy to mess it up, and it relies on the current structure of the model. That’s why we advise simple references whenever possible.
25. Delete what you don’t need
Just because a spreadsheet is big and complex, doesn’t actually mean it does very much. It happens way too often that old parts of a spreadsheet just keep existing without any real purpose because no one exactly knows why it’s there. This is where external software can help a lot. If you want to keep a model usable and readable, you must remove unnecessary parts.
26. Mind your version management
A colleague sends you a new version of a spreadsheet, in the cloud there are 2 different spreadsheets with the same name and you had also personally made some edits in one of the versions locally. What’s the newest version? How do your merge the spreadsheets? Version management isn’t difficult if you have clear agreements about naming and saving. Be consistent in this, because when it goes wrong it will be very difficult to find the best version without external software.
27. Properly finalize your model
The finishing touches make all the difference. In Excel this isn’t just visual, proper protection of formulas and sheets is important as well. Test data and helper formulas need to be removed, and correctly labeling and formatting cells and worksheets will encourage users to use the model properly.
28. Don’t take shortcuts
We’ve all said it before, “after the meeting tomorrow, I’ll finish up the model, but it works for now.” In reality this never really happens. The next time you need that spreadsheet you’ll be confronted with a situational, temporary solution that hasn’t been properly finished as a model.
29. Garbage in, garbage out
As great as Excel is, it’s still true that if you put garbage into your spreadsheet, you’re going to get garbage results. That’s we advise you to first properly control, clean, and format your data before working on the rest of the model.
30. Every model needs a second opinion
Building a model on your own is usually fine, although it can cost a lot of time, but you should, at the very least, have someone else look over the model when it’s done. Someone else will always see things just a bit differently. The longer you spend building a model, the harder it is to spot your own mistakes. In an ideal situation pick a few different testers for your model.
31. Don’t use Excel for everything
Excel is awesome, and the best users tend to be a bit addicted to it. Be careful, Excel is usually a great solution, but not always. Things like surveys should be hosted somewhere else and then you can handle the results in Excel. Always consciously make the decision to use software for the task it is best suited for.
32. Don’t reference pivot tables
Pivot tables are a powerful reporting tool, and look great in a dashboard or report sheet. On the other hand, you shouldn’t use them as a calculation tool. Don’t reference a Pivot table because if data shifts the Pivot table won’t recognize the change in cell location, and will present incorrect or incomplete results.
33. Retire old models
It might not feel like it, but even Excel files grow old. Over the years old models will collect little bits and unneeded pieces from older versions of Excel. At a certain point it is simply better to rebuild the model from scratch to ensure your model runs optimally.
34. Use consistent names
Grab every opportunity to keep your model transparent, since it greatly reduces the potential for mistakes. Carefully consider how to name worksheets, row- and column headings, and defined names.
35. Use external sources sparingly
It’s a dilemma. One the one hand you want data to come into your model just right and automatically. On the other hand you don’t want to lose overview of which external sources are being used from predefined locations which could result in issues when using the file somewhere else. Either way the concept remains true: properly organize your external sources. Clearly mark any areas you are using external data, and don’t do this more than necessary. More than 2 external sources is already risky. This doesn’t apply to the proper use and application of Power Query, in that case you can be a bit more flexible.
36. Even you make mistakes
You would assume people with more experience should make less mistakes in Excel. We wish! Research has shown that making mistakes in Excel is a constant. The rule is: The better we get, the more thing we try, and as complexity increases, we make more mistakes. So, do not only trust your own abilities. Build in automatic controls, control your work with external software, and have your colleagues double check your work!