Create & Maintain good Spreadsheets
In order to create and maintain with consistently good spreadsheets, there are a number of good practices to keep in mind. Here we provide a list of tips to improve the way you work with spreadsheets.
We want to help you create and maintain good spreadsheets. That’s why we’ve set up a list of expert tips, that address commonly made mistakes when using Excel.
Because of its flexibility, Excel is considered an easy general-purpose tool for spreadsheets. However, being easy to use comes with its own drawback. It is very easy to make mistakes in complex Excel spreadsheets. Errors that are commonly found in Excel spreadsheets are incomprehensible calculations, complex relations, hidden information or just plain mistakes.
Use these guidelines as a tool to create clear and concise spreadsheets. Do you want to make sure your spreadsheet is as good as it can be? Upload your spreadsheet with PerfectXL for a free risk analysis.
Before developing your spreadsheet, choose an organization standard and stick with it for as long as you’re using the spreadsheet. An organization standard sets the stage for all future users who end up working with the spreadsheet. A shared standard improves communication and saves up in development time. Standardized organization may include cell formatting, general layout, color scheme, ordering, etc.
Put different kinds of data on different worksheets. For example, use the first few worksheets for input information, the following worksheets for calculations and the last sheet as a presentation worksheet for graphs and results. Limit the amount of tables per worksheet to just one. Multiple tables per worksheet cause problems when attempting to sort, insert or format cells.
Explanatory worksheets show other users how the spreadsheet should be used.
When creating a large number of worksheets, add an explanatory worksheet or a worksheet dedicated to a table of contents. This makes it easier for someone to understand how the spreadsheet is set up. Explanatory worksheets also show other users how the spreadsheet should be used.
If your worksheet is user oriented, use an attractive “Results Worksheet”. If not, don’t! Most worksheets work best when they are designed to provide clarity of all present calculations. You can try splitting up long formulas, but do not hide them to provide more clarity for users.
Try to organize worksheets in such a way that information always flows from top left to bottom right. This makes it considerably easier for a user to understand how the sheet works. Exceptions to the rule are an “Input Worksheet” and/or a “Results Worksheet”. Putting these at the start increases clarity for users, which is especially useful for a user oriented worksheet. When managing the flow of information, avoid criss-cross dependencies as they greatly detract from understandability. At all times avoid circle chain relations, if a link to any previous data is needed.
Splitting up calculations into multiple smaller calculations is a great way to increase readability, as is correct use of spacing. Too many different operators or too many different references in a formula can make the formula unreadable. This causes the spreadsheet to be hard to use by anyone other than its creator. The original author ends up always being the one having to explain it to others, or even ends up being the only user actually able to work with the spreadsheet.
Many spreadsheets are hard to use by anyone other than its creator.
Use a separate input cell for fixed values and employ references to this cell in order to use it in calculations. A formula that contains fixed numbers is a major risk. If the value ever changes, it needs to be changed in every instance it appears. This is, at the very least, cumbersome when dealing with large worksheets. At worst, it is one of the easiest ways to create mistakes.
Hiding columns, rows, or even entire worksheets from view is almost never a good idea. It only increases the chance that a user overlooks something important when working with the spreadsheet, increasing the risk for errors. The only exception to this rule would be when it is absolutely necessary to hide information that somehow can not be put into a separate worksheet. Since it’s almost always possible to separate sensitive data, these cases are rare.
Hiding data increases the risk for errors.
Building in data verification, such as audit tests, alerts and automated checks, is a good way to avoid making any damaging changes or additions to existing work. It is important to build these in when the worksheet is created. Not doing so often leads to forgetting about them later on. Data verification is an excellent way to build a strong foundation for worksheet maintainability.
Your choice of styling should remain consistent throughout the entire spreadsheet. A simple and consistent style for formatting is critical to help viewers understand your spreadsheet. Always include a legend. Abbreviations and colored cell definitions can be listed on a separate worksheet if needed.
Conditional formatting helps the viewer understand how the spreadsheet works. Complex formatting rules defeat that purpose by obscuring how the spreadsheet is set up. It also causes confusion for the user. Keep the rules for conditional formatting simple (e.g. changing colors based on cell content).
Make sure viewers can always trace the units that are used in the spreadsheet. You can do this by either setting the units in the cell properties within Excel, or you can mention them in a column label. But beware: never type a currency symbol (€, $, £ etc.) directly after a value. This causes problems when using the value in a formulas.
Always use Excel’s solutions before resorting to VBA macros. VBA macros make the spreadsheet less transparent, as they abstract away logic. For some tasks, VBA macros even perform worse than Excel’s default solutions.
For many calculations in your spreadsheet, it is good practice to incorporate additional empty rows or columns. This prevents future edits of the spreadsheet from messing up your formulas. The less you use separate small ranges, the smaller the chance of erroneous calculations in the future.