Custom Number Formats

Quick reference

• 00:04 In this video we're going to look at custom number formats.
• 00:07 Now you're used to working with number formats already.
• 00:10 We have values in all these cells and they look a little bit different.
• 00:13 Normally we'd control those probably through the settings up on the top here.
• 00:17 But in this case,
• 00:18 we've got the inventory items for a bike store and some product numbers.
• 00:22 The way that the bike store would like to see these is as product numbers that
• 00:25 are six digits long with leading zeroes.
• 00:27 The problem is, when you go and
• 00:29 put a leading zero in front of a number in Excel, if it's formatted purely as
• 00:32 a number, when you hit Enter Excel just kind of removes the leading zeros and
• 00:36 they don't show up anymore at all, which is not good.
• 00:39 So let's go and fix that little issue.
• 00:42 I'm gonna right-click, and I'm gonna say Format Cells.
• 00:45 And I'm gonna go into the Format Cells dialogue box.
• 00:48 If I click on Number you'll see that our sample here
• 00:51 doesn't give us our leading zeros.
• 00:53 It's working from the original cell that I selected here.
• 00:56 Let me show you a neat little trick here though.
• 00:57 If you go to Special and we get our language back into Canadian English,
• 01:04 we can see Social Insurance Number.
• 01:07 Notice that the Social Insurance Number has leading 0s and
• 01:10 there's the 1, 2, 3, 4, 5.
• 01:12 If I select on this, this is close to what I want.
• 01:16 I then go to Custom.
• 01:17 You'll see that it actually gives me the numeric masks that's being used to
• 01:21 give me the Social Insurance Number.
• 01:23 And from this part, I can look at this and say well,
• 01:25 this is interesting because every zero puts in a zero if there's no value in that
• 01:30 particular numeric place holder or it puts in the value if there is.
• 01:34 So what would happen if I highlighted this entire thing and
• 01:37 I just typed in six zeros?
• 01:41 It will actually give me.
• 01:43 Five characters plus a 0 padding it.
• 01:45 Let's give this a shot.
• 01:47 And there we go, so we've now got leading 0s in place.
• 01:51 You can also go and take a look,
• 01:53 this is kinda the coolest little trick in the way that you can sort of deal with all
• 01:57 these things if your language defaults back to something useful.
• 02:01 Basically, the key that you're looking for here is,
• 02:04 if you wanted to get into Dates for example, you could select a date.
• 02:07 And if we grab this guy here and we went to Custom,
• 02:10 it inherits the numeric mask that you're looking for.
• 02:13 So you now know that dd will give you a two-digit date.
• 02:16 If you go back over to this again and we say, all right, let's look at Saturday,
• 02:21 March 14th.
• 02:22 We can see that dddd will give me the full day.
• 02:26 So this is how you sort of read these little masks.
• 02:28 And you can start to put the characters together to work into the format that you
• 02:32 actually want.
• 02:33 I'm gonna show you another little trick here as well to help display variances
• 02:37 a little bit better.
• 02:38 This is something that a lot of people have challenges understanding or
• 02:42 reading when they're looking at these statements.
• 02:44 The idea is that we're trying to figure out if our actual revenues are larger
• 02:48 than budget this means that is a favorable number, it's positive.
• 02:52 If it's negative, it's unfavorable.
• 02:53 But the challenge is, is that depending on how the accountant has done the math on
• 02:56 it, sometimes negatives can be good and sometimes negatives can be bad.
• 03:00 So, rather than leave people scratching their heads,
• 03:02 I'm gonna show you a little trick here that we can actually apply to our
• 03:05 statements where your user never has to work that out again.
• 03:08 And it's by putting an F for favorable versus a U for
• 03:12 unfavorable after the number itself.
• 03:14 So, to do this, we're gonna right-click and say Format Cells.
• 03:18 And we're gonna go in and we're gonna write our own custom number format.
• 03:22 And the way that I'm gonna start doing this is I'm gonna say well,
• 03:26 I need 6,000 so this must be 0, 0, 0, 0 F, for favorable.
• 03:31 F is not a reserved character, so it'll show up as an F.
• 03:35 Next we put in a semicolon.
• 03:37 Now everything before the semicolon is the positive number format.
• 03:41 So this will deal with what happens if it's negative.
• 03:43 So we'll go, 0,000 U.
• 03:48 And then we put in another comma.
• 03:50 And it says, what would you like to have happen if the number is actually worth 0?
• 03:54 So I'll say, '-', everything between the quotes will be returned as text.
• 04:00 Okay, so that's the secret there.
• 04:01 There's one more semicolon that we can use,
• 04:04 we don't have to, but if we put a final semicolon there,
• 04:08 this is what will be returned if the value in the cell is text.
• 04:11 So, I put it between two quotes and said text.
• 04:15 When I say OK,
• 04:16 you'll notice that my positive numbers have little F's after them.
• 04:20 My negative ones have U's after them, which is great.
• 04:23 But what happens if suddenly this drops down to let's say, 270,710?
• 04:30 Well, you'll notice that I've now got 0, and I don't really want that.
• 04:37 So I need to modify this number format, I don't want a 0 there.
• 04:41 What I need to do is I actually don't want to use 0s.
• 04:44 What 0s do is they put in a 0 whether or not there's a value for that placeholder.
• 04:49 The #, on the other hand, will actually put in a number
• 04:55 if one exists for that placeholder, otherwise it just actually ignores it.
• 05:00 So at this point, you'll see that it gives me a much better output.

Lesson notes are only available for subscribers.