Excel training

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

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

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 consistent names

Use consistent names // PerfectXL

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

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.

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!

More about Excel best practices

Follow our Excel Training for Businesses as a team. Improve transparency and communication around Excel within your company resulting in fewer mistakes, and a faster, more efficient work environment.

Follow our 4 week course on the basics of Excel Modelling. Learn from the best Excel users in the world, and watch your Excel use improve immediately!

This course was developed to help users that already understand the basic principles in Excel to greatly improve their skills. Read exactly what you will learn in our program.

This course was designed by PerfectXL (Infotron) in collaboration with 11 of Microsoft’s designated “Most Valuable Professionals” and 3 of the most prestigious universities of the Netherlands.

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.

To create and maintain with consistently good Excel files, there are a number of best practices to keep in mind. Here we provide a list of Excel tips to improve the way you work with spreadsheets.

This is a question people are starting to ask more often today. Back in the 1990’s at the peak of Excel’s popularity it wasn’t even a question, but times change. Now the market is filled with alternatives…

Which VBA developer isn’t familiar with the battles fought against dreaded Excel Errors? It can be a real rough and tumble to bend VBA to your will.

Excel training, it’s so incredibly important! There are many courses for quick tips and tricks, productivity, shortcuts and basics, but what was missing? A training on Best Practice and design.

By bringing consistency to your formatting you automatically improve the quality of your spreadsheets. Not only will it look better, thinking about the purpose of the cells in your model and how to format them accordingly will change your mindset too.

This article is a list of 5 excellent Excel blogs with different purposes and strengths, where you as a user can learn more about the wonderful world of Excel!

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: