Custom Number Formats

Quick reference

• 00:04 In this video, we're going to look at custom number formats.
• 00:07 Building our own formats for these cells,
• 00:09 to show numbers the way that we actually want to see them.
• 00:13 If we look at the dates on the left here, these are showing in a US format.
• 00:17 And unfortunately, I'd like to show these in a month or a year, month Date format,
• 00:21 but you'll notice that none of the formats that I have pre-built
• 00:24 in my version of Excel show those.
• 00:26 So I need to do this manually.
• 00:28 So to do this, we're gonna right-click on Format cells.
• 00:32 And we're gonna go to the number Tab and
• 00:35 I wannna show you how this stuff sort of looks and how it works.
• 00:38 You'll notice that we've got right now a date, and
• 00:41 if I scroll through my options I don't see a date that goes with year, month, day.
• 00:46 I see something that comes close, maybe this one here.
• 00:49 That gives me some idea.
• 00:50 And now what we're gonna do is, we're gonna select that format that's close, and
• 00:54 go to Custom.
• 00:55 And you can see that it's built like this.
• 00:58 The key piece we wanna recognize here is that these D's and
• 01:02 M's actually mean something.
• 01:03 So I'm gonna actually go and start building my own format.
• 01:06 We're gonna go with YYYY-MM-DD.
• 01:11 These are special characters that are used inside your
• 01:16 text formats inside a custom number format.
• 01:19 Y's are for years, M's are for months, and D's are for days.
• 01:22 And what you will notice is, if we went with M, again we would get to Dec and
• 01:27 M again would give me December.
• 01:29 But in this case I actually wanna go back to 12 Ds,
• 01:33 same would actually give me different variables as well.
• 01:37 So this is how I can build an .ISO compliant date format,
• 01:41 even if I don't have one.
• 01:43 Let's look at product numbers.
• 01:45 We'll go right-click and Format Cells here.
• 01:47 One of the challenge with these product numbers is that they're kinda shrinking
• 01:50 and they're supposed to be six digits long.
• 01:52 So if I go and take a look at some special formats.
• 01:55 I find something that says Zip Code + 4, and look at this, this is interesting.
• 01:59 This has got leading zeros on it.
• 02:00 So I found this.
• 02:02 Let's go look at custom and see how it's built.
• 02:05 It appears to be 000.
• 02:06 So if I wanna try six zeros for
• 02:08 my format, I've got five here right now, let's try one more.
• 02:13 And notice that it gives me a sample from the top right cell that I've selected.
• 02:17 So we've got 0, 1, 2, 3, 4, 5.
• 02:19 Let's try this.
• 02:20 Okay.
• 02:21 And notice that all of my values change so
• 02:24 they now have six digits even though the value in the cell is still showing 6352.
• 02:29 So that's kind of cool.
• 02:31 We can use this kind of knowledge to go and
• 02:33 actually start building some really interesting custom formats.
• 02:37 And one of those that I'm gonna try is on this Variants column.
• 02:41 There is a challenge here is that this has got positive variances and
• 02:44 negative variances, and nobody ever understands if that's good or not.
• 02:48 So what I'd like to do is I'd like to actually show this with a favorable or
• 02:52 an unfavorable.
• 02:53 So an F or a U in order to show whether or not these are good or bad.
• 02:58 So I'm gonna right-click on this and say Format cells.
• 03:01 And I'm gonna go to Custom, and
• 03:03 we're gonna try leveraging some of what we used before here.
• 03:06 So I got 6,000 here.
• 03:08 Now, custom number formats are broken into some different pieces here.
• 03:11 What I'd like to see is, I'd like to see this, where it says 0,000 and
• 03:17 that would give me 6,000 for my 6,000, you can see in the sample.
• 03:23 I'm also gonna hit Space F.
• 03:25 So positive number will show up with an F after it for favorable.
• 03:30 This is a interesting, cool piece about these custom number formats,
• 03:32 is that there's actually four parts to it.
• 03:35 If we put in this semicolon, we get to the next part.
• 03:38 And that part is gonna be 0,000 U, for unfavorable.
• 03:43 And then we can put in a semicolon again, and
• 03:47 "-" will give us a dash for any zero value.
• 03:52 So it's positive negative zero.
• 03:56 And watch what happens when I hit OK.
• 03:59 My numbers now show up as, the positive number show up with F after them for
• 04:04 favorable, my negative numbers show up with U's after them for unfavorable and
• 04:08 that's kinda cool.
• 04:09 The only challenge with this is, what happens if this value ends up at -105?
• 04:15 And you'll notice it now shows up at 0105 that's not really so good.
• 04:21 What I'd really rather see is where these 0s only show up if they're needed.
• 04:25 And we have a special character for that as well.
• 04:28 If we go into format cells we're gonna change this out to go with.
• 04:32 #,##0 and we'll try this again.
• 04:36 #,##0.
• 04:40 This will always give us a 0 placeholder.
• 04:43 But the number sign, as a reserve character,
• 04:45 says put in a number if one exists for that spot.
• 04:48 So that's really kind of helpful.
• 04:51 We can also pick off other number formats as well of course if we go inside.
• 04:54 The real trick behind this is find a format that you like the look of.
• 04:58 We'll say none here, put 0 decimals.
• 05:01 You can go back to custom and see what it's built for you.
• 05:04 Remembering 0, is reserved character,
• 05:06 commas are anything between quotes comes out as a text.
• 05:12 We can also even go and add question marks to indent a little bit from the right.
• 05:17 If I were to go back, right click, say, Format Cells, and
• 05:20 take my dash here and put a couple of question marks after it,
• 05:25 you'll notice that that's gonna actually indent these guys a little
• 05:28 bit from the right-hand side of the cell in the case for my zero format.
• 05:31 So positive semi-colon, negative semi-colon, 0.
• 05:37 Those are the formats that we can use, some of the different characters
• 05:40 that you can find in order to help you build your own formats for cells.

Lesson notes are only available for subscribers.