This is where Excel principles come in. ICAEW's “20 Principles for Good Spreadsheet Practice” are an excellent starting point, but also intentionally open-ended. Take principle 11, “Structure worksheets to avoid inconsistent logic and layout”. It’s a great principle, but when is logic inconsistent? What common pitfalls could lead to such inconsistencies?

Principles are the foundation of good Excel models, but to have meaning we need to explore how these principles can be put to practice. Let’s grab a handful of difficult-to-implement principles:

Principle 6: Tailor the design and documentation to the needs of the audience and dependent processes.

When asked what percentage of spreadsheets contain some form of documentation, Excel MVP (and ICAEW Excel Community contributor) Liam Bastick estimated less than ten percent. Creating documentation is something Excel users rarely make time for, let alone tailoring that documentation to fit the needs of the audience. How many Excel users stop to ask themselves if their spreadsheet is also usable for someone who is colourblind or visually impaired?

Here are a few concrete tips to get you started:

• Start with a table of contents: Simply having all the tab names in one place, with a short description for each tab, helps a lot!
• Use colourblind-friendly colours: Choose a palette that includes colours discernible to colourblind individuals. Pro tip: Once you’ve selected a palette, save the formats as styles so you can reuse them in other models.
• Be consistent: This may sound obvious, but if you make an input cell yellow in one place, make all input cells yellow. Consistency makes models much easier to read and understand.

Principle 9: Structure workbooks with a clear flow of inputs, processes, and outputs.

Most Excel users agree with this principle: clear separation results in cleaner models with greater readability and a reduced margin of error. Unfortunately, it’s quite difficult to see in Excel. That’s one of the reasons this continues to go wrong. If a user has not used formatting to clarify where inputs, outputs, or processes are, then it all just looks like numbers or text in cells. It’s up to the Excel designer to add clarity and provide additional information to the users.

Some more concrete tips to achieve this:

• Order your worksheets: Too often, worksheets have a seemingly random order. Whether from right to left or left to right, keep your input sheets, process sheets, and output sheets separate. Settings or documentation spreadsheets should also be grouped.
• Use colours: Minimising formatting can sometimes be the right choice, but colour is an easy way to convey information in Excel. Give different types of tabs different colours.
• Create a visual representation: Making the structure of a model visual helps to maintain and communicate the flow or indicates that some flows may not be as linear as they should be. This can be done by hand or automatically (eg, Microsoft Inquire or PerfectXL Explore).
• Use Trace Precedents & Dependents: An incredibly helpful feature in Excel that shows the precedents or dependents of a cell. Formulas > Trace Precedents. Hotkey: Ctrl + [.

Principle 18: Test and review to reduce the risk of error and identify inefficiencies.

One of the most taxing yet crucial tasks when developing a model is testing and checking. Most checks focus strictly on outputs, but an important part of this principle is also to identify inefficiencies. This not only reduces errors but also the risks of errors. Does the model work with twice as much data? What happens when you insert a row or column? If you have to change a variable, can you do that in just one place?

Asking the right questions can be tricky. Here are some specific things to look out for:

• Excel Errors: Did you know you can search in the whole workbook? You can search for “#” in the whole model by using: Ctrl + F, Options >>, Within: Workbook, Look in: Values.
• Formulas: Look for hard-coded numbers, overly long formulas, and inconsistent formulas. Check if formulas reference all relevant data, and make sure referenced cells are filled. Also, look for volatile functions and approximate lookups.
• Hidden information: Check for hidden sheets, hidden rows and columns, invisible cells (same background and foreground colour), and very hidden sheets (VBA hidden, check developer).

More general tips for testing and reviewing:

• Four eyes principle: It’s important to test and validate your own models, but it’s also very easy to miss something. Involve someone else in the process to reduce risks as much as possible.
• Change the inputs (drastically): What happens to the model if you have much more data, extreme outliers, invalid or very little data? Test some extremes to know how the model responds and adjust accordingly.
• Build in automated checks: Build simple checks into the model. Check for things like empty cells in input sheets, or use simple logic checks with IF statements to catch many of the more obvious risks.

We hope these tips help you to get stuck in and start implementing the 20 principles if you want to see a drastic improvement in the spreadsheets you use. You can read all 20 principles here.

Our team at PerfectXL is committed to helping Excel users build better spreadsheets. We do this largely through our software which helps users build better spreadsheets, following principles and best practices. Interested in how we can help you and your team? Email us directly at: info@perfectxl.com

*The views expressed are the author's and not ICAEW's.