Home

Principles of 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.

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

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

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.

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

Consider the worksheet order // PerfectXL

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

Don't be afraid to add another worksheet // PerfectXL

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

Separate input, output, and calculations // PerfectXL

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

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 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 fixed 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 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.

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. Clearly indicate negative numbers

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.

20. Use Power Query

Use Power Query // PerfectXL

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. 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.

22. Clearly document your work

clearly document your work // PerfectXL

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

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!

24. Use simple ranges

Use simple ranges // PerfectXL

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

Delete what you don't need // PerfectXL

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

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.

27. Properly finalize your model

Properly finalize your model // PerfectXL

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

Don't take shortcuts // PerfectXL

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

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.

30. Every model needs a second opinion

Every model needs a second opinion // PerfectXL

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

Don't use excel for everything // PerfectXL

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

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.

33. Retire old models

Retire old models // PerfectXL

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 external sources sparingly

Use external sources sparingly // PerfectXL

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.

How can PerfectXL help you?

If you have any questions for us around these principles or our software feel free to reach out! Type out your question below: