Locked lesson.
About this lesson
The proper habits to adopt when linking cells together, and the reasons why.
Exercise files
Download this lesson’s related exercise files.
Linking Tips.xlsm60.2 KB Linking Tips - Solution.xlsx
12.6 KB
Quick reference
Linking Tips
Learn some tips for linking within your financial models.
When to use
Linking workbooks can assist in building a financial model in Excel which is easy to understand and navigate through. Import and export sheets should be mirror images.
Instructions
Overview
- Formula links between worksheets in different workbooks
- Make it clear what is intentional:
- External workbook exports
- Links to an external workbook should be located on a dedicated (export) sheet
- External workbook imports
- Links from an external workbook should be located on a dedicated (import) sheet
- External workbook exports
Example
- To link an export sheet to an import sheet:
- Copy the entire export sheet
- Paste as ‘Special’ – ‘Values’ and ‘Formats’ into the import sheet
- Create a formula in cell A1 to link back to the export sheet – ensuring that the $ are removed from the formula by hitting the F4 button
- Copy that cell - CTRL + C
- Before pasting hit the F5 button
- Select ‘Special’ and ensure the ‘Numbers’, ‘Text’, ‘Logicals’ and ‘Errors’ are ticked
- Paste ‘Special’ selecting Formulas – this will link the sheets together
- 00:04 This next session can be a little controversial sometimes.
- 00:07 It's frowned upon to link files in some quarters
- 00:10 of the financial modeling community.
- 00:14 >> Quite frankly though, sometimes it just can't be helped.
- 00:18 It may be, you've got files that are far too large to put them all in one workbook.
- 00:22 It may be that people need to work on them in different locations.
- 00:26 It may be some of the data is confidential.
- 00:28 It could be that they have sort of different purposes and so on and so
- 00:32 on and so on.
- 00:34 Look sometimes linking models is just a fact of modeling life.
- 00:38 Let's take the two examples here, Workbook 1 and Workbook 2,
- 00:42 that just coincidentally have the same number of sheets.
- 00:46 Don't read anything that.
- 00:48 Now, most people,
- 00:48 when they're linking workbooks, use the industry standard URM approach.
- 00:54 Never heard of it?
- 00:55 I'm sure you have, I'm sure you use it.
- 00:58 Look, let me give you an example.
- 01:00 It may be that Sheet 1 from Workbook 1 links into Sheet 1 in Workbook 2.
- 01:06 And also Sheet 2 has something that links into Workbook 1.
- 01:09 And by the way, there's something from Sheet 3 that works into Sheet 5,
- 01:13 something from Sheet 4 that links into Sheet 3,
- 01:16 something from Sheet 5 that links into Sheet 7.
- 01:18 And Sheet 6 links into Sheet 4, and Sheet 7 links into Sheet 2, my goodness, no!
- 01:25 Don't do this, we're out of control!
- 01:26 I don't know what should be linked where.
- 01:29 How am I going to maintain it?
- 01:31 Or what if there's something from Workbook 2 that links back into Workbook 1?
- 01:35 You could have a circular reference and not even realize it.
- 01:38 This is crazy.
- 01:39 Let's start again.
- 01:42 A better approach is before we start off is to create a worksheet in Workbook 1
- 01:47 where all the actual references that are going to be exported will be contained.
- 01:53 We'll call that an export sheet,
- 01:55 we'll create a mirror image of this as an import sheet in Workbook 2.
- 02:00 Now find all the things you need to link in Sheets 1 to 7 or Workbook 1.
- 02:04 And feed them first into the export sheet.
- 02:07 Then link those into import on Workbook 2.
- 02:12 That can then be disseminated into the workbook, be controlled, and
- 02:17 you have one place where you can find all the links.
- 02:20 Exporting Workbook 1, importing Workbook 2.
- 02:23 Easy, I think it's time to go back to our good old Excel template
- 02:28 file you should be getting familiar with now.
- 02:31 This time I'm using a variant that's going to illustrate the export, import idea.
- 02:38 So, here's my base template form.
- 02:41 Let's take a quick look at it first of all.
- 02:43 I'm gonna click on the Navigator tab here.
- 02:46 And you can see, I've got three sales assumptions sheets here, domestic,
- 02:51 overseas, and underseas, and an export sheet.
- 02:54 So let's take a peek.
- 02:57 If I click on the domestic one, first of all, you'll see I've got sales volumes for
- 03:00 domestic product.
- 03:04 And if I go to the next sheet, I have corresponding values for
- 03:08 overseas, and the next one for whatever underseas is.
- 03:12 Yes, thank you Liam for
- 03:13 the new terminology I've introduced to the English language.
- 03:17 The next sheet's interesting as well, the Export tab.
- 03:20 This is the sheet we need to, if you recall,
- 03:22 where you bring in everything you're going to export in.
- 03:25 So I wanna clear we're going to export domestic but you know what I mean,
- 03:30 we're going to export to another workbook.
- 03:32 I've chosen the delightfully named Gottlieb and Rancid products,
- 03:36 I'm sure they're big sellers.
- 03:39 Here this links to row 23 of the domestic sales assumption worksheet.
- 03:43 This one to row 34 and something I've linked things from the overseas
- 03:48 worksheet 2 row 25 for Indigo, row 40 for Xylophone, etc.
- 03:53 So what I need to do is create a mirror image of this for
- 03:56 the import sheet in the alternative workbook.
- 04:00 To do that, I need to create a new Excel workbook,
- 04:05 which I'll do by going Ctrl+N.
- 04:08 Here's my new Excel workbook, this is where I want to make a copy of the data.
- 04:14 Now if I just go copy and paste, I wanna create links,
- 04:16 I'm gonna bring in all the styles and everything.
- 04:19 I don't want that, I want to go copy paste special.
- 04:22 Now you might think I'm going to paste special formulas there and
- 04:27 maybe formats afterwards.
- 04:29 No, you'd be wrong.
- 04:30 And the reason being, if I copy and paste special as formulas,
- 04:34 I'm actually going to link back to the sheets,
- 04:38 the domestic, the overseas and the underseas assumption sheet.
- 04:42 Because that's what the current export sheet is doing.
- 04:46 Don't want to do that.
- 04:47 What I'm going to do, surprisingly, is copy and
- 04:51 paste special, get this, as values.
- 04:55 And then put formats in afterwords.
- 04:57 Let me show you.
- 04:59 So let me go back to the other one.
- 05:01 So this is the export sheet.
- 05:04 I'm going to highlight this entire sheet.
- 05:07 The way to do that is go and click on the top left-hand corner and
- 05:11 then go Ctrl+C, and that's copying the whole thing.
- 05:16 And now I'm going to go back to the other workbook, Alt+Tab,
- 05:19 and Paste Special as values.
- 05:25 So into here and go down to values and press Enter.
- 05:32 Now I've done that, I want to Paste Special as formats too.
- 05:38 Alt+E+S+T, Enter, voila!
- 05:44 Yes, I've got the grid lines.
- 05:45 Yes, it looks slightly different 'cause it's a slightly different workbook
- 05:48 formatted differently.
- 05:49 I've got the colors a little off here.
- 05:51 But you get the idea.
- 05:53 This is what it is.
- 05:53 I can put a frozen pane in here.
- 05:56 It's very easy to do.
- 05:57 All I've got to do is go up to the View tab, into Freeze Panes.
- 06:01 Freeze Panes as we did before.
- 06:03 I could even switch the grid lines off and make it look a little neater,
- 06:06 should you wish.
- 06:07 And everything here is a value.
- 06:13 Why have I done that?
- 06:14 Well, now I'm gonna go back here, and I'm going to go = and
- 06:18 link back to the other workbook.
- 06:23 Click on that.
- 06:24 And change this, get rid of the $ sign.
- 06:27 So it's just A1s, so copy it, press Enter.
- 06:29 Now, let's copy that with Ctrl+C.
- 06:33 Now before I paste anywhere, I'm going to bring the F5 function key up, or
- 06:38 Ctrl+G, to bring up the Go To dialog box, and click on Special.
- 06:44 And I want to choose Constants, and I want it to be Numbers, Text,
- 06:48 Logicals, Errors, all the values it finds on here.
- 06:51 If I click OK, it's now selected all of those.
- 06:56 And now I can Paste Special as formulas into there.
- 06:59 That's Alt+E+S+F, for formulas, and then click OK.
- 07:05 Voila, everything here now links back to the other workbook,
- 07:10 in the same place, but nothing in any of the blank cells.
- 07:15 That's a pretty cool trick.
- 07:16 And now I've got a complete link,
- 07:18 and I change this to import, change the name of this file, rename this, I'm done.
- 07:23 Very easy way of creating an import sheet from an export one, I think you'll find.
Lesson notes are only available for subscribers.