Locked lesson.
About this lesson
Formatting tips to improve the look and keep the layout of the template consistent.
Exercise files
Download this lesson’s related exercise files.
Layout Tips Part 5.xlsx11.4 KB Layout Tips Part 5 - Solution.xlsx
14 KB
Quick reference
Layout Tips Part 5
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
Formatting Cells
- To format a range of cells you first need to highlight all of the cells. A quick way of doing this is as follows:
- Click anywhere in the range
- Hold CTRL + A
- CTRL + 1 is the shortcut to format cells. This can also be accessed on the Home tab / Number group / select the arrow in the bottom right hand corner of the Number group
- Use Number Format to edit the type of data that is entered.
- Go through each of the tabs in the Format Cells dialog box shown above to format the cells as desired. Ensure for consistency that each sheet is formatted in the same manner
Protecting a Workbook
- Protect Workbook
- Structure of workbook cannot be changed, e.g. hiding / moving / deleting of sheets
- Protect Worksheet
- Locked cells cannot be accessed or modified
- Lock Cells
- Cannot be accessed / modified if worksheet is protected
- To make a copy of the sheet once all of the formatting is complete, a shortcut is as follows:
- Hold CTRL
- Pick up the Sheet Title tab using the mouse
- Continue to hold down the CTRL button whilst you drag the sheet and drop it
- 00:04 In this Layout Tips section, after a little bit of tidying up to start off
- 00:09 with, I want to talk about how you can pretty up the template with formatting.
- 00:16 So after a little bit of tidying up first,
- 00:18 I want to talk about formatting in this layout tips section.
- 00:22 First of all, I want to actually stop these numbers being random and
- 00:27 keep being volatile.
- 00:28 So I'm going to copy and paste specialties as values.
- 00:32 So to copy them, I can go Edit > Copy on the ribbon, or
- 00:36 I can just go Ctrl+C to copy.
- 00:38 Ctrl+V is paste.
- 00:40 Now, a lot of people sometimes ask, why is V paste?
- 00:42 My theory is, on the QWERTY keyboards,
- 00:45 it's because V is next to letter C, and Ctrl+P is print, or
- 00:49 what you do when you're busting for the toilet and can't find one.
- 00:53 Sorry, there are jokes in this, after all.
- 00:56 Anyway, moving back to this, what I'm going to do here is Paste Special.
- 01:01 So I go up here and I can go to Paste Special in here, I can go to values.
- 01:05 Our keyboard shortcut is Alt+E for edit,
- 01:09 S for Paste Special, and Z for values.
- 01:13 There are other ways of doing it too.
- 01:15 This is the one I use cuz I was brought up on Excel 2003, and then press enter.
- 01:20 These are now, just numbers.
- 01:24 I did the same quickly with these.
- 01:26 And with a bit of practice, you can do it as quickly.
- 01:28 Control C, alt enter, bang.
- 01:31 How fast is that?
- 01:32 And here, Ctrl+A.
- 01:34 Selected it all in one go.
- 01:36 Then, I'm going to go Ctrl+C to copy and
- 01:41 then, alt esv enter and done.
- 01:45 I'd say I got these numbers sorted out here.
- 01:48 I noticed these numbers relate to these periods because as I scroll down,
- 01:52 I can always see the dates on here.
- 01:54 If you've go numbers that don't relate to those periods,
- 01:58 let's say you got to constantly want to multiply everything by 50%, whatever.
- 02:03 You may put it in these columns here, which is why I have these spare columns.
- 02:08 And you can put various stuff in here.
- 02:10 You need more columns, put more columns in, but do it on every sheet.
- 02:15 Be consistent, and don't hide them.
- 02:18 If you got so many, you can just take these and, if necessary,
- 02:21 just narrow the columns like that to where they're not used on the worksheets.
- 02:25 It's okay, you've still got a consistent layout.
- 02:29 People will get it.
- 02:32 Now, before I actually format as well,
- 02:34 I'm just going to actually make a copy of this shape.
- 02:37 Now, how people normally do that is right click on the top and
- 02:41 go move all copy, and then just click on create a copy, and move to end.
- 02:46 I'm falling asleep here.
- 02:48 Much faster way.
- 02:50 A mix of both keyboard and mouse.
- 02:52 Hold the control button down.
- 02:55 With the mouse button, pick this up with the left mouse button like this,
- 03:01 sort of like that, then keep the control button held and let go of the mouse.
- 03:07 Tada, we have a copy Of the sheet, look at that.
- 03:12 And notice that's called Sheet Title (2) cuz my formula's working, magnific!
- 03:17 So let's look at formatting now.
- 03:20 Formatting cells, as I said in an earlier session,
- 03:22 Control + 1's the keyboard shortcut.
- 03:25 So I'm gonna highlight all of these here and go Control + 1.
- 03:29 Format Cells, and it brings up the Format Cells dialogue box.
- 03:34 There are six properties here.
- 03:37 Now I'm going to go for Number.
- 03:39 We're gonna talk about the Swahili down here in another session, but
- 03:43 I can actually look at different ways of formatting it should I wish.
- 03:48 I will have the number to zero decimal places with a comma.
- 03:52 The alignment,
- 03:53 I'm just going to have it that if it gets too big I want it to shrink to fit.
- 03:59 The font, let's go for bold to italic bright red, looks wonderful.
- 04:05 Border I just have an outline border.
- 04:07 You can select from here: fill.
- 04:10 Well, typically these are kind of a yellow-y color, and
- 04:14 the protection, well, if this is an input cell it needs unlocking.
- 04:19 That means, if I protect this sheet, I can still edit it.
- 04:22 Watch click, okay.
- 04:23 Look at that.
- 04:26 And I've got a border all around it.
- 04:28 If you don't like the border, if you think, no I don't like it,
- 04:31 well you can just go straight back into it.
- 04:32 Ctrl+1, format cells, think I'll play with the border.
- 04:35 I want the inside as well.
- 04:38 Yep that looks better if you think so.
- 04:40 You might think, I don't like the red, don't like the colors, okay.
- 04:43 It's my show, I'm running it.
- 04:45 Now, I've unprotected this, which means if I were to protect the sheet, so I
- 04:51 can protect the sheet by going to Format, and then I go to Protect Sheet down here.
- 04:57 Or the keyboard shortcut, I always think of Native American toilet.
- 05:03 Alt TPP, sorry the jokes are getting worse here.
- 05:08 Either way, it'll allow you to actually protect various things and
- 05:11 put a password in.
- 05:12 I never bother putting a password on because you're always looking for
- 05:17 the password later and it's not secure anyway.
- 05:20 It's usually pretty easy to crack.
- 05:23 So doing that, I protected it.
- 05:24 If I try and type in here now, it won't let me.
- 05:29 But if I try and type in here, it will.
- 05:32 That's side you have unlocking the cells.
- 05:34 Now to unprotect the sheet, because I'm still designing it,
- 05:38 it's just alt TPP again, and now I can type anywhere.
- 05:41 That's formating in a nutshell, nice and quick.
Lesson notes are only available for subscribers.