Layout Tips Part 1

Quick reference

• 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.