A few how-to rules for getting your formulas right.
Download a sample Excel spreadsheet for this article
Using formulas in Excel can rid you of hours of manual input while also making your reports more accurate. But there are a few rules for getting your formulas right.
Could you write an article on Excel guidelines?
This question was asked during a break at CPA Week.
Guidelines can be applied to many aspects of Excel.
This article will focus on four guidelines for formula creation.
1. Avoid keyed in values in formulas
When entering a value into a formula consider whether that value could ever change.
If the value could change, then it should be entered into a separate cell and a reference to that cell included in the formula. This adds more flexibility and transparency to the spreadsheet.
Input cells should:
- be clearly labelled as part of the sheet documentation
- use a consistent colour scheme (I prefer a light yellow fill colour)
- be positioned together
In general, you should not be editing formulas to change values as part of the normal operation of the spreadsheet.
An example that demonstrates this is included with the next guideline.
2. One formula to rule them all
The goal when creating a formula is to maximise the number of cells that the formula can be copied to.
This reduces maintenance and development time.
This may involve adding extra cells to the sheet to enable you to make the final formula simpler and easier to copy.
It can also involve using fixed, mixed and relative references.
Table 1 has examples of the three types of references.
The F4 function key is the shortcut key to add $ signs to references when creating or editing a formula.
When pressed initially it will convert the reference into a fixed reference, each press of the F4 key will move the $ signs around the reference until it returns to a relative reference and the cycle starts again.
To remember F4 is the shortcut key, note the number 4 key has the $ sign above it.
Figure 1 has an example that encompasses the first two guidelines.
Normally the data and the report are on separate sheets, but for this example they are on the same sheet.
Columns A to F contain the table that we will extract from using a VLOOKUP function.
The range I1:K1 contains helper cells that will enable creating a single formula that can be copied across and down.
The range H3:H5 contains the input cells for the codes to extract.
The formula for cell I3 is: =VLOOKUP($H3,$A$2:$F$7,I$1,0)
In many cases, the column number to extract in the VLOOKUP function is keyed in.
I have linked this column number to row 1 using the reference I$1. This enables the formula to be copied across and down.
When the column number is keyed in you have to manually amend the column number for each formula as it is copied across. Helper cells simplify the formula creation process.
Note the use of the 0 at the end of the VLOOKUP function. This is used instead of the word FALSE which specifies an exact match for the lookup.
FALSE and 0 mean the same in Excel. It is quicker to type 0 and it shortens the formula.
An alternative formula can eliminate the need for helper cells. It uses the MATCH function. I will cover this function in more detail in the companion video.
The alternative formula for cell I3 is: =VLOOKUP($H3,$A$2:$F$7,MATCH(I$2,$A$1:$F$1,0),0)
3. Centralise your logic calculations
When working with reporting or budget models you often need to perform a number of logic tests.
These logic tests are often repeated in many formulas. Rather than repeat the logic it is more efficient to centralise the logic calculation in a single row and refer to that row in subsequent formulas.
Examine the structure in Figure 2.
Cell B1 specifies how many months of actuals to include in the report. The report is a revised forecast combining actual and forecast figures over the months.
We need to identify which months are actuals and which are forecast. Typically an IF function is used for the formulas in rows 7 and 8.
There are two other sheets called Actuals and Forecast with identical layouts where the values will be extracted from. The normal type of formula for cell B7 is: =IF($B$1>=B$3,Actuals!B7,Forecast!B7)
This formula can be copied down and across, meeting our second guideline, and it has no keyed in values.
Rather than including the logic test $B$1>=B$3 in the individual IF formulas, it is preferable to determine whether the column is an actual or a forecast in a single row. Row 5 will be used to hold the result.
The formula for cell B5 is: =$B$1>=B$3
This formula displays TRUE if the month is an actual and FALSE if it isn’t. The formula for cell B7 becomes: =IF(B$5,Actuals!B7,Forecast!B7)
The advantage with this is that if we need to amend the logic for determining actual and forecast values we only need to change one formula and copy it across, rather than changing all the IF formulas below.
Note: You may have thought the amended formula for B7 should have been: =IF(B$5=TRUE,Actuals!B7,Forecast!B7)
But when a cell contains TRUE or FALSE you don’t need to compare it to TRUE, you just need to refer to it.
4. Use range names in Excel formulas
Using range names has several advantages:
- faster formula creation
- self-documenting formulas
- reduced need for $ signs in formulas
- simpler formula maintenance
- improved spreadsheet structure
Range names may add an extra level of complexity to spreadsheets, but their advantages far outweigh any disadvantages. We can see a quick demonstration of their use with our previous example.
Row 5 has been named IsActual, see Figure 3.
I can then amend the formula in cell B7 as follows: =IF(IsActual,Actuals!B7,Forecast!B7)
The beauty of this formula is that there is no need for $ signs and it also makes the final formula easier to read and understand. The related article on itbdigital.com will include an example file plus a video.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected].
This article is from the November 2014 issue of INTHEBLACK.