Layout Tips Part 2

Quick reference

• 00:04 In the second part of layout tips,
• 00:06 I'm going to continue talking about my template on constructing.
• 00:11 Let's get on with Excel.
• 00:16 Here's a continuation from last time.
• 00:19 You may recall at the end of the last session I finished with this wonderful
• 00:23 formula for Sheet Title.
• 00:25 What I've done in the meantime, is I've sneakily put another one in, another
• 00:30 formula you don't really need to follow, other than the idea of a cell file name.
• 00:33 And these are things that you will steal time and
• 00:35 time again, that finds the workbook name.
• 00:38 So if I change the workbook name, it will automatically update.
• 00:42 These are things you just put in.
• 00:44 I don't pretend that we actually need to know and understand the mechanics of
• 00:47 every single formula, it doesn't follow the rule of thumb.
• 00:50 But the fact is, everyone understands it, and nobody's looking at that
• 00:54 to understand the formula, because no one will even suspect it's a formula.
• 00:58 So don't worry about it.
• 01:00 Keep it simple stupid for the things everyone's interested in.
• 01:03 We're trying to create a nice simple template.
• 01:07 In cell A3, I would create a hyperlink.
• 01:10 Now, I'm gonna talk about how to prepare hyperlinks later in the course, so for
• 01:15 now I'm going to make a pretend one.
• 01:16 I like says Table of Contents.
• 01:21 And what I'm going to do, is I'm gonna highlight these cells like this and
• 01:25 I'm going to go up here and I'm going to go merge across.
• 01:29 And that becomes one big cell.
• 01:31 Now that's not great if I'm going to set this column.
• 01:34 So if I'm on column C here, I got to Control spacebar, you see,
• 01:38 it highlights columns A,
• 01:40 B, C, D, and E because I've got this whopping big merged cell here in row 3.
• 01:45 Be careful with merged cells, they do cause problems, but
• 01:48 it's useful when you're creating a hyperlink because it means you can
• 01:51 click anywhere inside that cell and it will work.
• 01:54 If I hadn't merged it, that cell's only an example.
• 01:57 Do you see if I clicked here, that's a different cell, it wouldn't work.
• 02:00 It's just one of those crafty tricks you have to employ in Excel that
• 02:05 basically separates professionals from amateurs.
• 02:08 Not that I'm trying to get my status to keep going to the Olympics next
• 02:11 time anyway.
• 02:13 So table of contents, and so that would look, I'm going to make it blue and
• 02:16 underline it.
• 02:18 Just because it will make it look like a hyperlink, even if it's not.
• 02:22 And then another thing I'll put in here, which we'll talk about later as well,
• 02:25 is error check summary And we might have a little box here that says, OK.
• 02:32 Now that is not an error check.
• 02:34 If I just quick put a box around that one here at the moment.
• 02:38 And let's color it a nice little green to make it look like its okay.
• 02:42 Looks wonderful.
• 02:43 But that should be a formula.
• 02:46 You can't just have a cell that says OK.
• 02:48 It deceives people, but we will be talking about how to create that formula.
• 02:52 And how it fits in with the rest of the model later.
• 02:56 But I'm just trying to set up the layout for now.
• 03:00 Other things we'll have is the date.
• 03:02 The date needs to be put in here as well.
• 03:05 Now, I'm gonna start the dates in Column J.
• 03:08 So, I'm going to have Date one here.
• 03:11 And just because we don't need thousands of these things,
• 03:13 I'm only gonna go to column N.
• 03:15 Make it bold and center it.
• 03:17 And I've got my dates in here.
• 03:21 Now, what should happen for
• 03:23 consistency is that these dates should be of equal duration.
• 03:27 Should be monthly, quarterly, half yearly, annually.
• 03:31 Now the more pedantic of you will say,
• 03:33 well hang on a minute, Liam, some of the months got a different number of days.
• 03:37 The answer to that is, you know what I mean per the plugs.
• 03:41 The point is, we want to have a consistent periodicity.
• 03:45 Another thing we want is the date to always start in column J and
• 03:49 end in column N on this sheet.
• 03:50 They should do that on any other sheet.
• 03:53 So if we're linking from one sheet to another like we do with imports,
• 03:56 calculations, and outputs as we separate them as I was explaining earlier.
• 04:01 You want it so it's an easy link that you can see you're linking to the right
• 04:04 cell from another sheet.
• 04:06 The best way to do that is have everything starting and ending in the same columns.
• 04:11 Now, I want to talk about frozen panes.
• 04:15 Freezing panes is a useful alternative to hiding rows or
• 04:19 columns because people can see what you've hidden.
• 04:23 And it's accessed by going to the View tab on the ribbon and
• 04:26 then selecting the drop-down menu Freeze Panes, the first selection, Freeze Panes.
• 04:31 Freeze Top Row and Freeze First Column,
• 04:33 which the other two options on there, they're not quite so popular.
• 04:37 And you'll find that you can do mostly what you want to do just doing that
• 04:40 first selection.
• 04:42 Essentially, it has the following effect.
• 04:45 Imagine you've selected cell D2 as in this image.
• 04:48 You'll find that if you freeze panes on cell D2,
• 04:51 then the row above and the column to the left are the frozen columns.
• 04:57 So, as you scroll across, columns D, E, F will go out of view.
• 05:02 I've got column V showing straight after column C.
• 05:04 If I scroll down, there's 2, 3, 4 will go out view and it'll keep row 1,
• 05:09 you'll see row 12 next to it.
• 05:11 Let's take a look with an Excel example.
• 05:14 Back in Excel then, let's see how that works.
• 05:17 What I'm going to do is I'm going to select cell A5.
• 05:20 That will freeze everything to the left of column A.
• 05:23 IE, there'll be nothing frozen column wise,
• 05:26 but it will also freeze the first four rows.
• 05:29 And I go to the View Tab on the ribbon, Freeze Panes, and then Freeze Panes.
• 05:35 And that's it, I've now got my frozen panes.
• 05:38 As I scroll down, I have all that above me still sorted.
• 05:42 And this gives me, then, the dates, and the table contents access,
• 05:46 when I need them.
• 05:48 If I'm actually gonna go and scroll off screen, if I have more dates,
• 05:51 I might want to anchor some of the columns too.
• 05:53 But not on this example as I'm showing you.
• 05:55 Let's now look in the next section about how we populate this down here.

Lesson notes are only available for subscribers.