Time for some Key Advice

This article considers perhaps one of the greatest time savers in financial modelling: copying formulae across rows. By Liam Bastick, director with SumProduct Pty Ltd.

Query

I build financial models on a regular basis, forecasting over multiple periods. Typically, I will copy a formula by dragging the mouse across many columns. Is there a more efficient way?

Advice

Sometimes when modelling you need to copy a formula across many columns (e.g. building a 20-year monthly forecast model). Dragging a formula across using the mouse on a regular basis could lead to a claim for Repetitive Strain Injury. Unless these cells are already non-blank keyboard shortcuts such as CTRL + SHIFT + Right Arrow appear useless. However, all is not lost.

The following trick can be used on many occasions.

Imagine you needed to copy formulae across columns J to XZ (say):

• Select the column TWO columns to the right of the last column required (here, this would be column YB)
• A quick way to get there would be to press the F5 function key and then type YB1 + ENTER to go to cell YB1 and then press CTRL + SPACEBAR to select the column YB
• Next, highlight all columns to the right (assuming these are all blank) using CTRL + SHIFT + Right Arrow
• Right click on the mouse and select ‘Hide’ (this will hide columns YB to the end)
• Now return to where the formula will start (say, cell J9)
• Type the formula in and press ENTER
• Select the cell again and then use the keystroke CTRL + SHIFT + Right Arrow which will highlight cells J9:YA9 inclusive
• Having removed all fingers from the keyboard press SHIFT + Left Arrow which will reduce the range to cells J9:XZ9 inclusive
• Having removed all fingers from the keyboard once more, press CTRL + R to fill the formulae into all cells simultaneously.

Practice will make this second nature!

Word to the Wise

Three points to note:

1. On first glance, there appears to be no reason to retain a blank column. However, if column XZ is the final column, CTRL + SHIFT + Right Arrow will take you straight to column XZ. However, if you fill in this point, all of the hidden columns will have formulae copied into them too, leading to potential model errors and needless file size bloating.
2. Some modellers will hide or group columns instead of using the above technique. The problem with this approach is that whilst these columns are hidden, errors may occur in these columns which are only picked up when the fields are made visible once more. This can lead to tremendous re-work which could have been avoided if the developer had inspected ranges periodically instead.
3. CTRL + R will not always retain all formats. If this is important, copy the formula (CTRL + C) before highlighting the whole range and then paste in the usual way (CTRL + V) instead.

If you have a query for this section, please feel free to drop Liam a line at liam.bastick@sumproduct.com or visit the website www.sumproduct.com