Principes voor goed Excelgebruik

Keep it going

De prestaties en onderhoudbaarheid van modellen zijn van groot belang voor goede prestaties van modellen. Door consistent te zijn in zaken als structuur en formulegebruik, kunnen anderen het model ook makkelijk gebruiken en het verder onderhouden. Zaken als vluchtige functies en kringverwijzingen vertragen uw spreadsheet. Net als bij een auto moet je er tijd in steken om ervoor te zorgen dat je model blijft rijden.

Don’t be afraid to remodel

Remodel // PerfectXL

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.

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!

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.

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.

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.

Use Power Query // 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

Retire old models

Retire old models // PerfectXL

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.

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

Heb je vragen?

We beantwoorden graag al je vragen over de bovenstaande principes en onze software. Gebruik het onderstaande contactformulier en we streven ernaar om je zo spoedig mogelijk te beantwoorden!