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, consistent labels: Titles, row & column headers, named ranges, and clear worksheet names.
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.
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.
Avoid hard coding 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.
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.
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.
Build in automated checks
Excel is the kind 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.
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.
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.
Clearly indicate negative numbers
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.
Clearly label units
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.
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.
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!
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.
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.
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.
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.
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.
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.
Retire old models
It may not seem like it, but Excel models get old. Throughout the years Excel models begin to collect random little bits of unnecessary data and objects from earlier versions of Excel. Eventually you have to make the decision to rebuild.
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!