Number Formatting Part 4

Quick reference

• 00:04 More on Number Formatting.
• 00:07 To recap then, I showed you in the last session how to make positive
• 00:12 numbers blue, negative numbers red, in brackets and line up.
• 00:17 I said this time out I was going to differentiate between a number that's
• 00:21 approximately 0, and one that is precisely 0, and that's what I'm going to do.
• 00:27 So let's highlight my four numbers again, and then I'm going to go Ctrl + 1
• 00:32 to format cells, and I'm going to put a second semicolon in.
• 00:36 Now instead of it being non-negative and negative,
• 00:40 it now reverts to positive, negative, and 0.
• 00:45 So positive numbers will be blue and be aligned with brackets,
• 00:50 negative numbers will be in brackets in red, and I'm going to make 0, Green.
• 00:57 Now, because a dash is text, I should put it speech
• 01:02 marks like that, now, that's how you put text in,
• 01:07 but I want to show you another way, \-.
• 01:11 When you've just got one character to put in, the backslash is saying I've
• 01:16 got one text character to put in and then the next character is treated as text.
• 01:22 This is because the fact, sometimes you might want to show in number formatting,
• 01:26 for some crazy reason, speech marks, how would you do it otherwise?
• 01:30 And you'd have to go backslash, speech marks,
• 01:34 if you wanted to show a backslash then, you'd have to go backslash backslash or
• 01:38 open speech marks, backslash, close speech marks, confusing or what?
• 01:44 If I now do that and click OK, it would actually not be aligned,
• 01:50 so I've got to make sure that before the backslash,
• 01:54 I put in another underscore open round bracket and
• 01:57 afterwards another underscore, closed round brackets, and click OK.
• 02:02 Do you see now that is a green dash that lines up?
• 02:06 Last thing I want to do is change the color of dog, just to show you,
• 02:12 you can do that with number formatting, so again Control 1,
• 02:17 I put a fourth section in but
• 02:21 a third semicolon, and now it goes positive, negative zero.
• 02:26 And text, and I want to make the text yellow,
• 02:32 you'll have a rather pale doc, click OK, you can hardly see it and
• 02:38 I've done that deliberately, I've made my dog very yellow, it's a scared dog.
• 02:44 What this shows is that number formatting trumps text formatting for
• 02:49 text, so be careful in Excel, number formatting wins,
• 02:54 now there's lots of other things you can do in here as well.
• 02:58 I haven't got time to show them all but one that might be useful for instance,
• 03:02 is if you have the number 44 in here and you are writing a check, maybe
• 03:08 you want a whole lot of dashes before that so that people couldn't fill it in.
• 03:13 A way you can do that is go to Control 1, go back to Custom, and
• 03:18 you type in here the asterisk symbol, and
• 03:23 then I'm going to put a dash, and then I'm gonna put a zero, and click OK.
• 03:30 Now that asterisk says, make the whole cell fill up with whatever the next
• 03:35 symbol is, so if I show that again, it's going to fill it up with these dashes.
• 03:40 You can use another character instead, and
• 03:42 it doesn't matter how wide I make this, it's there.
• 03:47 Now, you could go and modify this and put a number four amongst the list,
• 03:50 I have a huge check to payout but
• 03:53 you get the idea of the sorts of things you can do with number formatting.
• 03:57 I do strongly recommend that you look at Number Formatting and
• 04:02 don't get too scared about what appears to be Swahili in here, it's not
• 04:07 half as complicated as it first looks, and it comes with practice, have a play.

Lesson notes are only available for subscribers.