Locked lesson.
About this lesson
Layout tips can assist in building a financial model in Excel which is easy to navigate through and understand.
Exercise files
Download this lesson’s related exercise files.
Layout Tips Part 1.xlsx7.7 KB Layout Tips Part 1 - Solution.xlsx
8.3 KB
Quick reference
Layout Tips Part 1
Layout tips explained using the CRaFT methodology.
When to use
Layout tips can assist in building a financial model in Excel which is easy to navigate through and understand.
Instructions
- Layout Tips
- Narrow the first few columns (highlight columns, then right-click):
- Enter the Sheet Title in cell A1 using the formula shown below. This will be the same as the description in the sheet tab:
=IFERROR(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))),"")
Hints & tips
The formula
=IF(ISERROR(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))),"",(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))))
can also be used to get the same results and can be used in any version of Excel. The formula
=IFERROR(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))),"")
uses the function IFERROR which only came into being in Excel 2007 onwards to avaoid unnecessary repetition.
Login to download- 00:04 It's finally time to talk layout tips.
- 00:08 It's not all going to be done in one session.
- 00:10 We are going to have a few sessions on this, but let's get started.
- 00:13 How many times have we seen this slide?
- 00:17 But it's important, make sure you've done your groundwork.
- 00:21 Make sure you know what's going to be in here.
- 00:24 Make sure you know the content and purpose of each sheet.
- 00:28 We're going to talk about the layout of setting it out
- 00:31 in the actual Excel workboard now.
- 00:33 That's what this is about, not about helping you decide what goes where.
- 00:39 We're going to look at creating what I call a template,
- 00:42 in inverted commas, model.
- 00:44 It may not look exactly the same as this,
- 00:46 this is just an idea of how we sometimes make them look.
- 00:49 We're going to do it completely from first principles.
- 00:54 Here's something I didn't prepare earlier.
- 00:57 It's but one, a brand new Excel workbook created by going Ctrl+N and up it pops.
- 01:05 I'm going to show you right from first principles
- 01:08 how I would put together the layout of a financial model.
- 01:12 This isn't about content and purpose,
- 01:14 it's about layout design following the principles of the CRaFT ideology
- 01:18 namely consistency, robustness, Flexibility and transparency.
- 01:24 I'd like to say as well this is how I do it.
- 01:27 You might have a slightly different view.
- 01:29 As long as you adopt those four principles.
- 01:32 If you do it slightly differently but it still fits that's still fine.
- 01:36 I'm just giving you some ideas, you don't have to copy me, just be inspired.
- 01:42 So one of the first things I'm gonna need to do is select some columns.
- 01:45 Now because this is an Excel training course as well,
- 01:47 I thought I'd show you some useful keyboard shortcuts along the way.
- 01:50 I can highlight the columns just by going to A, B, C,
- 01:55 D, E like this and selecting it like that.
- 01:58 But where's the fun in that?
- 02:00 I'm just gonna select a cell at random.
- 02:01 I've selected cell I9.
- 02:04 Keyboard time, if I press the Home button once, it takes me to cell A9,
- 02:08 what that does, is it takes me to the home cell of the row, which is column A.
- 02:13 If I hold the Shift button down and press the right Alt row 4 times,
- 02:17 I get cells A9 to E9 highlighted.
- 02:21 Holding the Ctrl button down and pressing spacebar, selects the columns.
- 02:27 Shift+Spacebar select rows, Ctrl+Spacebar select columns.
- 02:32 Be careful if you've got any merged cells though,
- 02:34 because it will then select multiple columns.
- 02:37 Once I've got the column selected, let's go back with the mouse now.
- 02:39 Right click and go to Column Width.
- 02:42 And I'm going to take it away from the default value
- 02:45 of that exciting 8.43 number and make it a value of 3, and click OK.
- 02:50 Now why have I chosen three?
- 02:53 Well, it's because these cells are approximately square.
- 02:58 Now look, don't get out your micrometers and start measuring with them here.
- 03:01 I did say, approximately square.
- 03:04 And the reason I've actually narrowed these columns will become
- 03:06 apparent as As we go through this example.
- 03:09 Just take me at my word for the time being.
- 03:12 You might not need five columns but I'm just trying to show you the idea, okay?
- 03:16 So what else do I need?
- 03:17 Well cell A1 should be something informative.
- 03:21 And I think there could be nothing more informative than explaining
- 03:24 to the end user what the content and the purpose of this sheet is.
- 03:27 And how do we do that?
- 03:29 With a sheet title, so I'm going to put the sheet title in cell A1.
- 03:36 Ideally, whatever the title is here in cell A1,
- 03:40 it should also be in the sheet tab down here.
- 03:43 So I can rename down here, and call this Sheet Title.
- 03:50 Now wouldn't it be great if there were a formula I could actually use, that
- 03:55 no matter what I change this tab to, the Sheet Title would update automatically.
- 04:00 Wouldn't that be cool?
- 04:02 Well if I make the formula bar a little deeper and get rid of Sheet Title here for
- 04:06 a second.
- 04:08 Abra Kadabra, look at this formula out of nowhere.
- 04:11 Wouldn't it be good if you could create those yourself?
- 04:14 Press enter, and you'll be amazed!
- 04:16 Look at this, press enter, it's blank.
- 04:20 Are you staggered?
- 04:22 Or what?
- 04:24 Because the book is unsaved, at the moment, it doesn't work.
- 04:29 Well by the magic of the product that we're using to record this in,
- 04:34 I have actually saved the file like lightning, and
- 04:38 what's happened is once I save the file this has actually now become sheet title.
- 04:44 It doesn't work when the workbook isn't saved.
- 04:47 But if I now rename this to something else,
- 04:54 I'm nothing if not literal.
- 04:59 Do you see now the tab uses that particular formula.
- 05:04 Now I'm not really going to go through that formula here,
- 05:07 its beyond what we need to know for financial modeling and
- 05:10 it's something that everyone just copies and steals, and you'll have the example
- 05:13 workbook associated with this particular video at your perusal in any case.
- 05:19 Essentially it all falls round the following fact, that if I use a function
- 05:24 called cell in Excel, and I then I say I want the filename,
- 05:27 and then I just pick any cell in Excel at random.
- 05:32 How about DD346, for instance, it doesn't matter what.
- 05:37 It will actually give me the address of the file D.
- 05:41 It's called Liam Example Something Else.
- 05:44 And what I'm trying to then is get rid of the actual workbook name and
- 05:48 just find the tab name, and that's all this function is doing.
- 05:53 Useful tip, gives me an idea of how to move on.
- 05:57 We've got our actual file name in here and we've narrowed the columns and
- 06:00 it's getting us an idea together.
- 06:02 And in the next one we'll look at putting some more of the headings in and
- 06:06 freezing the panes.
Lesson notes are only available for subscribers.