Core modelling skill 2: How to create a link

To obtain the worked example file to accompany this chapter buy the financial modelling handbook.

When we created the power tariff input, we expressed the value in USD / kWh.

The standard monetary unit in the model is thousands of dollars. We have modelled electricity generation in GWh. We, therefore, need conversion factors.

The conversion factors we need are already on the input sheet.

We need to link to them to bring them into our calculation block.

Step 2: Copy the label


Ctrl+Shift+q triggers the "quick link" from the Productivity Pack macros. The macro knows when you create a cross-worksheet link and will mark the link as an import.


Ctrl+Shift+a triggers the "copy across" operation from the Productivity Pack macros. The macro knows when you are linking to a constant and will only copy across as far as the units column. As you'll see below, when you link to a series line item, Ctrl+Shift+a will copy all the way over the timeline.

Repeat the process for the other constant we need, units in a thousand.

We now want to link to electricity generation.

We will repeat the steps above, but this time we are linking to a line item that a. on the same sheet and b. is a series, not a constant.

The steps are the same, however.

Step 2: Copy the label


Notice that this Quick Link macro knows that you are creating a link to a calculation on the same sheet - and therefore formats it in black rather than blue font.


Notice that on this occasion, the copy across macro knows that you are linking to a series item and copies it all the way over the timeline.

What are the productivity pack macros actually doing?

Both the "Quick link" and "Copy across" macros perform a series of steps that would be laborious to do by hand. However, it is helpful for you to know what steps the macro is performing.

The steps below list out what the macro is doing, and the keystrokes you would use if you did this operation manually. As you can see, the manual process involves a lot of steps that are automated by the macros.

Step 1: Paste Special

Alt, E, S

Step 2: Paste Link

L (if paste special dialogue is active)

Step 3: Fix row anchoring

Note: See Skill 4 for more information on why links should always be row anchored

F2 (to enter edit mode), F4 twice to fix row anchoring.

Step 4: Mark as import

If the link is imported from another sheet, mark it as an import.


Copy across

Every time we create a link or write a formula, the next thing we want to do is copy it across the timeline.

Performing this manually without the copy across macro would involve the following steps. These are the steps that the macro is performing:

Step 1: Copy the active cell


Step 2: Copy across the timeline

Shift+Ctrl+right arrow

Step 3: Return to the left of the timeline


Step 4: Paste into the copied cells


Note: Enter pastes the clipboard's contents, and clears the clipboard. Ctrl+v pastes the clipboard's contents but leaves the contents in the clipboard.

Step 5: Return to the left of the timeline


Step 6: Perform a local recalculation


Note - this calculates the active sheet only. It is a valuable check that what you have copied across is working as expected, without recalculating the whole model.


Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.

Subscribe to Financial Modelling Handbook

Don’t miss out on the latest financial modelling guides. Sign up now to get access to the library of members-only guides.