Principles of good Excel use

Keep it Simple

Het principe “houdt het simpel” geldt óók voor spreadsheets. Eenvoudig betekent dat een spreadsheet gewoon complexe berekeningen moet doen, maar wel in een begrijpelijk en goed gestructureerd model. Een spreadsheet moet niet teveel calculaties tegelijk doen, het moet duidelijk zijn wat er gebeurt en waarom bepaalde keuzes zijn gemaakt.

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.

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.

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.

Remodel // PerfectXL

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.

Don’t be afraid to add another worksheet

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.

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

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.

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.

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.

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

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!

Delete what you don’t need

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.

Delete what you don't need // PerfectXL

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. Testdata and helper formulas need to be removed, and correctly labeling and formatting cells and worksheets will encourage users to use the model properly.

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!