Principles of good Excel use

Keep it safe

Spreadsheets are generally used by 13 or more people and 88% of spreadsheet contain serious risks. Making sure your formulas are calculating properly, treating sensitive information with respect, and paying attention to protection are just a few of the ways you can keep models safe.

This concept makes sense, but the questions is how do I practically do this? On this page you will find 14 principles that will help you keep your spreadsheets safe.

Use consistent labels

Use consistent names // PerfectXL

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 Timeline Consistent // PerfectXL

Keep formulas readable

Keep formulas readable // PerfectXL

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.

Avoid fixed numbers in formulas // PerfectXL

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.

Protect. don't hide // PerfectXL

Build in automated checks

Build in automated checks // PerfectXL

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.

Keep your formatting consistent

Choose a uniform architecture

Choose a uniform architecture // PerfectXL

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 indicate negative numbers // PerfectXL

Clearly label units

clearly label units // PerfectXL

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.

clearly document your work // PerfectXL

Avoid VBA

avoid vba // PerfectXL

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.

Use simple ranges // PerfectXL

Mind your version management

Mind your version management // PerfectXL

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.

Don't take shortcuts // PerfectXL

Garbage in, garbage out

Garbage in, garbage out // PerfectXL

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.

Every model needs a second opinion // PerfectXL

Don’t reference Pivot Tables

Don't reference pivot tables // PerfectXL

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.

Retire old models // PerfectXL

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!

Have any questions for us?

We’re happy to answer questions about these principles, our software, or really anything else. Use the contact form below to get in touch and we will respond as soon as we can!