01
Keep your formatting consistent
Being consistent means that color usage, fonts, row and column formatting are applied in a predictable way across all worksheets. Consistent formatting of a spreadsheet makes the model easier to follow. The more complicated an Excel model is, the more important consistent formatting becomes. A well-formatted model is good for maintainability, especially for other users.
02
Work like an architect
Spreadsheets often last for years. It is therefore important to choose an architecture that works for all the functional requirements of a model. Architecture concerns the choices made when building an Excel model. For example: Have separate input sheets for database imports, use of tables and Power Query, and handle ID numbers across rows in a consistent fashion.
03
Don’t be afraid to remodel
Form follows function. If the function of a model changes, then the form of the model must also be adapted. Don’t make these changes a ‘hack’; if new functional requirements call for a new calculation method, don’t hesitate to apply the necessary changes. Only by keeping your model up to date you eliminate the risk of turning the model into a convoluted mess.
04
Note the order of worksheets
Large Excel 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 and predictable worksheet order makes a model better to work with.
05
Give each table its own worksheet
It is a common misconception that more worksheets make a model more complicated. Often, the opposite is true. But worksheets that try to do too many things, for example include multiple tables, are cluttered and prone to error. Excel used to be limited in the maximum number of worksheets, but this is no longer the case. Schematically, a model may seem to become more complex because of extra tabs, but in practice you will find that this often makes the model clearer.
“This toolkit finds everything and far beyond. I will be using this for years to come. It is a brilliant system!”
06
Retire your model in time
It may not feel like it, but even Excel files grow old. Over the years, a document accumulates leftovers from previous models and old Excel versions. At some point, it’s better to rebuild a model.
07
Make agreements with colleagues
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. Share these records with all relevant Excel users, such as coworkers and clients.
08
Don’t use Excel for everything
Excel is great and, let’s face it, real Excel users are addicted to it. But be warned, Excel is not always the best choice. Don’t use Excel as a survey platform (but use it to process the results of a survey). Always think about the software you use and whether it’s the best option for the task you’re trying to accomplish.
09
Choose a consistent timeline
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 in one table. 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. Instead, introduce a separate table for every unique unit.
10
Use clear, consistent labels
Take 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.
11
Separate inputs, outputs and calculations
Excel doesn’t make it immediately clear which cells contain formula results and which contain entered values. It’s important to keep them well separated; ideally in separate worksheets, or else through clear formatting.Why is it important to keep formulas separate from other information? Because this way you’ll avoid accidentally overwriting calculations with new input data. When mixing in- and output is inevitable, take extra care to give them a distinctive look.
Meet the PerfectXL Suite: three tools and an Excel add-in for everything you need to check, build and maintain quality spreadsheets.
12
Don’t make spaghetti
Everyone agrees but somehow this goes wrong so often! Before you know it, an Excel model becomes a big mess of spaghetti references. To avoid this, build a slightly bigger model with clear and distinctive input, calculations, and output worksheets. Make sure your calculations only reference inputs, and that your output only references the calculations and the input. Visualize the model to check whether or not this has been done effectively.
13
Keep formulas readable
It sometimes seems like a competition who can write the longest Excel formula. This may seem clever, but it’s an even bigger challenge to write a mathematical model that can be easily understood by someone else. It is a balancing act: by breaking up too much, the reader loses track, by putting too much together in one cell, the formula becomes unreadable. Tip: use clear column names for intermediate results, and place recurring partial results in a separate cell with a clear name. Finally, do not be afraid to move calculations to another tab, to keep things simple for the end user.
14
Avoid duplication
It’s unnecessary to calculate the same thing twice. Calculating once and referring to it elsewhere is better. Not only does this save calculation time (and speed up your model), you also avoid forgetting to adjust one calculation if something changes in a later version of the model. Do you need the result of the calculation often? Then name the cell that the calculation is in (defined name).
15
Don’t use hardcoded numbers in formulas
This should be obvious for most of our readers, but with fixed numbers in formulas, it is easy to go wrong. The numbers become almost unverifiable, because you lack a clear overview of the formulas and which numbers are used in them. Especially if a number recurs frequently (VAT, for example), things often go wrong. Instead, define constants separately (preferably in one place) and then refer to them. Frequently used constants should be identified through a defined name.
16
Never 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 across selection” option. The merging will only be done visually.
17
Protecting is better than hiding
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! Not even in 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 relevant formulas and sheets.
18
Save styling for last
Make sure you don’t have to format twice (or more). If it’s not clear yet how big a table will be, where an input cell will be used, etc, then don’t start formatting, as tempting as it may be! Especially things like the lines between cells often need to be changed when you start with the styling too early.
19
Use simple conditional formatting rules
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 will 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 apply. So, feel free to use conditional formats, but use them sparingly.
20
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.
21
Excel is not a database
Excel is an incredible tool for reporting, documentation, processing information, giving insights into data, and even as a storage space, but it’s not a database. Direct read/write operations through tens of people over millions of records, those are operations for which databases excel! Excel will struggle to perform in these scenarios. That being said, it’s not one or the other, Excel has great functionality that allows it to work side by side with databases.
22
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.
23
Label units clearly
You were taught to do this at school: clearly label units! Sadly this 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.
24
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 also about 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.
25
Avoid VBA when possible
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, 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, and are therefore inflexible. A lot of the new Excel functions replace what was once done in VBA, like the =UNIQUE() function.
“This toolkit finds everything and far beyond. I will be using this for years to come. It is a brilliant system!”
26
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 one reference to get data from several different worksheets. This sounds 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.
27
Delete what you don’t need
Just because a spreadsheet is big and complex, it doesn’t necessarily perform well. It happens much too often that old parts of a spreadsheet just keep existing without any real purpose. This is where external software can help a lot. If you want to keep a model usable and readable, you must continuously remove unnecessary parts.
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 does not happen. The next time you need that spreadsheet you’ll be confronted with a situational, temporary solution.
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 why we advise you to first properly control, clean, and format your data before working on the rest of the model.
30
Don’t reference Pivot Tables
Pivot tables are a powerful reporting tool, and work great in a dashboard or report sheet. On the other hand, due to their dynamic nature you shouldn’t use them as a calculation tool. Never reference a pivot table, because if data shifts, changing the pivot table, the reference won’t update!
31
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 control because of sources that are location dependent. Either way the concept remains true, properly organize your external sources. Clearly mark any areas where you are using external data, and do not reference more sources than necessary. More than two external sources is already risky. This does not always apply to the proper use and application of Power Query, in that case you can be a bit more flexible.
Meet the PerfectXL Suite: three tools and an Excel add-in for everything you need to check, build and maintain quality spreadsheets.
32
Use plugins as tools, not bricks
Add-ins can be helpful for accessing certain data or information allowing you to process complex tasks. That being said, try to build your model in such a way that it can also function properly when no add-ins are present. If an (external) colleague needs to use the model, the current Excel version does not work with the add-in, or you want to work with the model online (for COM add-ins), then your model needs to be self-reliant!
33
One column, one task
Use one formula per column or row in a table. If you use several different formulas, or mixes of formulas and data, your model becomes hard to update, maintain, or expand. Build your tables with consistent formulas so that all users know exactly what to expect across ranges.
34
Build in automated checks
Excel is the “king of the numbers”, use that to your advantage! For example, add-up subtotals and compare that result to the total. Double check that those two numbers are equal. With bigger models we advise a separate worksheet, called a “control sheet.” Use smart formulas there that check if a model is, for example, balancing properly. This way you can see in one glance whether something is going wrong.
35
Mind your version management
A colleague sends you a new version of a spreadsheet, in the cloud there are two 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 is not difficult if you have clear agreements about naming and saving. Be consistent in this, because if it goes wrong it is very difficult to find the correct version without the use of external software.
36
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 zero you’re asking for problems. That’s why we advise you to make negative numbers very clear, for example, you can mark them in red.
37
Develop for the end user
It sounds obvious, but make sure your models meet the needs of your end users. Is it important that they can easily see the input data or is it more important that the model is easy to read with just one dashboard sheet? What is more important, simplicity or more detail? Should you make the model idiot-proof or use transparent, adjustable calculations? When a model just barely misses the mark, problems occur, as end users (non-experts) will make changes to the model on their own.
38
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 double check 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.
39
Even you make mistakes
You might assume people with more experience 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 things we try, and as complexity increases, we make more mistakes. So, do not only trust your own abilities. Build in automated checks, control your work with external software, and have your colleagues double check your work!
