Layout Tips Part 5

Quick reference

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