Excel is often considered a useful general-purpose tool because of its flexibility. However, the same flexibility can make its usage very risky: hidden information, incomprehensible calculations, complex relations, or even just plain errors. To help you minimise the risks associated with the usage of spreadsheets, Infotron has devised a list of expert tips in coordination with the research department Spreadsheet Lab at the TU Delft.
Glance over these guidelines and let them inspire you to build clear and concise spreadsheets. Want to be certain that your spreadsheet is as intelligible as can be? Have one of our experts look at it or take a look at the options at our spreadsheet analysis tool PerfectXL.
Use 1 table per worksheet
Put different kinds of data on different worksheets. For example, put the input information on the first few worksheets (1 table per worksheet), the calculations in the worksheets that follow and end with a presentation worksheet where you can put graphs and results. Multiple tables per worksheet might give you trouble when attempting to sort, insert or format cells. Therefore, it is wise to use at most 1 table per worksheet.
Don’t repeat yourself
Avoid repetitive calculations. An (exact) duplicate of a formula doubles the risk for errors. In addition, changes in one formula aren’t automatically replicated in duplicates, which as a result are frequently forgotten. Our tip is to refer to an earlier calculation when the same result is required, rather then exactly repeating the same formula.
Use column labels
Columns without clear and unique names might not be problematic for you, but to others it might appear confusing at least. That is why you should always label columns of tables. The same applies to horizontal tables, only use row labels in such case.
Use positive numbers
Ever accidentally subtracted what should have been added? Chances are this was the result of an input cell that was entered as a negative number. For this reason, with a few exceptions, always try to build a spreadsheet that promotes the use of positive numbers.
Make worksheets work left to right
Try to organise worksheets so that information flows left to right. This makes it considerably easier for a reader to see and understand what happens. Exception to the rule are an “Input worksheet” and “Results worksheet”, putting these at the start increases clarity. At all times try to avoid circle chain relations.
Keep the timeline consistent
It is advisable to keep the timeline consistent across worksheets, even if this means empty rows will emerge. Consistent timelines vastly improve the understandibility of the spreadsheet for others taking an interest and it reduces the risk of incorrect formulas. For example, to shorten four months into quarterly figures might not cause issues in terms of clarity; it might very well result in incorrect totals. Put a timeline exactly once per worksheet, at the top in a frozen header row.
Work from the top left to the bottom right
It is preferable to make information flow from left to right and top to bottom within a worksheet. An author will usually know how cell relations are set up, but to someone external there is little as tiresome and annoying as criss-cross dependencies.
Clarity over looks
A “Results worksheet” is okay to be user-oriented. Other worksheets are most elegant when they are designed for clear calculations. So split up long formulas and don’t hide cells.
Never hide anything
It’s rarely useful to hide columns, rows or even entire worksheets. Hidden information reduces overal clarity and increases the risk for errors during checks and modifications because of one might overlook hidden rows.
Don’t merge cells
Merging cells is seldom good practice. It usually seems to benefit looks, but eventually only leads to problems with calculations and references. The biggest risk is referencing merged cells. All merged cells can be part of calculations, but only 1 is correct.
Clarify your sources
When complicated calculations are made on cells from other worksheets, it can be good practice to refer to these cells first, after which formulas only refer to the references mentioned in the same worksheet. This way someone reading the worksheet does not have to constantly switch between various worksheets to discover where information comes from.
Be clear which units are used
Make sure that one can always trace the units used. This can be achieved in two ways: it can be set in the cell properties within Excel or by means of a mention in the column label. N.B.: never type a currency symbol (€, $, £ etc.) literally after a value, this will make it very hard to use in formulas.
Use simple ranges
In many calculations it is good practice to incorporate (additional) empty rows or columns. This prevents “changing circumstances” from breaking your formulas. The less you use separate small ranges, the smaller the chance of erroneous calculations in the future.
Keep formulas readable
Too many different operators or too many different references in a formula can make it unreadable and hard to understand. This makes the spreadsheet no longer transferable to other editors, which leaves the original author to be the only one who can work on it. To split up calculations into multiple smaller calculations is a key to achieve this, as well as using spaces when this promotes readability.
Do not use fixed numbers in formulas
A formula that contains fixed numbers forms a risk, for the values are almost always variable. When the value changes, changing it everywhere it’s used can prove to be quite a challenge. A good solution is to use a separate input cell for fixed values and employ references to this cell in order to use it in calculations.
Annotate your spreadsheets
Ever considered adding a separate worksheet for explanatory purposes? A table of contents might also be useful. It makes it easier for someone to gain insight into the structure of the spreadsheet. It can also emphasise how it should be used.
Apply consistent styling
A simple and foremost consistent style in formatting tables and graphs is critical to help users understand your spreadsheet. Also, don’t forget to include a legend. Abbreviations and coloured cell definitions can be listed on a separate worksheet. But whatever you choose your styling to be, above all: keep it consistent. This keeps the spreadsheet predictable.
Use macros only as a last resort
VBA macros are a great tool to fill the blanks in Excel, adding specialised functionality where needed. Unfortunately, it also makes the spreadsheet less transparent by abstracting away logic. For some tasks, it doesn't even perform as well as Excels default solutions. Therefore, you should always try to use VBA macros only as a last resort.
Save the glamour for the end
Excel's formatting options are quite extensive: fine-tuning the appearance of cell values and plenty of options for colours, borders and features alike. Though it is true that styling can help to keep your spreadsheet clear, frequently the process of "prettifying" your spreadsheets includes hiding information for the end user. This is good, but it makes spreadsheet development considerably harder and the risk for errors increases. That's why you should wait with styling your spreadsheet until the very end.
Keep Conditional Formatting rules simple
Rules of conditional formatting – e.g. changing colours based on cell content – should be kept simple. Conditional formatting should aid the end user understand what's happening. Complex formatting rules surpass that purpose by obscuring the true working of the spreadsheet, confusing the user in the process.