Custom Number Formats

Quick reference

• 00:04 In Excel, you think that working with numbers and formatting would be easy.
• 00:08 And in some case, it is.
• 00:10 Our actual and budget sales here, we can just go an hit them with comma style,
• 00:13 decrease the decimals a little bit, and everything is good.
• 00:16 But what about working with things like this?
• 00:20 Dates.
• 00:21 Now, I live in Canada, and one of the challenges we have,
• 00:23 is that we get a lot of data from the US.
• 00:27 The data here is in a month, day,
• 00:28 year format, which is synonymous with the US date format.
• 00:31 But Canada's official date format is actually day, month,
• 00:34 year, so when we look at stuff, we have to try and
• 00:37 figure it out right away as to which it is, and that's a real challenge.
• 00:41 So I'd like to actually format this to look a little bit easier to read, and
• 00:44 to do that, I'm gonna go to format cells.
• 00:49 Now, in format cells, you'll notice that on the number tab, we
• 00:51 actually have a lot of different versions of dates that we can play around with, and
• 00:55 the key thing I want you to recognize is from my selection.
• 00:58 One of the cells, the white one here is the active cell.
• 01:01 And that is the sample that is being used to display the date format here.
• 01:06 Now, I can pick some different ones,
• 01:07 I could see what it would actually look like for my date.
• 01:11 I'm gonna find one here, this is the ISO format,
• 01:14 which might get me part way to what I want.
• 01:16 But then, I'm gonna try and tweak it.
• 01:18 So I'm gonna go to custom, whatever date format I'd selected is now pulled across
• 01:23 into this type with this funny-looking formatting string,
• 01:26 now, I'll tell you here.
• 01:27 We don't need the last piece.
• 01:29 I'm just gonna delete that, but what you can see now,
• 01:31 is that I can actually look at this and play around with it.
• 01:34 It appears that the yyyy is for the year, mm is for the month.
• 01:38 What happens if I add another m?
• 01:40 Well, I get Dec.
• 01:42 And another one, I'll get the full December.
• 01:44 Now, to make this less ambiguous, I think I'm gonna go with Dec.
• 01:49 But the other piece that I might wanna do,
• 01:50 is maybe I wanna see if I can actually get my day names, as well.
• 01:54 With D, an extra D there, I'll get Sun, and Sunday with four.
• 01:59 But that doesn't really make a lot of sense, cuz I do need the day number.
• 02:02 So let's put in a parenthesis, ddd.
• 02:04 And now, I can see that it's December 9th, which is a Sunday.
• 02:08 So that's pretty cool.
• 02:09 I can now say OK.
• 02:11 And just like that,
• 02:11 all of my dates are formatted with that particular format that I created custom.
• 02:16 What about my product codes?
• 02:18 When I buy from my suppliers, my product codes actually have leading zeros on them,
• 02:22 they're six digits long.
• 02:23 So I should be seeing 012345, and 006352.
• 02:29 Well, we can use a custom number format to do that, too.
• 02:32 So let's go right click, Format Cells.
• 02:35 Now, let's poke around under special.
• 02:38 There's all kinds of different formats you can play around with here.
• 02:41 But notice that special has a few interesting ones.
• 02:43 Zip code, which doesn't do anything different to my data.
• 02:47 I've got 12345, that's what shows here.
• 02:50 Phone number, puts in a dash that's a little strange.
• 02:54 Social security number has some zeros leading zeros.
• 02:57 That's kind of interesting.
• 02:58 And Zip Code + 4 also has leading zeros.
• 03:01 Now, I can still see my 12345, but those leading zeros do intrigue me.
• 03:06 So I'm gonna go to custom.
• 03:09 And notice that it's brought the number from over 0000.
• 03:11 So it appears what's happening here, is that everywhere that there is a value,
• 03:15 it actually uses that value, and everywhere that there isn't,
• 03:19 it pads with leading zeros.
• 03:20 So what if we just cut this down?
• 03:22 We're gonna get rid of these ones in the middle.
• 03:24 And we now have six zeros, and
• 03:26 it looks promising, 012345, does that really show in Excel?
• 03:31 And we say, OK.
• 03:31 It sure does.
• 03:34 Even though the value in the cell is still truly 6352, it now displays leading zeros.
• 03:40 So that's pretty cool.
• 03:43 Now, the last one I wanna focus on is this variance column.
• 03:46 And the variance column is a very specific one that's near and dear to me, because I
• 03:50 really hate it when I'm watching somebody as they try to read the statement and
• 03:53 figure out, I'm working on revenues, or I'm working on expenses.
• 03:57 In this case, this looks like revenues.
• 03:59 So it's actual bigger than budget.
• 04:01 Yes, that's good.
• 04:01 So positive numbers are good.
• 04:03 Actual is lower than budget, so that must be bad.
• 04:05 So negative numbers, in this case, are bad.
• 04:07 But if we're working with expenses, it flips around the other way.
• 04:10 I hate seeing that mental math, so I wanna apply a little cost accounting trick to
• 04:14 this to actually show positive numbers here as, favorable,
• 04:19 by having an F after them, and negative numbers as unfavorable by having a U.
• 04:24 And that way, nobody ever really has to worry about it anymore.
• 04:27 So let's do that with a custom number format.
• 04:29 We'll go to Format Cells, and we'll start by picking a number format that's close
• 04:34 enough to what we want, so I want thousand separators, and no decimal places.
• 04:40 Now, at this point, close enough, let's go to custom, and
• 04:43 we can see that the number format is #,##0.
• 04:46 That returns 6,000.
• 04:50 Interesting, the hash marks,
• 04:52 are actually somewhat different than using zeros for this.
• 04:55 Well, let's see now.
• 04:56 We're gonna go space F, to put an F after it.
• 04:59 Now, if you try to use a reserve character,
• 05:01 you may have to wrap this in quotes, but F is not reserve for anything.
• 05:05 Here's the real seeker with the custom number format, semicolon.
• 05:08 The first format before the semicolon is positive.
• 05:11 This one come a ##0 U, will be applied only to negative numbers.
• 05:16 Another semi colon, and now, we could into the third part,
• 05:20 which is applied only if a value is zero, and now, check out the difference when I
• 05:25 apply this, compared to using zeros for my string.
• 05:31 If I use zeros, these things would have leading zeros, but
• 05:33 they don't, the 0 is suppressed.
• 05:36 And now, if I were to turn around, and say, okay, that looks good, favorable and
• 05:41 unfavorable instead of positive and negative, and if I change this value now
• 05:45 to be 20, just like what we actually have in the other cell there,
• 05:49 I get my zeros showing up just the way I want.
• 05:52 So that's where we can take ultimate control of how our numbers actually show.

Lesson notes are only available for subscribers.