Spreadsheet good practice guidelines

Guidelines:

• Values are not hard coded in formulae.
• Scale factors are not hard coded.
• Constants and symbols are in named cells.

Including values in a formula – known as hard coding – is a common, high-risk, practice that often leads to errors.

Examples of hard coding

Hard coding values in formulae is very common. It is also a very common cause of errors.

There are many ways in which values can be hard coded in formulae. For example:

• In the formula =0.3*B7, what does the hard coded value 0.3 mean? If the cell is labelled "Tax", then we might infer that the tax rate is 30%. But what if the tax rate changes? We shouldn't simply search and replace all 0.3 values with the new rate, as some of the values may have other meanings.
• The formula =SUMIFS(B5:B32,A5:A32,">="&DATEVALUE("10/01/2024")) sums values from a specified date. A better formula would put the date in a cell, say D3, and then refer to that cell: =SUMIFS(B5:B32,A5:A32,">="&D3). That approach makes the date visible, easier to change, and the formula is now simpler.
• This formula has several hard coded values: =IF(A2="M",-16.48-0.12*D2+1.99*W2,-9.15-0.015*D2+1.37*W2). Their meanings are not obvious. Each value needs to extracted from the formula and put in separate cells. We can then label the values, to indicate their meaning.

In this context, we don't just mean numbers. Hard coded values can also include dates, text, and arrays (for example, {1,2,4,8,16}).

Problems with hard coding in formulae

Potential problems that can result from hard coding values in formulae include:

• The meaning of the data may not be clear. Even if the meaning is clear when the formula is written, will it still be clear to another user later?
• Updating the data may be problematic, as the user must find and replace each instance of every value, without incorrectly replacing different data with the same value. Not only is this inefficient, but being confident that all instances have been found and replaced correctly may be difficult.
• Results may be incorrect, due to multiple, inconsistent copies of the data being used.

Use named ranges instead of hard coding

Rather than hard coding values in a formula, a better approach is to put the value in a cell or block of cells. Doing this has several advantages:

• All formulae that use the values have a single source.
• The values can be easily changed.
• We can label the values, defining what they mean.
• Units or other information can be added, to aid the user.
• Other documentation, such as a cell comment, can be added to supply further guidance for the user.

Importantly, we can name the cell(s), to make the formulae more meaningful.

Avoid scaling factors hard coded in formulae

A specific case of hard coded values in formulae is the common practice of using scaling factors that convert between orders of magnitude.

For example, we have a dollar amount in B4 and in another cell =B4/1000 expresses the amount in units of $000. This is usually a bad idea, as it can lead to formulae that inadvertently work with values of different magnitudes. In addition, if we later want to change the scaling factor, say to units of $1,000,000, then doing so quickly and accurately can be difficult.

A better approach is to change the display of the values using a custom number format, like #,##0.0,;-#,##0.0,;- for multiples of $1,000. This changes only the display – the underlying values are unchanged.

But if you must scale the values, then put the scale factor in a named cell and refer to it. For example, put the scale factor of 1000 in a cell named Scale, then use a formula like =B4/Scale. This approach still has the risk of formulae working with numbers of different formulae – you'll need to manage that risk separately.

Hard coded values might cause problems later

The image shows a discounted cash flow model designed to evaluate an investment opportunity. That is, given an initial investment amount, the opportunity returns a series of annual dividend payments plus a final payment. The net cash flow each year is intended to be discounted using a rate of 8.00% pa, as specified in C7.

In E15, we use the NPV function to calculate the Net Present Value of the cash flows (adjusted for the appropriate timing of those cash flows). The positive NPV of $1.18 indicates that this is a worthwhile investment.

As a check, we select the discounted cash flows in E10:E14 and note that the sum displayed in the Status Bar matches the calculated NPV.

We then decide that the discount rate should be 9%, so we make that change in C7. The Status Bar now says that the sum of E10:E14 is -1.30, while the NPV is still 1.18. So, what went wrong?

Like in a real spreadsheet, the image hides the problem. We can see the value in E15 is displayed as 1.18. What we don't see is that the formula in E15 is =D10+NPV(0.08,D11:D14). That formula was correct when the discount rate was 8%, but that rate is hard coded in the formula. Changing the assumption in C7 has no effect on the NPV calculation.

The correct formula should be =D10+NPV(C7,D11:D14), which now returns the correct NPV of -1.30. Importantly, using the higher discount rate, the NPV is negative – meaning that the investment opportunity is not worthwhile.

A further improvement would be to name the discount rate cell, then use that name in all formulae that use the discount rate. For example, C7 could be named DiscountRate, or possibly Disc as a short name. The formula in E15 would then be =D10+NPV(Disc,D11:D14). Using the named range makes it easier to interpret the formula. It would also make it easier to find all cells that use the discount rate.

Don't use symbols directly in formulae

Sometimes we want to use a symbol to mark a value in some way. For example, using a tick or a cross to indicate if a result is good or bad. We can use Conditional Formatting for this purpose, which is usually a better method. But sometimes we want to use a formula, like:

=IF(B10<=$B$3,"✘","✔")

Instead of hard coding the symbols in the formula – or potentially many formulae – put the symbols in cells and name them. Then the formula can be:

=IF(B10<=$B$3,Bad,Good)

This approach makes the meaning of the symbols clearer. Also, if we want to change the symbol, then we need to change only one cell instead of every place where the symbol is used.

Exceptions for special values

Exceptions can be made for special values, such as 0 and 1, or other values where the meaning is clear. For example, we can convert a number stored as text to be an actual number by multiplying by 1, like =1*A3. We don't need to put the 1 in a separate cell.

However, be sure that the meaning is clear to other users, not just to you. It might be obvious to you that the formula =D5*3.6 makes use of the fact that 1 kWh is equivalent to 3.6 MJ. But other users of your spreadsheet may not immediately recognize the meaning of that 3.6 number in a formula, so make it clear for them.