Locked lesson.
About this lesson
Layout tips for updating workbook names, adding hyperlinks, dates and freezing panes.
Exercise files
Download this lesson’s related exercise files.
Layout Tips Part 2.xlsx9.7 KB Layout Tips Part 2 - Solution.xlsx
10 KB
Quick reference
Layout Tips Part 2
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 understand and navigate through.
Instructions
Layout Tips
- Enter the Workbook Title in cell A2 using the formula shown below. The workbook is required to be saved for this formula to work. If the workbook name is updated, then the titles in the workbook will automatically update
=IFERROR(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),"")
- A hyperlink can be inserted into cell A3 which can link back to another sheet i.e. ‘Go To Table of Contents’.
- An Error Check box can be inserted into cell F4 – this check box is a formula and will be discussed later
- Ensure the dates are shown consistently i.e. monthly, quarterly, yearly etc. Ensure they are shown in the same columns on each sheet
- Freezing Panes are useful when the worksheet has a heading and data that is not visible on the screen
- Freeze panes will freeze what is above and left of the selected cell
- The black lines indicate where the window has been locked
Login to download
- 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.